如何用Presto SQL可扩展实现每日满足条件的累计行数统计

你可以通过「一次性过滤符合条件的记录+生成连续日期序列关联统计」的逻辑实现需求，仅需修改统计起止日期即可适配任意时间范围，不需要重复编写CTE，性能也比原方案高得多。

以下是适配Presto/Trino生态的实现代码（你用到的from_iso8601_timestamp属于该生态标准函数）：

-- 统一配置统计参数，仅需修改这里即可调整统计范围
WITH params AS (
    SELECT 
        DATE '2021-09-01' AS start_date,
        CURRENT_DATE AS end_date
),
-- 生成从起始日到结束日的连续自然日序列
date_series AS (
    SELECT date
    FROM params,
    UNNEST(sequence(start_date, end_date, INTERVAL '1' day)) AS t(date)
),
-- 单次扫表过滤符合条件的记录，统计每个账号首次满足条件的日期
eligible_accounts AS (
    SELECT 
        account_id,
        MIN(DATE(from_iso8601_timestamp(timestamp))) AS first_appear_date
    FROM default_table
    CROSS JOIN params
    WHERE
        secret_column = 'secret value'
        AND lower(device_os) LIKE '%android%'
        AND DATE(from_iso8601_timestamp(timestamp)) <= end_date
    GROUP BY account_id
)
-- 关联计算每日累计符合条件的账号数
SELECT
    ds.date,
    COUNT(ea.account_id) AS rows_staisfying_conditions
FROM date_series ds
LEFT JOIN eligible_accounts ea
    ON ea.first_appear_date <= ds.date
GROUP BY ds.date
ORDER BY ds.date

方案说明 #

• 适配性：仅需调整params中的start_date和end_date即可覆盖任意统计周期，没有代码冗余问题
• 性能：原方案统计N天需要扫表N次，本方案仅需扫表1次，统计周期越长性能优势越明显
• 输出结果直接为你需要的date+累计值的两列结构，不需要额外处理

如果使用的是Hive等不支持sequence函数的引擎，只需替换date_series的生成逻辑即可：

date_series AS (
    SELECT 
        date_add(start_date, pos) AS date
    FROM params
    LATERAL VIEW posexplode(split(space(datediff(end_date, start_date)), ' ')) t AS pos, val
)

如果你的需求是统计累计行数而非去重账号数，仅需将eligible_accounts逻辑修改为按日统计当日符合条件的行数，再通过SUM(day_count) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)窗口函数计算累计值即可，整体结构不需要改动。

内容的提问来源于stack exchange，提问作者K. Claesson