1.我们有如下的用户访问数据 userId visitDate visitCount u01 2017/1/21 5 u02 2017/1/23 6 u03 2017/1/22 8 u04 2017/1/20 3 u01 2017/1/23 6 u01 2017/2/21 8 u02 2017/1/23 6 u01 2017/2/22 4 要求使用SQL统计出每个用户的累积访问次数，如下表所示： 用户id 月份 小计 累积 u01 2017-01 11 11 u01 2017-02 12 23 u02 2017-01 12 12 u03 2017-01 8 8 u04 2017-01 3 3
分析： 1.首先是将日期的格式转换成所需要的格式类型
select userId,date_format(regexp_replace(visitDate,'/','-'),'YYYY-MM') mn,visitCount；t1 2.统计出每个人单个月的访问量 select userId,mn,sum(visitCount) monCount from t1 group by userId,mn; t2 3.a按照月累计访问量 select userId,mn,monCount,sum(monCount) over(partition by userId order by mn) from t2; 4.最终sql select userId, mn, mn_count, sum(mn_count) over(partition by userId order by mn) from ( select userId, mn, sum(visitCount) mn_count from (select userId, date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn, visitCount from action)t1 group by userId,mn)t2;2.有50W个店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit 访客的用户id为user_id，被访问的店铺名称为shop，请统计： u1 a u2 b u1 b u1 a u3 c u4 b u1 a 1）每个店铺的UV（访客数）
select shop ,count(distinct userId) from Visit group by shop2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数 （1）查询每个店铺被每个用户访问次数
select shop,user_id,count(*) ct from visit group by shop,user_id;t1
（2）计算每个店铺被用户访问次数排名
select shop,user_id,ct,rank() over(partition by shop order by ct) rk from t1;t2（3）取每个店铺排名前3的
select shop,user_id,ct from t2 where rk<=3; select shop,user_id,ct from t2 where rk<=3;（4）最终SQL
SELECT SHOP, USER_ID, CT FROM (SELECT SHOP, USER_ID, CT, RANK() OVER(PARTITION BY SHOP ORDER BY CT) RK FROM (SELECT SHOP, USER_ID, COUNT(*) CT FROM VISIT GROUP BY SHOP, USER_ID) T1) T2 WHERE RK <= 3;3.已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。请给出sql进行统计:数据样例:2017-01-01,10029028,1000003251,33.57。 1）给出 2017年每个月的订单数、用户数、总成交金额。
select date_format(DATE,'YYYY-MM'),count(Order_id),count(User_id),count(amount) from ORDER_tab group by date_format(DATE,'YYYY-MM')2）给出2017年11月的新客数(指在11月才有第一笔订单)
select count(user_id) from order_tab group by user_id having date_format(min(dt),'yyyy-MM')='2017-11';