ClickHouse SQL: 结合日期表获取连续日期的累计值

有两段 SQL 代码，第一段：

SELECT ts,
  arraySum(arraySlice(total, 1, i)) as total_num
FROM
  (
    SELECT groupArray(min_Ds) as t,
      groupArray(app_count) as total
    from (
        SELECT min_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 t as ts,
  arrayEnumerate(total) as i where ts >= toDate('2022-01-01')

第二段：

WITH date_table AS (
  SELECT toDate('2022-01-01') AS date
  UNION ALL SELECT date + 1 FROM date_table WHERE date < toDate('2022-01-31')
)
SELECT dt.date AS ts, COALESCE(total_increase_num, 0) AS total_increase_num
FROM date_table dt
LEFT JOIN (
  SELECT ts,
    arraySum(arraySlice(total, 1, i)) AS total_increase_num
  FROM (
    SELECT groupArray(min_Ds) AS t,
      groupArray(app_count) AS total
    FROM (
      SELECT min_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 t AS ts,
  arrayEnumerate(total) AS i
  WHERE ts >= toDate('2022-01-01')
) result
ON result.ts = dt.date
ORDER BY ts;

如何将这两个表整合在一起？没有 ts 没有对应 total_increase_num 的，total_increase_num 赋值为 0，使每个日期都有值。

可以使用左连接将第一段代码中的结果与一个日期表连接（日期表包含所有日期），并使用 COALESCE 函数将没有对应值的 total_increase_num 赋值为 0。

示例代码：

WITH date_table AS (
  SELECT toDate('2022-01-01') AS date
  UNION ALL SELECT date + 1 FROM date_table WHERE date < toDate('2022-01-31')
)
SELECT dt.date AS ts, COALESCE(total_increase_num, 0) AS total_increase_num
FROM date_table dt
LEFT JOIN (
  SELECT ts,
    arraySum(arraySlice(total, 1, i)) AS total_increase_num
  FROM (
    SELECT groupArray(min_Ds) AS t,
      groupArray(app_count) AS total
    FROM (
      SELECT min_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 t AS ts,
  arrayEnumerate(total) AS i
  WHERE ts >= toDate('2022-01-01')
) result
ON result.ts = dt.date
ORDER BY ts;

此代码创建了一个日期表 date_table，包含从 2022-01-01 到 2022-01-31 的所有日期。然后使用左连接将 date_table 与第一段代码的结果连接，并使用 COALESCE 函数将没有对应值的 total_increase_num 赋值为 0。

这样，最终结果将包含所有日期，每个日期都有对应的 total_increase_num 值，即使没有对应数据也为 0。