SQL Server 2019交通出行数据聚合计算方法咨询

所有统计需求都可以通过SQL聚合函数+条件判断完成，完全不需要游标这类低效工具。核心思路是先提取每个CARD_ID的交通模式组合，再结合重复次数分组统计。

一、任务1：按重复次数分组统计占比 #

步骤1：合并所有表并标记重复次数 #

先将拆分的8张表合并，给每张表添加对应的重复次数标记（用8代表≥8的分组）：

WITH all_data AS (
    SELECT CARD_ID, MODE, 1 AS replication_count FROM table_rep1
    UNION ALL
    SELECT CARD_ID, MODE, 2 AS replication_count FROM table_rep2
    UNION ALL
    SELECT CARD_ID, MODE, 3 AS replication_count FROM table_rep3
    UNION ALL
    SELECT CARD_ID, MODE, 4 AS replication_count FROM table_rep4
    UNION ALL
    SELECT CARD_ID, MODE, 5 AS replication_count FROM table_rep5
    UNION ALL
    SELECT CARD_ID, MODE, 6 AS replication_count FROM table_rep6
    UNION ALL
    SELECT CARD_ID, MODE, 7 AS replication_count FROM table_rep7
    UNION ALL
    SELECT CARD_ID, MODE, 8 AS replication_count FROM table_rep8 -- 代表≥8次
),

步骤2：提取每个CARD_ID的交通模式组合 #

对每个CARD_ID去重，判断其使用的交通模式，生成组合标签：

card_mode_groups AS (
    SELECT
        CARD_ID,
        replication_count,
        -- 按T/S/B的存在情况生成组合标签
        CONCAT(
            CASE WHEN COUNT(DISTINCT CASE WHEN MODE = 'TRAIN' THEN 1 END) > 0 THEN 'T' ELSE '' END,
            CASE WHEN COUNT(DISTINCT CASE WHEN MODE = 'SUBWAY' THEN 1 END) > 0 THEN '-S' ELSE '' END,
            CASE WHEN COUNT(DISTINCT CASE WHEN MODE = 'BUS' THEN 1 END) > 0 THEN '-B' ELSE '' END
        ) AS mode_combination
    FROM all_data
    GROUP BY CARD_ID, replication_count
),

步骤3：统计分组占比 #

计算每个重复次数分组的记录数、CARD_ID数，以及各模式组合的占比：

rep_group_base AS (
    SELECT
        replication_count,
        COUNT(*) AS total_cards,
        (SELECT COUNT(*) FROM all_data WHERE replication_count = rg.replication_count) AS total_records
    FROM card_mode_groups rg
    GROUP BY replication_count
)
SELECT
    CASE WHEN replication_count = 8 THEN '≥8' ELSE CAST(replication_count AS VARCHAR) END AS "# of replications",
    total_records AS "# of records",
    total_cards AS "# of CARD_IDs",
    -- 计算各组合占比，无法出现的组合显示N/A
    CASE WHEN replication_count < 2 THEN 'N/A' ELSE ROUND(100.0 * SUM(CASE WHEN mode_combination = 'T' THEN 1 ELSE 0 END)/total_cards, 2) END AS "T",
    CASE WHEN replication_count < 2 THEN 'N/A' ELSE ROUND(100.0 * SUM(CASE WHEN mode_combination = 'S' THEN 1 ELSE 0 END)/total_cards, 2) END AS "S",
    CASE WHEN replication_count < 2 THEN 'N/A' ELSE ROUND(100.0 * SUM(CASE WHEN mode_combination = 'B' THEN 1 ELSE 0 END)/total_cards, 2) END AS "B",
    CASE WHEN replication_count < 2 THEN 'N/A' ELSE ROUND(100.0 * SUM(CASE WHEN mode_combination = 'T-S' THEN 1 ELSE 0 END)/total_cards, 2) END AS "T-S",
    CASE WHEN replication_count < 2 THEN 'N/A' ELSE ROUND(100.0 * SUM(CASE WHEN mode_combination = 'T-B' THEN 1 ELSE 0 END)/total_cards, 2) END AS "T-B",
    CASE WHEN replication_count < 2 THEN 'N/A' ELSE ROUND(100.0 * SUM(CASE WHEN mode_combination = 'S-B' THEN 1 ELSE 0 END)/total_cards, 2) END AS "S-B",
    CASE WHEN replication_count < 3 THEN 'N/A' ELSE ROUND(100.0 * SUM(CASE WHEN mode_combination = 'T-S-B' THEN 1 ELSE 0 END)/total_cards, 2) END AS "T-S-B"
