如何改写含窗口函数的7日吞吐量SQL 查询改用简单关键词实现

我不希望该SQL查询过于复杂，请问是否存在其他写法，可以使用简单关键词实现相同功能，即便代码较长也可以接受。

表结构及测试数据 #

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');

待改写的原始SQL #

WITH cte AS 
(
    SELECT ds, COUNT(job_id) AS no_of_jobs, SUM(time_spent) AS time_taken
    FROM job_data
    WHERE event in ('transfer', 'decision')
    AND ds BETWEEN '2020-11-01' AND '2020-11-30'
    GROUP BY ds
)
SELECT ds, SUM(no_of_jobs) 
OVER ( order  by ds range between unbounded preceding and current row)/sum(time_taken) 
over (order by ds range between unbounded preceding and current row) as throughput_7d 
from cte;

改写后SQL（仅使用基础关键词，无窗口函数、CTE） #

SELECT 
    t1.ds,
    SUM(t2.no_of_jobs) / SUM(t2.time_taken) AS throughput_7d
FROM 
    (
        SELECT ds, COUNT(job_id) AS no_of_jobs, SUM(time_spent) AS time_taken
        FROM job_data
        WHERE event in ('transfer', 'decision')
        AND ds BETWEEN '2020-11-01' AND '2020-11-30'
        GROUP BY ds
    ) t1
JOIN 
    (
        SELECT ds, COUNT(job_id) AS no_of_jobs, SUM(time_spent) AS time_taken
        FROM job_data
        WHERE event in ('transfer', 'decision')
        AND ds BETWEEN '2020-11-01' AND '2020-11-30'
        GROUP BY ds
    ) t2
ON t2.ds <= t1.ds
GROUP BY t1.ds
ORDER BY t1.ds;

逻辑一致性说明 #

改写后的SQL和原始SQL计算结果完全一致：

• 两个子查询分别按天聚合得到符合条件的单日任务数、耗时，和原始CTE逻辑完全相同
• 通过自关联t2.ds <= t1.ds实现累计到当前日期的所有数据求和，等价于原始SQL中unbounded preceding and current row的窗口函数逻辑
• 最终累计任务数除以累计耗时得到的吞吐量和原始结果完全匹配

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