SQL 如何在postgresql中按周分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36024712/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to group by week in postgresql
提问by user6058071
I've a database table commitswith the following columns:
我有一个commits包含以下列的数据库表：
id | author_name | author_email | author_date (timestamp) | total_lines
身 | 作者姓名 | author_email | author_date（时间戳）| 总行数
Sample contents are:
示例内容为：
1 | abc | [email protected] | 2013-03-24 15:32:49 | 1234
2 | abc | [email protected] | 2013-03-27 15:32:49 | 534
3 | abc | [email protected] | 2014-05-24 15:32:49 | 2344
4 | abc | [email protected] | 2014-05-28 15:32:49 | 7623
I want to get a result as follows:
我想得到如下结果：
id | name | week | commits
1 | abc | 1 | 2
2 | abc | 2 | 0
I searched online for similar solutions but couldnt get any helpful ones.
我在网上搜索了类似的解决方案，但找不到任何有用的解决方案。
I tried this query:
我试过这个查询：
SELECT date_part('week', author_date::date) AS weekly,
COUNT(author_email)
FROM commits
GROUP BY weekly
ORDER BY weekly
But its not the right result.
但它不是正确的结果。
回答by Gordon Linoff
If you have multiple years, you should take the year into account as well. One way is:
如果您有多个年份，则还应考虑年份。一种方法是：
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;
A more natural way to write this uses date_trunc():
一种更自然的写法使用date_trunc()：
SELECT date_trunc('week', author_date::date) AS weekly,
COUNT(author_email)
FROM commits
GROUP BY weekly
ORDER BY weekly;
回答by saintlyzero
It has been very long since this question was asked.
Anyways, if at all anyone comes through this.
问这个问题已经很长时间了。
无论如何，如果有任何人通过这个。
If you want the count of all the intermediate weeksas well where there are no commits/records, you can get it by providing a start_dateand end_dateto generate_series()function
如果你希望所有的计数中间周以及那里有没有提交/记录，你可以通过提供一个得到它start_date，并end_date以generate_series()功能
SELECT t1.year_week week,
t2.commit_count
FROM (SELECT week,
To_char(week, 'IYYY-IW') year_week
FROM generate_series('2020-02-01 06:06:51.25+00'::DATE,
'2020-04-05 12:12:33.25+00'::
DATE, '1 week'::interval) AS week) t1
LEFT OUTER JOIN (SELECT To_char(author_date, 'IYYY-IW') year_week,
COUNT(author_email) commit_count
FROM commits
GROUP BY year_week) t2
ON t1.year_week = t2.year_week;
The output will be:
输出将是：
week | commit_count
----------+-------------
2020-05 | 2
2020-06 | NULL
2020-07 | 1
