月度薪资区间用户流转分析SQL实现优化技术问询

现有两张月度数据表（12月表Dec、1月表Jan），仅包含user_id和salary_range字段（薪资区间如0-5000、5000-10000等），需分析用户在不同薪资区间的流转情况，包括区间迁移、流失用户（left_clients）、新增用户（new_clients）。

示例数据 #

12月表（Dec） #

1月表（Jan） #

期望输出 #

原有实现问题 #

自行编写的SQL代码冗余度极高，通过重复UNION和大量CASE WHEN语句实现，维护成本高，扩展性差（新增薪资区间需修改大量代码）。原有代码如下：

SELECT '0-5K' AS RANGE,
  SUM(CASE WHEN oct.N_1 = nov.N_1 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '0-5K'N, 
  SUM(CASE WHEN oct.N_1 = nov.N_2 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '5K-10K'N, 
  SUM(CASE WHEN oct.N_1 = nov.N_3 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '10K-15K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_4 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '15K-20K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_5 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '20K-25K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_6 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '25K-30K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_7 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '30K-35K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_8 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '35K-40K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_9 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '40K-45K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_10 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '45K-50K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_11 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '50K-55K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_12 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '55K-60K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_13 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '60K-65K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_14 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '65K-70K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_15 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '70K-100K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_16 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '100K-150K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_17 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '150K-200K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_18 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '200K-250K'N,
  SUM(CASE WHEN oct.N_1 = nov.N_19 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS '+200'N,
  SUM(CASE WHEN oct.N_1 = 1 AND nov.N_1 IS NULL THEN 1 ELSE 0 END) / &USERS_A_C AS LEAVING,
  SUM(CASE WHEN oct.N_1 IS NULL AND nov.N_1 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS new 
  FROM october_clients AS oct 
  FULL JOIN november_clients AS nov 
  ON oct.user_id = nov.user_id 
  UNION SELECT '5-10K' AS RANGE,
  SUM(CASE WHEN oct.N_2 = nov.N_1 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '0-5K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_2 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '5K-10K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_3 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '10K-15K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_4 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '15K-20K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_5 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '20K-25K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_6 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '25K-30K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_7 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '30K-35K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_8 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '35K-40K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_9 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '40K-45K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_10 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '45K-50K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_11 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '50K-55K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_12 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '55K-60K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_13 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '60K-65K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_14 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '65K-70K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_15 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '70K-100K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_16 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '100K-150K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_17 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '150K-200K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_18 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '200K-250K'N,
  SUM(CASE WHEN oct.N_2 = nov.N_19 = 1 THEN 1 ELSE 0 END) / &USERS_B_C AS '+200'N,
  SUM(CASE WHEN oct.N_2 = 1 AND nov.N_2 IS NULL THEN 1 ELSE 0 END) / &USERS_B_C AS LEAVING,
  SUM(CASE WHEN oct.N_2 IS NULL AND nov.N_2 = 1 THEN 1 ELSE 0 END) / &USERS_A_C AS new 
  FROM october_clients AS oct 
  FULL JOIN november_clients AS nov 
  ON oct.user_id = nov.user_id;

优化后的实现方案 #

核心思路 #

1. 用FULL JOIN关联两个月的数据，标记用户的流失/新增状态，统计每个原区间到目标区间的用户数。
2. 生成全量薪资区间维度表，确保输出包含所有区间，无数据时显示0值。
3. 通过条件聚合或关联查询实现透视统计，计算各区间流转占比、流失占比和新增占比。

具体SQL代码（兼容主流数据库） #

-- 1. 定义所有薪资区间（可根据实际情况扩展）
WITH all_ranges AS (
    SELECT '0-5k' AS range UNION ALL
    SELECT '5k-10k' AS range UNION ALL
    SELECT '10k-15k' AS range UNION ALL
    SELECT '15k-20k' AS range UNION ALL
    SELECT '20k-25k' AS range UNION ALL
    SELECT '25k-30k' AS range UNION ALL
    SELECT '30k-35k' AS range UNION ALL
    SELECT '35k-40k' AS range UNION ALL
    SELECT '40k-45k' AS range UNION ALL
    SELECT '45k-50k' AS range UNION ALL
    SELECT '50k-55k' AS range UNION ALL
    SELECT '55k-60k' AS range UNION ALL
    SELECT '60k-65k' AS range UNION ALL
    SELECT '65k-70k' AS range UNION ALL
    SELECT '70k-100k' AS range UNION ALL
    SELECT '100k-150k' AS range UNION ALL
    SELECT '150k-200k' AS range UNION ALL
    SELECT '200k-250k' AS range UNION ALL
    SELECT '+200k' AS range
),
-- 2. 关联两个月数据，获取用户流转情况
user_flow AS (
    SELECT
        COALESCE(d.salary_range, '新增') AS original_range,
        COALESCE(j.salary_range, '流失') AS target_range,
        COUNT(DISTINCT COALESCE(d.user_id, j.user_id)) AS user_count
    FROM Dec d
    FULL JOIN Jan j ON d.user_id = j.user_id
    GROUP BY original_range, target_range
),
-- 3. 计算每个原区间的总用户数
total_users AS (
    SELECT
        original_range,
        SUM(user_count) AS total
    FROM user_flow
    WHERE original_range != '新增'
    GROUP BY original_range
),
-- 4. 计算新增用户总数（用于占比计算）
new_total AS (
    SELECT SUM(user_count) AS total FROM user_flow WHERE original_range = '新增'
)
-- 5. 生成最终透视表
SELECT
    ar.range AS "range（原区间）",
    -- 计算各目标区间的占比
    CONCAT(ROUND(COALESCE(uf1.user_count / tu.total * 100, 0), 0), '%') AS "0-5k",
    CONCAT(ROUND(COALESCE(uf2.user_count / tu.total * 100, 0), 0), '%') AS "5k-10k",
    CONCAT(ROUND(COALESCE(uf3.user_count / tu.total * 100, 0), 0), '%') AS "10k-15k",
    CONCAT(ROUND(COALESCE(uf4.user_count / tu.total * 100, 0), 0), '%') AS "15k-20k",
    CONCAT(ROUND(COALESCE(uf5.user_count / tu.total * 100, 0), 0), '%') AS "20k-25k",
    CONCAT(ROUND(COALESCE(uf6.user_count / tu.total * 100, 0), 0), '%') AS "25k-30k",
    CONCAT(ROUND(COALESCE(uf7.user_count / tu.total * 100, 0), 0), '%') AS "30k-35k",
    CONCAT(ROUND(COALESCE(uf8.user_count / tu.total * 100, 0), 0), '%') AS "35k-40k",
    CONCAT(ROUND(COALESCE(uf9.user_count / tu.total * 100, 0), 0), '%') AS "40k-45k",
    CONCAT(ROUND(COALESCE(uf10.user_count / tu.total * 100, 0), 0), '%') AS "45k-50k",
    CONCAT(ROUND(COALESCE(uf11.user_count / tu.total * 100, 0), 0), '%') AS "50k-55k",
    CONCAT(ROUND(COALESCE(uf12.user_count / tu.total * 100, 0), 0), '%') AS "55k-60k",
    CONCAT(ROUND(COALESCE(uf13.user_count / tu.total * 100, 0), 0), '%') AS "60k-65k",
    CONCAT(ROUND(COALESCE(uf14.user_count / tu.total * 100, 0), 0), '%') AS "65k-70k",
    CONCAT(ROUND(COALESCE(uf15.user_count / tu.total * 100, 0), 0), '%') AS "70k-100k",
    CONCAT(ROUND(COALESCE(uf16.user_count / tu.total * 100, 0), 0), '%') AS "100k-150k",
    CONCAT(ROUND(COALESCE(uf17.user_count / tu.total * 100, 0), 0), '%') AS "150k-200k",
    CONCAT(ROUND(COALESCE(uf18.user_count / tu.total * 100, 0), 0), '%') AS "200k-250k",
    CONCAT(ROUND(COALESCE(uf19.user_count / tu.total * 100, 0), 0), '%') AS "+200k",
    -- 计算流失用户占比
    CONCAT(ROUND(COALESCE(uf_left.user_count / tu.total * 100, 0),