SQL Server中创建支持动态分组的多参数表值函数咨询

表结构与数据初始化 #

-- 创建包含部门和性别的表
CREATE TABLE df 
(
    country VARCHAR(50),
    year INT,
    val1 INT,
    val2 INT,
    val3 INT,
    department VARCHAR(50),
    gender VARCHAR(10)
);

-- 插入数据
INSERT INTO df (country, year, val1, val2, val3, department, gender) 
VALUES ('USA', 2020, 4, 4, 5, 'Sales', 'Male'),
('USA', 2020, 4, 4, 5, 'Sales', 'Male'),
('USA', 2020, 5, 5, 5, 'Sales', 'Female'),
('USA', 2020, 5, 5, 5, 'Sales', 'Female'),
('USA', 2020, 1, 1, 5, 'Sales', 'Male'),
('USA', 2020, 3, 3, 5, 'Sales', 'Female'),
('USA', 2020, 4, 2, 5, 'Sales', 'Male'),
('USA', 2020, 1, 1, 5, 'Sales', 'Female'),
('USA', 2020, 2, 2, 5, 'Sales', 'Male'),
('Canada', 2020, 2, 2, 3, 'HR', 'Female'),
('Canada', 2020, 2, 2, 3, 'HR', 'Female'),
('Canada', 2020, 2, 2, 3, 'HR', 'Male'),
('Canada', 2020, 2, 2, 3, 'HR', 'Male'),
('Canada', 2020, 5, 5, 3, 'HR', 'Female'),
('Canada', 2020, 5, 5, 3, 'HR', 'Male'),
('Canada', 2020, 1, 1, 3, 'HR', 'Female'),
('Canada', 2020, 1, 1, 3, 'HR', 'Male'),
('Canada', 2020, 3, 4, 3, 'HR', 'Female'),
('Canada', 2020, 3, 4, 3, 'HR', 'Male'),
('Canada', 2020, 5, 4, 3, 'HR', 'Female'),
('Canada', 2020, 5, 4, 5, 'HR', 'Male'),
('Canada', 2020, 5, 4, 5, 'HR', 'Female'),
('Germany', 2022, 5, 5, 4, 'IT', 'Male'),
('France', 2020, 1, 1, 2, 'Finance', 'Female'),
('France', 2020, 1, 1, 2, 'Finance', 'Female'),
('France', 2020, 3, 2, 2, 'Finance', 'Male'),
('France', 2020, 3, 4, 2, 'Finance', 'Female'),
('France', 2020, 3, 5, 5, 'Finance', 'Male'),
('France', 2020, 3, 4, 4, 'Finance', 'Female'),
('France', 2020, 3, 4, 4, 'Finance', 'Male'),
('France', 2020, 3, 4, 3, 'Finance', 'Female'),
('UK', 2021, 4, 2, 3, 'Marketing', 'Male'),
('Australia', 2022, 3, 3, 4, 'Support', 'Female'),
('Italy', 2020, 5, 5, 5, 'Operations', 'Male'),
('Italy', 2020, 5, 5, 5, 'Operations', 'Female'),
('Italy', 2020, 5, 1, 1, 'Operations', 'Male'),
('Italy', 2020, 4, 4, 1, 'Operations', 'Female'),
('Italy', 2020, 2, 1, 2, 'Operations', 'Male'),
('Italy', 2020, 3, 5, 3, 'Operations', 'Female'),
('Spain', 2021, 1, 2, 3, 'Customer Service', 'Male'),
('Mexico', 2022, 4, 4, 4, 'Logistics', 'Female'),
('Brazil', 2020, 4, 1, 1, 'R&D', 'Male'),
('Brazil', 2020, 4, 1, 1, 'R&D', 'Female'),
('Brazil', 2020, 4, 3, 4, 'R&D', 'Male'),
('Brazil', 2020, 5, 3, 5, 'R&D', 'Female'),
('Brazil', 2020, 5, 3, 5, 'R&D', 'Male'),
('Brazil', 2020, 3, 3, 1, 'R&D', 'Female'),
('Brazil', 2020, 2, 3, 1, 'R&D', 'Male');

-- 查询验证数据
SELECT * FROM df;

原查询语句 #

-- 参数定义
DECLARE @Year INT = 2020;
DECLARE @Metric VARCHAR(50) = 'count'; 
DECLARE @Gender VARCHAR(20) = NULL; -- 指定性别（如'Male'、'Female'）或NULL包含全部
DECLARE @Department VARCHAR(50) = NULL; -- 指定部门（如'HR'、'Engineering'）或NULL包含全部
-- @Metric可选值：'dissatisfaction'、'satisfaction'、'count'

