Postgres:按两列分组-按分组包括所有唯一的组合,0表示空计数

首先选择所有操作系统/设备组合,然后再次外部连接表:

select o.os, d.device, count(e.events_count)
from (select distinct os from events) o
cross join (select distinct device from events) d
left join events e on e.os = o.os and e.device = d.device
group by o.os, d.device
order by o.os, d.device;

(对于此类数据,通常有一个OS表和一个设备表。您可以交叉连接这些表,然后外部连接事件表。)

我会使用 cross join

select t.os, t.device, count(c.os)
from 
(
  select os, device
  from
  (
    select distinct os from cases
  ) t1
  cross join 
  (
    select distinct device from cases
  ) t2
) t 
left join cases c on t.os = c.os and 
                     t.device = c.device
group by t.os, t.device