問題描述

我有一個由以下人員創建的表:

I have a table created by:

CREATE TABLE #test_table 
(
id INT
,EventName VARCHAR(50)
,HomeTeam VARCHAR(25)
,Metric INT
)

INSERT INTO #test_table VALUES
(1, 'Team A vs Team B', 'Team A', 5),
(2, 'Team A vs Team B', 'Team A', 7),
(3, 'Team C vs Team D', 'Team C', 6),
(4, 'Team Z vs Team A', 'Team Z', 8),
(5, 'Team A vs Team B', 'Team A', 9),
(6, 'Team C vs Team D', 'Team C', 3),
(7, 'Team C vs Team D', 'Team C', 1),
(8, 'Team E vs Team F', 'Team E', 2)

結果:

id  EventName           HomeTeam    Metric
------------------------------------------
1   Team A vs Team B    Team A      5
2   Team A vs Team B    Team A      7
3   Team C vs Team D    Team C      6
4   Team Z vs Team A    Team Z      8
5   Team A vs Team B    Team A      9
6   Team C vs Team D    Team C      3
7   Team C vs Team D    Team C      1
8   Team E vs Team F    Team E      2

A 想要計算一個新列 PreviousMetricN，其中 N 可以是 1, 2, 3, ... 這顯示了 Metric 的前一個值，但前提是HomeTeam 參與了之前的活動.例如:

A want to calculate a new column PreviousMetricN where N can be 1, 2, 3, ... which shows the previous value for Metric, but only if the HomeTeam was involved in the previous event. For example:

id  EventName           HomeTeam    Metric  PreviousMetric1 PreviousMetric2
------------------------------------------------------------------------
1   Team A vs Team B    Team A      5       NULL            NULL
2   Team A vs Team B    Team A      7       5               NULL
3   Team C vs Team D    Team C      6       NULL            NULL
4   Team Z vs Team A    Team Z      8       NULL            NULL
5   Team A vs Team B    Team A      9       8               7
6   Team C vs Team D    Team C      3       6               NULL
7   Team C vs Team D    Team C      1       3               6
8   Team E vs Team F    Team E      2       NULL            NULL

我一直在嘗試使用 PARTITION BY 子句中的新分組變量的 LAG 變體，例如

I have been trying variations of LAG with a new grouping variable in the PARTITION BY clause such as

LAG(Metric) OVER(Partition by (CASE WHEN CHARINDEX(HomeTeam, EventName)>0 THEN 1 ELSE 0 END) ORDER BY id)

但沒有任何成功.這怎么辦?

but without any success. How can this be done?

我也在這里為熊貓問過這個問題:Pandas shift - 如果滿足多個條件，則獲取之前的值

I've also asked this question for Pandas here: Pandas shift - get previous value if multiple conditions satisfied

推薦答案

我在這里看不到使用窗口函數和單次掃描表格的答案.我們可以在單次掃描中執行此查詢，如下所示:

I see no answer here that uses window functions and a single scan of the table. We can do this query in a single scan as follows:

讓我們假設您在另一列中有 AwayTeam.

Let us assume you have the AwayTeam in another column.

如果你還沒有這個并且你想從 EventData 中解析它:
我們可以使用:SUBSTRING(EventData, CHARINDEX(' vs ', EventData) + 4)
我敦促您遵循適當的規范化并將其創建為表格中的適當列.

If you don't have this yet and you wanted to parse it out of EventData:
We could use: SUBSTRING(EventData, CHARINDEX(' vs ', EventData) + 4)
I urge you to follow proper normalization and create this as a proper column in your table.

我們的算法是這樣運行的:

Our algorithm runs like this:

1. 使用CROSS APPLY
將兩個團隊相乘(逆透視)作為單獨的行
2. 使用LAG計算之前的Metric，按合并后的Team列進行分區
3. 過濾掉翻倍的行，這樣我們的原始行只有一行

1. Multiply out (unpivot) the two teams as separate rows, using CROSS APPLY
2. Calculate the previous Metrics using LAG, partitioning by the merged Team column
3. Filter back down the doubled up rows, so that we only get a single row for each of our original ones

SELECT id, HomeTeam, AwayTeam, Metric, Prev1, Prev2, Prev3
FROM (

  SELECT *
    ,Prev1 = LAG(Metric, 1) OVER (PARTITION BY v.Team ORDER BY id)
    ,Prev2 = LAG(Metric, 2) OVER (PARTITION BY v.Team ORDER BY id)
    ,Prev3 = LAG(Metric, 3) OVER (PARTITION BY v.Team ORDER BY id)
    -- more of these ......
  FROM test_table
  CROSS APPLY (VALUES (HomeTeam, 1),(AwayTeam, 0)) AS v(Team,IsHome)
) AS t

WHERE IsHome = 1
-- ORDER BY id  --if necessary

重要的是，我們無需使用多種不同的排序、分區或排序，也無需使用自聯接即可完成此操作.只需一次掃描.

Importantly, we can do this without the use of multiple different sorts, partitions or ordering, and without the use of a self-join. Just a single scan.

結果:

這篇關于SQL - 如果滿足使用多個先前列的條件，則 LAG 以獲取先前值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！