sql - 计算每个时间段的第一个出现记录

Question

在我的表 trips 中，我有两列:created_at 和 user_id

唯一身份用户会进行许多不同的旅行。我的目标是计算每年每月每个 user_id 的 very first 旅行次数。我知道在这种情况下应该应用 min() 函数。

在之前的查询中，聚合了每年-每月的所有唯一用户:

SELECT to_char(created_at, 'YYYY-MM') as yyyymm, COUNT(DISTINCT user_id)
FROM trips
GROUP BY yyyymm
ORDER BY yyyymm;

在上面的查询中，min() 应该集成在什么地方？换句话说，我不需要计算每个月所有唯一用户 ID，我只需要计算每个月第一次出现的唯一用户 ID。

示例输入如下所示:

> routes
   user_id          created_at
1        1 2015-08-07 07:18:21
2        2 2015-05-06 20:43:52
3        3 2015-05-06 20:53:54
4        1 2015-03-30 20:09:07
5        2 2015-10-01 18:28:32
6        3 2015-08-07 07:29:29
7        1 2015-08-28 13:45:44
8        2 2015-08-07 07:37:31
9        3 2015-03-30 20:14:04
10       1 2015-08-07 07:08:50

输出将是:

   count     Y-m   
1        0 2015-01 
2        0 2015-02 
3        2 2015-03
4        0 2015-04 
5        1 2015-05

因为 user_id 1 和 3 第一次出现是在三月，而 user_id 2 第一次出现是在五月

Answer 1

您可以使用 2 级聚合来执行此操作。获取每个 user_id 的 min 时间，然后 count。

SELECT to_char(first_time, 'YYYY-MM'),count(*)
from (
SELECT user_id,MIN(created_at) as first_time
FROM trips
GROUP BY user_id
) t
GROUP BY to_char(first_time, 'YYYY-MM')