postgreCombine从SQL Union复制到每ID一行

Question

我有以下查询，它从几个表中整理数据，并产生以下格式的结果:

id  | name  |             email             | surname |        last_updated        |      phone    |  phone_no     | birthday   | order_count | email_col_count | review_track_count | loyalty_count 
-----+-------+-------------------------------+---------+----------------------------+---------------+---------------+------------+-------------+-----------------+--------------------+---------------
232 |       | [e-mail]                |         | 2023-04-02 20:05:53.186+00 |               |               | 1994/11/07 |           0 |               0 |                  0 |             1
231 |       | [e-mail]             |         | 2023-04-02 20:01:17.629+00 |               |               | 1994/11/07 |           0 |               0 |                  0 |             1
230 |       | [e-mail]                |         | 2023-04-02 19:58:44.432+00 |               |               |            |           4 |               0 |                  0 |             0
230 |       | [e-mail]                |         | 2023-04-02 19:58:44.432+00 |               | [phone]  | 1994/11/07 |           0 |               0 |                  0 |             2
230 |       | [e-mail]                |         | 2023-04-02 19:58:44.432+00 |               |               |            |           0 |               1 |                  0 |             0
229 |       | [e-mail]                |         | 2023-04-02 19:45:25.49+00  |               | [phone]  | 1994/01/11 |           0 |               0 |                  0 |             1

问题是，我希望每个id只有一行，数据整理如下:

1. last_updated应该是该用户
last_updated列中最近的值
2. name,surname,phone_no,phone,birthday应该是最近的(最新的last_updated) NOT NULL值，如果存在，则为NULL，否则为NULL
3. 所有count字段应该是给定用户的各自计数字段的总和。

因此，对于230，行应该像这样:

id  | name  |             email             | surname |        last_updated        |      phone    |  phone_no     | birthday   | order_count | email_col_count | review_track_count | loyalty_count 
-----+-------+-------------------------------+---------+----------------------------+---------------+---------------+------------+-------------+-----------------+--------------------+---------------
230 |       | [e-mail]                |         | 2023-04-02 19:58:44.432+00 |               | [phone]  | 1994/11/07  |           4 |              1 |                  0 |             2

我如何改变我的查询来实现这一点?

SELECT id, 
name, 
email, 
surname, 
last_updated, 
phone, 
phone_no, 
birthday, 
Sum(order_count)        AS order_count, 
Sum(email_col_count)    AS email_col_count, 
Sum(review_track_count) AS review_track_count, 
Sum(loyalty_count)      AS loyalty_count 
FROM     ( 
SELECT   u.id, 
u.name, 
u.email, 
u.surname, 
'order_user'           AS type, 
Max(u."updatedAt")     AS last_updated, 
Max(ord."phoneNumber") AS phone, 
NULL                   AS phone_no, 
NULL                   AS birthday, 
Count(DISTINCT ord.id) AS order_count, 
0                      AS email_col_count, 
0                      AS review_track_count, 
0                      AS loyalty_count 
FROM     users u 
JOIN     orders ord 
ON       u.id=ord."orderUserId" 
AND      ord."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6) 
GROUP BY u.id, 
type 
UNION 
SELECT   u.id, 
u.name, 
u.email, 
u.surname, 
'email_collection'     AS type, 
Max(u."updatedAt")     AS last_updated, 
NULL                   AS phone, 
NULL                   AS phone_no, 
NULL                   AS birthday, 
0                      AS order_count, 
Count(DISTINCT col.id) AS email_col_count, 
0                      AS review_track_count, 
0                      AS loyalty_count 
FROM     users u 
JOIN     "userEmailCollections" col 
ON       u.id=col."userId" 
AND      col."restaurantId" = 6 
GROUP BY u.id, 
type 
UNION 
SELECT   u.id, 
u.name, 
u.email, 
u.surname, 
'review_track'         AS type, 
Max(u."updatedAt")     AS last_updated, 
NULL                   AS phone, 
NULL                   AS phone_no, 
NULL                   AS birthday, 
0                      AS order_count, 
0                      AS email_col_count, 
Count(DISTINCT rev.id) AS review_track_count, 
0                      AS loyalty_count 
FROM     users u 
JOIN     "reviewTracks" rev 
ON       u.email=rev."email" 
AND      rev."restaurantId" = 6 
GROUP BY u.id, 
type 
UNION 
SELECT   u.id, 
u.name, 
u.email, 
u.surname, 
'loyalty_campaign_redemption' AS type, 
Max(u."updatedAt")            AS last_updated, 
NULL                          AS phone, 
Max(loyy."phoneNo")           AS phone_no, 
Max(loyy.birthday)            AS birthday, 
0                             AS order_count, 
0                             AS email_col_count, 
0                             AS review_track_count, 
Count(DISTINCT loyy.id)       AS loyalty_count 
FROM     users u 
JOIN     "loyaltyCampaignRedemptions" loyy 
ON       u.id=loyy."userId" 
AND      loyy."restaurantId" = 6 
GROUP BY u.id, 
type ) AS subquery 
GROUP BY id, 
name, 
email, 
surname, 
type, 
last_updated, 
phone, 
phone_no, 
birthday 
ORDER BY last_updated DESC limit 50;

