基于赢、平和输的连续连胜/非连胜，按团队分组

Question

我正在尝试根据输赢和平局标准计算结果数据库中的连胜纪录。

目标：获得按团队分组的最长连胜/非连胜纪录

我已经尝试了来自其他线程的不同SQL查询建议，但是我要么错过了分组，要么错过了团队列，并且通常只采用2向选项(输赢( - 我需要3向选项(

赢，输，平，包括不赢，不输和不平局(看着这个 - https://www.sqlteam.com/articles/detecting-runs-or-streaks-in-your-data

但我不知道如何让团队包括分组到组合中

方案：

CREATE TABLE teamresults (matchid varchar(255), date DATE, time TIME, team varchar(255), teamresult varchar(255))

数据样本：

INSERT INTO teamresults (matchid,"date","time",team,teamresult) VALUES 
('030420181800acfc','2018-04-03','18:00:00','AC Horsens','L')
,('080420181600brac','2018-04-08','16:00:00','AC Horsens','L')
,('150420181400aaac','2018-04-15','14:00:00','AC Horsens','L')
,('180420181800acfc','2018-04-18','18:00:00','AC Horsens','D')
,('210420181600fcac','2018-04-21','16:00:00','AC Horsens','L')
,('270420181900acfc','2018-04-27','19:00:00','AC Horsens','L')
,('040520181900acaa','2018-05-04','19:00:00','AC Horsens','W')
,('110520181900fcac','2018-05-11','19:00:00','AC Horsens','L')
,('180520182000acbr','2018-05-18','20:00:00','AC Horsens','D')
,('210520181800fcac','2018-05-21','18:00:00','AC Horsens','L')
,('120520191200veac','2019-05-12','12:00:00','AC Horsens','W')
,('190520191400acve','2019-05-19','14:00:00','AC Horsens','D')
,('140720191400acfc','2019-07-14','14:00:00','AC Horsens','L')
,('210720191200siac','2019-07-21','12:00:00','AC Horsens','W')
,('270720191730acfc','2019-07-27','17:30:00','AC Horsens','L')
,('040820191600brac','2019-08-04','16:00:00','AC Horsens','W')
,('010420181400hoag','2018-04-01','14:00:00','AGF','W')
,('080420181800agsi','2018-04-08','18:00:00','AGF','W')
,('130420181900agfc','2018-04-13','19:00:00','AGF','W')
,('170420181900fcag','2018-04-17','19:00:00','AGF','L')
,('230420181900agho','2018-04-23','19:00:00','AGF','L')
,('300420181900siag','2018-04-30','19:00:00','AGF','W')
,('060520181200agob','2018-05-06','12:00:00','AGF','W')
,('130520181800obag','2018-05-13','18:00:00','AGF','W')
,('190520181600ags�','2018-05-19','16:00:00','AGF','D')
;

下面的查询确实有效，但只执行单个输入语句 - 所以我只能获得连胜、输或平局 - 不是非赢、非输和非平局。

SELECT
team,
MAX(cnt)
FROM
(
SELECT
team,
COUNT(*) AS cnt
FROM 
(
SELECT
team, 
date,
teamresult,
SUM(CASE WHEN teamresult <> 'W'  THEN 1 else 0 END) 
OVER (PARTITION BY team 
ORDER BY date 
ROWS UNBOUNDED PRECEDING) AS dummy
FROM teamresults
) dt
WHERE teamresult = 'W' 
GROUP BY team, dummy
) dt
GROUP BY team;

我还希望能够找到按团队分组的最长非条纹

SQL 小提琴可在此处获得： http://sqlfiddle.com/#!18/3a2ac/1

提前致谢

更新：戈登查询正在工作，但这些查询在 postgres/cockroach 中不起作用 - 所以现在尝试通过窗口函数 rank(( 将它们转换为支持的查询

select team, teamresult, cnt, rank() over (order by cnt desc) from
(SELECT team, teamresult, COUNT(*) as cnt
FROM (SELECT tr.*,
ROW_NUMBER() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
ROW_NUMBER() OVER (PARTITION BY team, teamresult ORDER BY "date", "time") as seqnum_r
FROM teamresults tr
) tr
WHERE teamresult = 'W'
GROUP BY team, teamresult, (seqnum - seqnum_r)
ORDER BY ROW_NUMBER() OVER (PARTITION BY team ORDER BY COUNT(*) DESC)) as ranked

这确实给了我这样的输出(来自我的数据库的数据样本(：

FC København    W   9   1
AaB             W   8   2
FC København    W   8   2
FC København    W   8   2
FC København    W   8   2
Brøndby IF      W   7   6
FC Midtjylland  W   7   6
FC København    W   7   6
FC København    W   7   6
FC København    W   7   6
Esbjerg fB      W   6   11
FC Midtjylland  W   6   11
AaB             W   6   11
Brøndby IF      W   6   11
Brøndby IF      W   6   11

预期产出：

Team           Longest consecutive streak
FC København       9
AaB                8
Brøndby IF         7
FC Midtjylland     7
Esbjerg fB         6

Answer 1

您可以使用以下方法获得所有条纹：

SELECT team, teamresult, COUNT(*) as cnt
FROM (SELECT tr.*,
ROW_NUMBER() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
ROW_NUMBER() OVER (PARTITION BY team, teamresult ORDER BY "date", "time") as seqnum_r
FROM teamresults tr
) tr
GROUP BY team, teamresult, (seqnum - seqnum_r);

您可以修改此设置以获得每支球队最长的连胜纪录：

SELECT TOP(1) WITH TIES team, teamresult, COUNT(*) as cnt
FROM (SELECT tr.*,
ROW_NUMBER() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
ROW_NUMBER() OVER (PARTITION BY team, teamresult ORDER BY "date", "time") as seqnum_r
FROM teamresults tr
) tr
WHERE teamresult = 'W'
GROUP BY team, teamresult, (seqnum - seqnum_r)
ORDER BY ROW_NUMBER() OVER (PARTITION BY team ORDER BY COUNT(*) DESC);

如果您只想要任何类型的最长条纹，请删除WHERE。 如果您希望每个团队的每种类型最长，请将teamresult添加到PARTITION BY中。

这是一个数据库<>小提琴。

编辑：

如果你想要非获胜，你需要通过一个表达式进行分区：

SELECT TOP(1) WITH TIES team,
(CASE WHEN teamresult = 'W' THEN 'W' END) as is_win,
COUNT(*) as cnt
FROM (SELECT tr.*,
ROW_NUMBER() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
ROW_NUMBER() OVER (PARTITION BY team, (CASE WHEN teamresult = 'W' THEN 'W' END) ORDER BY "date", "time") as seqnum_r
FROM teamresults tr
) tr
-- WHERE teamresult = 'W'
GROUP BY team, (CASE WHEN teamresult = 'W' THEN 'W' END), (seqnum - seqnum_r)
ORDER BY ROW_NUMBER() OVER (PARTITION BY team ORDER BY COUNT(*) DESC)

Answer 2

感谢戈登，我用以下查询解决了我的案子：

最长连续连胜(用 D 代替 W 表示平局，L 表示输球(按团队分组

select team, max(cnt) longeststreak from (
SELECT team, teamresult, COUNT(*) as cnt
FROM (SELECT tr.*,
RANK() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
RANK() OVER (PARTITION BY team, teamresult ORDER BY "date", "time") as seqnum_r
FROM teamresults tr
) tr
WHERE teamresult = 'W'
GROUP BY team, teamresult, (seqnum - seqnum_r)
ORDER BY RANK() OVER (PARTITION BY team ORDER BY COUNT(*) DESC)
)
group by team
order by longeststreak DESC

最长连续不胜纪录(用D代替W表示非平局，L表示不败(按团队分组

select team, max(cnt) longestnonstreak from (
SELECT team, 
(CASE WHEN teamresult = 'W' THEN 'W' END) as is_win,
COUNT(*) as cnt
FROM (SELECT tr.*,
RANK() OVER (PARTITION BY team ORDER BY "date", "time") as seqnum,
RANK() OVER (PARTITION BY team, (CASE WHEN teamresult = 'W' THEN 'W' END) ORDER BY "date", "time") as seqnum_r
FROM teamresults tr
) tr
GROUP BY team, (CASE WHEN teamresult = 'W' THEN 'W' END), (seqnum - seqnum_r)
ORDER BY RANK() OVER (PARTITION BY team ORDER BY COUNT(*) DESC)
)
group by team
order by longestnonstreak desc

感谢戈登协助解决方案。