OVER 子句中的 ORDER BY 和 PARTITION BY 參數有什麼區別？

下面的這些查詢都給了我完全相同的結果，我認為這是因為我的數據集而不是參數的工作方式。

使用子句時，和OVER之間有什麼區別。ORDER BY``PARTITION BY

稍有不同的是，為什麼不使用該術語GROUP BY而不是聽起來更複雜的PARTITION BY，因為在這種情況下使用分區似乎可以實現與分組相同的效果。我知道您可以更改這些內部分區，然後這些更改會反映在表中。是這個原因嗎？

查詢一：

SELECT
   CP.iYear,
   AVG(AVG(CP.mUpgradeCost)) OVER(ORDER BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

查詢 2：

SELECT
   CP.iYear,
   AVG(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

該PARTITION BY作品作為一個“視窗組”，ORDER BY並在組內進行排序。但是，因為您使用的是 ，所以您GROUP BY CP.iYear有效地將視窗減少到僅一行（在視窗函式之前GROUP BY執行）。在您的情況下，單行的平均值將是該行的值。AVG(CP.mUpgradeCost)

至於查詢 2，您是要創建執行平均值還是什麼？甚至不確定您希望該查詢返回什麼。

如果您刪除GROUP BY CP.iYear並更改AVG(AVG(...))為 just AVG()，您會看到不同之處。

這是一個範例，有望解釋PARTITION BY和/或的用法ORDER BY：

隨機表：

a     b
----- ------
X     1001
X     1002
X     1003
Y     1001
Y     1002

現在，

SELECT a, b, COUNT(*) OVER (PARTITION BY a) FROM r;

將返回

a     b
----- ------ ------
X     1001   3
X     1002   3
X     1003   3
Y     1001   2
Y     1002   2

所以你可以看到有 3 行 a=X 和 2 行 a=Y。當您想要一個有序的視窗函式時，該ORDER BY子句就會發揮作用，例如行號或執行總計。

SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b),
            SUM(b) OVER (PARTITION BY a ORDER BY b ROWS UNBOUNDED PRECEDING)
FROM r;

… 給你：

a     b
----- ------ ------ ------
X     1001   1      1001
X     1002   2      2003
X     1003   3      3006
Y     1001   1      1001   <- row number and running total resets here; new partition
Y     1002   2      2003

引用自：https://dba.stackexchange.com/questions/168037