如何编写SQL查询获取小于各季度首日的最大可用日期

核心思路 #

先生成数据覆盖范围内的所有季度首日，再对每个季度首日，查询days表中小于该日期的最大oper_day值。由于oper_day是dd.mm.yyyy格式的字符串，需要先转换为日期类型进行比较，避免字符串排序的逻辑错误。

以下是几种主流数据库的实现语句：

MySQL #

WITH quarters AS (
    -- 生成数据覆盖的所有季度首日
    SELECT 
        DATE_FORMAT(
            STR_TO_DATE(CONCAT(year_num, '-', (q*3-2), '-01'), '%Y-%m-%d'),
            '%d.%m.%Y'
        ) AS quarter_start
    FROM (
        -- 提取days表中的所有年份，结合4个季度
        SELECT DISTINCT YEAR(STR_TO_DATE(oper_day, '%d.%m.%Y')) AS year_num
        FROM days
    ) AS years
    CROSS JOIN (SELECT 1 AS q UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS quarters
    -- 过滤掉超出数据最大日期的季度
    WHERE STR_TO_DATE(CONCAT(year_num, '-', (q*3-2), '-01'), '%Y-%m-%d') <= (SELECT MAX(STR_TO_DATE(oper_day, '%d.%m.%Y')) FROM days)
)
-- 关联查询每个季度对应的最大前置日期
SELECT 
    q.quarter_start,
    MAX(d.oper_day) AS max_date_before_quarter
FROM quarters q
LEFT JOIN days d 
    ON STR_TO_DATE(d.oper_day, '%d.%m.%Y') < STR_TO_DATE(q.quarter_start, '%d.%m.%Y')
GROUP BY q.quarter_start
ORDER BY STR_TO_DATE(q.quarter_start, '%d.%m.%Y');

PostgreSQL #

WITH quarters AS (
    -- 生成数据覆盖的所有季度首日
    SELECT 
        TO_CHAR(DATE_TRUNC('quarter', date_range), 'DD.MM.YYYY') AS quarter_start
    FROM (
        -- 按季度生成连续日期序列
        SELECT GENERATE_SERIES(
            DATE_TRUNC('year', MIN(TO_DATE(oper_day, 'DD.MM.YYYY'))),
            MAX(TO_DATE(oper_day, 'DD.MM.YYYY')),
            '3 months'::interval
        ) AS date_range
    ) AS q_dates
)
-- 关联查询每个季度对应的最大前置日期
SELECT 
    q.quarter_start,
    MAX(d.oper_day) AS max_date_before_quarter
FROM quarters q
LEFT JOIN days d 
    ON TO_DATE(d.oper_day, 'DD.MM.YYYY') < TO_DATE(q.quarter_start, 'DD.MM.YYYY')
GROUP BY q.quarter_start
ORDER BY TO_DATE(q.quarter_start, 'DD.MM.YYYY');

SQL Server #

WITH quarters AS (
    -- 生成数据覆盖的所有季度首日
    SELECT 
        FORMAT(DATEADD(QUARTER, q_num, DATEFROMPARTS(year_num, 1, 1)), 'dd.MM.yyyy') AS quarter_start
    FROM (
        -- 提取days表中的所有年份
        SELECT DISTINCT YEAR(CONVERT(date, oper_day, 104)) AS year_num
        FROM days
    ) AS years
    CROSS JOIN (VALUES(0),(1),(2),(3)) AS quarters(q_num)
    -- 过滤掉超出数据最大日期的季度
    WHERE DATEADD(QUARTER, q_num, DATEFROMPARTS(year_num, 1, 1)) <= (SELECT MAX(CONVERT(date, oper_day, 104)) FROM days)
)
-- 关联查询每个季度对应的最大前置日期
SELECT 
    q.quarter_start,
    MAX(d.oper_day) AS max_date_before_quarter
FROM quarters q
LEFT JOIN days d 
    ON CONVERT(date, d.oper_day, 104) < CONVERT(date, q.quarter_start, 104)
GROUP BY q.quarter_start
ORDER BY CONVERT(date, q.quarter_start, 104);

说明 #

• 如果某季度首日之前没有可用日期（比如第一个季度的首日是01.01.2021，而表中最小日期也是该值），max_date_before_quarter会返回NULL，可根据需求用COALESCE函数替换为默认值。
• 所有语句都先将字符串格式的日期转换为数据库原生日期类型进行比较，确保逻辑正确。

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