Gilles has uploaded a new change for review. https://gerrit.wikimedia.org/r/130308
Change subject: Add percentiles to the Geo data ...................................................................... Add percentiles to the Geo data Change-Id: I9c4ac62fd6c2efdfbcaa31d8931988026051ec0c Mingle: https://wikimedia.mingle.thoughtworks.com/projects/multimedia/cards/506 --- M perf/country-api.sql M perf/country-image.sql 2 files changed, 36 insertions(+), 2 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/analytics/multimedia refs/changes/08/130308/1 diff --git a/perf/country-api.sql b/perf/country-api.sql index 81a9025..9e75f2e 100644 --- a/perf/country-api.sql +++ b/perf/country-api.sql @@ -1,4 +1,21 @@ -SELECT event_country as country, EXP(AVG(LOG(event_total))) as mean, EXP(STD(LOG(event_total))) as std, COUNT(event_total) as sample_size FROM ( +SET group_concat_max_len = 10485760; +SELECT event_country as country, +EXP(AVG(LOG(event_total))) as mean, +EXP(STD(LOG(event_total))) as std, +COUNT(event_total) as sample_size, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 1/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 1st_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 50th_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 90/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 90th_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 99th_percentile +FROM ( SELECT timestamp, event_total, event_country, event_type FROM MultimediaViewerNetworkPerformance_7393226 UNION ALL SELECT timestamp, event_total, event_country, event_type FROM MultimediaViewerNetworkPerformance_7488625 diff --git a/perf/country-image.sql b/perf/country-image.sql index ee1a2d9..c3e4364 100644 --- a/perf/country-image.sql +++ b/perf/country-image.sql @@ -1,4 +1,21 @@ -SELECT event_country as country, EXP(AVG(LOG(event_total))) as mean, EXP(STD(LOG(event_total))) as std, COUNT(event_total) as sample_size FROM ( +SET group_concat_max_len = 10485760; +SELECT event_country as country, +EXP(AVG(LOG(event_total))) as mean, +EXP(STD(LOG(event_total))) as std, +COUNT(event_total) as sample_size, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 1/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 1st_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 50th_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 90/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 90th_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 99th_percentile +FROM ( SELECT timestamp, event_total, event_country, event_type FROM MultimediaViewerNetworkPerformance_7393226 UNION ALL SELECT timestamp, event_total, event_country, event_type FROM MultimediaViewerNetworkPerformance_7488625 -- To view, visit https://gerrit.wikimedia.org/r/130308 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I9c4ac62fd6c2efdfbcaa31d8931988026051ec0c Gerrit-PatchSet: 1 Gerrit-Project: analytics/multimedia Gerrit-Branch: master Gerrit-Owner: Gilles <gdu...@wikimedia.org> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
