我有一个在工作项上发生的"操作"的表格.我最初使用Row Number和Partition按顺序对表中的每个操作进行计数,如下所示工作得很好.
Current Table个
+---------------+--------------------+-----------+-----------+--------------+
| Work Item Ref | Work Item DateTime | Auditable | Action ID | Action Count |
+---------------+--------------------+-----------+-----------+--------------+
| 2500 | 19/05/2023 10:01 | Yes | 20 | 1 |
| 2501 | 19/05/2023 10:02 | Yes | 11 | 1 |
| 2501 | 19/05/2023 10:03 | Yes | 9 | 2 |
| 2501 | 19/05/2023 10:04 | No | 19 | 3 |
| 2501 | 19/05/2023 10:06 | Yes | 5 | 4 |
| 2502 | 19/05/2023 10:04 | No | 2 | 1 |
| 2502 | 19/05/2023 10:05 | Yes | 4 | 2 |
+---------------+--------------------+-----------+-----------+--------------+
Code个
ROW_NUMBER() OVER(PARTITION BY [Work Item Ref] ORDER BY [Work Item DateTime] asc) AS [Action Count]
但现在,我们需要相同的计数,但只在"Audable"列显示为"Yes"的情况下才需要.我try 在PARTITION BY
和ORDER BY
之间添加一个WHERE子句,但意识到这不是正确的语法.我需要它基本上是only个顺序计数,当标准满足时.如何实现下面的示例?
Desired Results个
+---------------+--------------------+-----------+-----------+--------------+
| Work Item Ref | Work Item DateTime | Auditable | Action ID | Action Count |
+---------------+--------------------+-----------+-----------+--------------+
| 2500 | 19/05/2023 10:01 | Yes | 20 | 1 |
| 2501 | 19/05/2023 10:02 | Yes | 11 | 1 |
| 2501 | 19/05/2023 10:03 | Yes | 9 | 2 |
| 2501 | 19/05/2023 10:04 | No | 19 | |
| 2501 | 19/05/2023 10:06 | Yes | 5 | 3 |
| 2502 | 19/05/2023 10:04 | No | 2 | |
| 2502 | 19/05/2023 10:05 | Yes | 4 | 1 |
+---------------+--------------------+-----------+-----------+--------------+