使用DENSE_RANK统计风格数后按CustomerName排序的SQL问题咨询

根因说明 #

DENSE_RANK窗口函数的计算仅依赖其OVER子句内定义的排序规则，和外层查询的最终排序逻辑完全独立。你之前的写法是按StyleID排序生成累计值，而需求实际需要的是按CustomerName排序后，累计截止到当前客户的全局不同风格总数，两者排序基准不一致，因此结果不符合预期。

修改后SQL（兼容所有标准SQL数据库） #

SELECT 
    c.CustomerName,
    ms.StyleName,
    ct.RunningTotal
FROM EntertainmentAgencyExample.Musical_Preferences mp
JOIN EntertainmentAgencyExample.Customers c USING (CustomerID)
JOIN EntertainmentAgencyExample.Musical_Styles ms USING (StyleID)
-- 关联每个客户对应的累计风格总数
JOIN (
    SELECT 
        CustomerName,
        SUM(AddStyleCnt) OVER (ORDER BY CustomerName ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
    FROM (
        -- 统计每个客户新增的未在更早客户中出现过的风格数量
        SELECT 
            c.CustomerName,
            COUNT(DISTINCT CASE WHEN c.CustomerName = t.FirstCusOfStyle THEN ms.StyleID END) AS AddStyleCnt
        FROM EntertainmentAgencyExample.Musical_Preferences mp
        JOIN EntertainmentAgencyExample.Customers c USING (CustomerID)
        JOIN EntertainmentAgencyExample.Musical_Styles ms USING (StyleID)
        -- 关联每个风格首次出现的客户名称
        JOIN (
            SELECT 
                StyleID,
                MIN(c2.CustomerName) AS FirstCusOfStyle
            FROM EntertainmentAgencyExample.Musical_Preferences mp2
            JOIN EntertainmentAgencyExample.Customers c2 USING (CustomerID)
            GROUP BY StyleID
        ) t USING (StyleID)
        GROUP BY c.CustomerName
    ) cus_add
) ct USING (CustomerName)
ORDER BY c.CustomerName ASC;

逻辑说明 #

1. 最内层子查询先计算每个音乐风格首次出现的客户名称，作为判断风格是否为当前客户新增的依据
2. 中间层统计每个客户新增的风格数量，仅统计首次出现在当前客户的风格
3. 外层按客户名称排序，累计计算截止到每个客户的全局不同风格总数，关联回原始数据后按客户名称排序，即可得到和预期完全一致的结果

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