BigQuery-使用标准SQL聚合行

Question

考虑这个表

+------------+----------------+-------------+
|    date    |   region_name  |  population |
+------------+----------------+-------------+
| 2000-02-11 | Lower Normandy |   1.000.000 |
+------------+----------------+-------------+
| 2000-02-11 | Upper Normandy |   1.100.000 |
+------------+----------------+-------------+
| 2020-04-25 | Lower Normandy |   1.800.000 |
+------------+----------------+-------------+
| 2020-04-25 | Upper Normandy |   1.900.000 |
+------------+----------------+-------------+

我想将Lower Normandy和Upper Normandy的行聚合为Normandy，并根据date列对population求和。

预期结果是：

+------------+----------------+-------------+
|    date    |   region_name  |  population |
+------------+----------------+-------------+
| 2000-02-11 |       Normandy |   2.100.000 |
+------------+----------------+-------------+
| 2020-04-25 |       Normandy |   3.700.000 |
+------------+----------------+-------------+

然后将使用此聚合的结果来创建一个新视图。

如何在BigQuery中使用标准SQL来完成此操作？

Answer 1

您可以使用case表达式来更改名称：

select date,
(case when region_name like '%Normandy' then 'Normandy' else region_name end) as region_name,
sum(population) as population
from t
group by 1, 2;

这与任何以"诺曼底"结尾的内容相匹配。当然，您可以使用region_name in ('Upper Normandy', 'Lower Normany')来获得更高的精度(以及更多的类型(。

或者，如果您只关心这四行，您可以只分配region_name:

select date, 'Normandy' as region_name, sum(population) as population
from t
where region_name like '%Normandy'
group by date;