ClickHouse SQL: 如何获取每日数据并处理缺失值

您可以使用一个日期范围表进行左连接，然后将结果中的 NULL 替换为 0 来实现每天都有数据的效果。具体 SQL 示例如下：

WITH dates AS ( SELECT toDate('#monthly_active_new_business.start#') AS date UNION ALL SELECT date + 1 FROM dates WHERE date < toDate('#monthly_active_new_business.end#') )

SELECT dates.date AS ts, if(i >= 31, arraySum(arraySlice(total_increase, i-30, 31)), arraySum(arraySlice(total_increase, 1, i))) AS total_increase_num FROM dates LEFT JOIN ( SELECT groupArray(Ds) AS t, groupArray(app_count) AS total_increase FROM ( SELECT min_Ds AS Ds, count(distinct AppIdentifier) AS app_count FROM ( SELECT AppIdentifier, min(Ds) AS min_Ds FROM log_iMonkey_iOS_overview GROUP BY AppIdentifier ) GROUP BY min_Ds ORDER BY min_Ds ) ) ARRAY ON 1 = 1 LEFT JOIN arrayEnumerate(t) AS i ON 1 = 1 OR dates.date >= t[1] WHERE dates.date BETWEEN toDate('#monthly_active_new_business.start#') AND toDate('#monthly_active_new_business.end#') ORDER BY dates.date ASC;