原始表（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