Skip to content

Instantly share code, notes, and snippets.

@mustangostang

mustangostang/gist:4ddfa3de221991c963a5c3f3dc82dbbe

Created November 24, 2017 16:16
Show Gist options
  • Save mustangostang/4ddfa3de221991c963a5c3f3dc82dbbe to your computer and use it in GitHub Desktop.
Save mustangostang/4ddfa3de221991c963a5c3f3dc82dbbe to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
SELECT
*
FROM
(SELECT
PlayerId,
SUM(GKSave) AS GKSave,
SUM(GKReflex) AS GKReflex,
SUM(GKExit) AS GKExit,
SUM(GKSave + 3 * GKReflex + 0.2 * GKExit + 1) / SUM(
2 * GoalAgainst + GKSave + GKReflex + 1
) AS GKVote,
SUM(DirectKick) AS DirectKick,
SUM(CrossAction) AS CrossAction,
SUM(Acceleration) AS Acceleration,
SUM(Dribbling) AS Dribbling,
SUM(OffDuel) AS OffDuel,
SUM(1vs1Passive) AS 1vs1Passive,
SUM(Marking) AS Marking,
SUM(Ball5050) AS Ball5050,
SUM(Pressing) AS Pressing,
SUM(AerialAbility) AS AerialAbility,
SUM(sub.Done) AS FoulDone,
SUM(sub.Received) AS FoulReceived,
SUM(WithoutBall) AS WithoutBall,
SUM(HeadShoot) AS HeadShot,
SUM(Shoot) AS Shot,
SUM(Offside) AS Offside,
SUM(MissedPenalty) AS MissedPenalty,
SUM(PenaltySave) AS PenaltySave,
SUM(AssistGoal) AS AssistGoal,
SUM(Assist) AS Assist,
SUM(Opportunity) AS Opportunity,
SUM(Interception) AS Interception,
SUM(Clearance) AS Clearance,
SUM(AnticipationAgainst) AS AnticipationAgainst,
SUM(AnticipationDone) AS AnticipationDone,
SUM(Tackle) AS Tackle,
SUM(LinkupPlay) AS LinkupPlay,
SUM(CounterAttack) AS CounterAttack,
SUM(Through) AS Through,
SUM(Yellow) AS YellowCards,
SUM(Red) AS RedCards,
SUM(ActionToShoot) AS ActionToShoot,
SUM(ActionToCross) AS ActionToCross,
SUM(Pass) AS Pass,
SUM(OtherPass) AS OtherPass,
SUM(Goal) AS Goal,
SUM(GoalAgainst) AS GoalAgainst,
SUM(OwnGoal) AS OwnGoal,
SUM(Plus) AS SpecialPlus,
SUM(Minus) AS SpecialMinus,
SUM(BallLost) AS BallLost,
(
(SUM(Pass) + SUM(OtherPass)) / (SUM(PassTOT) + 0.01)
) AS Accuracy,
SUM(PassTOT) AS PassTOT #SUM(ShootOut) AS ShootOut,
#SUM(ShootOnGoal) ShootOnGoal,
#SUM(Assist+AssistGoal+ActionToShoot+KeySpecial) AS KeyPass,
#SUM(CleanSheet) AS CleanSheet,
#SUM(GKSave+GKReflex) AS Save,
#SUM(KeySpecial) AS Special
FROM
(SELECT
match_events_stats_final.playerId AS PlayerId,
SUM(IF(tag_type = 1
AND tag_goal = 0, 1, 0)) AS GKSave,
SUM(IF(tag_type = 2
AND tag_goal = 0, 1, 0)) AS GKReflex,
SUM(IF(tag_type = 3
AND tag_goal = 0, 1, 0)) AS GKExit,
SUM(IF(tag_type = 5, 1, 0)) AS IndirectKick,
#cross
SUM(IF(tag_type = 6, 1, 0)) AS DirectKick,
#kick shot
SUM(
IF(
tag_type = 15
AND tag_vote IN (0, 1, 2),
1,
0
)
) AS CrossAction,
SUM(IF(tag_type = 17, 1, 0)) AS "Acceleration",
SUM(
IF(
tag_type = 18
AND tag_one_vs_one_side IN (1, 2)
AND tag_vote IN (1, 2),
1,
0
)
) AS "Dribbling",
SUM(
IF(
tag_type = 18
AND tag_one_vs_one_side = 0
AND tag_vote IN (1, 2),
1,
0
)
) AS OffDuel,
SUM(
IF(
tag_type = 19
AND tag_one_vs_one_side = 1
AND tag_vote IN (1, 2),
1,
0
)
) AS 1vs1Passive,
#vengo puntato
SUM(
IF(
tag_type = 19
AND tag_one_vs_one_side = 0
AND tag_vote IN (1, 2),
1,
0
)
) AS "Marking",
SUM(
IF(tag_type = 20
AND tag_vote IN (1, 2), 1, 0)
) AS Ball5050,
SUM(IF(tag_type = 21, 1, 0)) AS "Pressing",
SUM(
IF(
tag_type IN (22, 23)
AND tag_vote IN (1, 2),
1,
0
)
) AS AerialAbility,
SUM(IF(tag_type = 24, 1, 0)) AS "Foul",
#fallo commesso
SUM(IF(tag_type IN (25-29, 43-44), 1, 0)) AS "Other Foul",
#fallo commesso r_playerId chi lo ha subito
SUM(IF(tag_type = 30, 1, 0)) AS WithoutBall,
SUM(
IF(
tag_type = 36
AND tag_goal = 0
AND tag_foot = 3
AND shootZone NOT LIKE "o%",
1,
0
)
) AS HeadShoot,
#non fuori
SUM(
IF(
tag_type = 36
AND tag_goal = 0
AND tag_foot != 3
AND shootZone NOT LIKE "o%",
1,
0
)
) AS Shoot,
#non fuori
SUM(IF(tag_type = 37, 1, 0)) AS "Offside",
SUM(IF(tag_type = 38
AND tag_goal = 0, 1, 0)) AS MissedPenalty,
SUM(
IF(
tag_type IN (1, 2)
AND tag_goal = 0
AND r_tag_type = 38,
1,
0
)
) AS PenaltySave,
SUM(IF(tag_assist = 1, 1, 0)) AS AssistGoal,
SUM(IF(tag_assist IN (2, 3), 1, 0)) AS Assist,
SUM(
IF(
tag_goal = 0
AND tag_opportunity > 0,
1,
0
)
) AS "Opportunity",
SUM(
IF(
tag_interception IN (1, 2)
AND match_events_stats_final.teamId = r_teamId,
1,
0
)
) AS "Interception",
SUM(IF(tag_clearance = 1, 1, 0)) AS "Clearance",
SUM(
IF(
tag_anticipation = 1
AND tag_vote IN (1),
1,
0
)
) AS AnticipationAgainst,
SUM(
IF(
tag_anticipation = 2
AND tag_vote IN (1, 2),
1,
0
)
) AS AnticipationDone,
SUM(
IF(
tag_tackle = 1
AND tag_vote IN (1, 2),
1,
0
)
) AS "Tackle",
SUM(IF(tag_linkup_play = 1, 1, 0)) AS LinkupPlay,
SUM(IF(tag_counterattack = 1, 1, 0)) AS "Counterattack",
SUM(IF(tag_through = 1, 1, 0)) AS "Through",
SUM(IF(tag_card = 1, 1, 0)) AS Yellow,
SUM(IF(tag_card = 2, 1, 0)) AS Red,
SUM(
IF(
r_tag_type = 36
AND tag_own_goal = 0
AND tag_assist = 0
AND match_events_stats_final.playerId != r_playerId
AND match_events_stats_final.teamId = r_teamId = 1,
1,
0
)
) AS ActionToShoot,
SUM(
IF(
r_tag_type = 15
AND tag_own_goal = 0
AND tag_assist = 0
AND match_events_stats_final.playerId != r_playerId
AND match_events_stats_final.teamId = r_teamId = 1,
1,
0
)
) AS ActionToCross,
SUM(
IF(
tag_type IN (7, 8, 10, 13)
AND match_events_stats_final.teamId = r_teamId = 1,
1,
0
)
) AS "Pass",
SUM(
IF(
tag_type IN (41, 12)
AND match_events_stats_final.teamId = r_teamId = 1,
1,
0
)
) AS OtherPass,
#smart e long_range
SUM(
IF(
tag_goal = 1
AND tag_type NOT IN (1, 2, 3),
1,
0
)
) AS Goal,
SUM(
IF(
tag_goal = 1
AND tag_type IN (1, 2, 3),
1,
0
)
) AS GoalAgainst,
SUM(IF(tag_own_goal = 1, 1, 0)) AS OwnGoal,
SUM(
IF(
tag_type IN (6, 36)
AND tag_goal = 0
AND shootZone NOT LIKE "b%"
AND shootZone NOT LIKE "o%",
1,
0
)
) AS ShootOnGoal,
#Tiro in Porta
SUM(
IF(
tag_type IN (6, 36)
AND tag_goal = 0
AND (
shootZone LIKE "b%"
OR shootZone LIKE "o%"
),
1,
0
)
) AS ShootOut,
#Tiro non in Porta
SUM(IF(tag_type IN (24, 25-29, 43-44), 1, 0)) AS Done,
#fallo commesso,
0 AS Received,
#fallo subito
0 AS CleanSheet,
0 AS MinPlayed,
0 AS GameWeek,
match_events_stats_final.matchId AS MatchId,
SUM(
IF(
r_id IN
(SELECT
id
FROM
match_events_stats_final
WHERE tag_type = 22
AND r_tag_type = 36
AND playerId = r_playerId
AND teamId = r_teamId
AND playerId != 0
AND match.seasonId = 14288 ###################################################
),
1,
0
)
) AS KeySpecial,
SUM(IF(tag_special = 1, 1, 0)) AS Plus,
SUM(IF(tag_special = 2, 1, 0)) AS Minus,
SUM(IF(tag_ball_lost = 1, 1, 0)) AS BallLost,
SUM(
IF(tag_type IN (7, 8, 10, 13, 41, 12), 1, 0)
) AS PassTOT
FROM
match_events_stats_final
LEFT JOIN `match`
ON match_events_stats_final.matchId = `match`.id
LEFT JOIN match_player
ON match_player.playerId = match_events_stats_final.playerId
AND match_player.matchId = `match`.id
WHERE match_events_stats_final.playerId != 0
AND match.seasonId = 14288 ###################################################
GROUP BY match_events_stats_final.playerId,
gameweek,
match_events_stats_final.matchId
UNION
SELECT
match_events_stats_final.r_playerId AS PlayerId,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0 AS ShootOnGoal,
0 AS ShootOut,
0 AS Done,
SUM(IF(tag_type IN (24, 25-29, 43-44), 1, 0)) AS Received,
#fallo subito
0 AS CleanSheet,
0 AS MinPlayed,
0 AS GameWeek,
match_events_stats_final.matchId AS MatchId,
0,
0,
0,
0,
0
FROM
match_events_stats_final
LEFT JOIN `match`
ON match_events_stats_final.matchId = `match`.id
WHERE match_events_stats_final.r_playerId != 0
AND match.seasonId = 14288
GROUP BY match_events_stats_final.r_playerId,
gameweek,
matchId) AS sub
GROUP BY PlayerId
ORDER BY PlayerId) AS tmp
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment