oracle的开窗函数

开窗函数指的是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:

oracle的开窗函数

分析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:

oracle的开窗函数

分析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;

结果:

oracle的开窗函数

?

“人生中最让人痛苦的,不在于【得不到】,而在于【我本来可以】。”

相关文章

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注