查找库存差异的开始日期和结束日期

wqlqzqxt  于 2021-06-24  发布在  Hive
关注(0)|答案(4)|浏览(331)

请建议使用好的sql查询来查找库存差异的开始日期和结束日期
想象一下,我把数据放在下面的表格里。
样本表

transaction_date  stock
2018-12-01          10
2018-12-02          10
2018-12-03          20
2018-12-04          20
2018-12-05          20
2018-12-06          20
2018-12-07          20
2018-12-08          10
2018-12-09          10
2018-12-10          30

预期结果应为

Start_date     end_date      stock
2018-12-01     2018-12-02     10
2018-12-03     2018-12-07     20
2018-12-08     2018-12-09     10
2018-12-10     null           30
rn0zuynd

rn0zuynd1#

这就是缺口和孤岛问题。您可以使用row\u numer和group by进行此操作。

select  t.stock, min(transaction_date), max(transaction_date)
from (
   select row_number() over (order by transaction_date) -
      row_number() over (partition by stock order by transaction_date) grp,
      transaction_date,
      stock
   from data
) t
group by t.grp, t.stock

在下面的dbfiddle演示中,我还解决了 null 最后一个组的值,但查找连续行的主要思想是建立在上述查询的基础上。
你可以检查这个来解释这个解决方案。

uurity8g

uurity8g2#

尝试使用 GROUP BYMIN 以及 MAX :

SELECT
  stock,
  MIN(transaction_date) Start_date,
  CASE WHEN COUNT(*)>1 THEN MAX(transaction_date) END end_date      
FROM Sample_table
GROUP BY stock
ORDER BY stock
r1wp621o

r1wp621o3#

您可以尝试使用lead、lag函数,如下所示:

select currentStockDate as startDate, 
LEAD(currentStockDate,1) as EndDate,
currentStock
from
(select *
from
(select 
LAG(transaction_date,1) over(order by transaction_date) as prevStockDate,
 transaction_date as CurrentstockDate,
LAG(stock,1) over(order by transaction_date) as prevStock,
stock as currentStock
from sample_table) as t
where (prevStock <> currentStock) or (prevStock is null)
) as t2
m1m5dgzv

m1m5dgzv4#

你可以在下面使用 row_number() ```
select stock,min(transaction_date) as start_date,
case when min(transaction_date)=max(transaction_date) then null else max(transaction_date) end as end_date
from
(
select *,row_number() over(order by transaction_date)-
row_number() over(partition by stock order by transaction_date) as rn
from t1
)A group by stock,rn

相关问题