使用Dense_Rank()计算两类不同去重计数的SQL技术问题

需求说明 #

按资源（res）统计两类唯一客户数：

• 类别X的唯一customer_id数量
• 类别Y的唯一customer_id数量

原SQL问题分析 #

原代码的核心问题：

• 基于每行的cat字段做条件判断，导致X和Y类别的数据行被拆分，出现NULL值和多行结果
• 分区逻辑错误，未将同一资源下的目标类别客户统一计算排名

修正后的SQL代码 #

DROP TABLE IF EXISTS #test_data 

CREATE TABLE #test_data
(
line int 
,res varchar(1)
,cat varchar(1)
,customer_id int
)

INSERT INTO #test_data (line,res,cat,customer_id) VALUES (1,'A','X',1)
INSERT INTO #test_data (line,res,cat,customer_id) VALUES (2,'A','X',1)
INSERT INTO #test_data (line,res,cat,customer_id) VALUES (3,'A','Y',1)
INSERT INTO #test_data (line,res,cat,customer_id) VALUES (4,'A','X',2)
INSERT INTO #test_data (line,res,cat,customer_id) VALUES (5,'B','X',1)
INSERT INTO #test_data (line,res,cat,customer_id) VALUES (6,'B','Y',1)

-- 修正后的查询
SELECT 
    res AS 资源,
    MAX(CASE WHEN cat = 'X' THEN dr END) AS X类别唯一客户数,
    MAX(CASE WHEN cat = 'Y' THEN dr END) AS Y类别唯一客户数
FROM (
    SELECT 
        res,
        cat,
        -- 按资源+类别分区，对客户ID排序，DENSE_RANK的最大值即为去重客户数
        DENSE_RANK() OVER (PARTITION BY res, cat ORDER BY customer_id) AS dr
    FROM #test_data
) t
GROUP BY res
ORDER BY res;

验证结果 #

执行修正后的SQL，将得到预期结果：

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