PostgreSQL:COUNT、GROUP和MIN date_part数据来获得第一次交互

Question

我有一份电子邮件列表和多个活动的互动日期，我正试图逐年合并这些日期，并通过电子邮件找到第一次互动，以及它发生在哪一年。

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

非常感谢

Answer 1

只需将查询嵌套在另一个子查询中。这允许您执行两个不同级别的聚合。。。

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