时间戳

首页 > 解决方案 > 玩家平均连胜时长的 SQL 查询

sql - 玩家平均连胜时长的 SQL 查询

问题描述

假设我有一个包含三列和以下数据的表：

* gamedate,playername,pointsScored
* 20180101,George,34
* 20180102,George,37
* 20180103,George,38
* 20180104,George,3
* 20180105,George,35
* 20180106,George,37
* 20180107,George,31
* 20180108,George,31
* 20180109,George,32
* 20180110,George,7

我正在寻找一个sql query能告诉我该球员平均连续多少场比赛可以保持 30+ 的分数。我知道它必须是某种group by功能，但我有点坚持如何制定这个查询。这是一个Oracle table如果有什么不同的话。

一些额外的测试日期：

with
inputs(gamedate,playername,pointsscored) as (
select to_date('20180912','yyyymmdd'), 'George',52 from dual union all
select to_date('20180907','yyyymmdd'), 'George',47 from dual union all
select to_date('20180829','yyyymmdd'), 'George',9 from dual union all
select to_date('20180823','yyyymmdd'), 'George',55 from dual union all
select to_date('20180818','yyyymmdd'), 'George',49 from dual union all
select to_date('20180811','yyyymmdd'), 'George',58 from dual union all
select to_date('20180805','yyyymmdd'), 'George',31 from dual union all
select to_date('20180730','yyyymmdd'), 'George',40 from dual union all
select to_date('20180720','yyyymmdd'), 'George',44 from dual union all
select to_date('20180712','yyyymmdd'), 'George',45 from dual union all
select to_date('20180707','yyyymmdd'), 'George',29 from dual union all
select to_date('20180701','yyyymmdd'), 'George',-5 from dual union all
select to_date('20180626','yyyymmdd'), 'George',46 from dual union all
select to_date('20180620','yyyymmdd'), 'George',22 from dual union all
select to_date('20180614','yyyymmdd'), 'George',49 from dual union all
select to_date('20180609','yyyymmdd'), 'George',40 from dual union all
select to_date('20180602','yyyymmdd'), 'George',40 from dual
)

标签： sqloracle

解决方案

在 Oracle 12.1 或更高版本中，该MATCH_RECOGNIZE子句可以快速完成此类分配：

with
  inputs(gamedate,playername,pointsscored) as (
    select to_date('20180101','yyyymmdd'), 'George', 34 from dual union all
    select to_date('20180102','yyyymmdd'), 'George', 37 from dual union all
    select to_date('20180103','yyyymmdd'), 'George', 38 from dual union all
    select to_date('20180104','yyyymmdd'), 'George',  3 from dual union all
    select to_date('20180105','yyyymmdd'), 'George', 35 from dual union all
    select to_date('20180106','yyyymmdd'), 'George', 37 from dual union all
    select to_date('20180107','yyyymmdd'), 'George', 31 from dual union all
    select to_date('20180108','yyyymmdd'), 'George', 31 from dual union all
    select to_date('20180109','yyyymmdd'), 'George', 32 from dual union all
    select to_date('20180109','yyyymmdd'), 'George',  7 from dual
  )
select playername, avg(cnt) as average_streak
from   inputs
match_recognize(
  partition by playername
  order by     gamedate
  measures     count(*) as cnt
  one row per match
  pattern ( a+ )
  define a as pointsscored >= 30
)
group by playername
;

PLAYER AVERAGE_STREAK
------ --------------
George              4

解释：

MATCH_RECOGNIZE按玩家划分输入行，并按游戏日期在每个分区内对它们进行排序。“匹配”是出现一个或多个连续行（a+在PATTERN子条款中），得分为 30 或更多（参见DEFINE子条款）。MATCH_RECOGNIZE为它找到的每场比赛返回一行，特别是它返回球员姓名和比赛中的行数（这是连胜的长度，连续行数或游戏）。外部查询按玩家分组并采用平均连胜长度。

推荐阅读