· 7 years ago · Dec 14, 2018, 01:30 AM
1DROP TABLE IF EXISTS oj_stats.person_Events_Extra;
2SET @p = NULL, @c = 0, @dd = NULL, @d = NULL;
3CREATE TABLE oj_stats.person_Events_Extra
4SELECT a.personId, a.personName, a.personCountryId, a.personContinentId, a.eventId, a.PBSingle, a.PBAverage,
5 a.comps, a.rounds, a.finals, a.attempts, a.completedSolves, a.averages, a.DNFs, a.DNFAverages, b.PBs, b.singlePBs, b.averagePBs,
6 a.bestPos, a.worstPos, a.podiums, a.gold, a.silver, a.bronze, a.wcPodiums, a.wcGold, a.wcSilver, a.wcBronze,
7 a.records, a.singleRecords, a.averageRecords, a.WRs, a.singleWRs, a.averageWRs, a.CRs, a.singleCRs, a.averageCRs, a.NRs, a.singleNRs, a.averageNRs
8FROM
9 (SELECT personId, personName, personCountryId, personContinentId, eventId,
10 MIN(CASE WHEN best > 0 THEN best END) 'PBSingle',
11 (CASE WHEN eventId NOT IN ('444bf','555bf','333mbf','333mbo') THEN MIN(CASE WHEN average > 0 THEN average END) ELSE NULL END) 'PBAverage',
12 COUNT(DISTINCT competitionId) 'comps',
13 COUNT(*) 'rounds',
14 COUNT(CASE WHEN roundTypeId IN ('c','f') THEN 1 END) 'finals',
15 COUNT(CASE WHEN value1 NOT IN (0,-2) THEN 1 END)+COUNT(CASE WHEN value2 NOT IN (0,-2) THEN 1 END)+COUNT(CASE WHEN value3 NOT IN (0,-2) THEN 1 END)+COUNT(CASE WHEN value4 NOT IN (0,-2) THEN 1 END)+COUNT(CASE WHEN value5 NOT IN (0,-2) THEN 1 END) 'attempts',
16 COUNT(CASE WHEN value1 > 0 THEN 1 END)+COUNT(CASE WHEN value2 > 0 THEN 1 END)+COUNT(CASE WHEN value3 > 0 THEN 1 END)+COUNT(CASE WHEN value4 > 0 THEN 1 END)+COUNT(CASE WHEN value5 > 0 THEN 1 END) 'completedSolves',
17 COUNT(CASE WHEN average > 0 THEN 1 END) 'averages',
18 COUNT(CASE WHEN value1 = -1 THEN 1 END)+COUNT(CASE WHEN value2 = -1 THEN 1 END)+COUNT(CASE WHEN value3 = -1 THEN 1 END)+COUNT(CASE WHEN value4 = -1 THEN 1 END)+COUNT(CASE WHEN value5 = -1 THEN 1 END) 'DNFs',
19 COUNT(CASE WHEN average = -1 THEN 1 END) 'DNFAverages',
20 MIN(CASE WHEN best > 0 THEN pos END) 'bestPos',
21 MAX(pos) 'worstPos',
22 COUNT(CASE WHEN roundTypeId IN ('c','f') AND pos <= 3 AND best > 0 THEN 1 END) 'podiums',
23 COUNT(CASE WHEN roundTypeId IN ('c','f') AND pos = 1 AND best > 0 THEN 1 END) 'gold',
24 COUNT(CASE WHEN roundTypeId IN ('c','f') AND pos = 2 AND best > 0 THEN 1 END) 'silver',
25 COUNT(CASE WHEN roundTypeId IN ('c','f') AND pos = 3 AND best > 0 THEN 1 END) 'bronze',
26 COUNT(CASE WHEN roundTypeId IN ('c','f') AND pos <= 3 AND best > 0 AND competitionId IN (SELECT competition_id FROM wca_dev.championships WHERE championship_type = 'world') THEN 1 END) 'wcPodiums',
27 COUNT(CASE WHEN roundTypeId IN ('c','f') AND pos = 1 AND best > 0 AND competitionId IN (SELECT competition_id FROM wca_dev.championships WHERE championship_type = 'world') THEN 1 END) 'wcGold',
28 COUNT(CASE WHEN roundTypeId IN ('c','f') AND pos = 2 AND best > 0 AND competitionId IN (SELECT competition_id FROM wca_dev.championships WHERE championship_type = 'world') THEN 1 END) 'wcSilver',
29 COUNT(CASE WHEN roundTypeId IN ('c','f') AND pos = 3 AND best > 0 AND competitionId IN (SELECT competition_id FROM wca_dev.championships WHERE championship_type = 'world') THEN 1 END) 'wcBronze',
30 COUNT(CASE WHEN regionalSingleRecord != '' THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord != '' THEN 1 END) 'records',
31 COUNT(CASE WHEN regionalSingleRecord != '' THEN 1 END) 'singleRecords',
32 (CASE WHEN eventId NOT IN ('444bf','555bf','333mbf','333mbo') THEN COUNT(CASE WHEN regionalAverageRecord != '' THEN 1 END) ELSE NULL END) 'averageRecords',
33 COUNT(CASE WHEN regionalSingleRecord = 'WR' THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord = 'WR' THEN 1 END) 'WRs',
34 COUNT(CASE WHEN regionalSingleRecord = 'WR' THEN 1 END) 'singleWRs',
35 (CASE WHEN eventId NOT IN ('444bf','555bf','333mbf','333mbo') THEN COUNT(CASE WHEN regionalAverageRecord = 'WR' THEN 1 END) ELSE NULL END) 'averageWRs',
36 COUNT(CASE WHEN regionalSingleRecord NOT IN ('','NR','WR') THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord NOT IN ('','NR','WR') THEN 1 END) 'CRs',
37 COUNT(CASE WHEN regionalSingleRecord NOT IN ('','NR','WR') THEN 1 END) 'singleCRs',
38 (CASE WHEN eventId NOT IN ('444bf','555bf','333mbf','333mbo') THEN COUNT(CASE WHEN regionalSingleRecord NOT IN ('','NR','WR') THEN 1 END) ELSE NULL END) 'averageCRs',
39 COUNT(CASE WHEN regionalSingleRecord = 'NR' THEN 1 END)+COUNT(CASE WHEN regionalAverageRecord = 'NR' THEN 1 END) 'NRs',
40 COUNT(CASE WHEN regionalSingleRecord = 'NR' THEN 1 END) 'singleNRs',
41 (CASE WHEN eventId NOT IN ('444bf','555bf','333mbf','333mbo') THEN COUNT(CASE WHEN regionalAverageRecord = 'NR' THEN 1 END) ELSE NULL END) 'averageNRs'
42 FROM wca_stats.Results_Extra
43 GROUP BY personId, eventId) a
44JOIN
45 (SELECT personId, eventId,
46 COUNT(*) 'PBs',
47 COUNT(CASE WHEN format = 's' THEN 1 END) 'singlePBs',
48 (CASE WHEN eventId NOT IN ('444bf','555bf','333mbf','333mbo') THEN COUNT(CASE WHEN format = 'a' THEN 1 END) ELSE NULL END) 'averagePBs'
49 FROM wca_stats.pbs
50 GROUP BY personId, eventId) b
51ON a.personId = b.personId AND a.eventId = b.eventId
52ORDER BY a.personId, FIELD(a.eventId,'222','333','444','555','666','777','333bf','333fm','333oh','333ft','clock','minx','pyram','skewb','sq1','444bf','555bf','333mbf','magic','mmagic','333mbo');