ClickHouse 时间序列数据填充：让曲线连续

您可以使用时间序列填充函数来填充没有数据的日期。以下是一个 SQL 示例：

SELECT 
    date,
    coalesce(total_num, lag(total_num) OVER (ORDER BY date)) AS total_num
FROM (
    SELECT 
        toDate(ts) as date,
        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 
        (
            SELECT 
                toDate('2021-01-01') + number * INTERVAL 1 DAY as ts
            FROM 
                numbers(365)
        ) as all_dates 
        ON toDate(ts) = all_dates.ts
    LEFT JOIN 
        (
            SELECT 
                toDate(ts) as date,
                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(#accumulated_access_services.start#)
            GROUP BY 
                date
        ) as filled_data 
        ON filled_data.date = toDate(ts)
) final_data
ORDER BY 
    date

此查询使用时间序列填充函数来生成一个包含所有日期的序列，然后使用 LEFT JOIN 将填充的数据与原始数据合并。最后，使用 COALESCE 函数将缺失的值填充为前一个日期的值。