BigQuery中的行号？ [英] Row number in BigQuery?

问题描述

有什么方法可以获取BigQuery中每条记录的行号？ （从规范中，我没有看到任何关于它的内容）有一个NTH（）函数，但它适用于重复的字段。

数字在BigQuery中不是必需的，例如使用TOP（）或LIMIT函数。但是，我需要它来模拟一些分析函数，如累计和（）。为此，我需要用连续的数字来标识每条记录。任何解决方法吗？

感谢您的帮助！

Leo

$ b $好消息：BigQuery现在有一个row_number函数。

简单的例子：

pre $ SELECT $ [$] $ $ $ $ $ $ $ $ $ $ $ b

更复杂的工作示例：

  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）
  

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

解决方案

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屋！