Postgres 按季度分组 [英] Postgres group by quarter

问题描述

我有包含列的表:主题、人员、已发布日期.我想创建一个查询来帮助我计算每个人每个季度在特定主题中写了多少次.示例:

I have table with columns: topic, person, published_date. I would like to create query which help me compute how many times every person wrote in specific topic in every quarter. Example:

topic person published_date

'world'  JS   2016-05-05 
'world'  JS   2016-05-10
'nature'  AR   2016-12-01

应该返回类似的东西

topic person quarter how_many_times
'world'  JS     2          2
'nature' AR     4          1

我可以按主题和人分组

select topic, person, published_date, count(*) from table group by topic, person, published_date

但是如何将published_date 分成几个季度?

but how group published_date into quarters?

推荐答案

假设 published_date 是日期类型的列，您可以像这样使用 extract 函数:

Assuming that the published_date is a date type column you can use the extract function like this:

select 
  topic, 
  person,
  extract(quarter from published_date) as quarter, 
  count(*)
from 
  table1
group by 
  topic, 
  person,
  extract(quarter from published_date)
order by 
  extract(quarter from published_date) asc

示例 SQL 小提琴

如果日期可以属于不同的年份，您可能希望将年份添加到选择和分组依据中.

If the dates can fall into different years you might want to add the year to the select and group by.

这篇关于Postgres 按季度分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！