给了订单表和用户点击表:(1)每天支付用户数和人均支付金额(2)每笔订单支付前最近一次点击订单商品时间
create table if not exists idata_tmp.order_chenrui
(
ds string,
user_id string
)
comment '订单表'
row format delimited
fields terminated by '\t';
insert into idata_tmp.order_chenrui values('1','a');
insert into idata_tmp.order_chenrui values('1','b');
insert into idata_tmp.order_chenrui values('1','c');
insert into idata_tmp.order_chenrui values('2','a');
insert into idata_tmp.order_chenrui values('2','b');
create table if not exists idata_tmp.click_chenrui
(
ds string,
user_id string
)
comment '点击表'
row format delimited
fields terminated by '\t';
insert into idata_tmp.click_chenrui values('1','a');
insert into idata_tmp.click_chenrui values('2','a');
insert into idata_tmp.click_chenrui values('1','b');
insert into idata_tmp.click_chenrui values('2','b');
insert into idata_tmp.click_chenrui values('1','c');
insert into idata_tmp.click_chenrui values('2','c');
insert into idata_tmp.click_chenrui values('3','c');
select
ds, user_id, ds2
from
(
select
a.ds, a.user_id, b.ds as ds2,
row_number() over(partition by a.ds, a.user_id order by b.ds) as rn
from
(
select
ds ,user_id
from
idata_tmp.order_chenrui
) a
join
(
select
ds ,user_id
from
idata_tmp.click_chenrui
) b
on
a.user_id = b.user_id
where
a.ds >= b.ds
order by
a.ds
) a
where
rn = 1