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