postgresql 将SQL Union中的重复项合并为每个ID一行

ny6fqffe 于 2023-04-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(157)

我有以下查询，它整理了几个表中的数据，并以以下格式生成结果：

id  | name  |             email             | surname |        last_updated        |      phone    |  phone_no     | birthday   | order_count | email_col_count | review_track_count | loyalty_count 
-----+-------+-------------------------------+---------+----------------------------+---------------+---------------+------------+-------------+-----------------+--------------------+---------------
 232 |       | 8888@gmail.com                |         | 2023-04-02 20:05:53.186+00 |               |               | 1994/11/07 |           0 |               0 |                  0 |             1
 231 |       | 1234457@gmail.com             |         | 2023-04-02 20:01:17.629+00 |               |               | 1994/11/07 |           0 |               0 |                  0 |             1
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               |               |            |           4 |               0 |                  0 |             0
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               | +18125555555  | 1994/11/07 |           0 |               0 |                  0 |             2
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               |               |            |           0 |               1 |                  0 |             0
 229 |       | 7112@gmail.com                |         | 2023-04-02 19:45:25.49+00  |               | +19098003700  | 1994/01/11 |           0 |               0 |                  0 |             1

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

last_updated应该是该用户last_updated列中的最新值
name、surname、phone_no、phone、birthday应该是最近的（最新的last_updated）NOT NULL值（如果存在），否则为NULL
1.所有count字段都应该是给定用户的相应计数字段的总和。
因此，对于230，行应该是这样的：

id  | name  |             email             | surname |        last_updated        |      phone    |  phone_no     | birthday   | order_count | email_col_count | review_track_count | loyalty_count 
-----+-------+-------------------------------+---------+----------------------------+---------------+---------------+------------+-------------+-----------------+--------------------+---------------
 230 |       | 9999@gmail.com                |         | 2023-04-02 19:58:44.432+00 |               | +18125555555  | 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;

postgresql

来源：https://stackoverflow.com/questions/75923827/combine-duplicates-from-sql-union-into-one-row-per-id

1条答案

按热度按时间

ni65a41a1#

例如，你可以使用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

赞(0）回复(0）举报 2023-04-05

我来回答

postgresql 将SQL Union中的重复项合并为每个ID一行

1条答案

相关问题

热门标签

最新问答