前提:
按照OVER() 进行partion,然后每个分组执行函数计算。
最后为每一个分组增加对应的字段数据。最后原始数据行数
没有改变,可以在此基础上继续使用。
一、计算累计和
统计1-12月的累积销量,即1月为1月份的值,2月为1.2月份值的和,3月为123月份的和,12月为1-12月份值的和
SELECT
month,SUM(amount) month_amount,
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM table_name
GROUP BY month
ORDER BY month;
其中:
SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值,在上述可以换为 month_amount
ORDER BY month 按月份对查询读取的记录进行排序,就是窗口范围内的排序
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点,UNBOUNDED PRECEDING 为起点,表明从第一行开始, CURRENT ROW为默认值,就是这一句等价于:
ROWS UNBOUNDED PRECEDING
PRECEDING:在前 N 行的意思。
FOLLOWING:在后 N 行的意思。
注意以下几点:
1)order by 后面省去了窗口子句,还原起来就是order by xxx range between unbounded preceding and current row.
2)partition by之后没有使用order by,窗口大小默认就是整个分组
二、排序行号
SELECT
column_name,
RANK() OVER (ORDER BY column_name DESC) AS rank,
DENSE_RANK() OVER (ORDER BY SUM(column_name) DESC) AS dense_rank
FROM table_name
RANK()在出现等级相同的元素时预留为空,DENSE_RANK()不会。
Eg:某产品类型有两个并列第一
RANK():第一二为1,第三位3
DENSE_RANK():第一二为1,第三位2
SELECT
ROW_NUMBER() OVER (ORDER BY column_name DESC)AS row_name
FROM table_name;
上面两个的区别?如下:
(1)Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,
同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
(2)Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
(3)Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
三、过滤数据
Plain Text
select
*
from (SELECT
event,
pt,
sv,
dm,
time,
PERCENTILE_APPROX(time, 0.90) OVER (PARTITION by event,pt,sv,dm) time_percent
FROM
log.xm_sdk_log
WHERE
dt = '20160427'
AND
time IS NOT NULL)tmp
where time > time_percent