SQL Server中创建表值函数实现行级众数与数据过滤

需求说明 #

基于SurveyData表，创建一个表值函数，接收country和year两个参数，返回符合该条件的原表数据，并新增一列mode_val，显示每行val1、val2、val3三个值的统计众数（出现频率最高的值；若多个值频率相同，取数值最小的那个）。

表结构与测试数据 #

-- 创建表
CREATE TABLE SurveyData 
(
    country VARCHAR(50),
    year INT,
    val1 INT,
    val2 INT,
    val3 INT
);

-- 插入测试数据
INSERT INTO SurveyData (country, year, val1, val2, val3) 
VALUES ('USA', 2020, 4, 4, 5),
       ('Canada', 2021, 2, 4, 3),
       ('Germany', 2022, 5, 5, 4),
       ('France', 2020, 3, 4, 2),
       ('UK', 2021, 4, 2, 3),
       ('Australia', 2022, 3, 3, 4),
       ('Italy', 2020, 5, 5, 5),
       ('Spain', 2021, 1, 2, 3),
       ('Mexico', 2022, 4, 4, 4),
       ('Brazil', 2020, 2, 3, 1);

表值函数实现代码 #

CREATE FUNCTION dbo.GetSurveyDataWithMode
(
    @Country VARCHAR(50),
    @Year INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sd.country,
        sd.year,
        sd.val1,
        sd.val2,
        sd.val3,
        -- 计算当前行的众数：将三个列转成行统计频率，取频率最高的数值（频率相同时取最小）
        (SELECT TOP 1 val
         FROM (VALUES (sd.val1), (sd.val2), (sd.val3)) AS vals(val)
         GROUP BY val
         ORDER BY COUNT(*) DESC, val ASC) AS mode_val
    FROM SurveyData sd
    WHERE sd.country = @Country 
      AND sd.year = @Year
);

函数调用示例 #

-- 查询USA 2020的结果（众数为4）
SELECT * FROM dbo.GetSurveyDataWithMode('USA', 2020);

-- 查询Spain 2021的结果（三个值均唯一，众数取最小的1）
SELECT * FROM dbo.GetSurveyDataWithMode('Spain', 2021);

关键逻辑说明 #

1. 内联表值函数：采用内联格式，执行效率高于多语句表值函数，适配这类简单查询场景。
2. 众数计算逻辑：通过VALUES将每行的三个数值转换为临时行集，分组统计每个值的出现次数后按频率降序排序；若多个值频率相同，通过val ASC保证返回最小数值（可根据需求调整排序规则，比如改为val DESC取最大数值）。

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