Created
November 24, 2017 16:16
-
-
Save mustangostang/4ddfa3de221991c963a5c3f3dc82dbbe to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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