Answer 1

您可以使用FIRST_VALUE，例如，尝试下面的代码:

SELECT
id
,name
,email
,surname
,Max(last_updated) as last_updated
,phone
,phone_no
,birthday
,Sum(order_count)       AS order_count
,Sum(email_col_count)    AS email_col_count
,Sum(review_track_count) AS review_track_count
,Sum(loyalty_count)      AS loyalty_count
FROM
(
SELECT id, 
FIRST_VALUE(name) OVER (PARTITION BY id ORDER BY last_updated DESC) as name, 
FIRST_VALUE(email) OVER (PARTITION BY id ORDER BY last_updated DESC) as email, 
FIRST_VALUE(surname) OVER (PARTITION BY id ORDER BY last_updated DESC) as surname, 
last_updated, 
FIRST_VALUE(phone) OVER (PARTITION BY id ORDER BY last_updated DESC) as phone, 
FIRST_VALUE(phone_no) OVER (PARTITION BY id ORDER BY last_updated DESC) as phone_no, 
FIRST_VALUE(birthday) OVER (PARTITION BY id ORDER BY last_updated DESC) as birthday, 
order_count, 
email_col_count, 
review_track_count, 
loyalty_count 
FROM     ( 
SELECT   u.id, 
u.name, 
u.email, 
u.surname, 
'order_user'           AS type, 
Max(u."updatedAt")     AS last_updated, 
Max(ord."phoneNumber") AS phone, 
NULL                   AS phone_no, 
NULL                   AS birthday, 
Count(DISTINCT ord.id) AS order_count, 
0                      AS email_col_count, 
0                      AS review_track_count, 
0                      AS loyalty_count 
FROM     users u 
JOIN     orders ord 
ON       u.id=ord."orderUserId" 
AND      ord."restaurantTableId" IN (12,7,9,8,10,11,14,99,100,6) 
GROUP BY u.id, 
type 
UNION 
SELECT   u.id, 
u.name, 
u.email, 
u.surname, 
'email_collection'     AS type, 
Max(u."updatedAt")     AS last_updated, 
NULL                   AS phone, 
NULL                   AS phone_no, 
NULL                   AS birthday, 
0                      AS order_count, 
Count(DISTINCT col.id) AS email_col_count, 
0                      AS review_track_count, 
0                      AS loyalty_count 
FROM     users u 
JOIN     "userEmailCollections" col 
ON       u.id=col."userId" 
AND      col."restaurantId" = 6 
GROUP BY u.id, 
type 
UNION 
SELECT   u.id, 
u.name, 
u.email, 
u.surname, 
'review_track'         AS type, 
Max(u."updatedAt")     AS last_updated, 
NULL                   AS phone, 
NULL                   AS phone_no, 
NULL                   AS birthday, 
0                      AS order_count, 
0                      AS email_col_count, 
Count(DISTINCT rev.id) AS review_track_count, 
0                      AS loyalty_count 
FROM     users u 
JOIN     "reviewTracks" rev 
ON       u.email=rev."email" 
AND      rev."restaurantId" = 6 
GROUP BY u.id, 
type 
UNION 
SELECT   u.id, 
u.name, 
u.email, 
u.surname, 
'loyalty_campaign_redemption' AS type, 
Max(u."updatedAt")            AS last_updated, 
NULL                          AS phone, 
Max(loyy."phoneNo")           AS phone_no, 
Max(loyy.birthday)            AS birthday, 
0                             AS order_count, 
0                             AS email_col_count, 
0                             AS review_track_count, 
Count(DISTINCT loyy.id)       AS loyalty_count 
FROM     users u 
JOIN     "loyaltyCampaignRedemptions" loyy 
ON       u.id=loyy."userId" 
AND      loyy."restaurantId" = 6 
GROUP BY u.id, 
type ) AS subquery 
GROUP BY id
ORDER BY last_updated DESC limit 50;
)
GROUP BY
id
,name
,email
,surname
,last_updated
,phone
,phone_no
,birthday