SQL CASE语句(多对一)

Question

从下表中的多个位置的多种花朵类型：

Location1....Lantana
Location1....Lantana
Location1....Alba
Location1....Alba
Location2....Lantana
Location2....Gallica
Location2....Gallica
Location3....Noisette
Location3....Noisette

我想为4种不同的花朵类型创建4列(布尔型) CASE语句，如果在该位置至少有一种花朵具有4种类型中的1种，则每列返回'1‘。

示例：

如果Location1只有50架Lantana和50架Alba

Location2有20辆Lantanas，30辆Gallicas

所需的输出表

Location#  |  Total # of flowers  | # of Lantana  |  Alba  |  Gallica  |  Noisette
Location1...........100.................1...............1............0.........0
Location2...........50..................1...............0............1.........0

Answer 1

以下是一种方法：

select location, count(*) as num_flowers,
       max(case when flow = 'Lantana' then 1 else 0 end) as Lantana,
       max(case when flow = 'Alba' then 1 else 0 end) as Alba,
       max(case when flow = 'Gallica' then 1 else 0 end) as Gallica,
       max(case when flow = 'Noisette' then 1 else 0 end) as Noisette
from t
group by location;

Answer 2

不知何故，我总是尽量避免使用CASE (当然，在我可以的情况下)，至少是因为CASE语句使查询看起来笨重而笨拙

下面是CASE-less版本:o)

#standardSQL
SELECT location, COUNT(*) AS num_flowers,
   SIGN(COUNTIF(flower = 'Lantana')) AS Lantana,
   SIGN(COUNTIF(flower = 'Alba')) AS Alba,
   SIGN(COUNTIF(flower = 'Gallica')) AS Gallica,
   SIGN(COUNTIF(flower = 'Noisette')) AS Noisette
FROM `project.dataset.table`
GROUP BY location
-- ORDER BY location

注意：SIGN只是将实际计数设为1或0(根据您的要求)。因此，如果您删除SIGN() - yo将获得实际计数-这可能比0/1更有用

您可以使用示例中的虚拟数据来测试/使用它，如下所示

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Location1' location, 'Lantana' flower UNION ALL
  SELECT 'Location1', 'Lantana' UNION ALL
  SELECT 'Location1', 'Alba' UNION ALL
  SELECT 'Location1', 'Alba' UNION ALL
  SELECT 'Location2', 'Lantana' UNION ALL
  SELECT 'Location2', 'Gallica' UNION ALL
  SELECT 'Location2', 'Gallica' UNION ALL
  SELECT 'Location3', 'Noisette' UNION ALL
  SELECT 'Location3', 'Noisette' 
)
SELECT location, COUNT(*) AS num_flowers,
   SIGN(COUNTIF(flower = 'Lantana')) AS Lantana,
   SIGN(COUNTIF(flower = 'Alba')) AS Alba,
   SIGN(COUNTIF(flower = 'Gallica')) AS Gallica,
   SIGN(COUNTIF(flower = 'Noisette')) AS Noisette
FROM `project.dataset.table`
GROUP BY location
ORDER BY location