【HQL】HQL实现每日订单量和未填地址订单量以及未填地址订单量N日后的变化

原始表(order_tbl),按dt分区,每天分区保存全量订单

(20200321分区)

order_id

address

trade_time

dt

1

天津

20200320

20200321

2

北京

20200320

20200321

3

北京

20200319

20200321

4

天津

20200319

20200321

5

北京

20200321

20200321

6


20200321

20200321


(20200320分区)

order_id

address

trade_time

dt

1


20200320

20200320

2

北京

20200320

20200320

3

北京

20200319

20200320

4


20200319

20200320


使用SQL实现,每日订单量,当日未填地址订单数,1日后未填地址数量,2日后未填地址数量,3日后未填地址数量



解释: 【1日后未填地址数量】,指某日的订单中,下一天未填地址订单的量

例如:trade_time=20200320,未填地址订单1个,【1日后未填地址数量】要统计分区dt=20200321下trade_time=20200320在的未填地址订单0个



结果表


trade_time

order_cnt

no_address_cnt

1_day_later

2_day_later

3_day_later

4_day_later

5_day_later

20200323

300

50

0

0

0

0

0

20200322

400

80

50

0

0

0

0

20200321

333

55

41

32

0

0

0

……








20200312










with order_tbl as 
(
-- 20200320分区
select 1  as order_id, '天津' as address, 20200319 as trade_time, 20200320 as dt union all 
select 2  as order_id, null  as address, 20200319 as trade_time, 20200320 as dt union all 
select 3  as order_id, '北京' as address, 20200320 as trade_time, 20200320 as dt union all 
select 4  as order_id, '北京' as address, 20200320 as trade_time, 20200320 as dt union all 
select 5  as order_id, null  as address, 20200320 as trade_time, 20200320 as dt union all 
select 6  as order_id, null  as address, 20200320 as trade_time, 20200320 as dt union all 
select 7  as order_id, null  as address, 20200320 as trade_time, 20200320 as dt union all
-- 20200321分区
select 1  as order_id, '天津' as address, 20200319 as trade_time, 20200321 as dt union all 
select 2  as order_id, '天津'  as address, 20200319 as trade_time, 20200321 as dt union all 
select 3  as order_id, '北京' as address, 20200320 as trade_time, 20200321 as dt union all 
select 4  as order_id, '北京' as address, 20200320 as trade_time, 20200321 as dt union all 
select 5  as order_id, '北京'  as address, 20200320 as trade_time, 20200321 as dt union all 
select 6  as order_id, null  as address, 20200320 as trade_time, 20200321 as dt union all 
select 7  as order_id, null  as address, 20200320 as trade_time, 20200321 as dt union all
select 8  as order_id, '北京' as address, 20200321 as trade_time, 20200321 as dt union all 
select 9  as order_id, null  as address, 20200321 as trade_time, 20200321 as dt union all 
select 10 as order_id, null  as address, 20200321 as trade_time, 20200321 as dt union all 
-- 20200322分区
select 1  as order_id, '天津' as address, 20200319 as trade_time, 20200322 as dt union all 
select 2  as order_id, '天津'  as address, 20200319 as trade_time, 20200322 as dt union all 
select 3  as order_id, '北京' as address, 20200320 as trade_time, 20200322 as dt union all 
select 4  as order_id, '北京' as address, 20200320 as trade_time, 20200322 as dt union all 
select 5  as order_id, '北京'  as address, 20200320 as trade_time, 20200322 as dt union all 
select 6  as order_id, '北京'  as address, 20200320 as trade_time, 20200322 as dt union all 
select 7  as order_id, null  as address, 20200320 as trade_time, 20200322 as dt union all
select 8  as order_id, '北京'  as address, 20200321 as trade_time, 20200322 as dt union all 
select 9  as order_id, null  as address, 20200321 as trade_time, 20200322 as dt union all 
select 10 as order_id, null  as address, 20200321 as trade_time, 20200322 as dt union all 
select 11 as order_id, '北京'  as address, 20200322 as trade_time, 20200322 as dt union all 
select 12 as order_id, null   as address, 20200322 as trade_time, 20200322 as dt union all 
select 13 as order_id, null   as address, 20200322 as trade_time, 20200322 as dt 
)
select trade_time, order_cnt, 1_day_later, 2_day_later, 3_day_later from 
(
    select 
        trade_time, 
        order_cnt, 
        no_address_cnt,
        LEAD(no_address_cnt, 1, 0 ) over (partition by trade_time order by dt asc) as 1_day_later,
        LEAD(no_address_cnt, 2, 0 ) over (partition by trade_time order by dt asc) as 2_day_later,
        LEAD(no_address_cnt, 3, 0 ) over (partition by trade_time order by dt asc) as 3_day_later,
        row_number () over(partition by trade_time ORDER BY dt) as rn
    from 
    (
        select dt, trade_time, count(1) as order_cnt, sum(case when address is null then 1 else 0 end) as no_address_cnt from order_tbl group by dt, trade_time
    ) a
) b
where rn = 1
order by trade_time desc


上一篇:易盛内外通用版交易API转websocket


下一篇:项目中的支付宝SDK下载打入本地仓库