将按月分组的行计数为列

Coderama

我有以下数据（实际上跨越了数年，但在此示例中，我只包括了4个月）

id  created_at  staff
--------------------------------
1   2010-01-01  Coder
2   2010-01-15  Developer
3   2010-03-01  Data Analyst
4   2010-01-20  Developer
5   2010-03-13  Data Analyst
6   2010-04-05  Tester
7   2010-04-01  Tester
8   2010-04-04  Business Analyst
9   2010-01-22  Business Analyst
10  2010-01-25  Coder

我想创建一个查询，该查询计算staff每个年月的数量。下面是我期望从该查询中获得的输出示例。

staff               2010-01   2010-02   2010-03   2010-04   
----------------------------------------------------------
Coder               2         0         0         0
Developer           2         0         0         0
Data Analyst        0         0         2         0
Tester              0         0         0         2
Business Analyst    1         0         0         1

任何帮助是极大的赞赏！

专线小巴

对于固定的月份列表，可以进行条件汇总：

select staff,
    sum(case when date_trunc('month', created_at) = date '2010-01-01' then 1 else 0 end) as cnt_2010_01,
    sum(case when date_trunc('month', created_at) = date '2010-02-01' then 1 else 0 end) as cnt_2010_02,
    sum(case when date_trunc('month', created_at) = date '2010-03-01' then 1 else 0 end) as cnt_2010_03,
    sum(case when date_trunc('month', created_at) = date '2010-04-01' then 1 else 0 end) as cnt_2010_04
from mytable
group by staff

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。