BigQuery 中的行号? [英] Row number in BigQuery?
问题描述
有没有办法获取 BigQuery 中每条记录的行号?(从规范来看,我没有看到任何关于它的内容)有一个 NTH() 函数,但它适用于重复的字段.
在 BigQuery 中有一些不需要行号的场景,例如使用 TOP() 或 LIMIT 函数.但是,我需要它来模拟一些分析函数,例如累积 sum().为此,我需要用序列号标识每条记录.有什么解决方法吗?
预先感谢您的帮助!
狮子座
2018 更新:如果你想要的只是每一行的唯一 ID
#standardSQL选择 GENERATE_UUID() uuid, *发件人表
2018 #standardSQL 解决方案:
SELECTROW_NUMBER() OVER() row_number,contributor_username,数数从 (SELECT 贡献者_用户名, COUNT(*) 计数来自`publicdata.samples.wikipedia`GROUP BY 贡献者_用户名按计数 DESC 排序限制 5)
<小时><块引用>
但是关于查询执行期间资源超出:无法在分配的内存中执行查询.OVER() 运算符使用了太多内存.."
好的,让我们重现那个错误:
SELECT *, ROW_NUMBER() OVER()从`publicdata.samples.natality`
是的 - 发生这种情况是因为 OVER() 需要将所有数据放入一个 VM - 您可以使用 PARTITION 来解决:
SELECT *, ROW_NUMBER() OVER(PARTITION BY year, month) rn从`publicdata.samples.natality`
<小时><块引用>
但现在很多行都有相同的行号,我想要的只是每行一个不同的 ID"
好的,好的.让我们使用分区来为每一行提供一个行号,然后我们将该行号与分区字段结合起来以获得每行的唯一 ID:
SELECT *, FORMAT('%i-%i-%i', year, month, ROW_NUMBER() OVER(PARTITION BY year, month)) id从`publicdata.samples.natality`
<小时>2013 年的原始解决方案:
好消息:BigQuery 现在有一个 row_number 函数.
简单例子:
SELECT [field], ROW_NUMBER() OVER()发件人 [表格]GROUP BY [字段]
更复杂的工作示例:
SELECTROW_NUMBER() OVER() row_number,贡献者_用户名,数数,从 (选择贡献者_用户名,计数(*)计数,来自 [publicdata:samples.wikipedia]GROUP BY 贡献者_用户名按计数 DESC 排序限制 5)
Is there any way to get row number for each record in BigQuery? (From the specs, I haven't seen anything about it) There is a NTH() function, but that applies to repeated fields.
There are some scenarios where row number is not necessary in BigQuery, such as the use of TOP() or LIMIT function. However, I need it to simulate some analytical functions, such as a cumulative sum(). For that purpose I need to identify each record with a sequential number. Any workaround on this?
Thanks in advance for your help!
Leo
2018 update: If all you want is a unique id for each row
#standardSQL
SELECT GENERATE_UUID() uuid
, *
FROM table
2018 #standardSQL solution:
SELECT
ROW_NUMBER() OVER() row_number, contributor_username,
count
FROM (
SELECT contributor_username, COUNT(*) count
FROM `publicdata.samples.wikipedia`
GROUP BY contributor_username
ORDER BY COUNT DESC
LIMIT 5)
But what about "Resources exceeded during query execution: The query could not be executed in the allotted memory. OVER() operator used too much memory.."
Ok, let's reproduce that error:
SELECT *, ROW_NUMBER() OVER()
FROM `publicdata.samples.natality`
Yes - that happens because OVER() needs to fit all data into one VM - which you can solve with PARTITION:
SELECT *, ROW_NUMBER() OVER(PARTITION BY year, month) rn
FROM `publicdata.samples.natality`
"But now many rows have the same row number and all I wanted was a different id for each row"
Ok, ok. Let's use partitions to give a row number to each row, and let's combine that row number with the partition fields to get an unique id per row:
SELECT *
, FORMAT('%i-%i-%i', year, month, ROW_NUMBER() OVER(PARTITION BY year, month)) id
FROM `publicdata.samples.natality`
The original 2013 solution:
Good news: BigQuery now has a row_number function.
Simple example:
SELECT [field], ROW_NUMBER() OVER()
FROM [table]
GROUP BY [field]
More complex, working example:
SELECT
ROW_NUMBER() OVER() row_number,
contributor_username,
count,
FROM (
SELECT contributor_username, COUNT(*) count,
FROM [publicdata:samples.wikipedia]
GROUP BY contributor_username
ORDER BY COUNT DESC
LIMIT 5)
这篇关于BigQuery 中的行号?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!