如何在SQL中实现tidyr的完整()？

Question

使用一个虚拟示例，我需要完成一个包含隐式缺失值的数据集。这在R中使用tidyr的函数complete是微不足道的。

library(tidyr)

df <- data.frame(Borough = c('Brooklyn', 'Brooklyn', 'Queens'),
                 Crime = c('Robbery', 'Homicide', 'Drug'),
                 Count=c(1, 2, 1))

> df
   Borough    Crime Count
1 Brooklyn  Robbery     1
2 Brooklyn Homicide     2
3   Queens     Drug     1

#Complete implicit missing values

> complete(df, Borough, Crime, fill=list(Count=0))
   Borough    Crime Count
1 Brooklyn     Drug     0
2 Brooklyn Homicide     2
3 Brooklyn  Robbery     1
4   Queens     Drug     1
5   Queens Homicide     0
6   Queens  Robbery     0

但是，在实际数据非常大并且存储在Oracle的SQL表中的情况下，如何使用SQL查询来完成这一任务？

Answer 1

交叉连接不同的行政区与犯罪，左加入原来的表，以获得缺失的行与0的罪名。

select b.borough,c.crime,coalesce(t.count,0) as count
from (select distinct borough from tbl) b
cross join (select distinct crime from tbl) c
left join tbl t on t.borough=b.borough and t.crime=c.crime