如何编写条件化SQL查询，按用户获取指定时段的营收数据

问题描述 #

我有一张包含user_id、revenue、publish_month字段的表，目前通过GROUP BY user_id和SUM(revenue)统计单个用户的总营收。现在需要用单条SQL实现条件化的时段用户营收查询：

• 如果某用户存在当前月份的publish_month数据，就查询该用户当前月、上月、上上月的营收；
• 如果该用户没有当前月份的数据，则查询该用户上月、上上月、上上上月的营收。

补充示例：

• 示例1（用户无当月数据）：假设当前月是2024-05，用户33没有2024-05的数据，最终返回该用户2024-04、2024-03、2024-02的营收记录；
• 示例2（用户有当月数据）：用户有2024-05的数据，最终返回该用户2024-05、2024-04、2024-03的营收记录。

解决方案 #

可以通过子查询+条件筛选实现单条SQL查询，核心思路是先判断每个用户是否存在当月数据，再动态确定统计的月份范围，最后关联原表完成统计。以下是通用SQL实现（以PostgreSQL为例，其他数据库可调整日期函数）：

WITH user_month_check AS (
    -- 标记每个用户是否有当月数据
    SELECT 
        user_id,
        EXISTS (
            SELECT 1 
            FROM your_table t2 
            WHERE t2.user_id = t1.user_id 
              AND t2.publish_month = TO_CHAR(CURRENT_DATE, 'YYYY-MM')
        ) AS has_current_month_data
    FROM your_table t1
    GROUP BY user_id
),
target_months AS (
    -- 根据标记生成每个用户需要统计的三个月份
    SELECT 
        umc.user_id,
        UNNEST(
            CASE 
                WHEN umc.has_current_month_data THEN ARRAY[
                    TO_CHAR(CURRENT_DATE, 'YYYY-MM'),
                    TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYY-MM'),
                    TO_CHAR(CURRENT_DATE - INTERVAL '2 month', 'YYYY-MM')
                ]
                ELSE ARRAY[
                    TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYY-MM'),
                    TO_CHAR(CURRENT_DATE - INTERVAL '2 month', 'YYYY-MM'),
                    TO_CHAR(CURRENT_DATE - INTERVAL '3 month', 'YYYY-MM')
                ]
            END
        ) AS target_month
    FROM user_month_check umc
)
-- 关联原表统计目标月份营收
SELECT 
    tm.user_id,
    tm.target_month AS publish_month,
    COALESCE(SUM(t.revenue), 0) AS revenue
FROM target_months tm
LEFT JOIN your_table t 
    ON tm.user_id = t.user_id 
    AND tm.target_month = t.publish_month
GROUP BY tm.user_id, tm.target_month
ORDER BY tm.user_id, tm.target_month DESC;

逻辑说明 #

1. user_month_check CTE：通过EXISTS子查询判断每个用户是否存在当前月份的数据，生成标记字段has_current_month_data；
2. target_months CTE：根据标记动态生成每个用户需要统计的三个月份，用UNNEST将数组拆分为行，方便后续关联；
3. 主查询：左关联原表统计每个用户在目标月份的营收，COALESCE确保无数据的月份显示0而非NULL。

不同数据库适配调整 #

• MySQL：无UNNEST函数，改用UNION ALL生成目标月份，日期函数替换为DATE_FORMAT和DATE_SUB：

  -- 替换target_months的示例
  SELECT user_id, DATE_FORMAT(CURDATE(), '%Y-%m') AS target_month FROM user_month_check WHERE has_current_month_data
  UNION ALL
  SELECT user_id, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') FROM user_month_check
  UNION ALL
  SELECT user_id, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m') FROM user_month_check WHERE has_current_month_data
  UNION ALL
  SELECT user_id, DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m') FROM user_month_check WHERE NOT has_current_month_data
  

• SQL Server：使用VALUES子句生成目标月份，日期函数替换为FORMAT和DATEADD。

简化需求（仅统计总营收） #

如果不需要按月份拆分，只需要三个月份的总营收，可简化主查询：

SELECT 
    tm.user_id,
    COALESCE(SUM(t.revenue), 0) AS total_revenue
FROM target_months tm
LEFT JOIN your_table t 
    ON tm.user_id = t.user_id 
    AND tm.target_month = t.publish_month
GROUP BY tm.user_id;

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