时间戳

首页 > 解决方案 > 在 PostgreSQL 中按周分组时，如何始终获得完整的时间段？

postgresql - 在 PostgreSQL 中按周分组时，如何始终获得完整的时间段？

问题描述

在分析每周数据时，我习惯使用以下语法：

select week(creation_date)::date as week,
       count(*) as n
from table_1
where creation_date > current_date - 30
group by 1

但是，通过这样做，我将只获得第一周的一部分。有什么聪明的方法可以在一开始就获得一整周的时间吗？就像得到一周的第一天，我会得到一半。

标签： postgresqldategroup-by

解决方案

首先，您需要定义“周”的含义。这比看起来要困难得多。虽然人类从一周开始就有直觉，但计算机并没有那么聪明。有两种常见的约定：ISO-8601 标准，以及由于缺乏更好的术语，传统的。ISO-8601 将一周定义为始终从星期一开始，并且始终包含 7 天。传统的星期（通常）从星期日开始，但可能有少于 7 天的星期。这是因为一年中的第一周从 1 月 1 日开始，无论星期几。因此，第一周和/或最后一周可能少于 7 天。ISO-8601 将它自己的曲线融入其中：一年中的第一周从包含 1 月 4 日的那一周开始。因此，12 月的最后几天可能在下一年的第 1 周，而 1 月的第一天可能在上一年的第 52/53 周。

以下所有内容均假设ISO-8061。

其次，Postgres 中没有星期功能。在你需要提取功能。因此，对于这种特殊情况：

select extract(week from creation_date)::integer as week, ...

最后，您的谓词 (current_date - 30) 确保您不会在一周的第一天开始。要获得正确的日期，请将该结果退回 1 周，然后转到下周一。

with days_to_monday (day_adj) as  
     ( values ('{7,6,5,4,3,2,1}'::int[]) )  
select current_date - 30
     , current_date - 30 - 7 + day_adj[extract (isodow from current_date - 30 )]
  from table_1 cross join days_to_monday;

CTE 建立一个数组，该数组对于一周中的某一天包含到下周一所需的天数。该主查询提取当前日期的星期几并使用它来索引数组。添加相应的值以获得正确的日期。
将其与您的原始查询一起得出：

with next_week (monday) as  
 ( values (current_date - 30 - 7 
          + ('{7,6,5,4,3,2,1}'::int[])[extract (isodow from current_date - 30 )])
 ) 
select extract(week from creation_date) as week,
       count(*) as n
 from table_1
where creation_date >= (select monday from next_week) 
group by 1
order by 1;

有关完整示例，请参见fiddle。

推荐阅读