Déterminer quelles personnes restent dans une population/cohorte de l'année de base à la dernière année et aux années intermédiaires.

Question

J'ai un tableau avec des colonnes : c

Le tableau contient des informations sur les relations entre les joueurs et les équipes sur plusieurs années (par exemple, de 2010 à 2020).

Ce que je veux savoir, c'est : - Pour l'année de départ, quels joueurs appartenaient à l'équipe Blueberry - Pour l'année 2, quels joueurs de l'année de départ appartiennent toujours à l'équipe Blueberry. - et ainsi de suite jusqu'à la dernière année étudiée

J'ai le sentiment tenace que ce tableau peut être présenté comme un tableau unique utilisant une seule requête.

Aidez-moi, s'il vous plaît.

Year Player_id team_id
2012 kitliu    Blueberry
2012 bobross   Blueberry
2012 jacksnake Blueberry
2012 kittyjr   Blueberry
2013 kitliu    Blueberry
2013 bobross   Blueberry
2013 narutol   yellow
2014 kitliu    Blueberry
2014 narutol   Red

result:
2012 kitliu    Blueberry
2012 bobross   Blueberry
2012 jacksnake Blueberry
2012 kittyjr   Blueberry
2013 kitliu    Blueberry
2013 bobross   Blueberry
2014 kitliu    Blueberry

result, count retained player/team combos from base year:
Year Count
2012 4
2013 2
2012 1```

Answer 1

Je pense que la requête ci-dessous pourrait aider, comme je l'ai fait comme une alternative, c'est-à-dire en sélectionnant par chaque année si le joueur n'a pas changé d'équipe.

  SELECT year, playerid, 
  count(distinct teamid) 
   from 
  table t group by year, playerid having 
    playerid,count(distinct teamid) IN (
     Select 
     playerid,count(distinct teamid) group 
     by 
    playerid)
     ;

Answer 2

Enumérer les joueurs de l'année de base. Utilisez ensuite cette liste pour vérifier qu'il n'y a pas de lacunes :

select team, year, count(*)
from (select t.*,
             row_number() over (partition by team, player_id order by year) as seqnum
      from t
      where year >= 2012
     ) t
where year = 2012 + seqnum - 1
group by team, year;

Ici est un db<>fiddle.

Answer 3

Vous pouvez utiliser la fonction analytique.

SELECT YEAR, PLAYER_ID, TEAM_ID 
FROM
(SELECT YEAR, PLAYER_ID, TEAM_ID,
ROW_NUMBER() OVER (PARTITION BY PLAYER_ID, TEAM_ID ORDER BY YEAR) AS RN,
DENSE_RANK() OVER (PARTITION BY TEAM_ID ORDER BY YEAR) AS RNK_YEAR 
FROM YOUR_TABLE)
WHERE RN = RNK_YEAR

Vous pouvez utiliser count et group by pour obtenir le nombre d'années en plus de cette requête.

Santé !

Answer 4

J'espère que cela vous conviendra :

with teamyr(year, playerid, teamid) as
(
select min(year), playerid, teamid
from teams
group by playerid, teamid
)
select t1.year, t1.playerid, t1.teamid
from teamyr t1
where t1.year = (select min(year) from teamyr)
union all
select t2.year, t1.playerid, t1.teamid
from teamyr t1
inner join teams t2 on t2.playerid = t1.playerid and t2.teamid = t1.teamid
and t2.year > t1.year