ClickHouse 查询每天数据，缺失日期填充为0

您可以使用一个外部日期表来实现。首先，创建一个包含所有日期的数据表，例如：

CREATE TABLE all_dates (
  date Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY date;

然后填充数据表，例如：

INSERT INTO all_dates
SELECT 
  toDate('2021-01-01') + toIntervalDay(number) AS date
FROM 
  numbers(365)

接下来，您可以使用外部日期表来生成每天的数据。例如：

SELECT all_dates.date,
  if(i >= 31, arraySum(arraySlice(total_increase, i-30, 31)), arraySum(arraySlice(total_increase, 1, i))) as total_increase_num
FROM 
  (
    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
  JOIN all_dates ON all_dates.date >= toDate('monthly_active_new_business.start') AND all_dates.date <= toDate('monthly_active_new_business.end')
  LEFT JOIN t as ts ON ts = all_dates.date
  LEFT JOIN arrayEnumerate(t) as i ON 1
ORDER BY all_dates.date

这将生成包含所有日期的数据，如果某个日期没有数据，则默认赋值为0。