FROM card_mode_groups cmg
JOIN rep_group_base rgb ON cmg.replication_count = rgb.replication_count
GROUP BY cmg.replication_count, total_records, total_cards
ORDER BY cmg.replication_count;

二、任务2：统计所有CARD_ID的模式组合总数 #

基于同样的合并逻辑，直接统计全量CARD_ID的各组合数量：

WITH all_data AS (
    -- 同任务1的合并逻辑
    SELECT CARD_ID, MODE, 1 AS replication_count FROM table_rep1
    UNION ALL
    SELECT CARD_ID, MODE, 2 AS replication_count FROM table_rep2
    UNION ALL
    SELECT CARD_ID, MODE, 3 AS replication_count FROM table_rep3
    UNION ALL
    SELECT CARD_ID, MODE, 4 AS replication_count FROM table_rep4
    UNION ALL
    SELECT CARD_ID, MODE, 5 AS replication_count FROM table_rep5
    UNION ALL
    SELECT CARD_ID, MODE, 6 AS replication_count FROM table_rep6
    UNION ALL
    SELECT CARD_ID, MODE, 7 AS replication_count FROM table_rep7
    UNION ALL
    SELECT CARD_ID, MODE, 8 AS replication_count FROM table_rep8
),
card_mode_groups AS (
    SELECT
        CARD_ID,
        CONCAT(
            CASE WHEN COUNT(DISTINCT CASE WHEN MODE = 'TRAIN' THEN 1 END) > 0 THEN 'T' ELSE '' END,
            CASE WHEN COUNT(DISTINCT CASE WHEN MODE = 'SUBWAY' THEN 1 END) > 0 THEN '-S' ELSE '' END,
            CASE WHEN COUNT(DISTINCT CASE WHEN MODE = 'BUS' THEN 1 END) > 0 THEN '-B' ELSE '' END
        ) AS mode_combination
    FROM all_data
    GROUP BY CARD_ID
)
SELECT
    COUNT(*) AS "Total CARD_IDs",
    SUM(CASE WHEN mode_combination = 'T' THEN 1 ELSE 0 END) AS "# of T",
    SUM(CASE WHEN mode_combination = 'S' THEN 1 ELSE 0 END) AS "# of S",
    SUM(CASE WHEN mode_combination = 'B' THEN 1 ELSE 0 END) AS "# of B",
    SUM(CASE WHEN mode_combination = 'T-S' THEN 1 ELSE 0 END) AS "# of T-S",
    SUM(CASE WHEN mode_combination = 'T-B' THEN 1 ELSE 0 END) AS "# of T-B",
    SUM(CASE WHEN mode_combination = 'S-B' THEN 1 ELSE 0 END) AS "# of S-B",
    SUM(CASE WHEN mode_combination = 'T-S-B' THEN 1 ELSE 0 END) AS "# of T-S-B"
FROM card_mode_groups;

关键说明 #

1. 无需游标：所有统计通过SQL聚合逻辑完成，效率远高于游标遍历；
2. 数据合并：用UNION ALL合并拆分的表，避免重复编写逻辑；
3. 性能优化：如果数据量极大，可先对每张表单独生成CARD_ID的模式组合，再合并统计，减少中间数据量。

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