WITH UnpivotedData AS 
(
    SELECT country, gender, department, year, Vals
    FROM 
        (SELECT country, gender, department, year, val1, val2, val3
         FROM df) AS SourceTable
    UNPIVOT 
        (Vals FOR ValueColumn IN (val1, val2, val3)) AS Unpivoted
    WHERE year = @Year
),
Proportions AS 
(
    SELECT 
        country,
        gender,
        department,
        CASE 
            WHEN Vals = 1 THEN 'Very Dissatisfied'
            WHEN Vals = 2 THEN 'Dissatisfied'
            WHEN Vals = 3 THEN 'Neutral'
            WHEN Vals = 4 THEN 'Satisfied'
            WHEN Vals = 5 THEN 'Very Satisfied'
        END AS SatisfactionLevel,
        COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (PARTITION BY country, gender, department) AS Proportion
    FROM 
        UnpivotedData
    GROUP BY 
        country, gender, department, Vals
),
Pivoted AS 
(
    SELECT country, gender, department, 
           [Very Dissatisfied], 
           [Dissatisfied], 
           [Neutral], 
           [Satisfied], 
           [Very Satisfied]
    FROM Proportions
    PIVOT 
        (MAX(Proportion)
         FOR SatisfactionLevel IN ([Very Dissatisfied], [Dissatisfied], [Neutral], [Satisfied], [Very Satisfied])) AS p
),
CountryCounts AS 
(
    SELECT 
        CASE WHEN country IS NULL THEN 'Unknown' ELSE country END AS country,
        gender, 
        department,
        COUNT(*) AS Total
    FROM df
    WHERE year = @Year
    -- 应用性别和部门筛选
    AND (@Gender IS NULL OR gender = @Gender)
    AND (@Department IS NULL OR department = @Department)
    GROUP BY CASE WHEN country IS NULL THEN 'Unknown' ELSE country END, gender, department
),
OrderedData AS 
(
    SELECT 
        p.country,
        p.gender,
        p.department,
        [Very Dissatisfied],
        [Dissatisfied],
        [Neutral],
        [Satisfied],
        [Very Satisfied],
        c.Total,
        CASE 
            WHEN @Metric = 'satisfaction' THEN ISNULL([Satisfied], 0) + ISNULL([Very Satisfied], 0)
            WHEN @Metric = 'dissatisfaction' THEN ISNULL([Very Dissatisfied], 0) + ISNULL([Dissatisfied], 0)
            WHEN @Metric = 'count' THEN c.Total
        END AS SortValue
    FROM Pivoted AS p
    INNER JOIN CountryCounts AS c ON p.country = c.country AND p.gender = c.gender AND p.department = c.department
)
SELECT 
    country,
    gender,
    department,
    [Very Dissatisfied],
    [Dissatisfied],
    [Neutral],
    [Satisfied],
    [Very Satisfied],
    Total
FROM 
    OrderedData
ORDER BY 
    SortValue DESC;

需求说明 #

需要创建一个包含以下三个参数的表值函数：

• Metric：指定统计指标，可选值为'satisfaction'、'dissatisfaction'、'count'；
• Year：传入具体年份则按该年份筛选并分组；为NULL则包含所有年份且不按年分组；
• Factor：指定分组维度，可取值为Gender、Department，或同时包含两者（如'Gender,Department'）；若为NULL或默认值则不进行分组。

实现方案 #

可以在SQL Server 2016中实现该表值函数，核心通过动态处理分组字段、年份筛选逻辑及Metric对应的排序规则完成。以下是完整的表值函数代码：

