我有以下查询,它从几个表中整理数据,并产生以下格式的结果:
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
应该是该用户name
,surname
,phone_no
,phone
,birthday
应该是最近的(最新的last_updated
) NOT NULL值,如果存在,则为NULL,否则为NULL- 所有
count
字段应该是给定用户的各自计数字段的总和。
last_updated
列中最近的值因此,对于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;
您可以使用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