Over子句开窗函数

开窗函数

开窗函数，开窗函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化
开窗函数是在 ISO SQL 标准中定义的。SQL Server 提供排名开窗函数和聚合开窗函数。窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。
可以在单个查询中将多个排名或聚合开窗函数与单个 FROM 子句一起使用。但是，每个函数的 OVER 子句在分区和排序上可能不同。OVER 子句不能与 CHECKSUM 聚合函数结合使用。

PARTITION BY

将结果集分为多个分区。开窗函数分别应用于每个分区，并为每个分区重新启动计算。
和聚合函数不同的是，聚合函数对于聚合字段仅返回一行，而开窗函数返回多行。

/**
示例1：基于SQL Server
**/
WITH TJB (fMonth,Sales,Area,fWeight) AS
(
  SELECT '01月' fMonth,320 Sales,'江苏' Area,'200g' fWeight
  UNION
  SELECT '01月' fMonth,110 Sales,'安徽' Area,'250g' fWeight
  UNION
  SELECT '02月' fMonth,220 Sales,'福建' Area,'180g' fWeight
  UNION
  SELECT '02月' fMonth,130 Sales,'浙江' Area,'200g' fWeight
  UNION
  SELECT '02月' fMonth,210 Sales,'山东' Area,'120g' fWeight
  UNION
  SELECT '02月' fMonth,320 Sales,'河北' Area,'125g' fWeight
)
SELECT
  fMonth,Area,fWeight,Sales,
  SUM(Sales) OVER (PARTITION BY fMonth) AS 'Total',
  AVG(Sales) OVER (PARTITION BY fMonth) AS 'Avg',
  MAX(Sales) OVER (PARTITION BY fMonth) AS 'Max',
  MIN(Sales) OVER (PARTITION BY fMonth) AS 'Min',
  COUNT(Sales) OVER (PARTITION BY fMonth) AS 'Count'
FROM TJB
fMonth  Area    fWeight Sales Total Avg Max Min Count
------------------------------------------------------
01月    安徽    250g    110   430   215 320 110 2
01月    江苏    200g    320   430   215 320 110 2
02月    浙江    200g    130   880   220 320 130 4
02月    山东    120g    210   880   220 320 130 4
02月    福建    180g    220   880   220 320 130 4
02月    河北    125g    320   880   220 320 130 4
--SELECT fMonth,Sales,ROW_NUMBER() OVER (ORDER BY fMonth) AS fMonth1 FROM TJB

/**
示例1：基于Oracle
**/
WITH TJB (fMonth,Sales,Area,fWeight) AS
(
  SELECT '01月' fMonth,320 Sales,'江苏' Area,'200g' fWeight FROM DUAL
  UNION
  SELECT '01月' fMonth,110 Sales,'安徽' Area,'250g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth,220 Sales,'福建' Area,'180g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth,130 Sales,'浙江' Area,'200g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth,210 Sales,'山东' Area,'120g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth,320 Sales,'河北' Area,'125g' fWeight FROM DUAL
)
SELECT
  fMonth,Area,fWeight,Sales,
  SUM(Sales) OVER (PARTITION BY fMonth) AS Total,
  AVG(Sales) OVER (PARTITION BY fMonth) AS AVG,
  MAX(Sales) OVER (PARTITION BY fMonth) AS Max,
  MIN(Sales) OVER (PARTITION BY fMonth) AS MIN,
  COUNT(Sales) OVER (PARTITION BY fMonth) AS Count
FROM TJB

fMonth  Area    fWeight Sales Total Avg Max Min Count
------------------------------------------------------
01月    安徽    250g    110   430   215 320 110 2
01月    江苏    200g    320   430   215 320 110 2
02月    浙江    200g    130   880   220 320 130 4
02月    山东    120g    210   880   220 320 130 4
02月    福建    180g    220   880   220 320 130 4
02月    河北    125g    320   880   220 320 130 4

将 OVER 子句与 ROW_NUMBER 函数结合使用

每个排名函数（ROW_NUMBER、DENSE_RANK、RANK、NTILE）都使用 OVER 子句。以下示例显示了将 OVER 子句与 ROW_NUMBER 结合使用。

--示例2：基于SQL Server
WITH TJB (fMonth,Sales,Area,fWeight) AS
(
    SELECT '01月' fMonth,320 Sales,'江苏' Area,'200g' fWeight
    UNION
    SELECT '01月' fMonth,110 Sales,'安徽' Area,'250g' fWeight
    UNION
    SELECT '02月' fMonth,220 Sales,'福建' Area,'180g' fWeight
    UNION
    SELECT '02月' fMonth,130 Sales,'浙江' Area,'200g' fWeight
    UNION
    SELECT '02月' fMonth,210 Sales,'山东' Area,'120g' fWeight
    UNION
    SELECT '02月' fMonth,320 Sales,'河北' Area,'125g' fWeight
)
SELECT   
  fMonth,Area,fWeight,Sales,
  ROW_NUMBER() OVER (ORDER BY fMonth) AS fID
FROM TJB
fMonth  Area fWeight Sales fID
--------------------------------
01月 安徽   250g    110    1
01月 江苏   200g    320    2
02月 浙江   200g    130    3
02月 山东   120g    210    4
02月 福建   180g    220    5
02月 河北   125g    320    6

--示例2：基于ORACLE
WITH TJB (fMonth,Sales,Area,fWeight) AS
(
  SELECT '01月' fMonth,320 Sales,'江苏' Area,'200g' fWeight FROM DUAL
  UNION
  SELECT '01月' fMonth,110 Sales,'安徽' Area,'250g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth,220 Sales,'福建' Area,'180g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth,130 Sales,'浙江' Area,'200g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth,210 Sales,'山东' Area,'120g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth,320 Sales,'河北' Area,'125g' fWeight FROM DUAL
)
SELECT fMonth,Area,fWeight,Sales,ROW_NUMBER() OVER (ORDER BY fMonth) AS fID FROM TJB
fMonth  Area fWeight Sales fID
--------------------------------
01月 安徽   250g    110    1
01月 江苏   200g    320    2
02月 浙江   200g    130    3
02月 山东   120g    210    4
02月 福建   180g    220    5
02月 河北   125g    320    6