按Customer和SiteNumber分组获取多列最常见非空值

原始数据表 #

Customer    SiteNumber  DeviceNumber    Address         ValueA      ValueB
100         1           T1              123 Main St     50          60  
100         1           T2              123 Main St     50          null
100         1           T3              null            50          60
100         3           T1              27 Front St     100         null    
100         3           T2              null            null        null
100         3           T3              27 Front St     100         60
200         1           T1              222 Main St     40          60  
200         1           T2              222 Main St     40          null
200         1           T3              null            40          60

需求说明 #

按Customer、SiteNumber分组，统计每组中Address、ValueA、ValueB的最常见非空值（值无需来自同一行）。若组内存在输入错误或空值，通过多数规则确定合理值。

期望结果 #

Customer    SiteNumber  Address         ValueA      ValueB
100         1           123 Main St     50          60  
100         3           27 Front St     100         60  
200         1           222 Main St     40          60

实现方案 #

方案1：通用窗口函数版（支持MySQL 8+、PostgreSQL、SQL Server等） #

利用窗口函数对每列的非空值统计出现次数，标记出每组内次数最多的记录，最后关联结果：

WITH address_stats AS (
    SELECT
        Customer,
        SiteNumber,
        Address,
        COUNT(*) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY Customer, SiteNumber ORDER BY COUNT(*) DESC) AS rn
    FROM your_table_name
    WHERE Address IS NOT NULL
    GROUP BY Customer, SiteNumber, Address
),
valuea_stats AS (
    SELECT
        Customer,
        SiteNumber,
        ValueA,
        COUNT(*) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY Customer, SiteNumber ORDER BY COUNT(*) DESC) AS rn
    FROM your_table_name
    WHERE ValueA IS NOT NULL
    GROUP BY Customer, SiteNumber, ValueA
),
valueb_stats AS (
    SELECT
        Customer,
        SiteNumber,
        ValueB,
        COUNT(*) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY Customer, SiteNumber ORDER BY COUNT(*) DESC) AS rn
    FROM your_table_name
    WHERE ValueB IS NOT NULL
    GROUP BY Customer, SiteNumber, ValueB
)
SELECT
    COALESCE(a.Customer, va.Customer, vb.Customer) AS Customer,
    COALESCE(a.SiteNumber, va.SiteNumber, vb.SiteNumber) AS SiteNumber,
    a.Address,
    va.ValueA,
    vb.ValueB
FROM address_stats a
FULL JOIN valuea_stats va ON a.Customer = va.Customer AND a.SiteNumber = va.SiteNumber
FULL JOIN valueb_stats vb ON a.Customer = vb.Customer AND a.SiteNumber = vb.SiteNumber
WHERE a.rn = 1 AND va.rn = 1 AND vb.rn = 1;

方案2：MySQL 5.x兼容版（无窗口函数支持） #

通过子查询对每个分组单独获取各列的众数：

SELECT
    t.Customer,
    t.SiteNumber,
    (SELECT Address FROM your_table_name 
     WHERE Customer = t.Customer AND SiteNumber = t.SiteNumber AND Address IS NOT NULL
     GROUP BY Address ORDER BY COUNT(*) DESC LIMIT 1) AS Address,
    (SELECT ValueA FROM your_table_name 
     WHERE Customer = t.Customer AND SiteNumber = t.SiteNumber AND ValueA IS NOT NULL
     GROUP BY ValueA ORDER BY COUNT(*) DESC LIMIT 1) AS ValueA,
    (SELECT ValueB FROM your_table_name 
     WHERE Customer = t.Customer AND SiteNumber = t.SiteNumber AND ValueB IS NOT NULL
     GROUP BY ValueB ORDER BY COUNT(*) DESC LIMIT 1) AS ValueB
FROM (SELECT DISTINCT Customer, SiteNumber FROM your_table_name) t;

说明 #

• 两个方案均会忽略空值，仅统计非空值的出现频率
• 若某列存在多个出现次数相同的众数，方案会返回排序后的第一个值（可根据需求调整排序规则）

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