sql - 如何计算 PostgreSQL 中多个日期范围内的出现次数

Question

我可以进行查询，以获取每月和每个商店光顾一家商店的 18 至 24 岁顾客的数量。我是这样做的:

select year, month, shop_id, count(birthday) 
from customers 
where birthday 
BETWEEN '1992-01-01 00:00:00' AND '1998-01-01 00:00:00'
group by year, month, shop_id;

现在，我在同时查询多个范围时遇到了问题。

我目前有这个数据库模式:

shop_id | birthday | year | month |
--------+----------+------+--------
 567   | 1998-10-10 | 2014 | 10 |
 567   | 1996-10-10 | 2014 | 10 |
 567   | 1985-10-10 | 2014 | 10 |
 234   | 1990-10-10 | 2014 | 10 |
 123   | 1970-01-10 | 2014 | 10 |
 123   | 1974-01-10 | 2014 | 11 |

我想得到这样的东西:

shop_id | year | month | 18 < age < 25 | 26 < age < 35
--------+------+-------+---------------+-------------
567   |  2014  | 10    | 2             | 1
234   |  2014  | 10    | 1             | 0
123   |  2014  | 10    | 0             | 0

在第一个查询中，它不管理一个商店没有客户的情况。没有怎么得到0？

如何同时查询多个日期范围？

Answer 1

用例语句代替过滤器:

select year, month, shop_id, 
count(case when birthday between <range1> then 1 end) RANGE1,
count(case when birthday between <range2> then 1 end) RANGE2,
count(case when birthday between <range3> then 1 end) RANGE3
from customers 
group by year, month, shop_id;