如何在PostgreSQL中按周分组

小编典典

如何在PostgreSQL中按周分组

sql

我有一个数据库表，commits其中包含以下列：

id | author_name | author_email | author_date（时间戳）| total_lines

样本内容为：

1 | abc | abc@xyz.com | 2013-03-24 15:32:49 | 1234
2 | abc | abc@xyz.com | 2013-03-27 15:32:49 | 534
3 | abc | abc@xyz.com | 2014-05-24 15:32:49 | 2344
4 | abc | abc@xyz.com | 2014-05-28 15:32:49 | 7623

我想得到如下结果：

id | name | week | commits
1  | abc  | 1    | 2
2  | abc  | 2    | 0

我在网上搜索了类似的解决方案，但找不到任何有用的解决方案。

我试过这个查询：

SELECT      date_part('week', author_date::date) AS weekly,
        COUNT(author_email)           
FROM        commits
GROUP BY    weekly
ORDER BY weekly

但这不是正确的结果。

阅读 417

2021-03-17

共1个答案

小编典典

如果您有多个年份，则也应该考虑年份。一种方法是：

SELECT date_part('year', author_date::date) as year,
       date_part('week', author_date::date) AS weekly,
       COUNT(author_email)           
FROM commits
GROUP BY year, weekly
ORDER BY year, weekly;

一种更自然的写方法是date_trunc()：

SELECT date_trunc('week', author_date::date) AS weekly,
       COUNT(author_email)           
FROM commits
GROUP BY weekly
ORDER BY weekly;

2021-03-17