在SQL Server中，COUNT OVER PARTITION是什么意思？

Question

SELECT MobilePhone, COUNT(MobilePhone) OVER(PARTITION BY MobilePhone) AS CountMobilePhone
FROM ES_TABLE 
WHERE applic IN ( SELECT appl 
                FROM Campaign JOIN Client ON Campaign.ClientID = Client.ClientID 
                WHERE Client.ClientCode = 'OPIS')

这是我的查询

输出结果为：

MobilePhone  | CountMobilePhone
121928       |    1
912819       |    1
129819       |    3
198219       |    5
918298       |    5

我想通过CountMobilePhone进行分组计数。应该像这样：

Count   | CountMobilePhone
2       |    1
1       |    3
2       |    5

有可能吗？我试了几次，但是一直出现错误。

- TARA

Answer 1

只需对计数本身进行另一次聚合即可：

SELECT CountMobilePhone, COUNT(*) AS cnt
FROM
(
    SELECT COUNT(MobilePhone) OVER(PARTITION BY MobilePhone) AS CountMobilePhone
    FROM ES_TABLE 
    WHERE applic IN (SELECT appl FROM Campaign INNER JOIN Client
                         ON Campaign.ClientID = Client.ClientID 
                     WHERE Client.ClientCode = 'OPIS')
) t
GROUP BY CountMobilePhone
ORDER BY CountMobilePhone;

- Tim Biegeleisen

Answer 2

试试这个

WITH CTE
AS
(

SELECT 
    MobilePhone, 
    COUNT(MobilePhone) OVER(PARTITION BY MobilePhone) AS CountMobilePhone
    FROM ES_TABLE 
    WHERE EXISTS  ( SELECT  1
                    FROM Campaign JOIN Client ON Campaign.ClientID = Client.ClientID 
                    WHERE Client.ClientCode = 'OPIS' AND appl = ES_TABLE.applic)

)
SELECT
    [Count] = COUNT(1),
    CountMobilePhone
    FROM CTE
        GROUP BY CountMobilePhone

我还将IN替换为exists，以获得更好的性能。

- Jayasurya Satheesh

---

谢谢，它起作用了。但是我看到贴比格尔森先回答了。但我投了你的票。 - TARA

你可以进行的另一个改进是在CTE中不选择“MobilePhone”，因为它并不真正需要来计算数量。 - Tim Biegeleisen

我们需要以最佳方式解决这个问题。 - Jayasurya Satheesh