英文:
How to pick out the date for a specific Action within an ID as well as the previous action
问题
我有一个ID列表,在每个ID中有多行不同操作以及操作日期。我需要提取特定操作的日期,以及在此操作之前发生的操作。
下面是一个示例:
对于ID 1
,我只想要包含操作为 d
和 a
的行。
对于ID 2
,我只想要包含操作为 d
和 b
的行。
对于ID 3
,我只想要包含操作为 d
和 a
的行。
对于ID 4
,我只想要包含操作为 d
和 c
的行。
我没有问题使用 WHERE action = d
子句来隔离只有操作 d
。我在获取之前的操作方面遇到了困难。
我该如何做到这一点?
英文:
I have a list of IDs, within each ID is multiple rows for different actions as well as an action date. I need to pick out the date of a specific action as well as what action come prior to this one.
An example below;
ID | Action | Date |
---|---|---|
1 | b | 01/02/2023 |
1 | c | 02/02/2023 |
1 | a | 03/02/2023 |
1 | d | 04/02/2023 |
2 | c | 01/02/2023 |
2 | b | 02/02/2023 |
2 | d | 03/02/2023 |
2 | a | 04/02/2023 |
3 | a | 01/02/2023 |
3 | d | 02/02/2023 |
3 | c | 03/02/2023 |
3 | b | 04/02/2023 |
4 | b | 01/02/2023 |
4 | a | 02/02/2023 |
4 | c | 03/02/2023 |
4 | d | 04/02/2023 |
Lets say I'm looking for Action d
. Then:
for ID 1
I'd want to only have the row where action is d
and a
for ID 2
rows where action d
and b
for ID 3
rows where action d
and a
for ID 4
rows where action d
and c
I have no problem isolating just action d using WHERE action = d
clause. I'm struggling to get the action before.
How can I do this?
答案1
得分: 0
以下是代码的中文翻译部分:
当提出这样的问题时,你应该提供创建示例数据的DDL和DML,这将更容易帮助你解决问题。
你想要选择那些操作为 d
的行,并且你在where
子句中使用了谓词来执行此操作,但你还想返回对于该ID最近执行的操作。
在这里,我们使用了一个窗口函数在公共表达式(CTE)中为每个操作分配了一个顺序号。由于我们现在知道我们可以期望前一个操作的顺序号比 d
操作小一,因此我们只需对自身加入到 CTE 并找到它。
ID | Action | Date | rn | ID | Action | Date | rn |
---|---|---|---|---|---|---|---|
1 | d | 2023-04-02 | 4 | 1 | a | 2023-03-02 | 3 |
2 | d | 2023-03-02 | 3 | 2 | b | 2023-02-02 | 2 |
3 | d | 2023-02-02 | 2 | 3 | a | 2023-01-02 | 1 |
4 | d | 2023-04-02 | 4 | 4 | c | 2023-03-02 | 3 |
英文:
When asking questions like this you should be providing DDL and DML that produces your example data. This makes it much easier to help with your question.
DECLARE @Table TABLE (ID INT, Action CHAR(1), Date DATE)
INSERT INTO @Table (ID, Action, Date) VALUES
(1, 'b', '01/02/2023'), (1, 'c', '02/02/2023'), (1, 'a', '03/02/2023'), (1, 'd', '04/02/2023'),
(2, 'c', '01/02/2023'), (2, 'b', '02/02/2023'), (2, 'd', '03/02/2023'), (2, 'a', '04/02/2023'),
(3, 'a', '01/02/2023'), (3, 'd', '02/02/2023'), (3, 'c', '03/02/2023'), (3, 'b', '04/02/2023'),
(4, 'b', '01/02/2023'), (4, 'a', '02/02/2023'), (4, 'c', '03/02/2023'), (4, 'd', '04/02/2023');
You want to select rows, where the action is d
, and you're using a predicate in the where clause to do so, but you also want to return which ever action was most recently performed for that ID as well.
;WITH rowN AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS rn
FROM @Table a
)
SELECT *
FROM rowN a
INNER JOIN rowN b
ON a.id = b.ID
AND a.rn = b.rn + 1
WHERE a.Action = 'd'
Here we've used a windowed function to give each action a sequential number in a CTE. Since we now know we can expect the previous action to have a sequential number which is one less than the d
action, we can simply just self join back to the CTE and find it.
ID | Action | Date | rn | ID | Action | Date | rn |
---|---|---|---|---|---|---|---|
1 | d | 2023-04-02 | 4 | 1 | a | 2023-03-02 | 3 |
2 | d | 2023-03-02 | 3 | 2 | b | 2023-02-02 | 2 |
3 | d | 2023-02-02 | 2 | 3 | a | 2023-01-02 | 1 |
4 | d | 2023-04-02 | 4 | 4 | c | 2023-03-02 | 3 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论