ClickHouse表缺失日期填充与累计乘积计算实现方案咨询

步骤1：补全每个ISIN的连续日期序列 #

首先需要确定每个ISIN的日期范围（最小到最大日期），生成该范围内的所有连续日期，以此补全原始表中缺失的日期记录。

WITH isin_date_ranges AS (
    SELECT
        ISIN,
        min(Date) AS min_date,
        max(Date) AS max_date
    FROM your_table_name
    GROUP BY ISIN
),
continuous_dates AS (
    SELECT
        r.ISIN,
        d.Date
    FROM isin_date_ranges r
    INNER JOIN (
        SELECT arrayJoin(sequence(min_date, max_date, INTERVAL 1 DAY)) AS Date
        FROM isin_date_ranges
    ) d ON d.Date BETWEEN r.min_date AND r.max_date
    GROUP BY r.ISIN, d.Date
)

步骤2：填充缺失日期的AdjCoef值 #

将连续日期表与原始表关联，使用LAST_VALUE窗口函数并配合IGNORE NULLS参数，按日期倒序的窗口取当前缺失日期之后的首个非空AdjCoef值，完成填充。

WITH isin_date_ranges AS (
    SELECT
        ISIN,
        min(Date) AS min_date,
        max(Date) AS max_date
    FROM your_table_name
    GROUP BY ISIN
),
continuous_dates AS (
    SELECT
        r.ISIN,
        d.Date
    FROM isin_date_ranges r
    INNER JOIN (
        SELECT arrayJoin(sequence(min_date, max_date, INTERVAL 1 DAY)) AS Date
        FROM isin_date_ranges
    ) d ON d.Date BETWEEN r.min_date AND r.max_date
    GROUP BY r.ISIN, d.Date
),
filled_data AS (
    SELECT
        cd.ISIN,
        cd.Date,
        LAST_VALUE(t.AdjCoef) OVER (
            PARTITION BY cd.ISIN
            ORDER BY cd.Date DESC
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        ) AS filled_AdjCoef
    FROM continuous_dates cd
    LEFT JOIN your_table_name t ON cd.ISIN = t.ISIN AND cd.Date = t.Date
)

步骤3：计算每个ISIN的累计乘积 #

使用ClickHouse的cumProd函数（版本20.12及以上支持），按ISIN分组、日期正序计算填充后AdjCoef的累计乘积。若版本较低，可使用exp(sum(log(filled_AdjCoef)))替代。

完整SQL代码 #

WITH isin_date_ranges AS (
    SELECT
        ISIN,
        min(Date) AS min_date,
        max(Date) AS max_date
    FROM your_table_name
    GROUP BY ISIN
),
continuous_dates AS (
    SELECT
        r.ISIN,
        d.Date
    FROM isin_date_ranges r
    INNER JOIN (
        SELECT arrayJoin(sequence(min_date, max_date, INTERVAL 1 DAY)) AS Date
        FROM isin_date_ranges
    ) d ON d.Date BETWEEN r.min_date AND r.max_date
    GROUP BY r.ISIN, d.Date
),
filled_data AS (
    SELECT
        cd.ISIN,
        cd.Date,
        LAST_VALUE(t.AdjCoef) OVER (
            PARTITION BY cd.ISIN
            ORDER BY cd.Date DESC
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        ) AS filled_AdjCoef
    FROM continuous_dates cd
    LEFT JOIN your_table_name t ON cd.ISIN = t.ISIN AND cd.Date = t.Date
)
SELECT
    ISIN,
    Date,
    filled_AdjCoef,
    cumProd(filled_AdjCoef) OVER (
        PARTITION BY ISIN
        ORDER BY Date ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_product
FROM filled_data
ORDER BY ISIN, Date;

特殊情况处理 #

如果AdjCoef存在0或负数，使用log计算时会报错，可修改累计乘积部分为：

exp(sum(if(filled_AdjCoef > 0, log(filled_AdjCoef), 0))) OVER (
    PARTITION BY ISIN
    ORDER BY Date ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_product

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