一、SUM,AVG,COUNT函数
关键是理解 ROWS BETWEEN 含义,也叫做window子句： PRECEDING：往前 FOLLOWING：往后 CURRENT ROW：当前行 UNBOUNDED：无边界， UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点- 1、叠加求sum必须加order by 不加orde by 结果是整个分区的sum
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv3, --当前行+往前3行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv4, --当前行+往前3行+往后1行 SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv5 ---当前行+往后所有行 FROM test1;二、RANK()、ROW_NUMBER()、DENSE_RANK() 函数 1、rank()函数 此排序方法进行排序时，相同的排序是一样的，而且下一个不同值是跳着排序的1，2，2，4…n 2、row_number()函数 此方法不管排名是否有相同的，都按照顺序1，2，3……n 3、dense_rank()函数 此方法对于排名相同的名次一样，且后面名次不跳跃1，2，2，3…n
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn FROM test1;