ClickHouse SQL: 使用 LEFT JOIN 和 COALESCE 函数填充缺失日期的统计数据

可以使用 LEFT JOIN 和 COALESCE 函数来实现。首先，将19个日期和对应的'total_increase'数组联接到一个包含所有日期的表中。然后，使用 COALESCE 函数将'total_increase_num'值设置为对应的'total_increase'数组元素值，如果该日期没有对应的'total_increase'值，则将'total_increase_num'设置为0。

以下是修改后的查询：

SELECT all_dates.date as ts,
  COALESCE(total_increase[i], 0) as total_increase,
  if(i >= 31, arraySum(arraySlice(total_increase, i-30, 31)), arraySum(arraySlice(total_increase, 1, i))) as total_increase_num
FROM 
(
  SELECT toDate(Ds) as date
  FROM system.calendar
  WHERE date >= toDate(#monthly_active_new_business.start#) AND date <= toDate(#monthly_active_new_business.end#)
) all_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
    ) 
) data_array ON all_dates.date = toDate(data_array.t[i])
ARRAY JOIN all_dates.date as ts,
  arrayEnumerate(data_array.t) as i
ORDER BY ts ASC

这样，查询结果将包含所有日期的'total_increase_num'值，其中19个日期的'total_increase_num'值将与'total_increase'数组元素相同，其他日期的'total_increase_num'值将为0。