如何使用SQL查找input_table中用户缺失的季度（Q1-Q4）数据

要找出所有用户每年缺失的Q1-Q4季度数据，核心思路是生成所有应存在的用户-年份-季度组合，再与原表比对筛选出不存在的记录。以下是具体实现方案：

通用逻辑步骤 #

1. 生成包含Q1到Q4的季度维度列表
2. 提取原表中每个用户的所有数据年份（或指定目标年份）
3. 交叉连接用户、年份、季度，得到所有预期的组合
4. 通过左连接原表，筛选出原表中无匹配的记录，即为缺失的季度

MySQL 实现代码 #

WITH quarters AS (
    SELECT 'Q1' AS qtr UNION ALL
    SELECT 'Q2' UNION ALL
    SELECT 'Q3' UNION ALL
    SELECT 'Q4'
),
user_years AS (
    -- 提取每个用户的所有数据年份
    SELECT DISTINCT name, YEAR(Date) AS year
    FROM input_table
)
SELECT 
    uy.name, 
    uy.year, 
    q.qtr AS missing_quarter
FROM user_years uy
CROSS JOIN quarters q
LEFT JOIN input_table it
    ON uy.name = it.name
    AND uy.year = YEAR(it.Date)
    AND q.qtr = it.quarter
WHERE it.quarter IS NULL  -- 筛选原表中不存在的组合
ORDER BY uy.name, uy.year, q.qtr;

Oracle 实现代码 #

WITH quarters AS (
    SELECT 'Q1' AS qtr FROM DUAL UNION ALL
    SELECT 'Q2' FROM DUAL UNION ALL
    SELECT 'Q3' FROM DUAL UNION ALL
    SELECT 'Q4' FROM DUAL
),
user_years AS (
    SELECT DISTINCT name, EXTRACT(YEAR FROM Date) AS year
    FROM input_table
)
SELECT 
    uy.name, 
    uy.year, 
    q.qtr AS missing_quarter
FROM user_years uy
CROSS JOIN quarters q
LEFT JOIN input_table it
    ON uy.name = it.name
    AND uy.year = EXTRACT(YEAR FROM it.Date)
    AND q.qtr = it.quarter
WHERE it.quarter IS NULL
ORDER BY uy.name, uy.year, q.qtr;

针对特定年份的查询（以2022年为例） #

如果只需要检查某一年（比如问题中提到的2022年）的缺失情况，可以简化代码：

WITH quarters AS (
    SELECT 'Q1' AS qtr UNION ALL
    SELECT 'Q2' UNION ALL
    SELECT 'Q3' UNION ALL
    SELECT 'Q4'
),
user_list AS (
    SELECT DISTINCT name FROM input_table
)
SELECT 
    ul.name, 
    2022 AS year, 
    q.qtr AS missing_quarter
FROM user_list ul
CROSS JOIN quarters q
LEFT JOIN input_table it
    ON ul.name = it.name
    AND YEAR(it.Date) = 2022
    AND q.qtr = it.quarter
WHERE it.quarter IS NULL
ORDER BY ul.name, q.qtr;

这段代码会返回2022年所有用户缺失的季度，比如Ram的2022年Q4会被明确列出。

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