用于计算 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"，因为"名称"字段只有 2 个不同的值("值 A"和"值 C"(。 High 下面有一个"1"，因为只有 1 个不同的名称值("ValueB"(。 对我来说，棘手的部分是我不是在尝试计算行数，而只是计算匹配的不同值。 如果您想知道，数据是重复的，因为还有其他列包含唯一值，但它们与此查询无关。

我自己能够获得的最接近的如下，但这只产生了"关键"列，我不知道如何添加逻辑来获得"高"列：

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

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

谢谢！

Answer 1

下面是 BigQuery Standard 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