ClickHouse生成日期范围内每月最后日期序列的查询问题

需要生成2020-06-24 23:00:00到2022-10-12 23:59:59范围内每个月最后日期的记录序列，由于所用ClickHouse版本不支持WITH FILL，编写的查询出现笛卡尔积问题，导致跨年后年份与月份不匹配（比如2021年1-3月的年份显示为2020），需修正年月对应关系。

原查询代码 #

SELECT id,
        start_date,
        mod_end_date,
        mod_end_month,
        arrayJoin(arr_year_no)  as sep_year,
        if(arrayJoin(arr_month_no) % 12 = 0,12, arrayJoin(arr_month_no) % 12)
                              as sep_month
from
(
    select id, 
        startdatetime                        as start_date,
        enddatetime                          as end_date,
       if(toDate(enddatetime) < toDate('2022-10-12 23:59:59'),      -- Input 2
                ifNull(toDateTime(enddatetime), today()),
                        toDateTime('2022-10-12 23:59:59'))  -- Input 2
                                             as mod_end_date,
        toMonth(mod_end_date)                as mod_end_month,
        toYear(mod_end_date)                 as mod_end_year,
        range(
                toUInt32(toYear(ifNull(start_date, today()))),
                toUInt32(mod_end_year + 1))  as arr_year_no,
        range(
                toUInt32(toMonth(ifNull(start_date, today()))),
                toUInt32(date_diff(month,start_date, mod_end_date) + 1)) as arr_month_no
    from table1
    WHERE toDate(startdatetime) between toDate('2020-06-24 23:00:00') -- Input 1
        AND toDate('2022-10-12 23:59:59')                          -- Input 2
        AND id = 1307
) tbl1

当前错误结果示例 #

注：最后3条记录的sep_year应为2021而非2020。

解决方案 #

原问题核心是两个独立的arrayJoin执行后产生笛卡尔积，正确做法是生成从起始月到结束月的连续月份序列，再从中提取对应年月。

修正后的查询 #

SELECT 
    id,
    start_date,
    mod_end_date,
    toYear(month_date) AS sep_year,
    toMonth(month_date) AS sep_month,
    lastDayOfMonth(month_date) AS month_last_date
FROM (
    SELECT 
        id,
        startdatetime AS start_date,
        enddatetime AS end_date,
        mod_end_date,
        -- 生成从起始月第一天到结束月第一天的连续月份序列
        arrayMap(
            i -> addMonths(toDate(start_date), i),
            range(0, dateDiff('month', toDate(start_date), mod_end_date) + 1)
        ) AS month_dates
    FROM (
        SELECT 
            id,
            startdatetime,
            enddatetime,
            -- 确定实际的结束日期
            if(
                toDate(enddatetime) < toDate('2022-10-12 23:59:59'),
                ifNull(toDateTime(enddatetime), today()),
                toDateTime('2022-10-12 23:59:59')
            ) AS mod_end_date
        FROM table1
        WHERE 
            toDate(startdatetime) BETWEEN toDate('2020-06-24 23:00:00') 
            AND toDate('2022-10-12 23:59:59')
            AND id = 1307
    )
)
ARRAY JOIN month_dates AS month_date

说明 #

1. 内层子查询先确定实际结束日期mod_end_date，逻辑与原查询一致。
2. 中间层通过dateDiff计算起始到结束的总月数，用range生成索引数组，再通过arrayMap和addMonths生成每个月第一天的连续日期序列。
3. 最后用ARRAY JOIN展开日期序列，提取年份、月份，并用lastDayOfMonth获取每月最后日期，确保年月一一对应，避免笛卡尔积问题。

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