sql - 如何计算表中对象的数量，按 has_many 中的列分组，其中某些条件很重要？

Question

我有一个名为汽车(id，品牌)的表。汽车可以有许多广告(id、car_id、state、dealer_name)。

我想计算dealer_name组中独特汽车的数量，其中该特定经销商是当前待售汽车的唯一经销商

https://www.db-fiddle.com/f/cjstvXSZ2sWYNqfe6LRcTc/1 (查询的预期输出也写在那里)

我已经设法在 ruby​​ 应用程序中以迭代方式执行此操作，为每个 dealer_name 执行一个查询，但是我无法在单个分组查询中执行此操作。

数据:

cars
id    make
1     'BMW'
2     'Mercedes'
3     'Mercedes'
4     'Volvo'
5     'Volvo'
6     'Volvo'
7     'Alfa Romeo'

adverts
id    car_id  state       dealer_name
1     1       'sold'      'Dealer 1'
2     2       'for sale'  'Dealer 1'
4     3       'for sale'  'Dealer 2'
5     4       'for sale'  'Dealer 2'
6     5       'for sale'  'Dealer 3'
7     5       'for sale'  'Dealer 4'
8     7       'sold'      'Dealer 4'

expected output:
dealer_name   Number of cars with only this dealer having the car currently 'for sale'
Dealer 1      1
Dealer 2      2
Dealer 3      0
Dealer 4      0

Answer 1

您可以在子查询中进行窗口计数，然后在外部查询中使用条件聚合:

select 
    dealer_name, 
    sum(case when cnt = 1 and state = 'for sale' then 1 else 0 end) cnt
from (
    select 
        dealer_name, 
        state,
        car_id, 
        count(*) over(partition by car_id, state) cnt
    from adverts a
) x 
group by dealer_name
order by dealer_name;

<强> Demo on DB Fiddle :

| dealer_name | cnt |
| ----------- | --- |
| Dealer 1    | 1   |
| Dealer 2    | 2   |
| Dealer 3    | 0   |
| Dealer 4    | 0   |

注意:这是一个有据可查的问题，所以可以使用更多!