sql - 计算 UNIQUE 匹配值数量的语法

Question

我正在处理一个如下所示的数据集:

    | Host      | Risk           | Name      |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | Critical       | ValueA    |
    | 10.1.1.1  | High           | ValueB    |
    | 10.1.1.1  | High           | ValueB    |
    | 10.1.1.1  | High           | ValueB    |
    | 10.1.1.1  | Critical       | ValueC    |
    | 10.1.1.1  | Critical       | ValueC    |
    | 10.1.1.1  | Critical       | ValueC    |
    | 10.1.1.1  | Critical       | ValueC    |

我正在尝试找出一个查询，该查询将生成如下所示的摘要:

    | Host      | Critical     | High      |
    | 10.1.1.1  | 2            | 1         |

关键在下面有一个“2”，因为 Name 字段(“ValueA”和“ValueC”)只有 2 个 DISTINCT 值。 High 下面有一个“1”，因为只有 1 个不同的 Name 值(“ValueB”)。对我来说棘手的部分是我不是要计算行数，而是要计算匹配的不同值。如果您想知道，数据是重复的，因为还有其他列包含唯一值，但它们与此查询无关。

我自己能够得到的最接近的如下所示，但这只会产生“关键”列，我无法弄清楚如何添加逻辑以获取“高”列:

    select Host, COUNT(DISTINCT Name) as Critical
    from [table]
    WHERE Risk = 'Critical'
    group by 1;

任何建议将不胜感激。我尝试了“COUNTIF”函数，但一直收到错误“无法识别的函数 countif”，这似乎很奇怪，因为 BigQuery 文档 ( https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#countif ) 中列出了“COUNTIF”。也试图让 CASE 工作，但没有取得很大进展。

谢谢!

Answer 1

下面是 BigQuery 标准 SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '10.1.1.1' Host, 'Critical' Risk, 'ValueA' Name UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueA' UNION ALL
  SELECT '10.1.1.1', 'High', 'ValueB' UNION ALL
  SELECT '10.1.1.1', 'High', 'ValueB' UNION ALL
  SELECT '10.1.1.1', 'High', 'ValueB' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueC' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueC' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueC' UNION ALL
  SELECT '10.1.1.1', 'Critical', 'ValueC' 
)
SELECT 
  Host, 
  COUNT(DISTINCT IF(Risk='Critical', Name, NULL)) Critical,
  COUNT(DISTINCT IF(Risk='High', Name, NULL)) High
FROM `project.dataset.table`
GROUP BY Host   

结果

Row Host        Critical    High     
1   10.1.1.1    2           1