如何计算PieCloudDB中球员最长连胜？SQL查询问题求助

表结构与数据 #

Matches表记录比赛结果，结构和数据如下：

需求与预期输出 #

需要计算每位球员的最长连胜（连胜不能被平局或输球中断），预期输出：

原SQL与问题 #

使用以下SQL查询时，球员3的longest_streak字段为空值，不符合预期：

select t1.player_id, max(t2.streak) as longest_streak 
from (select distinct player_id from Matches) t1
left join (
    select player_id, count(*) as streak
    from (
        select *, rank()over(partition by player_id order by match_day) as rk1,
        rank()over(partition by player_id, results order by match_day) as rk2
        from Matches) a
    where results = 'Win'
    group by player_id, rk1-rk2) t2
on t1.player_id = t2.player_id
group by 1
order by player_id;

问题原因 #

球员3没有任何获胜记录，子查询t2中不存在该球员的数据。LEFT JOIN后t2.streak对应的值为NULL，而MAX(NULL)的结果仍为NULL，无法得到预期的0。

修正方案 #

使用COALESCE函数将MAX(t2.streak)的空值转换为0，修改后的SQL如下：

select t1.player_id, COALESCE(max(t2.streak), 0) as longest_streak 
from (select distinct player_id from Matches) t1
left join (
    select player_id, count(*) as streak
    from (
        select *, rank()over(partition by player_id order by match_day) as rk1,
        rank()over(partition by player_id, results order by match_day) as rk2
        from Matches) a
    where results = 'Win'
    group by player_id, rk1-rk2) t2
on t1.player_id = t2.player_id
group by 1
order by player_id;

说明 #

COALESCE函数会返回传入参数中的第一个非空值，当max(t2.streak)为NULL时，就会返回0，正好满足没有获胜记录的球员最长连胜为0的需求。

内容的提问来源于stack exchange，提问作者Joe