如何选择特定ID中的日期以及先前的操作。

huangapple go评论68阅读模式
英文:

How to pick out the date for a specific Action within an ID as well as the previous action

问题

我有一个ID列表,在每个ID中有多行不同操作以及操作日期。我需要提取特定操作的日期,以及在此操作之前发生的操作。

下面是一个示例:

对于ID 1,我只想要包含操作为 da 的行。
对于ID 2,我只想要包含操作为 db 的行。
对于ID 3,我只想要包含操作为 da 的行。
对于ID 4,我只想要包含操作为 dc 的行。

我没有问题使用 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

huangapple
  • 本文由 发表于 2023年3月3日 22:18:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/75628229.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定