SQL Server 2016中用Window Function统计Distinct CustomerID报错求助

我来帮你搞定这个SQL统计的问题！先理清楚你的核心需求：你想要全局唯一的CustomerID总数（同一个ID不管出现在多少个Type/SubType分组里，全局统计只算一次），同时每个(Type, SubType)分组内要统计该分组下的唯一CustomerID数，另外你尝试用窗口函数实现，但写法上出了问题。

先说说你现有查询的几个问题：

• SQL Server 2016的窗口函数不支持COUNT(DISTINCT ...)，所以你用窗口函数去重统计的写法会报错；
• 你写的COUNT(CustomerID) OVER (PARTITION BY CustomerID, Type ORDER BY CustomerID)得到的是每个CustomerID在对应Type里的出现次数，不是分组的唯一CustomerID计数，这和你要的统计方向不对；
• Grand Total部分的窗口函数完全没必要，因为全局汇总不需要按CustomerID拆分统计。

下面给你两个针对性的解决方案，看你更需要哪种：

方案1：分组统计+全局唯一总数（最常用场景） #

这个方案直接给出每个(Type, SubType)分组的唯一CustomerID数，以及全局去重后的总CustomerID数，结果清晰高效：

-- 先创建测试表（保留你的原代码）
DROP TABLE IF EXISTS #T_Test
CREATE TABLE #T_Test(
 [Type] VARCHAR(50) NULL,
 SubType VARCHAR(50) NULL,
 CustomerID INT NULL
)
INSERT INTO #T_Test ([Type],[SubType],[CustomerID]) VALUES
('TypeA','SubTypeA',390),
('TypeA','SubTypeA',107),
('TypeB','SubTypeB',3),
('TypeB','SubTypeC',3),
('TypeB','SubTypeB',107),
('TypeB','SubTypeC',107),
('TypeB','SubTypeB',390),
('TypeB','SubTypeC',390),
('TypeB','SubTypeC',718),
('TypeB','SubTypeB',100120),
('TypeB','SubTypeC',100120),
('TypeB','SubTypeC',100120),
('TypeC','SubTypeD',107),
('TypeC','SubTypeE',100120),
('TypeC','SubTypeE',718)

-- 先计算全局唯一的CustomerID总数
DECLARE @GlobalUniqueCustomers INT
SELECT @GlobalUniqueCustomers = COUNT(DISTINCT CustomerID) FROM #T_Test

-- 合并全局汇总和分组统计结果
SELECT 
    'Grand Total' AS [Type], 
    '' AS SubType,
    @GlobalUniqueCustomers AS TotalCustomers,
    NULL AS TotalType, -- 全局汇总不需要Type级统计，若需要可单独计算
    NULL AS TotalSubType -- 全局汇总不需要SubType级统计
FROM #T_Test
GROUP BY () -- 空分组确保只返回一行全局汇总
UNION ALL
SELECT 
    [Type], 
    SubType,
    COUNT(DISTINCT CustomerID) AS TotalCustomers,
    -- 统计当前Type下跨所有SubType的唯一CustomerID数
    COUNT(DISTINCT CustomerID) OVER (PARTITION BY [Type]) AS TotalType,
    -- 当前SubType的唯一CustomerID数，和分组统计的TotalCustomers一致
    COUNT(DISTINCT CustomerID) AS TotalSubType
FROM #T_Test
GROUP BY [Type], SubType
-- 排序让全局汇总在最前面
ORDER BY 
    CASE WHEN [Type] = 'Grand Total' THEN 0 ELSE 1 END,
    [Type], SubType

方案2：每行对应CustomerID的出现次数（逐行统计场景） #

如果你想要的是每一条原始数据对应的该CustomerID在当前Type、SubType中的出现次数，同时显示全局唯一总数，可以用CTE先预统计各维度数据再关联：

WITH GroupStats AS (
    -- 预统计每个分组的基础数据
    SELECT 
        [Type],
        SubType,
        COUNT(DISTINCT CustomerID) AS GroupUniqueCustomers
    FROM #T_Test
    GROUP BY [Type], SubType
),
CustomerTypeCounts AS (
    -- 统计每个CustomerID在对应Type中的出现次数
    SELECT 
        [Type],
        CustomerID,
        COUNT(*) AS CustomerTypeCount
    FROM #T_Test
    GROUP BY [Type], CustomerID
),
CustomerSubTypeCounts AS (
    -- 统计每个CustomerID在对应Type+SubType中的出现次数
    SELECT 
        [Type],
        SubType,
        CustomerID,
        COUNT(*) AS CustomerSubTypeCount
    FROM #T_Test
    GROUP BY [Type], SubType, CustomerID
),
GlobalUnique AS (
    -- 全局唯一CustomerID总数
    SELECT COUNT(DISTINCT CustomerID) AS GlobalUniqueCount FROM #T_Test
)
-- 先输出全局汇总行
SELECT 
    'Grand Total' AS [Type],
    '' AS SubType,
    GlobalUniqueCount AS TotalCustomers,
    NULL AS TotalType,
    NULL AS TotalSubType
FROM GlobalUnique
UNION ALL
-- 再输出每条原始数据的统计
SELECT 
    t.[Type],
    t.SubType,
    gu.GlobalUniqueCount AS TotalCustomers,
    ctc.CustomerTypeCount AS TotalType,
    cstc.CustomerSubTypeCount AS TotalSubType
FROM #T_Test t
CROSS JOIN GlobalUnique gu
JOIN CustomerTypeCounts ctc ON t.[Type] = ctc.[Type] AND t.CustomerID = ctc.CustomerID
JOIN CustomerSubTypeCounts cstc ON t.[Type] = cstc.[Type] AND t.SubType = cstc.SubType AND t.CustomerID = cstc.CustomerID

额外说明 #

• SQL Server 2016确实不支持窗口函数里用COUNT(DISTINCT)，如果非要用窗口实现去重统计，得用DENSE_RANK()的技巧，但不如先分组统计来得直观高效；
• 全局唯一总数必须单独计算，因为如果直接在Union里用COUNT(DISTINCT)，会把不同分组里的同一个CustomerID重复计数，不符合你的需求。

内容的提问来源于stack exchange，提问作者Oded Dror