sql - 如何取得三连胜

Question

这是表格:

([TeamA],[TeamB],[Win],[date])

('KKR','HYD','KKR',1), 
('KKR','MUM','MUM',2), 
('RCB','HYD','HYD',3), 
('DEL','PUB','PUB',4), 
('RR','PUB','RR',4), 
('RR','DEL','RR',5),
('RCB','CSK','CSK',6),
('RR','CSK','RR',7),
('CSK','MUM','MUM',7),
('MUM','DEL','MUM',8),
('HYD','PUNE','PUNE',9),
('PUB','DEL','DEL',9),
('KKR','DEL','KKR',10),
('KKR','RCB','KKR',10)

所需答案应该是连胜 3 场的球队和计数。例如，RR 和 MUM 连续 3 次获胜。 KKR 有 3 场胜利，但是如果我们看到日期列，它不是连续 3 场，因此 KKR 不应该出现在答案中，输出应该是

RR 1
MUM 1

Answer 1

我的方法(可能可以用更干净的方式完成):

WITH cte AS
(
  SELECT TeamA AS team FROM #tab
  UNION 
  SELECT TeamB FROM #tab
), cte2 AS 
(
  SELECT c.team
      ,[opponent] = CASE WHEN c.team = t.teamA THEN t.teamB ELSE t.teamA END
      ,t.[win]
      ,t.[day]
      ,[is_winner] = CASE WHEN c.team = t.[win] THEN 1 ELSE 0 END
  FROM cte c
  JOIN #tab t
    ON c.team = t.teamA
    OR c.team = t.teamB
), cte3 AS
(
  SELECT team, [day], [is_winner],
    r =  ROW_NUMBER() OVER (PARTITION BY team ORDER BY [day])
  FROM cte2
), cte4 AS
(
  SELECT team, Length = MAX(r) - MIN(r) + 1 
  FROM (SELECT team, r
          ,rn=r-ROW_NUMBER() OVER (PARTITION BY team ORDER BY r)
        FROM cte3
        WHERE is_winner = 1) a
  GROUP BY team, rn
)
SELECT team, SUM(Length/3) AS [Number_of_hat_tricks] 
FROM cte4
WHERE Length >= 3
GROUP BY team;

LiveDemo

输出:

╔══════╦══════════════════════╗
║ team ║ Number_of_hat_tricks ║
╠══════╬══════════════════════╣
║ MUM  ║                    1 ║
║ RR   ║                    1 ║
╚══════╩══════════════════════╝

它是如何工作的:

• cte - 获取所有团队
• cte2 - 为每个团队找到对手并检查团队是否获胜
• cte3 - 添加连续数字
• cte4 - 计算每个岛屿的长度
• 最终 - 获得 >= 3 的岛屿并将它们相加(整数除法用于将行中的 6 场胜利计为 2，将行中的 9 场胜利计为 3，...)

最后一个想法:

最后一列中的值对于同一团队必须是唯一的:

('RR','CSK','RR',7)
('CSK','MUM','MUM',7)

CSK vs RR - 7
CSK vs MUM - 7

使用当前数据不可能以稳定的方式对其进行排序。所以它应该是日期和时间部分:

CSK vs RR  2015-12-07 10:00
CSK vs MUM 2015-12-07 21:00       -- now we know that it is the second match