如何使Union ALL查询中'All'行汇总为单行，保留其他明细行

问题说明 #

需要构建查询统计每个用户不同类型的复制次数，要求同时展示Initial、Delta的明细行，以及一行汇总所有类型的统计值。当前使用UNION ALL合并三类结果时，'All'行出现两行，需合并为单行（将836与2求和得到838），同时保留明细行不变。

原查询及问题 #

原查询中，'All'对应的子查询在GROUP BY里包含了sc.synchro_type，这会导致按复制类型分组统计，生成两行'All'记录。

原查询语句：

SELECT replica_name,
       user_id,
       short_name,
       number_of_replications,
       firstReplication,
       lastReplication
FROM   (SELECT 'Initial'            AS replica_name,
               sc.user_id           AS user_id,
               u.short_name         AS short_name,
               Count(sc.user_id)    AS number_of_replications,
               Min(sc.connected_at) AS firstReplication,
               Max(sc.connected_at) AS lastReplication
        FROM   phoenix.synchro_connections sc
               JOIN phoenix.users u
                 ON u.user_id = sc.user_id
        WHERE  Lower(sc.synchro_type) = 'initial'
               AND  sc.size_in_bytes IS NOT NULL
        GROUP  BY sc.user_id,
                  u.short_name,
                  sc.synchro_type
        UNION ALL
        SELECT 'Delta'              AS replica_name,
               sc.user_id           AS user_id,
               u.short_name         AS short_name,
               Count(sc.user_id)    AS number_of_replications,
               Min(sc.connected_at) AS firstReplication,
               Max(sc.connected_at) AS lastReplication
        FROM   phoenix.synchro_connections sc
               JOIN phoenix.users u
                 ON u.user_id = sc.user_id
        WHERE  Lower(sc.synchro_type) = 'delta'
               AND  sc.size_in_bytes IS NOT NULL
        GROUP  BY sc.user_id,
                  u.short_name,
                  sc.synchro_type
        UNION ALL
        SELECT 'All'                AS replica_name,
               sc.user_id           AS user_id,
               u.short_name         AS short_name,
               Count(sc.user_id)    AS number_of_replications,
               Min(sc.connected_at) AS firstReplication,
               Max(sc.connected_at) AS lastReplication
        FROM   phoenix.synchro_connections sc
               JOIN phoenix.users u
                 ON u.user_id = sc.user_id
        WHERE  Lower(sc.synchro_type) <> 'upload'
               AND  sc.size_in_bytes IS NOT NULL
        GROUP  BY sc.user_id,
                  u.short_name,
                  sc.synchro_type) AS t
WHERE  short_name = 'BY060955'
ORDER  BY replica_name ASC,
          number_of_replications DESC

原结果：

replica_name user_id short_name number_of_replications firstReplication             LastReplication
All          22472   BY060955   836                    2022-11-14 06:26:05.2415463  2022-12-08 10:25:17.4282712
All          22472   BY060955   2                      2022-11-14 06:25:08.2385837  2022-11-16 11:55:41.0263526
Delta        22472   BY060955   836                    2022-11-14 06:26:05.2415463  2022-12-08 10:25:17.4282712
Initial      22472   BY060955   2                      2022-11-14 06:25:08.2385837  2022-11-16 11:55:41.0263526

修正后的查询 #

将'All'子查询的GROUP BY中移除sc.synchro_type，这样就会按用户维度汇总所有符合条件的复制记录，生成单行'All'结果：

SELECT replica_name,
       user_id,
       short_name,
       number_of_replications,
       firstReplication,
       lastReplication
FROM   (SELECT 'Initial'            AS replica_name,
               sc.user_id           AS user_id,
               u.short_name         AS short_name,
               Count(sc.user_id)    AS number_of_replications,
               Min(sc.connected_at) AS firstReplication,
               Max(sc.connected_at) AS lastReplication
        FROM   phoenix.synchro_connections sc
               JOIN phoenix.users u
                 ON u.user_id = sc.user_id
        WHERE  Lower(sc.synchro_type) = 'initial'
               AND  sc.size_in_bytes IS NOT NULL
        GROUP  BY sc.user_id,
                  u.short_name,
                  sc.synchro_type
        UNION ALL
        SELECT 'Delta'              AS replica_name,
               sc.user_id           AS user_id,
               u.short_name         AS short_name,
               Count(sc.user_id)    AS number_of_replications,
               Min(sc.connected_at) AS firstReplication,
               Max(sc.connected_at) AS lastReplication
        FROM   phoenix.synchro_connections sc
               JOIN phoenix.users u
                 ON u.user_id = sc.user_id
        WHERE  Lower(sc.synchro_type) = 'delta'
               AND  sc.size_in_bytes IS NOT NULL
        GROUP  BY sc.user_id,
                  u.short_name,
                  sc.synchro_type
        UNION ALL
        SELECT 'All'                AS replica_name,
               sc.user_id           AS user_id,
               u.short_name         AS short_name,
               Count(sc.user_id)    AS number_of_replications,
               Min(sc.connected_at) AS firstReplication,
               Max(sc.connected_at) AS lastReplication
        FROM   phoenix.synchro_connections sc
               JOIN phoenix.users u
                 ON u.user_id = sc.user_id
        WHERE  Lower(sc.synchro_type) <> 'upload'
               AND  sc.size_in_bytes IS NOT NULL
        GROUP  BY sc.user_id,
                  u.short_name) AS t -- 移除了sc.synchro_type
WHERE  short_name = 'BY060955'
ORDER  BY replica_name ASC,
          number_of_replications DESC

修正后的结果 #

replica_name user_id short_name number_of_replications firstReplication             LastReplication
All          22472   BY060955   838                    2022-11-14 06:25:08.2385837  2022-12-08 10:25:17.4282712
Delta        22472   BY060955   836                    2022-11-14 06:26:05.2415463  2022-12-08 10:25:17.4282712
Initial      22472   BY060955   2                      2022-11-14 06:25:08.2385837  2022-11-16 11:55:41.0263526

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