1.得出2015年每个用户前2次订单,并得出每个订单购买的商品号列表
select *
from (
select
cust_id,parent_id,concat_ws('#', collect_set(product_id)),row_number() over (partition by cust_id order by order_creation_date) as paiming
from dwfact.order_send_detail orders
where orders.data_date >= '2015-01-01' and data_date < '2016-01-01'
and cust_id = '9680935'
group by cust_id,parent_id,order_creation_date
) b
where paiming < 3 ;
2.得到2015年6月1日的前30天、前60天、前90天的金额汇总
select
data_date, sum(moneys) over(order by data_date rows between 30 preceding and CURRENT ROW)
from
(
select data_date,sum(bargin_price*allot_quantity) as moneys
from
dwfact.order_send_detail orders
where data_date >= '2015-01-01' and data_date < '2015-05-01'
group by data_date
) a;
如果需求是每一天的前60天这个还可以。
建议使用下面的:
select
sum(case when datediff('20