dimanche 12 juin 2016

How to find the most common group of players

I am trying to find the frequency or count of player compositions using SQL. Here is my dataset

event key                   team_id player_id
12235384031441051453625936  1       27
12235384031441051453625936  1       11
12235384031441051453625936  1       42
12235384031441051453625936  1       15
12235384031441051453625936  1       6
12235384031441051453625936  2       30
12235384031441051453625936  2       35
12235384031441051453625936  2       9
12235384031441051453625936  2       6
12235384031441051453625936  2       28
12235384031441051453626082  1       14
12235384031441051453626082  1       28
12235384031441051453626082  1       27
12235384031441051453626082  1       9
12235384031441051453626082  1       6
12235384031441051453626082  2       11
12235384031441051453626082  2       27
12235384031441051453626082  2       42
12235384031441051453626082  2       15
12235384031441051453626082  2       6

So for each event, there players on team 1 and team 2, the player_ids represent a player. I have roughly 500 events, and a lot of these players will appear in multiple events, but not always with the same players. Every team will always have 5 players, and the event keys represent a game. So I basically have 500 of the dataset above.

I am trying to find the most common team composition. For example, using the dataset above, I see players 27,11,42,15,6 in event ending in 936 and event ending in 082. So based on THIS dataset, this team composition has a COUNT of 2.

I am trying to get an output that counts the amount of times a group of players appear in the same team together and I am struggling with coming up with a coherent statement that does this.

Aucun commentaire:

Enregistrer un commentaire