为SQL Server满意度比例查询结果新增国家行数统计列

在SQL Server中有一张名为df的表，现有查询可计算指定年份（示例为2020年）各国家的满意度等级占比，需要在查询结果中新增一列Count，显示每个国家在df表中的对应行数。

原查询及结果 #

原SQL查询 #

-- 参数定义
DECLARE @Year INT = 2020; --, @Country varchar(50)= 'Brazil';

WITH ModeData AS (
    SELECT country, 
           a.Mode
    FROM df
    CROSS APPLY (
        SELECT TOP 1 Mode, COUNT(*) AS cnt
        FROM (VALUES (val1), (val2), (val3)) AS t(Mode)
        GROUP BY Mode
        ORDER BY COUNT(*) DESC
    ) a
  WHERE year = @year --and  country=@country 
)

-- 计算占比并映射满意度标签
, Proportions AS (
    SELECT country, 
           CASE 
               WHEN Mode = 1 THEN 'Very Dissatisfied'
               WHEN Mode = 2 THEN 'Dissatisfied'
               WHEN Mode = 3 THEN 'Neutral'
               WHEN Mode = 4 THEN 'Satisfied'
               WHEN Mode = 5 THEN 'Very Satisfied'
           END AS SatisfactionLevel,
           COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (PARTITION BY country) AS Proportion
    FROM ModeData
    GROUP BY country, Mode
)

-- 透视转换为列展示各满意度占比
SELECT country, 
       [Very Dissatisfied], 
       [Dissatisfied], 
       [Neutral], 
       [Satisfied], 
       [Very Satisfied]
FROM Proportions
PIVOT (
    MAX(Proportion)
    FOR SatisfactionLevel IN ([Very Dissatisfied], [Dissatisfied], [Neutral], [Satisfied], [Very Satisfied])
) AS p
ORDER BY country;

原查询输出 #

修改后的查询（新增Count列） #

新增CountryCounts CTE统计各国家的行数，最后将透视结果与该统计关联即可：

-- 参数定义
DECLARE @Year INT = 2020; --, @Country varchar(50)= 'Brazil';

WITH ModeData AS (
    SELECT country, 
           a.Mode
    FROM df
    CROSS APPLY (
        SELECT TOP 1 Mode, COUNT(*) AS cnt
        FROM (VALUES (val1), (val2), (val3)) AS t(Mode)
        GROUP BY Mode
        ORDER BY COUNT(*) DESC
    ) a
    WHERE year = @year --and  country=@country 
),
-- 新增：统计指定年份下每个国家的行数
CountryCounts AS (
    SELECT country, COUNT(*) AS Count
    FROM df
    WHERE year = @year
    GROUP BY country
),
-- 计算占比并映射满意度标签
Proportions AS (
    SELECT country, 
           CASE 
               WHEN Mode = 1 THEN 'Very Dissatisfied'
               WHEN Mode = 2 THEN 'Dissatisfied'
               WHEN Mode = 3 THEN 'Neutral'
               WHEN Mode = 4 THEN 'Satisfied'
               WHEN Mode = 5 THEN 'Very Satisfied'
           END AS SatisfactionLevel,
           COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (PARTITION BY country) AS Proportion
    FROM ModeData
    GROUP BY country, Mode
)

-- 透视结果关联行数统计
SELECT p.country, 
       [Very Dissatisfied], 
       [Dissatisfied], 
       [Neutral], 
       [Satisfied], 
       [Very Satisfied],
       cc.Count
FROM Proportions
PIVOT (
    MAX(Proportion)
    FOR SatisfactionLevel IN ([Very Dissatisfied], [Dissatisfied], [Neutral], [Satisfied], [Very Satisfied])
) AS p
INNER JOIN CountryCounts cc ON p.country = cc.country
ORDER BY p.country;

期望输出结果 #

内容的提问来源于stack exchange，提问作者Homer Jay Simpson