如何将新增的查询占比列合并至单行统计结果集？

原SQL用于从entrypoint_sql_queries表获取过去1小时的各类统计数据，现在需要新增一列——统计查询量最高的entrypoint的查询占比，对应的子查询已经写出，以下是几种合并到原单行结果集的方法：

方法1：将子查询作为标量子查询嵌入SELECT列表 #

这是最直接的方式，因为新增的子查询返回单行单列的结果，可以直接作为原查询的一个列字段：

SELECT COUNT(1)                                                            batch_count,
       ROUND(MAX(batch_duration_milliseconds) / 1000)                      max_batch_duration_seconds,
       ROUND(AVG(batch_duration_milliseconds) / 1000)                      avg_batch_duration_seconds,
       ROUND(MIN(batch_duration_milliseconds) / 1000, 1)                   min_batch_duration_seconds,
       ROUND(SUM(total_queries_duration_milliseconds) / SUM(batch_duration_milliseconds) *
             100)                                                          query_duration_percentage,
       SUM(total_queries)                                                  queries,
       ROUND(SUM(monitoring_queries) / SUM(total_queries) * 100)           percent_monitoring_queries,
       ROUND(SUM(total_queries) / SUM(batch_duration_milliseconds) * 1000) queries_per_second,
       MAX(max_query_duration_milliseconds)                                max_query_duration,
       ROUND(AVG(avg_query_duration_milliseconds))                         avg_query_duration,

       ROUND(SUM(retrieved_data_bytes) / 1024 / 1024)                      data_mb,
       ROUND(MAX(retrieved_data_bytes) / 1024 / 1024)                      max_batch_data_mb,
       ROUND(ROUND(AVG(retrieved_data_bytes)) / 1024 / 1024)               avg_batch_data_mb,
       ROUND(SUM(retrieved_data_bytes_calculation_duration_milliseconds) / SUM(batch_duration_milliseconds) *
             100, 1)                                                       percent_data_calc_duration,
       -- 新增的统计列：查询量最高的entrypoint的查询占比
       (SELECT ROUND(MAX(total_queries) / SUM(total_queries) * 100)
        FROM (
                 SELECT SUM(total_queries) AS total_queries
                 FROM entrypoint_sql_queries
                 WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR)
                 GROUP BY entrypoint
             ) as t) AS percent_most_expensive_job_queries
FROM entrypoint_sql_queries
WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR);

方法2：用CROSS JOIN合并两个单行结果 #

因为原查询和新增子查询都只返回一行数据，用CROSS JOIN可以直接把两个结果集的列合并成一行：

SELECT main.*, sub.percent_most_expensive_job_queries
FROM (
    -- 原统计查询
    SELECT COUNT(1)                                                            batch_count,
           ROUND(MAX(batch_duration_milliseconds) / 1000)                      max_batch_duration_seconds,
           ROUND(AVG(batch_duration_milliseconds) / 1000)                      avg_batch_duration_seconds,
           ROUND(MIN(batch_duration_milliseconds) / 1000, 1)                   min_batch_duration_seconds,
           ROUND(SUM(total_queries_duration_milliseconds) / SUM(batch_duration_milliseconds) *
                 100)                                                          query_duration_percentage,
           SUM(total_queries)                                                  queries,
           ROUND(SUM(monitoring_queries) / SUM(total_queries) * 100)           percent_monitoring_queries,
           ROUND(SUM(total_queries) / SUM(batch_duration_milliseconds) * 1000) queries_per_second,
           MAX(max_query_duration_milliseconds)                                max_query_duration,
           ROUND(AVG(avg_query_duration_milliseconds))                         avg_query_duration,

           ROUND(SUM(retrieved_data_bytes) / 1024 / 1024)                      data_mb,
           ROUND(MAX(retrieved_data_bytes) / 1024 / 1024)                      max_batch_data_mb,
           ROUND(ROUND(AVG(retrieved_data_bytes)) / 1024 / 1024)               avg_batch_data_mb,
           ROUND(SUM(retrieved_data_bytes_calculation_duration_milliseconds) / SUM(batch_duration_milliseconds) *
                 100, 1)                                                       percent_data_calc_duration
    FROM entrypoint_sql_queries
    WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR)
) AS main
CROSS JOIN (
    -- 新增的统计子查询
    SELECT ROUND(MAX(total_queries) / SUM(total_queries) * 100) percent_most_expensive_job_queries
    FROM (
             SELECT SUM(total_queries) AS total_queries
             FROM entrypoint_sql_queries
             WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR)
             GROUP BY entrypoint
         ) as t
) AS sub;

