在Presto的时间戳字段中按月计数发生次数

Question

我有一个时间戳类型的字段，我想每月统计记录，如下所示：

Date
2021-01-23
2021-01-12
2021-03-12

应返回

Jan Mar
2    1

我如何在prestodb中做到这一点？

Answer 1

如果您想要数据透视，您可以执行以下操作：

-- sample data
WITH dataset (date) AS (
VALUES (date '2021-01-23'),
(date '2021-01-12'),
(date '2021-03-12')
) 
--query
select count_if(month(date) = 1) as Jan,
count_if(month(date) = 2) as Feb,
count_if(month(date) = 3) as Mar 
-- ...
from dataset

输出：

1

Jan	Febr	March
2	0