开窗函数指的是OVER(),和分析函数配合使用。
语法:
OVER(PARTITION BY 分组字段 ORDER BY 排序字段 ROWS BETWEEN 排序字段范围值1 AND 排序字段范围值2)
语法说明:开窗函数为分析函数带有的,包含三个分析子句:
1. 分组(PARTITION BY)。
2. 排序(ORDER BY)。
3. 窗口(ROWS)– 指定范围。
ROWS 有多个范围值:
1. UNBOUNDED PRECEDING 无限/不限定 先前行。
2. N PRECEDING N个先前行(N为1则是1个先前行,2则是2个先前行,以此类推)。
3. UNBOUNDED FOLLOWING 无限/不限定 的跟随行。
4. N FOLLOWING N个跟随行(N为1则是1个跟随行,2则是2个跟随行,以此类推)。
5. CURRENT ROW 当前行。
示例1:
SELECT CCTI.CTR_TYPE_ID,CCTI.ORDER_NUM,CCTI.NAME,WMSYS.WM_CONCAT(CCTI.NAME) OVER (PARTITION BY CCTI.CTR_TYPE_ID) CTR_TYPE_ITEM_STR FROM T_CTRG_CTR_TYPE_ITEM CCTI;
结果1:
分析1:
区别于GROUP BY子句的只返回分组行的结果,开窗函数每一行都会返回一个结果。
示例1的写法相当于指定了ROWS范围从不限定先前行到不限定跟随行(默认):
SELECT CCTI.CTR_TYPE_ID,WMSYS.WM_CONCAT(CCTI.NAME) OVER (PARTITION BY CCTI.CTR_TYPE_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) CTR_TYPE_ITEM_STR FROM T_CTRG_CTR_TYPE_ITEM CCTI;
示例2:
SELECT CCTI.CTR_TYPE_ID,WMSYS.WM_CONCAT(CCTI.NAME) OVER (PARTITION BY CCTI.CTR_TYPE_ID ORDER BY CCTI.ORDER_NUM) CTR_TYPE_ITEM_STR FROM T_CTRG_CTR_TYPE_ITEM CCTI;
结果2:
分析2:
加上了ORDER BY 之后,返回结果变成了逐级递增的效果。
写法相当于指定了ROWS范围从不限定先前行到当前行(加了ORDER BY默认):
SELECT CCTI.CTR_TYPE_ID,WMSYS.WM_CONCAT(CCTI.NAME) OVER (PARTITION BY CCTI.CTR_TYPE_ID ORDER BY CCTI.ORDER_NUM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CTR_TYPE_ITEM_STR FROM T_CTRG_CTR_TYPE_ITEM CCTI;
特别说明:如果SQL语句中存在ORDER BY语句(非开窗函数中的分析子句),会再分析函数分析结束后再执行排序,优先级低于分析子句。
后话:
因为开窗函数每一行都会返回一个结果,所以要取分组结果的话,可以在语句加上DISTINCT修饰即可返回分组结果(分组结果内有排序)。
语句:
SELECT DISTINCT CCTI.CTR_TYPE_ID,WMSYS.WM_CONCAT(CCTI.NAME) OVER (PARTITION BY CCTI.CTR_TYPE_ID ORDER BY CCTI.ORDER_NUM ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) CTR_TYPE_ITEM_STR FROM T_CTRG_CTR_TYPE_ITEM CCTI;
结果:
?
“人生中最让人痛苦的,不在于【得不到】,而在于【我本来可以】。”