方法3：用窗口函数优化（减少表扫描次数） #

上面两种方法会扫描表两次，用窗口函数可以只扫描一次表就完成所有统计，性能更优：

WITH entrypoint_totals AS (
    SELECT 
        entrypoint,
        SUM(total_queries) AS ep_total_queries,
        SUM(total_queries) OVER() AS global_total_queries,
        -- 计算当前entrypoint的查询量占比
        ROUND(SUM(total_queries) / SUM(total_queries) OVER() * 100) AS ep_percent,
        -- 原查询需要的全局统计字段
        COUNT(1) OVER() AS batch_count,
        ROUND(MAX(batch_duration_milliseconds) OVER() / 1000) AS max_batch_duration_seconds,
        ROUND(AVG(batch_duration_milliseconds) OVER() / 1000) AS avg_batch_duration_seconds,
        ROUND(MIN(batch_duration_milliseconds) OVER() / 1000, 1) AS min_batch_duration_seconds,
        ROUND(SUM(total_queries_duration_milliseconds) OVER() / SUM(batch_duration_milliseconds) OVER() * 100) AS query_duration_percentage,
        SUM(total_queries) OVER() AS queries,
        ROUND(SUM(monitoring_queries) OVER() / SUM(total_queries) OVER() * 100) AS percent_monitoring_queries,
        ROUND(SUM(total_queries) OVER() / SUM(batch_duration_milliseconds) OVER() * 1000) AS queries_per_second,
        MAX(max_query_duration_milliseconds) OVER() AS max_query_duration,
        ROUND(AVG(avg_query_duration_milliseconds) OVER()) AS avg_query_duration,
        ROUND(SUM(retrieved_data_bytes) OVER() / 1024 / 1024) AS data_mb,
        ROUND(MAX(retrieved_data_bytes) OVER() / 1024 / 1024) AS max_batch_data_mb,
        ROUND(ROUND(AVG(retrieved_data_bytes) OVER()) / 1024 / 1024) AS avg_batch_data_mb,
        ROUND(SUM(retrieved_data_bytes_calculation_duration_milliseconds) OVER() / SUM(batch_duration_milliseconds) OVER() * 100, 1) AS percent_data_calc_duration
    FROM entrypoint_sql_queries
    WHERE created_at > SUBDATE(NOW(), INTERVAL 1 HOUR)
    GROUP BY entrypoint, batch_duration_milliseconds, total_queries_duration_milliseconds, monitoring_queries, max_query_duration_milliseconds, avg_query_duration_milliseconds, retrieved_data_bytes, retrieved_data_bytes_calculation_duration_milliseconds
)
SELECT DISTINCT
    batch_count,
    max_batch_duration_seconds,
    avg_batch_duration_seconds,
    min_batch_duration_seconds,
    query_duration_percentage,
    queries,
    percent_monitoring_queries,
    queries_per_second,
    max_query_duration,
    avg_query_duration,
    data_mb,
    max_batch_data_mb,
    avg_batch_data_mb,
    percent_data_calc_duration,
    MAX(ep_percent) OVER() AS percent_most_expensive_job_queries
FROM entrypoint_totals;

这种方法通过窗口函数一次性计算所有全局统计和entrypoint级别的统计，最后取最高的占比，只需要扫描一次表，数据量大的时候优势明显。

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