如何获取Redshift非重叠查询总执行时间？求方案验证

我需要统计Redshift中查询的总运行时长，尝试用stl_query获取数据，但因为存在同时运行的查询，执行时间重叠，没法直接按日期聚合求和。我自己写了一段SQL，但不确定方法是否正确，希望得到验证：

WITH query_times AS (
    SELECT
        DATE_TRUNC('day', starttime) AS query_date,
        starttime,
        endtime
    FROM
        stl_query
    WHERE
        userid > 1  -- Exclude system queries
        AND starttime >= DATEADD(day, -7, CURRENT_DATE)
),
distinct_intervals AS (
    SELECT
        query_date,
        starttime AS period_start,
        LEAD(starttime) OVER (PARTITION BY query_date ORDER BY starttime) AS next_starttime,
        endtime
    FROM
        query_times
),
non_overlapping_intervals AS (
    SELECT
        query_date,
        period_start,
        CASE
            WHEN next_starttime IS NULL OR next_starttime > endtime THEN endtime
            ELSE next_starttime
        END AS period_end
    FROM
        distinct_intervals
)
SELECT
    query_date,
    SUM(DATEDIFF(seconds, period_start, period_end))/3600 AS total_running_time_hours
FROM
    non_overlapping_intervals
GROUP BY
    query_date
ORDER BY
    query_date;

你的思路方向是对的，但当前查询存在关键缺陷——无法正确处理多段嵌套重叠的场景。比如遇到这种情况：

• 查询1：08:00-12:00
• 查询2：09:00-10:00
• 查询3：11:00-13:00

你的逻辑会计算出总时长4小时，但实际非重叠的运行区间是08:00-13:00，总时长应为5小时。问题出在你只比较了当前查询和下一个查询的时间点，没有合并所有重叠的区间。

修正后的查询语句 #

使用经典的重叠区间合并算法，能准确计算每天的非重叠总运行时长：

WITH query_times AS (
    SELECT
        DATE_TRUNC('day', starttime) AS query_date,
        starttime,
        endtime
    FROM
        stl_query
    WHERE
        userid > 1  -- 排除系统查询
        AND starttime >= DATEADD(day, -7, CURRENT_DATE)
        AND status = 0 -- 可选：只统计成功完成的查询
),
sorted_intervals AS (
    SELECT
        query_date,
        starttime,
        endtime,
        -- 标记当前区间是否为新的合并组起点
        CASE
            WHEN starttime <= LAG(endtime) OVER (PARTITION BY query_date ORDER BY starttime) THEN 0
            ELSE 1
        END AS is_new_group,
        -- 生成合并组ID
        SUM(CASE
            WHEN starttime <= LAG(endtime) OVER (PARTITION BY query_date ORDER BY starttime) THEN 0
            ELSE 1
        END) OVER (PARTITION BY query_date ORDER BY starttime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM
        query_times
),
merged_intervals AS (
    SELECT
        query_date,
        MIN(starttime) AS period_start,
        MAX(endtime) AS period_end
    FROM
        sorted_intervals
    GROUP BY
        query_date,
        group_id
)
SELECT
    query_date,
    SUM(DATEDIFF(seconds, period_start, period_end)) / 3600 AS total_running_time_hours
FROM
    merged_intervals
GROUP BY
    query_date
ORDER BY
    query_date;

逻辑说明 #

1. query_times：筛选目标时间范围内的用户查询，排除系统查询，可选过滤成功的查询。
2. sorted_intervals：按日期和开始时间排序，判断每个区间是否与前一个区间重叠——若重叠则归为同一组，否则创建新组，最终生成每个区间的合并组ID。
3. merged_intervals：按日期和组ID分组，取每组的最早开始时间和最晚结束时间，得到合并后的非重叠区间。
4. 最后对合并后的区间计算总时长，得到准确的每日非重叠运行时长。

额外注意事项 #

• stl_query的日志保留时间由Redshift配置决定，若需要长期统计，建议将数据同步到外部存储或自建统计表。
• 可根据需求增加过滤条件（如按用户、队列筛选），或调整时间粒度（如按小时聚合）。

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