基于job_data表计算Throughput（吞吐量）的7日滚动平均值

Throughput：指每秒发生的事件数量。

任务说明：假设上述指标名为Throughput，请基于给定的job_data数据集计算其7日滚动平均值。

job_data数据集字段说明 #

• job_id: 作业的唯一标识符
• actor_id: 执行者的唯一标识符
• event: 操作类型，可选值为decision/skip/transfer
• language: 内容语言
• time_spent: 审核作业耗时（单位：秒）
• org: 执行者所属组织
• ds: 日期（格式为yyyy/mm/dd，文本形式存储，使用Presto运行，无需日期函数）

建表语句 #

CREATE TABLE job_data
(
    ds DATE,
    job_id INT NOT NULL,
    actor_id INT NOT NULL,
    event VARCHAR(15) NOT NULL,
    language VARCHAR(15) NOT NULL,
    time_spent INT NOT NULL,
    org CHAR(2)
);

插入测试数据 #

INSERT INTO job_data (ds, job_id, actor_id, event, language, time_spent, org)
VALUES ('2020-11-30', 21, 1001, 'skip', 'English', 15, 'A'),
    ('2020-11-30', 22, 1006, 'transfer', 'Arabic', 25, 'B'),
    ('2020-11-29', 23, 1003, 'decision', 'Persian', 20, 'C'),
    ('2020-11-28', 23, 1005,'transfer', 'Persian', 22, 'D'),
    ('2020-11-28', 25, 1002, 'decision', 'Hindi', 11, 'B'),
    ('2020-11-27', 11, 1007, 'decision', 'French', 104, 'D'),
    ('2020-11-26', 23, 1004, 'skip', 'Persian', 56, 'A'),
    ('2020-11-25', 20, 1003, 'transfer', 'Italian', 45, 'C');

计算7日滚动平均值的SQL语句 #

WITH daily_throughput AS (
    SELECT
        ds,
        COUNT(job_id) AS daily_events,
        SUM(time_spent) AS total_seconds,
        -- 计算每日Throughput：每秒发生的事件数量
        COUNT(job_id) / SUM(time_spent) AS throughput
    FROM job_data
    GROUP BY ds
)
SELECT
    ds,
    throughput,
    -- 基于Presto的窗口函数计算7日滚动平均值，覆盖当前日期及前6天
    AVG(throughput) OVER (
        ORDER BY ds
        RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg_throughput
FROM daily_throughput
ORDER BY ds DESC;

内容的提问来源于stack exchange，提问作者PURUSOTTAM