在蟑螂数据库中按每月天数分组

Question

在蟑螂数据库中，我希望对特定月份的每一天都进行这样的查询：

select count(*), sum(amount) 
from request
where code = 'code_string' 
and created_at >= '2022-07-31T20:30:00Z' and created_at < '2022-08-31T20:30:00Z' 

问题是我想在当地约会时穿。我该怎么办？我的目标是：

"月、日、计数、总和"；作为一个月的结果列。

---

更新：

我找到了一个合适的查询：

select count(amount), sum(amount), extract(month from created_at) as monthTime, extract(day from created_at) as dayTime 
from request 
where code = 'code_string' and created_at >= '2022-07-31T20:30:00Z' and created_at < '2022-08-31T20:30:00Z'
group by dayTime, monthTime 

感谢@histocrat提供更简单的答案：(通过替换

extract(month from created_at) as monthTime, extract(day from created_at) as dayTime 

通过这个：

date_part('month', created_at) as monthTime, date_part('day', created_at) as dayTime

Answer 1

要按月份和日期对结果进行分组，可以使用date_part函数。

select month, day, count(*), sum(things)
from request
where code = 'code_string'
group by date_part('month', created_at) as month, date_part('day', created_at) as day;

根据created_at的类型，您可能需要先对其进行强制转换或转换(例如group by date_part('month', created_at::timestamptz)(。