一、方法1:with
2017年月度 各店铺 单客户平均消费(店铺消费/店铺unique客户数量)
环比:每个月和上一个月比
withtabas(selectdate,store_id,avgCusPrice-lastAvgPrice priceDifffrom(selectdate,store_id,avgCusPrice,
lag(avgCusPrice,1,0) over(partition by store_id order bydate) lastAvgPricefrom(select totalprice/cusCount avgCusPrice,date,store_idfrom(select count(distinct customer_name) cusCount,sum(price) totalprice,date,store_idfrom(selectcustomer_name,price
,from_unixtime(unix_timestamp(date,'yyyy-MM-dd'),'yyyy-MM') date,store_idfromtransaction_details
)Tgroup bydate,store_id
)T
)T
)T
)selectt4.store_id,
t4.priceDiffasdiff4,
t5.priceDiffasdiff5,
t6.priceDiffasdiff6from(selectstore_id,priceDifffromtabwheredate= "2017-04"
)t4inner join tab t5 on t4.store_id=t5.store_id and t5.date=&#