Trouvez la plus longue séquence de scores parfaits par joueur

Ceci est un problème d'espace et d'îlot, vous pouvez essayer d'utiliser la fonction aggravée de condition SUM avec la fonction de fenêtre, en obtenant le numéro de l'écart.

puis utilisez MAX et COUNT fonction de la fenêtre à nouveau.

Requête 1 :

| player_id |              longest_streak | longest_streak |
|-----------|-----------------------------|----------------|
|       756 | 2018-12-04T19:57:48.961111Z |              2 |
|       399 | 2018-06-10T12:11:18.041521Z |              1 |
|       395 |  2018-12-02T08:56:06.83033Z |              1 |

Résultats :

WITH CTE AS (
    SELECT *,
           SUM(CASE WHEN points = 100 THEN 1 END) OVER(PARTITION BY player_id ORDER BY time) - 
           SUM(1) OVER(ORDER BY time) RN
    FROM T
)
SELECT player_id,
       MAX(longest_streak) longest_streak,
       MAX(cnt) longest_streak 
FROM (
  SELECT player_id,
         MAX(time) OVER(PARTITION BY rn,player_id) longest_streak, 
         COUNT(*) OVER(PARTITION BY rn,player_id)  cnt
  FROM CTE 
  WHERE points > 0
) t1
GROUP BY player_id

p>

Une façon de faire est de regarder combien de lignes entre les résultats non-100 précédents et suivants. Pour obtenir la longueur des stries:

with s as (
      select s.*,
             row_number() over (partition by player_id order by time) as seqnum,
             count(*) over (partition by player_id) as cnt          
      from scores s
     ),
     streaks as (
      select s.*,
             coalesce(next_seqnum - prev_seqnum) over (partition by player_id) as length,
             max(next_seqnum - prev_seqnum) over (partition by player_id) as max_length,
             max(next_seqnum) over (partition by player_id) as max_next_seqnum
      from (select s.*,
                   coalesce(max(seqnum) filter (where score <> 100) over (partition by player_id order by time), 0) as prev_seqnum,
                   coalesce(max(seqnum) filter (where score <> 100) over (partition by player_id order by time), cnt + 1) as next_seqnum
            from s
           ) s
      where score = 100
     )
select s.*
from streaks s
where length = max_length and
      next_seqnum = max_next_seqnum;

Vous pouvez ensuite incorporer les autres conditions:

with s as (
      select s.*,
             row_number() over (partition by player_id order by time) as seqnum,
             count(*) over (partition by player_id) as cnt          
      from scores s
     )
select s.*,
       coalesce(next_seqnum, cnt + 1) - coalesce(prev_seqnum, 0) - 1 as length
from (select s.*,
             max(seqnum) filter (where score <> 100) over (partition by player_id order by time) as prev_seqnum,
             max(seqnum) filter (where score <> 100) over (partition by player_id order by time) as next_seqnum
      from s
     ) s
where score = 100;

Une lacunes-et -island en effet.

En supposant:

• Les "séries" ne sont pas interrompues par des lignes d'autres joueurs.
• Toutes les colonnes sont définies NOT NULL . (Sinon, vous devez faire plus.)

Cela devrait être le plus simple et le plus rapide car il ne nécessite que deux Fonctions de la fenêtre row_number () :

SELECT DISTINCT ON (player_id)
       player_id, count(*) AS seq_len, min(ts) AS time_began
FROM  (
   SELECT player_id, points, ts
        , row_number() OVER (PARTITION BY player_id ORDER BY ts) 
        - row_number() OVER (PARTITION BY player_id, points ORDER BY ts) AS grp
   FROM   tbl
   ) sub
WHERE  points = 100
GROUP  BY player_id, grp  -- omit "points" after WHERE points = 100
ORDER  BY player_id, seq_len DESC, time_began DESC;

db fiddle ici

En utilisant le nom de colonne ts au lieu de time , qui est un mot réservé dans SQL standard. C'est autorisé dans Postgres, mais avec des limitations et c'est toujours une mauvaise idée de l'utiliser comme identifiant.

Le "truc" est de soustraire les numéros de ligne pour que les lignes consécutives tombent dans le même groupe ( grp ) par (player_id, points) . Ensuite filtrez ceux avec 100 points, regroupez-les par groupe et ne renvoyez que le résultat le plus long et le plus récent par joueur.
Explication de base de la technique:

• Sélectionnez la séquence continue la plus longue

Nous pouvons utiliser GROUP BY et DISTINCT ON dans le même SELECT , GROUP BY est appliqué avant DISTINCT ON . Considérez la séquence d'événements dans une requête SELECT :

• Meilleur moyen d'obtenir un résultat nombre avant que LIMIT ne soit appliqué

À propos de DISTINCT ON :

• Sélectionner la première ligne de chaque groupe GROUP BY?