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：

分析1：

区别于GROUP BY子句的只返回分组行的结果，开窗函数每一行都会返回一个结果。

示例1的写法相当于指定了ROWS范围从不限定先前行到不限定跟随行（默认）：

SELECT
  CCTI.CTR_TYPE_ID,
  CCTI.ORDER_NUM,
  CCTI.NAME,
  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,
  CCTI.ORDER_NUM,
  CCTI.NAME,
  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,
  CCTI.ORDER_NUM,
  CCTI.NAME,
  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;

结果：

 

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