使用 sqlite3 我試圖獲取記錄組的連續數，直到新序列開始，它應該從 1 開始。問題是我想按重復分組的值，但我希望在休息后重新開始計數在那個值。
資料：
DROP TABLE IF EXISTS so_demo;
CREATE TABLE so_demo(
game_pk INT, game_ds DATE, away_team VARCHAR, home_team VARCHAR
);
INSERT INTO so_demo VALUES
(529410,'2018-03-29','ARI','COL'),
(529462,'2018-04-02','SD','COL'),
(529508,'2018-04-06','COL','ATL'),
(529556,'2018-04-09','COL','SD'),
(529590,'2018-04-12','WSH','COL'),
(530343,'2018-06-08','ARI','COL')
ALTER TABLE so_demo ADD home_away VARCHAR;
UPDATE so_demo SET home_away = 'home' WHERE home_team='COL';
UPDATE so_demo SET home_away = 'away' WHERE home_team <> 'COL';
這給我留下了：
| 游戲包 | game_ds | 客隊 | 主隊 | home_away |
|---|---|---|---|---|
| 529410 | 2018-03-29 | 阿里 | 科爾 | 家 |
| 529462 | 2018-04-02 | 標清 | 科爾 | 家 |
| 529508 | 2018-04-06 | 科爾 | ATL | 離開 |
| 529556 | 2018-04-09 | 科爾 | 標清 | 離開 |
| 529590 | 2018-04-12 | WSH | 科爾 | 家 |
| 530343 | 2018-06-08 | 阿里 | 科爾 | 家 |
我想要一個專欄，說明每個“立場”（一系列連續的主場或客場比賽）已經進行了多長時間。在上面的示例中，我正在尋找：1,2, 1,2, 1,2,.
我試過了：
row_number()
WITH intermed AS (
SELECT *, (row_number() OVER (partition by home_away)) AS seq
FROM so_demo
ORDER BY game_pk
)
SELECT * FROM intermed
>>
529410,2018-03-29,ARI,COL,home,1
529462,2018-04-02,SD,COL,home,2
529508,2018-04-06,COL,ATL,away,1
529556,2018-04-09,COL,SD,away,2
529590,2018-04-12,WSH,COL,home,3
530343,2018-06-08,ARI,COL,home,4
因為我正在home_away對計數進行磁區，所以休息后不會重新啟動，我得到1,2, 1,2, 3,4. 這只是這些值的順序計數。
lead/lag
我可以用它來劃分家庭看臺或路看臺斷裂的位置：
SELECT *,
away_team != lag(away_team, 1) OVER (order by game_pk ASC)
AND away_team = 'COL'
AS start_away,
home_team != lag(home_team, 1) OVER (ORDER BY game_pk ASC)
AND home_team = 'COL'
AS start_home
FROM so_demo
>>
game_pk game_ds away_team home_team home_away start_away start_home
529410 2018-03-29 ARI COL home 0
529462 2018-04-02 SD COL home 0 0
529508 2018-04-06 COL ATL away 1 0
529556 2018-04-09 COL SD away 0 0
529590 2018-04-12 WSH COL home 0 1
530343 2018-06-08 ARI COL home 0 0
但我不知道如何將它們結合起來。
uj5u.com熱心網友回復：
我們標記每一個更改，用于count() over()在每次連續運行中分組，然后row_number()按日期對它們進行編號。
select game_pk
,game_ds
,away_team
,home_team
,home_away
,row_number() over(partition by grp order by game_ds) as h_a_cnt
from (
select *
,count(chng) over(order by game_ds) as grp
from (
select *
,case when home_away != lag(home_away) over(order by game_ds) then 1 end as chng
from t
) t
) t
| 游戲包 | game_ds | 客隊 | 主隊 | home_away | h_a_cnt |
|---|---|---|---|---|---|
| 529410 | 2018-03-29 | 阿里 | 科爾 | 家 | 1 |
| 529462 | 2018-04-02 | 標清 | 科爾 | 家 | 2 |
| 529508 | 2018-04-06 | 科爾 | ATL | 離開 | 1 |
| 529556 | 2018-04-09 | 科爾 | 標清 | 離開 | 2 |
| 529590 | 2018-04-12 | WSH | 科爾 | 家 | 1 |
| 530343 | 2018-06-08 | 阿里 | 科爾 | 家 | 2 |
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/511641.html
標籤：sqlsqlite