技术问询：编写查询语句查找月份对应的缺失值

Hey there! Let's work through how to identify missing monthly entries in your table. Since you didn't share your exact table schema, I'll use a common real-world setup as an example—let's say you have a table named monthly_metrics with columns:

• record_year: Integer (e.g., 2023)
• record_month: Integer (1-12)
• metric_value: The value you're tracking (could be sales, active users, etc.)

Basic Query: Single Year, No Grouping #

First, we need to generate a full list of 1-12 months, then compare it to your existing data to spot gaps. We'll use a CTE (Common Table Expression) to create the complete month list:

WITH all_months AS (
    -- Generate every month from 1 to 12
    SELECT 1 AS month UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12
),
target_year AS (
    -- Specify the year you want to check
    SELECT 2023 AS record_year
)
SELECT
    ty.record_year,
    am.month,
    'Missing' AS status
FROM target_year ty
CROSS JOIN all_months am
-- Left join to your table to find unmatched (missing) months
LEFT JOIN monthly_metrics mm
    ON ty.record_year = mm.record_year
    AND am.month = mm.record_month
-- Filter for rows where no match was found
WHERE mm.record_month IS NULL
ORDER BY am.month;

How this works: #

• all_months creates a complete set of 12 months so we don't miss any.
• target_year lets you define which year to audit (replace 2023 with your target year).
• The CROSS JOIN combines the year with every month to get all possible year-month pairs.
• The LEFT JOIN matches these pairs to your actual data, and the WHERE clause picks out pairs that have no matching entry in your table—these are your missing months.

Advanced Query: Multiple Years + Grouped Data #

If your table has grouped segments (like departments, products) and spans multiple years, adjust the query to cover those groups:

WITH all_months AS (
    SELECT 1 AS month UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12
),
target_groups AS (
    -- Get all unique group-year combinations from your table
    SELECT DISTINCT department_id, record_year FROM monthly_metrics
)
SELECT
    tg.department_id,
    tg.record_year,
    am.month,
    'Missing' AS status
FROM target_groups tg
CROSS JOIN all_months am
LEFT JOIN monthly_metrics mm
    ON tg.department_id = mm.department_id
    AND tg.record_year = mm.record_year
    AND am.month = mm.record_month
WHERE mm.record_month IS NULL
ORDER BY tg.department_id, tg.record_year, am.month;

Simplified Syntax for Modern Databases #

If you're using a database that supports sequence generation functions, you can shorten the all_months CTE:

• PostgreSQL: Use generate_series

  WITH all_months AS (
      SELECT generate_series(1, 12) AS month
  )
  

• MySQL 8.0+: Use GENERATE_SERIES

  WITH all_months AS (
      SELECT * FROM GENERATE_SERIES(1, 12) AS month
  )
  

• SQL Server: The initial UNION ALL approach works great, or you can use a recursive CTE for longer date ranges.

Quick Adjustment Tip #

If your table uses a single date column instead of separate year/month fields, extract the year and month using database-specific functions:

• PostgreSQL: EXTRACT(YEAR FROM date_col) / EXTRACT(MONTH FROM date_col)
• MySQL: YEAR(date_col) / MONTH(date_col)
• SQL Server: DATEPART(YEAR, date_col) / DATEPART(MONTH, date_col)

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