sql - 如何在 PostgreSQL 中创建一个显示 "occurrence number"一天的伪列？

Question

注意:PostgreSQL 9.4

假设我有一个这样的表:

• 列project_name:文本，不为空；
• date列:不带时区的时间戳，不为空；
• uuid列:UUID，不为空；
• 所有三列的唯一索引。

假设对于给定的 project_name，同一天有多个 date 出现。例如:

    uuid     | project_name |    analysis_date
-------------|------------------------------------
 uuid-1-here |  MY_PROJECT  | 2016-02-22 10:00:00
 uuid-2-here |  MY_PROJECT  | 2016-02-22 10:20:00
 uuid-3-here |  MY_PROJECT  | 2016-02-24 13:12:00

而且这种模式可以重复:我可以对 2015 年 2 月 23 日、1 月 12 日、12 月 30 日等进行多次分析。

我想获得这样的输出:

    uuid     | project_name | analysis_day | occurrence
-------------|------------------------------------------
 uuid-1-here |  MY_PROJECT  |  2016-02-22  |     1
 uuid-2-here |  MY_PROJECT  |  2016-02-22  |     2
 uuid-3-here |  MY_PROJECT  |  2016-02-24  |     1

也就是说，对于每一天，让伪列occurrence 显示当天发生的分析编号 -- 对于每一天。

好的，所以，analysis_day 的格式我可以管理；但我对如何生成 occurrence 列一无所知:(

我的第一次阅读告诉我应该使用某种PARTITION，但这是 SQL 的一部分，我完全无法理解，而且没有帮助。也许分区甚至不是这里的解决方案。

如何实现上述目标？

Answer 1

听起来你只需要GROUP BY:

SELECT  uuid, project_name, to_char(analysis_date, 'YYYY-MM-DD') d, count(*)
FROM    t
GROUP BY uuid, project_name, d
ORDER BY uuid, project_name, d
;

编辑:

好的，我意识到您不是要计数，而是要序列号。在那种情况下你可以这样说:

SELECT  uuid, project_name, to_char(analysis_date, 'YYYY-MM-DD') d, 
        row_number() OVER (PARTITION BY analysis_date::date ORDER BY analysis_date)
FROM    t
ORDER BY uuid, project_name, d
; 

或者，如果您想在每个项目中独立编号，请将其包含在 PARTITION 中，如下所示:PARTITION BY project_name, to_char(analysis_date, 'YYYY-MM-DD').

请注意，您将在这里遇到时区问题，因为 Postgres 必须决定每天的开始和结束时间。由于您有一个 TIMESTAMP WITHOUT TIME ZONE，因此不会根据客户端的时区设置进行自动转换。