問題描述
我有一個由以下人員創建的表:
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:
- 使用
CROSS APPLY將兩個團隊相乘(逆透視)作為單獨的行 - 使用
LAG計算之前的Metric，按合并后的Team列進行分區 - 過濾掉翻倍的行，這樣我們的原始行只有一行
- Multiply out (unpivot) the two teams as separate rows, using
CROSS APPLY - Calculate the previous
Metrics usingLAG, partitioning by the mergedTeamcolumn - 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.
結果:
| id | 家庭團隊 | 客隊 | 公制 | 上一頁 | 上一個 | 上一個 |
|---|---|---|---|---|---|---|
| 1 | A隊 | B 隊 | 5 | (空) | (空) | (空) |
| 2 | A隊 | B 隊 | 7 | 5 | (空) | (空) |
| 3 | C 組 | 團隊 D | 6 | (空) | (空) | (空) |
| 4 | Z 團隊 | A隊 | 8 | (空) | (空) | (空) |
| 5 | A隊 | B 隊 | 9 | 8 | 7 | 5 |
| 6 | C 組 | 團隊 D | 3 | 6 | (空) | (空) |
| 7 | C 組 | 團隊 D | 1 | 3 | 6 | (空) |
| 8 | 團隊 E | F 團隊 | 2 | (空) | (空) | (空) |
這篇關于SQL - 如果滿足使用多個先前列的條件，則 LAG 以獲取先前值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！