Union ALL sum结果为一行，并保留求和值

Question

我试图建立一个查询，每个用户获得不同类型的复制的总和。我想要显示一个统一的结果，其中包含每个复制的行和另一行，将它们之和为每个用户的所有。下面是查询:

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'是单行(836+2 = 838)，但' delta '和'Initial'保持原样

Answer 1

从最后一个查询中，删除按sc.synchro_type:

分组

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

---

您应该能够以更好的方式从表中读取一行来计算上面的内容。如果你需要同样的结果，你可以使用UNPIVOT:

SELECT sc.user_id AS user_id,
--
SUM(IIF(Lower(sc.synchro_type) = 'initial', 1, 0)) AS initial_number_of_replications,
MIN(IIF(Lower(sc.synchro_type) = 'initial', sc.connected_at, NULL)) AS initial_firstReplication,
MAX(IIF(Lower(sc.synchro_type) = 'initial', sc.connected_at, NULL)) AS initial_lastReplication,
--
SUM(IIF(Lower(sc.synchro_type) = 'delta', 1, 0)) AS delta_number_of_replications,
MIN(IIF(Lower(sc.synchro_type) = 'delta', sc.connected_at, NULL)) AS delta_firstReplication,
MAX(IIF(Lower(sc.synchro_type) = 'delta', sc.connected_at, NULL)) AS delta_lastReplication,
--
SUM(IIF(Lower(sc.synchro_type) <> 'upload', 1, 0)) AS ALL_number_of_replications,
MIN(IIF(Lower(sc.synchro_type) <> 'upload', sc.connected_at, NULL)) AS ALL_firstReplication,
MAX(IIF(Lower(sc.synchro_type) <> 'upload', sc.connected_at, NULL)) AS ALL_lastReplication
FROM phoenix.synchro_connections sc
INNER 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
AND u.short_name = 'BY060955'
GROUP  BY sc.user_id