如何查询指定日期前某SQL查询无返回结果的最近日期

查询现状 #

我执行以下SQL查询：

select max(date) 
from index_constituents 
where (opening_closing ='O' 
    and index_code ='buk350n' 
    and issuer = 'cboe') and date = '2024-04-25';

对应的表数据如下：

针对2024-04-24执行相同逻辑的查询：

select max(date) 
from index_constituents 
where (opening_closing ='O' 
    and index_code ='buk350n' 
    and issuer = 'cboe') and date = '2024-04-24';

能得到结果：

但查询2024-04-23时：

select max(date) 
from index_constituents 
where (opening_closing ='O' 
    and index_code ='buk350n' 
    and issuer = 'cboe') and date = '2024-04-23';

没有任何结果返回。

核心需求 #

我需要找到指定日期之前，满足上述查询无数据返回的最近日期，比如当前场景下期望返回2024-04-23。

方式1：生成日期序列并验证存在性 #

这种方法适合从某个起始日期到目标日期范围内查找缺失日期的场景，以目标日期2024-04-25为例：

WITH date_range AS (
    -- 生成从最早有数据的日期到目标日期的所有日期
    SELECT generate_series(
        (SELECT MIN(date) FROM index_constituents WHERE issuer='cboe' AND index_code='buk350n' AND opening_closing='O'),
        '2024-04-25'::DATE,
        '1 day'::INTERVAL
    ) AS check_date
)
SELECT check_date::DATE
FROM date_range
-- 筛选出没有对应数据的日期
WHERE NOT EXISTS (
    SELECT 1
    FROM index_constituents
    WHERE date = check_date
        AND opening_closing ='O' 
        AND index_code ='buk350n' 
        AND issuer = 'cboe'
)
-- 按日期倒序取最近的一个
ORDER BY check_date DESC
LIMIT 1;

方式2：基于已有数据的间隙查找 #

如果表中已有连续的有效日期，可通过相邻日期的差值定位缺失日期，无需生成全量日期序列：

WITH ordered_dates AS (
    -- 获取所有有数据的唯一日期并倒序排列
    SELECT DISTINCT date
    FROM index_constituents
    WHERE opening_closing ='O' 
        AND index_code ='buk350n' 
        AND issuer = 'cboe'
    ORDER BY date DESC
),
date_gaps AS (
    -- 对比当前日期和前一个日期的间隔
    SELECT 
        date, 
        LAG(date) OVER (ORDER BY date DESC) AS prev_date
    FROM ordered_dates
)
-- 提取间隔大于1天的缺失日期
SELECT (date + INTERVAL '1 day')::DATE AS missing_date
FROM date_gaps
WHERE prev_date - date > INTERVAL '1 day'
-- 补充处理最早有数据日期之前的缺失情况
UNION ALL
SELECT (MIN(date) - INTERVAL '1 day')::DATE
FROM ordered_dates
WHERE NOT EXISTS (
    SELECT 1
    FROM index_constituents
    WHERE date = (MIN(date) - INTERVAL '1 day')::DATE
        AND opening_closing ='O' 
        AND index_code ='buk350n' 
        AND issuer = 'cboe'
)
-- 取最近的缺失日期
ORDER BY missing_date DESC
LIMIT 1;

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