sqlGirl

sql难不难?

给了订单表和用户点击表:(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
sql难不难?已关闭评论