BIGQUERY标准的SQL相当于去年()从传统的SQL
问题描述:
我有标识和名称的表BIGQUERY标准的SQL相当于去年()从传统的SQL
id | name
----------
1 | apple
1 | banana
2 | carrot
3 | lemon
3 | orange
在传统的SQL,一个能写出像
SELECT
id,
LAST(name) AS last_record_of_name,
FROM
[project:table]
GROUP BY 1
声明和结果将是
id | last_record_of_name
----------
1 | banana
2 | carrot
3 | orange
这利用了函数的LAST https://cloud.google.com/bigquery/docs/reference/legacy-sql#last
如果在BigQuery中使用标准sql,是否有类似的功能?
答
来自传统SQL的关于LAST
的事情是,输出对于您给出的示例是未定义的;它没有指定在这种情况下如何确定“最后”,因为输入表扫描不能保证有任何特定的顺序。由于写的,你可以表达从BigQuery中的标准SQL与ANY_VALUE
此相同的查询,例如:
WITH SampleInput AS (
SELECT 1 AS id, 'apple' AS name UNION ALL
SELECT 1, 'banana' UNION ALL
SELECT 2, 'carrot' UNION ALL
SELECT 3, 'lemon' UNION ALL
SELECT 3, 'orange'
)
SELECT
id,
ANY_VALUE(name) AS last_record_of_name
FROM SampleInput
GROUP BY id;
+----+---------------------+
| id | last_record_of_name |
+----+---------------------+
| 1 | apple |
| 2 | carrot |
| 3 | lemon |
+----+---------------------+
我不认为这是你以后,虽然,如果我们的目标是让基于一些标准,如name
值的排列顺序的“最后”的值,那么你可以使用ARRAY_AGG
与ORDER BY
和LIMIT 1
,如:
WITH SampleInput AS (
SELECT 1 AS id, 'apple' AS name UNION ALL
SELECT 1, 'banana' UNION ALL
SELECT 2, 'carrot' UNION ALL
SELECT 3, 'lemon' UNION ALL
SELECT 3, 'orange'
)
SELECT
id,
ARRAY_AGG(name ORDER BY name DESC LIMIT 1)[OFFSET(0)] AS last_record_of_name
FROM SampleInput
GROUP BY id;
+----+---------------------+
| id | last_record_of_name |
+----+---------------------+
| 1 | banana |
| 2 | carrot |
| 3 | orange |
+----+---------------------+
查询的行为定义明确,并根据您的样本输入和输出提供期望的结果。