无题

窗口函数

什么是窗口函数？

1. OLAP，对数据库数据进行实时分析处理

2. select 窗口函数() over (partition by xx ordder by xx) from 表名;

   1. 聚合：
   2. partition by：
   3. order by：

3. 窗口函数不改变数据的行数，有时需要去重（在使用聚合函数时）

4. 聚合函数是对当前记录及以上记录求和、最大、最小值等

   drop table test1;
   CREATE TABLE test1 (
       userId varchar(50),
       visitDate varchar(50),
       visitCount INT );
   
   INSERT INTO test1
   VALUES
   ( '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 );
   
   select t1.userId, t1.visitDate, t1.小计
   from (select userId,visitDate, (sum(visitCount) over(partition by userId,visitDate)) 小计
       from test1) as t1;
   
   # 使用SQL统计出每个用户的累积访问次数
   # 法一
   select userId, 月份, 小计1 ,sum(小计1) over(partition by userId order by 月份) 累计
   from (
   select distinct userId, 月份 , sum(小计) over(partition by userId,月份 order by 月份) 小计1
   from (
   select t.userId, t.月份, 小计
   from (
   select distinct userId, date_format(visitDate,'%Y-%m') 月份 , sum(visitCount) over(partition by userId,visitDate order by visitDate) 小计 from test1 order by userId
   ) t) t1) t2;
   
   -- 法2
   SELECT t2.userid, t2.visitmonth, subtotal_visit_cnt, sum(subtotal_visit_cnt) over (partition BY userid ORDER BY visitmonth) AS total_visit_cnt
   FROM (SELECT userid, visitmonth,sum(visitcount) AS subtotal_visit_cnt
           FROM (SELECT userid, date_format(visitdate,'%Y-%m') AS visitmonth,visitcount FROM test1) t1
           GROUP BY userid, visitmonth) t2
    -- 法3       
   select *, sum(小计) over(partition by userId order by vis_m) 累计
   from (
   select userId,vis_m, sum(visitCount) 小计
   from (
   SELECT userId,DATE_FORMAT(visitDate,'%Y-%m') vis_m, visitCount  FROM test1 ORDER BY userId) t1
   group by userId,vis_m) t2
   
   -- 法4
       select distinct userId, 月份 , sum(visitCount) over(partition by userId,月份) 小计, sum(visitCount) over(partition by userId order by 月份) 累计
       from (
       select userId,date_format(visitDate,'%Y-%m') 月份, visitCount
       from test1 order by userId,月份) as t