CREATE FUNCTION dbo.GetSatisfactionStats
(
    @Metric VARCHAR(50),
    @Year INT = NULL,
    @Factor VARCHAR(100) = NULL
)
RETURNS TABLE
AS
RETURN
(
    WITH UnpivotedData AS 
    (
        SELECT 
            country,
            gender,
            department,
            year,
            Vals
        FROM 
            (SELECT country, gender, department, year, val1, val2, val3 FROM df) AS SourceTable
        UNPIVOT 
            (Vals FOR ValueColumn IN (val1, val2, val3)) AS Unpivoted
        WHERE 
            (@Year IS NULL OR year = @Year)
    ),
    GroupedProportions AS 
    (
        SELECT 
            country,
            -- 根据Factor动态决定是否包含gender分组
            CASE WHEN @Factor LIKE '%Gender%' THEN gender ELSE NULL END AS gender,
            -- 根据Factor动态决定是否包含department分组
            CASE WHEN @Factor LIKE '%Department%' THEN department ELSE NULL END AS department,
            -- 根据Year参数决定是否按年分组
            CASE WHEN @Year IS NOT NULL THEN year ELSE NULL END AS year,
            CASE 
                WHEN Vals = 1 THEN 'Very Dissatisfied'
                WHEN Vals = 2 THEN 'Dissatisfied'
                WHEN Vals = 3 THEN 'Neutral'
                WHEN Vals = 4 THEN 'Satisfied'
                WHEN Vals = 5 THEN 'Very Satisfied'
            END AS SatisfactionLevel,
            COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (
                PARTITION BY 
                    country,
                    CASE WHEN @Factor LIKE '%Gender%' THEN gender ELSE NULL END,
                    CASE WHEN @Factor LIKE '%Department%' THEN department ELSE NULL END,
                    CASE WHEN @Year IS NOT NULL THEN year ELSE NULL END
            ) AS Proportion
        FROM 
            UnpivotedData
        GROUP BY 
            country,
            CASE WHEN @Factor LIKE '%Gender%' THEN gender ELSE NULL END,
            CASE WHEN @Factor LIKE '%Department%' THEN department ELSE NULL END,
            CASE WHEN @Year IS NOT NULL THEN year ELSE NULL END,
            Vals
    ),
    PivotedSatisfaction AS 
    (
        SELECT 
            country,
            gender,
            department,
            year,
            [Very Dissatisfied], 
            [Dissatisfied], 
            [Neutral], 
            [Satisfied], 
            [Very Satisfied]
        FROM GroupedProportions
        PIVOT 
            (MAX(Proportion)
            FOR SatisfactionLevel IN ([Very Dissatisfied], [Dissatisfied], [Neutral], [Satisfied], [Very Satisfied])) AS p
    ),
    TotalCounts AS 
    (
        SELECT 
            CASE WHEN country IS NULL THEN 'Unknown' ELSE country END AS country,
            CASE WHEN @Factor LIKE '%Gender%' THEN gender ELSE NULL END AS gender,
            CASE WHEN @Factor LIKE '%Department%' THEN department ELSE NULL END AS department,
            CASE WHEN @Year IS NOT NULL THEN year ELSE NULL END AS year,
            COUNT(*) AS Total
        FROM df
        WHERE 
            (@Year IS NULL OR year = @Year)
        GROUP BY 
            CASE WHEN country IS NULL THEN 'Unknown' ELSE country END,
            CASE WHEN @Factor LIKE '%Gender%' THEN gender ELSE NULL END,
            CASE WHEN @Factor LIKE '%Department%' THEN department ELSE NULL END,
            CASE WHEN @Year IS NOT NULL THEN year ELSE NULL END
    ),
    OrderedResults AS 
    (
        SELECT 
            p.country,
            p.gender,
            p.department,
            p.year,
            ISNULL(p.[Very Dissatisfied], 0) AS [Very Dissatisfied],
            ISNULL(p.[Dissatisfied], 0) AS [Dissatisfied],
            ISNULL(p.[Neutral], 0) AS [Neutral],
            ISNULL(p.[Satisfied], 0) AS [Satisfied],
            ISNULL(p.[Very Satisfied], 0) AS [Very Satisfied],
            c.Total,
            CASE 
                WHEN @Metric = 'satisfaction' THEN ISNULL(p.[Satisfied], 0) + ISNULL(p.[Very Satisfied], 0)
                WHEN @Metric = 'dissatisfaction' THEN ISNULL(p.[Very Dissatisfied], 0) + ISNULL(p.[Dissatisfied], 0)
                WHEN @Metric = 'count' THEN c.Total
                ELSE NULL
            END AS SortValue
        FROM PivotedSatisfaction AS p
        INNER JOIN TotalCounts AS c 
            ON p.country = c.country
            AND ISNULL(p.gender, '') = ISNULL(c.gender, '')
            AND ISNULL(p.department, '') = ISNULL(c.department, '')
            AND ISNULL(p.year, 0) = ISNULL(c.year, 0)
    )
    SELECT 
        country,
        gender,
        department,
        year,
        [Very Dissatisfied],
        [Dissatisfied],
        [Neutral],
        [Satisfied],
        [Very Satisfied],
        Total
    FROM OrderedResults
    ORDER BY SortValue DESC
);

函数使用示例 #

• 按count排序，统计2020年所有数据，不分组：

SELECT * FROM dbo.GetSatisfactionStats('count', 2020, NULL);

• 按满意度排序，统计所有年份，按Gender和Department分组：

SELECT * FROM dbo.GetSatisfactionStats('satisfaction', NULL, 'Gender,Department');

• 按不满意占比排序，统计2022年数据，仅按Department分组：

SELECT * FROM dbo.GetSatisfactionStats('dissatisfaction', 2022, 'Department');

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