Hive查询生成缺失日期遇问题：需生成近1000天缺失日期

Got it, let's break down how to track down those missing dates. The core idea is straightforward: first generate the full sequence of dates from 1000 days before today up to yesterday, then compare that complete list against the dates already present in table1 to spot the gaps. Below are tailored solutions for the most common databases:

MySQL 8.0+ (Using Recursive CTE) #

MySQL 8.0 and newer support recursive Common Table Expressions (CTEs), which make generating date ranges a breeze:

WITH RECURSIVE date_range AS (
    -- Start with the date 1000 days prior to today
    SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1000 DAY) AS date
    UNION ALL
    -- Add one day at a time until we reach yesterday
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)
-- Left join with table1 to find dates that don't exist in the table
SELECT dr.date AS missing_date
FROM date_range dr
LEFT JOIN table1 t ON dr.date = t.date
WHERE t.date IS NULL
ORDER BY dr.date;

MySQL 5.x (Without Recursive CTE Support) #

If you're working with an older MySQL version, use a generated number list to build your date range:

SELECT 
    DATE_SUB(CURRENT_DATE(), INTERVAL (1000 - n) DAY) AS missing_date
FROM (
    -- Generate numbers from 1 to 1000
    SELECT @row := @row + 1 AS n
    FROM information_schema.tables, (SELECT @row := 0) r
    LIMIT 1000
) numbers
-- Match against existing dates in table1
LEFT JOIN table1 t ON DATE_SUB(CURRENT_DATE(), INTERVAL (1000 - n) DAY) = t.date
WHERE t.date IS NULL
ORDER BY missing_date;

PostgreSQL #

PostgreSQL has a built-in generate_series function that simplifies date range creation. You can use either EXCEPT or a left join:

Using EXCEPT #

SELECT generate_series(
    CURRENT_DATE - INTERVAL '1000 days',
    CURRENT_DATE - INTERVAL '1 day',
    INTERVAL '1 day'
)::DATE AS missing_date
EXCEPT
-- Get all existing dates in the target range from table1
SELECT date FROM table1
WHERE date >= CURRENT_DATE - INTERVAL '1000 days' AND date < CURRENT_DATE
ORDER BY missing_date;

Using Left Join #

SELECT dr.date AS missing_date
FROM generate_series(
    CURRENT_DATE - INTERVAL '1000 days',
    CURRENT_DATE - INTERVAL '1 day',
    INTERVAL '1 day'
)::DATE dr(date)
LEFT JOIN table1 t ON dr.date = t.date
WHERE t.date IS NULL
ORDER BY dr.date;

SQL Server #

For SQL Server, use a recursive CTE with the MAXRECURSION option (since we're recursing 1000 times):

WITH date_range AS (
    -- Start date: 1000 days before today
    SELECT DATEADD(DAY, -1000, CAST(GETDATE() AS DATE)) AS date
    UNION ALL
    -- Increment by 1 day until yesterday
    SELECT DATEADD(DAY, 1, date)
    FROM date_range
    WHERE date < DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
)
SELECT dr.date AS missing_date
FROM date_range dr
LEFT JOIN table1 t ON dr.date = t.date
WHERE t.date IS NULL
ORDER BY dr.date
OPTION (MAXRECURSION 1000); -- Required to allow 1000 recursive steps

All these solutions will return exactly the dates in your 1000-day window that are missing from table1.

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