如何在PostgreSQL中按周分组 [英] How to group by week in postgresql
本文介绍了如何在PostgreSQL中按周分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个数据库表 commits
,其中包含以下列:
I've a database table commits
with the following columns:
id | author_name | author_email | author_date(时间戳)|
total_lines
id | author_name | author_email | author_date (timestamp) | 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
我在网上搜索了类似的解决方案,但找不到任何有用的解决方案。
I searched online for similar solutions but couldnt get any helpful ones.
我尝试了以下查询:
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.
推荐答案
如果您有多个年份,则也应考虑年份。一种方法是:
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;
更自然的写法是使用 date_trunc()
:
SELECT date_trunc('week', author_date::date) AS weekly,
COUNT(author_email)
FROM commits
GROUP BY weekly
ORDER BY weekly;
这篇关于如何在PostgreSQL中按周分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文