英文:
row_number resets based on two columns in Bigquery
问题
我的目标是生成以下row_number，称为transaction_in_row。
row_number应该基于（partition_by）customer和has_transaction列进行重置。我的问题在于黄色列，Bigquery中的row_number将返回3，而不是1。
这是我当前使用的代码：
row_number() over(partition by customer, has_transaction order by month asc) as transaction_in_row
任何帮助将不胜感激，如有更多细节的问题，请随时提问。谢谢！
英文:
My goal is to generate the following row_number, called transaction_in_row
The row_number should reset based on (partition_by) customer and has_transaction column. My issue is on the yellow column, the row_number in Bigquery will return 3 instead of 1.
This is the code I'm currently using:
row_number() over(partition by customer, has_transaction order by month asc) as transaction_in_row
Any help will be appreciated and feel free to shoot me any question for more details. Thanks!
答案1
得分: 2
以下是翻译好的部分：
有一个间隔和岛屿问题，解决它的一种方法是使用两个row_number函数的差异，如下所示：
With tbl_name As (
Select 'x' As customer, 1 As month, TRUE As has_transaction Union All
Select 'x', 2, TRUE Union All
Select 'x', 3, FALSE Union All
Select 'x', 4, FALSE Union All
Select 'x', 5, TRUE Union All
Select 'x', 6, TRUE Union All
Select 'x', 7, FALSE),
grps as
(
select *,
row_number() over (partition by customer order by month) -
row_number() over (partition by customer, has_transaction order by month) grp
from tbl_name
)
select customer, month, has_transaction,
row_number() over (partition by customer, has_transaction, grp order by month) as transaction_in_row
from grps
order by customer, month
输出：
英文:
You have a gaps and islands problem, one approach to solve it is using the difference of two row_number functions as the following:
With tbl_name As (
Select 'x' As customer, 1 As month, TRUE As has_transaction Union All
Select 'x', 2, TRUE Union All
Select 'x', 3, FALSE Union All
Select 'x', 4, FALSE Union All
Select 'x', 5, TRUE Union All
Select 'x', 6, TRUE Union All
Select 'x', 7, FALSE),
grps as
(
select *,
row_number() over (partition by customer order by month) -
row_number() over (partition by customer, has_transaction order by month) grp
from tbl_name
)
select customer, month, has_transaction,
row_number() over (partition by customer, has_transaction, grp order by month) as transaction_in_row
from grps
order by customer, month
Output:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论