Change Details

SELECT * FROM (SELECT concat(substring(event_uploadTimestamp, 1, 4), '-', substring(event_uploadTimestamp, 5, 2)) AS datestring FROM ( SELECT event_uploadTimestamp FROM MultimediaViewerNetworkPerformance_10596581 WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) UNION ALL SELECT event_uploadTimestamp FROM MultimediaViewerNetworkPerformance_10774577 WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) ) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC) dates LEFT OUTER JOIN ( SELECT concat(substring(event_uploadTimestamp, 1, 4), '-', substring(event_uploadTimestamp, 5, 2)) AS datestring, EXP(AVG(LOG(event_total))) AS image_time_mean, EXP(STD(LOG(event_total))) AS image_time_std, COUNT(*) AS image_sample_size, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 1/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_1st_percentile, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_50th_percentile, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 90/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_90th_percentile, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 95/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_95th_percentile, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_99th_percentile FROM ( SELECT event_type, event_total, event_uploadTimestamp, wiki FROM MultimediaViewerNetworkPerformance_10596581 WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) AND event_total > 20 AND event_type = 'image' AND ( event_varnish1hits > 0 OR event_varnish2hits > 0 OR event_varnish3hits > 0) UNION ALL SELECT event_type, event_total, event_uploadTimestamp, wiki FROM MultimediaViewerNetworkPerformance_10774577 WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) AND event_total > 20 AND event_type = 'image' AND ( event_varnish1hits > 0 OR event_varnish2hits > 0 OR event_varnish3hits > 0) ) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC ) stats USING (datestring)

SET group_concat_max_len = 10485760; SELECT * FROM (SELECT concat(substring(event_uploadTimestamp, 1, 4), '-', substring(event_uploadTimestamp, 5, 2)) AS datestring FROM ( SELECT event_uploadTimestamp FROM MultimediaViewerNetworkPerformance_10596581 WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) UNION ALL SELECT event_uploadTimestamp FROM MultimediaViewerNetworkPerformance_10774577 WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) ) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC) dates LEFT OUTER JOIN ( SELECT concat(substring(event_uploadTimestamp, 1, 4), '-', substring(event_uploadTimestamp, 5, 2)) AS datestring, EXP(AVG(LOG(event_total))) AS image_time_mean, EXP(STD(LOG(event_total))) AS image_time_std, COUNT(*) AS image_sample_size, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 1/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_1st_percentile, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_50th_percentile, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 90/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_90th_percentile, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 95/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_95th_percentile, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), ',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_99th_percentile FROM ( SELECT event_type, event_total, event_uploadTimestamp, wiki FROM MultimediaViewerNetworkPerformance_10596581 WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) AND event_total > 20 AND event_type = 'image' AND ( event_varnish1hits > 0 OR event_varnish2hits > 0 OR event_varnish3hits > 0) UNION ALL SELECT event_type, event_total, event_uploadTimestamp, wiki FROM MultimediaViewerNetworkPerformance_10774577 WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) AND event_total > 20 AND event_type = 'image' AND ( event_varnish1hits > 0 OR event_varnish2hits > 0 OR event_varnish3hits > 0) ) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC ) stats USING (datestring)