我为这个问题做了一个
SQL Fiddle,如果这让任何人都更容易.
我有各种各样的幻想体育数据库,我想弄清楚的是如何得出“当前连胜”数据(如果球队赢得了他们的最后2场比赛,则为’W2′,如果他们输了,则为’L1’他们在赢得前一场比赛后的最后一场比赛 – 如果他们将最近的比赛打成平局,则为’T1′.
这是我的基本架构：
CREATE TABLE FantasyTeams (
team_id BIGINT NOT NULL
)
CREATE TABLE FantasyMatches(
match_id BIGINT NOT NULL,home_fantasy_team_id BIGINT NOT NULL,away_fantasy_team_id BIGINT NOT NULL,fantasy_season_id BIGINT NOT NULL,fantasy_league_id BIGINT NOT NULL,fantasy_week_id BIGINT NOT NULL,winning_team_id BIGINT NULL
)
wins_team_id列中的NULL值表示该匹配的平局.
这是一个示例DML语句,其中包含6个团队的一些示例数据和3周的比赛：
INSERT INTO FantasyTeams SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 INSERT INTO FantasyMatches SELECT 1,2,1,4,44,2 UNION SELECT 2,5,5 UNION SELECT 3,6,3,3 UNION SELECT 4,45,2 UNION SELECT 5,3 UNION SELECT 6,6 UNION SELECT 7,46,2 UNION SELECT 8,3 UNION SELECT 9,NULL GO
这是一个所需输出的例子(基于上面的DML),我甚至无法开始弄清楚如何派生：
| TEAM_ID | STEAK_TYPE | STREAK_COUNT | |---------|------------|--------------| | 1 | T | 1 | | 2 | W | 3 | | 3 | W | 3 | | 4 | T | 1 | | 5 | L | 2 | | 6 | L | 1 |
我尝试过使用子查询和CTE的各种方法,但我不能把它放在一起.我想避免使用游标,因为我可以拥有一个大型数据集以便将来运行它.我觉得可能有一种方法涉及表变量,这些变量以某种方式将这些数据连接到自身,但我仍在努力.
解决方法
由于您使用的是sql Server 2012,因此可以使用几个新的窗口函数.
with C1 as
(
select T.team_id,case
when M.winning_team_id is null then 'T'
when M.winning_team_id = T.team_id then 'W'
else 'L'
end as streak_type,M.match_id
from FantasyMatches as M
cross apply (values(M.home_fantasy_team_id),(M.away_fantasy_team_id)) as T(team_id)
),C2 as
(
select C1.team_id,C1.streak_type,C1.match_id,lag(C1.streak_type,C1.streak_type)
over(partition by C1.team_id
order by C1.match_id desc) as lag_streak_type
from C1
),C3 as
(
select C2.team_id,C2.streak_type,sum(case when C2.lag_streak_type = C2.streak_type then 0 else 1 end)
over(partition by C2.team_id
order by C2.match_id desc rows unbounded preceding) as streak_sum
from C2
)
select C3.team_id,C3.streak_type,count(*) as streak_count
from C3
where C3.streak_sum = 0
group by C3.team_id,C3.streak_type
order by C3.team_id;
C1计算每个团队的streak_type并匹配.
C2找到match_id desc排序的前一个streak_type.
C3生成由match_id desc排序的运行总和streak_sum,保持0 a long,因为streak_type与最后一个值相同.
主查询总结了streak_sum为0的条纹.
