我有一個電子郵件串列和多個活動的互動日期，我試圖按年份合并這些日期，并通過電子郵件查找第一次互動以及它屬于哪一年。
SELECT date_part('year', MIN(x.created_at)) as years
FROM (
SELECT email, created_at FROM s_subscribers_491 UNION ALL
SELECT email, created_at FROM s_subscribers_493 UNION ALL
SELECT email, created_at FROM s_subscribers_1190
) x
GROUP BY x.email
上面的查詢提供了每封電子郵件第一年的互動結果，但是我需要按年份對它們進行分組，然后按年份計算有多少，但是我遇到了 GROUPING BY date_part('year', MIN(x .created_at)) 甚至使用別名。
我從上述查詢中收到的結果：
years
1 2018
2 2018
3 2020
4 2020
5 2018
6 2021
7 2017
8 2020
9 2018
期望的結果：
years count
1 2017 1
2 2018 4
3 2020 3
4 2021 1
非常感謝
uj5u.com熱心網友回復：
只需將您的查詢嵌套在另一個子查詢中。這使您可以進行兩個不同級別的聚合...
SELECT
years,
COUNT(*)
FROM
(
SELECT
date_part('year', MIN(x.created_at)) as years
FROM
(
SELECT email, created_at FROM s_subscribers_491 UNION ALL
SELECT email, created_at FROM s_subscribers_493 UNION ALL
SELECT email, created_at FROM s_subscribers_1190
)
AS subscribers
GROUP BY
email
)
AS firstSubscription
GROUP BY
years
不過，我會使用 CTE 來更“線性”地布置它......
WITH
subscribers AS
(
SELECT email, created_at FROM s_subscribers_491 UNION ALL
SELECT email, created_at FROM s_subscribers_493 UNION ALL
SELECT email, created_at FROM s_subscribers_1190
),
firstSubscription AS
(
SELECT
date_part('year', MIN(x.created_at)) as years
FROM
subscribers
GROUP BY
email
)
SELECT
years,
COUNT(*)
FROM
firstSubscription
GROUP BY
years
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/432615.html
標籤：sql PostgreSQL
上一篇：使用JOIN從表中映射值