Over子句开窗函数

开窗函数

开窗函数，开窗函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化

开窗函数是在 ISO SQL 标准中定义的。SQL Server 提供排名开窗函数和聚合开窗函数。窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。

可以在单个查询中将多个排名或聚合开窗函数与单个 FROM 子句一起使用。但是，每个函数的 OVER 子句在分区和排序上可能不同。OVER 子句不能与 CHECKSUM 聚合函数结合使用。

PARTITION BY

将结果集分为多个分区。开窗函数分别应用于每个分区，并为每个分区重新启动计算。

和聚合函数不同的是，聚合函数对于聚合字段仅返回一行，而开窗函数返回多行。

/**
示例1：基于SQL Server
**/
WITH TJB (fMonth,Sales,Area,fWeight) AS
(
  SELECT '01月' fMonth, Sales,'江苏' Area,'200g' fWeight
  UNION
  SELECT '01月' fMonth, Sales,'安徽' Area,'250g' fWeight
  UNION
  SELECT '02月' fMonth, Sales,'福建' Area,'180g' fWeight
  UNION
  SELECT '02月' fMonth, Sales,'浙江' Area,'200g' fWeight
  UNION
  SELECT '02月' fMonth, Sales,'山东' Area,'120g' fWeight
  UNION
  SELECT '02月' fMonth, 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
------------------------------------------------------
月    安徽    g             
月    江苏    g             
月    浙江    g             
月    山东    g             
月    福建    g             
月    河北    g             
--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, Sales,'江苏' Area,'200g' fWeight FROM DUAL
  UNION
  SELECT '01月' fMonth, Sales,'安徽' Area,'250g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth, Sales,'福建' Area,'180g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth, Sales,'浙江' Area,'200g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth, Sales,'山东' Area,'120g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth, 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
------------------------------------------------------
月    安徽    g             
月    江苏    g             
月    浙江    g             
月    山东    g             
月    福建    g             
月    河北    g             
           

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

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

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

--示例：基于ORACLE
WITH TJB (fMonth,Sales,Area,fWeight) AS
(
  SELECT '01月' fMonth, Sales,'江苏' Area,'200g' fWeight FROM DUAL
  UNION
  SELECT '01月' fMonth, Sales,'安徽' Area,'250g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth, Sales,'福建' Area,'180g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth, Sales,'浙江' Area,'200g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth, Sales,'山东' Area,'120g' fWeight FROM DUAL
  UNION
  SELECT '02月' fMonth, 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
--------------------------------
月 安徽   g        
月 江苏   g        
月 浙江   g        
月 山东   g        
月 福建   g        
月 河北   g