如何连胜三胜

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和妈妈连续赢了3场。KKR有3胜，但是如果我们看到日期列，它不是连续3，因此KKR不应该在答案中，输出应该是

RR 1
MUM 1

Answer 1

空隙和岛屿。计算每个岛屿的长度。最后计数是岛的长度除以3(整数除法，丢弃小数部分)。

我增加了几行(团队A和B)来说明4胜A，然后7胜B，然后4胜A，这将导致A和B的最终计数为2和2。

样本数据

DECLARE @T TABLE (TeamA varchar(50), TeamB varchar(50), Win varchar(50), dt int);

INSERT INTO @T (TeamA, TeamB, Win, dt) VALUES
('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),

('A','B','A',11),
('A','B','A',12),
('A','B','A',13),
('A','B','A',14),

('A','B','B',15),
('A','B','B',16),
('A','B','B',17),
('A','B','B',18),
('A','B','B',19),
('A','B','B',20),
('A','B','B',21),

('A','B','A',22),
('A','B','A',23),
('A','B','A',24),
('A','B','A',25);

查询

通常，您会在一个单独的表中有一个团队列表，在这里，我将它构建在一个CTE_Teams中。CTE_Counts有连续连胜的3-wins-in-a-row数。由于一个团队可能有多个连胜记录(请参阅team A)，这将被进一步总结。可以在列TeamA和TeamB中按任何顺序列出团队，因此在CROSS APPLY中的WHERE中有一个OR来捕获这两个变体。

因此，对于每个团队，只选择与此团队相关的行。这是由CROSS APPLY完成的。

然后，用不同的分区对行进行两次编号，这是典型的gaps-and-islands。行号的差异给出了组(岛和间隙)。

过滤WHERE CTE_Teams.Team = CA.Win只留下获胜队伍的岛屿。

按CTE_Teams.Team分组给出了岛的大小，即连胜数。

查询工作在Server 2008中。

WITH
CTE_Teams
AS
(
    SELECT T.TeamA AS Team
    FROM @T AS T

    UNION -- sic! not ALL

    SELECT T.TeamB AS Team
    FROM @T AS T
)
,CTE_Counts
AS
(
    SELECT
        CTE_Teams.Team
        --,CA.Win
        --,rn1 - rn2 AS GroupNumber
        --,COUNT(*) AS GroupSize
        ,COUNT(*) / 3 AS FinalCount
    FROM
        CTE_Teams
        CROSS APPLY
        (
            SELECT
                T.Win
                ,T.dt
                ,ROW_NUMBER() OVER (PARTITION BY CTE_Teams.Team 
                    ORDER BY T.dt, T.TeamA, T.TeamB) AS rn1
                ,ROW_NUMBER() OVER (PARTITION BY CTE_Teams.Team, T.Win 
                    ORDER BY T.dt, T.TeamA, T.TeamB) AS rn2
            FROM @T AS T
            WHERE
                T.TeamA = CTE_Teams.Team
                OR T.TeamB = CTE_Teams.Team
        ) AS CA
    WHERE
        CTE_Teams.Team = CA.Win
    GROUP BY
        CTE_Teams.Team
        ,CA.Win
        ,rn1 - rn2
    HAVING COUNT(*) / 3 > 0
)
SELECT
    CTE_Counts.Team
    ,SUM(CTE_Counts.FinalCount) AS FinalCount
FROM CTE_Counts
GROUP BY CTE_Counts.Team
ORDER BY CTE_Counts.Team;

结果

+------+------------+
| Team | FinalCount |
+------+------------+
| A    |          2 |
| B    |          2 |
| MUM  |          1 |
| RR   |          1 |
+------+------------+

SQL Fiddle

Answer 2

在不使用CTE的情况下解决这一问题的另一种可能方法是：

create table #a
(
teama varchar(10), teamb varchar(10), win varchar(10), dat int)

insert into #a
values
('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);

select 
team, 
win,
row_number() over (partition by team order by dat) matchnum
into #res
  from 
    (
    select teamA team, case when teamA = win then 1 else 0 end as win, dat
    from #a 
    union all
    select teamB team, case when teamB = win then 1 else 0 end , dat
    from #a 
    )A 
order by team,dat


select 
    match1.team, count(*)/3 + 1 cntHatricks
from #res match1 join #res match2 
    on match1.team = match2.team and match2.matchnum = match1.matchnum+1
join #res match3 on match1.team = match3.team and match3.matchnum = match1.matchnum+2
where 
    match1.win = 1 and match2.win = 1 and match3.win = 1            
    group by match1.team

输出

+------+-------------+
| Team | cntHatricks |
+------+-------------+
| MUM  |          1  |
| RR   |          1  |
+------+-------------+

Answer 3

尝试这个简单的查询

select  tm,sum(case when win=tm  then 1 else 0 end)/3 hattrick from @a a
inner join (select teama tm from @a union select teamb from @a) t on a.win=t.tm 
group by tm
having count(distinct dat)>2

数据

declare @a table 
(teama varchar(10), teamb varchar(10), win varchar(10), dat int)
insert into @a
values
('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)

；