英文:
How do I conditionally increase the value of the proceeding row number by 1
问题
我需要将前一行的行号值增加1。当行遇到另一个条件时，我需要重置计数器。这可能最容易通过示例来解释：
所要创建的列是“Desired_Output”。从这个表格可以看出，我需要使用列“indicator”。我希望以下行是 n+1；除非下一行是 1。当再次遇到值 1 时，计数器需要重置。
我尝试过使用某种循环方法，但这没有产生所需的结果。
有可能以某种方式实现吗？
英文:
I need to increase the value of the proceeding row number by 1. When the row encounters another condition I then need to reset the counter. This is probably easiest explained with an example:
+---------+------------+------------+-----------+----------------+| Acct_ID | Ins_Date | Acct_RowID | indicator | Desired_Output |+---------+------------+------------+-----------+----------------+| 5841 | 07/11/2019 | 1 | 1 | 1 || 5841 | 08/11/2019 | 2 | 0 | 2 || 5841 | 09/11/2019 | 3 | 0 | 3 || 5841 | 10/11/2019 | 4 | 0 | 4 || 5841 | 11/11/2019 | 5 | 1 | 1 || 5841 | 12/11/2019 | 6 | 0 | 2 || 5841 | 13/11/2019 | 7 | 1 | 1 || 5841 | 14/11/2019 | 8 | 0 | 2 || 5841 | 15/11/2019 | 9 | 0 | 3 || 5841 | 16/11/2019 | 10 | 0 | 4 || 5841 | 17/11/2019 | 11 | 0 | 5 || 5841 | 18/11/2019 | 12 | 0 | 6 || 5132 | 11/03/2019 | 1 | 1 | 1 || 5132 | 12/03/2019 | 2 | 0 | 2 || 5132 | 13/03/2019 | 3 | 0 | 3 || 5132 | 14/03/2019 | 4 | 1 | 1 || 5132 | 15/03/2019 | 5 | 0 | 2 || 5132 | 16/03/2019 | 6 | 0 | 3 || 5132 | 17/03/2019 | 7 | 0 | 4 || 5132 | 18/03/2019 | 8 | 0 | 5 || 5132 | 19/03/2019 | 9 | 1 | 1 || 5132 | 20/03/2019 | 10 | 0 | 2 |+---------+------------+------------+-----------+----------------+
The column I want to create is 'Desired_Output'. It can be seen from this table that I need to use the column 'indicator'. I want the following row to be n+1; unless the next row is 1. The counter needs to reset when the value 1 is encountered again.
I have tried to use a loop method of some sort but this did not produce the desired results.
Is this possible in some way?
答案1
得分: 0
以下是翻译好的部分：
"The trick is to identify the group of consecutive rows starts from indicator 1 to the next 1. This is achieve by using the cross apply finding the Acct_RowID with indicator = 1 and use that as a Grp_RowID to use as partition by in the row_number() window function
select *,
Desired_Output = row_number() over (partition by t.Acct_ID, Grp_RowID
order by Acct_RowID)
from your_table t
cross apply
(
select Grp_RowID = max(Acct_RowID)
from your_table x
where x.Acct_ID = t.Acct_ID
and x.Acct_RowID <= t.Acct_RowID
and x.indicator = 1
) g"
请注意，代码部分未翻译。
英文:
The trick is to identify the group of consecutive rows starts from indicator 1 to the next 1. This is achieve by using the cross apply finding the Acct_RowID with indicator = 1 and use that as a Grp_RowID to use as partition by in the row_number() window function
select *,Desired_Output = row_number() over (partition by t.Acct_ID, Grp_RowIDorder by Acct_RowID)from your_table tcross apply(select Grp_RowID = max(Acct_RowID)from your_table xwhere x.Acct_ID = t.Acct_IDand x.Acct_RowID <= t.Acct_RowIDand x.indicator = 1) g
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论