[MediaWiki-commits] [Gerrit] Update the SQL queries for the new versions of the schemas - change (analytics/multimedia)
jenkins-bot has submitted this change and it was merged. Change subject: Update the SQL queries for the new versions of the schemas .. Update the SQL queries for the new versions of the schemas MediaViewer and MultimediaViewerNetworkPerformance have both been updated Bug: T78312 Change-Id: I20abf584c4862a98c30ae037fe11bc81391bb40d --- M actions/template.sql M geoperf/template.sql M perf/template.sql 3 files changed, 12 insertions(+), 1 deletion(-) Approvals: Gergő Tisza: Looks good to me, approved jenkins-bot: Verified diff --git a/actions/template.sql b/actions/template.sql index 1d80fc4..f936ea5 100644 --- a/actions/template.sql +++ b/actions/template.sql @@ -59,7 +59,9 @@ SUM(CASE WHEN event_action = 'options-close' THEN event_samplingFactor ELSE 0 END) AS 'options-close', SUM(CASE WHEN event_action = 'disable-about-link' THEN event_samplingFactor ELSE 0 END) AS 'disable-about-link', SUM(CASE WHEN event_action = 'enable-about-link' THEN event_samplingFactor ELSE 0 END) AS 'enable-about-link', - SUM(CASE WHEN event_action = 'image-unview' THEN event_samplingFactor ELSE 0 END) AS 'image-unview' + SUM(CASE WHEN event_action = 'image-unview' THEN event_samplingFactor ELSE 0 END) AS 'image-unview', + SUM(CASE WHEN event_action = 'metadata-scroll-open' THEN event_samplingFactor ELSE 0 END) AS 'metadata-scroll-open', + SUM(CASE WHEN event_action = 'metadata-scroll-close' THEN event_samplingFactor ELSE 0 END) AS 'metadata-scroll-close' FROM ( SELECT timestamp, event_action, 1 AS event_samplingFactor FROM MediaViewer_7670440 @@ -89,6 +91,9 @@ SELECT timestamp, event_action, event_samplingFactor FROM MediaViewer_10536413 WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) UNION ALL + SELECT timestamp, event_action, event_samplingFactor FROM MediaViewer_10606177 + WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) + UNION ALL SELECT timestamp, (CASE WHEN event_value = 0 THEN 'pref-optout-loggedin' ELSE 'pref-optin-loggedin' END) AS event_action, 1 AS event_samplingFactor FROM PrefUpdate_5563398 WHERE event_property = 'multimediaviewer-enable' AND timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) ) AS MediaViewerUnioned diff --git a/geoperf/template.sql b/geoperf/template.sql index 60309d2..0ec307c 100644 --- a/geoperf/template.sql +++ b/geoperf/template.sql @@ -21,4 +21,7 @@ FROM ( SELECT timestamp, event_total, event_country, event_type, wiki FROM MultimediaViewerNetworkPerformance_7917896 WHERE %wiki% %metricwhere% AND timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 DAY)) AND event_total > 20 AND event_country != '' +UNION ALL +SELECT timestamp, event_total, event_country, event_type, wiki FROM MultimediaViewerNetworkPerformance_10596581 +WHERE %wiki% %metricwhere% AND timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 DAY)) AND event_total > 20 AND event_country != '' ) MultimediaViewerNetworkPerformanceUnioned GROUP BY country HAVING sample_size > 10 ORDER BY mean ASC \ No newline at end of file diff --git a/perf/template.sql b/perf/template.sql index 54e2ad4..5401fbe 100644 --- a/perf/template.sql +++ b/perf/template.sql @@ -27,4 +27,7 @@ ',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS %metricname%_time_99th_percentile FROM ( SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_7917896 WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND event_total > 20 AND %metricwhere% +UNION ALL +SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_10596581 +WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND event_total > 20 AND %metricwhere% ) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC ) stats USING (datestring) \ No newline at end of file -- To view, visit https://gerrit.wikimedia.org/r/179159 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I20abf584c4862a98c30ae037fe11bc81391bb40d Gerrit-PatchSet: 1 Gerrit-Project: analytics/multimedia Gerrit-Branch: master Gerrit-Owner: Gilles Gerrit-Reviewer: Gergő Tisza Gerrit-Reviewer: Springle Gerrit-Reviewer: jenkins-bot <> ___ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
[MediaWiki-commits] [Gerrit] Update the SQL queries for the new versions of the schemas - change (analytics/multimedia)
Gilles has uploaded a new change for review. https://gerrit.wikimedia.org/r/179159 Change subject: Update the SQL queries for the new versions of the schemas .. Update the SQL queries for the new versions of the schemas MediaViewer and MultimediaViewerNetworkPerformance have both been updated Bug: T78312 Change-Id: I20abf584c4862a98c30ae037fe11bc81391bb40d --- M actions/template.sql M geoperf/template.sql M perf/template.sql 3 files changed, 12 insertions(+), 1 deletion(-) git pull ssh://gerrit.wikimedia.org:29418/analytics/multimedia refs/changes/59/179159/1 diff --git a/actions/template.sql b/actions/template.sql index 1d80fc4..f936ea5 100644 --- a/actions/template.sql +++ b/actions/template.sql @@ -59,7 +59,9 @@ SUM(CASE WHEN event_action = 'options-close' THEN event_samplingFactor ELSE 0 END) AS 'options-close', SUM(CASE WHEN event_action = 'disable-about-link' THEN event_samplingFactor ELSE 0 END) AS 'disable-about-link', SUM(CASE WHEN event_action = 'enable-about-link' THEN event_samplingFactor ELSE 0 END) AS 'enable-about-link', - SUM(CASE WHEN event_action = 'image-unview' THEN event_samplingFactor ELSE 0 END) AS 'image-unview' + SUM(CASE WHEN event_action = 'image-unview' THEN event_samplingFactor ELSE 0 END) AS 'image-unview', + SUM(CASE WHEN event_action = 'metadata-scroll-open' THEN event_samplingFactor ELSE 0 END) AS 'metadata-scroll-open', + SUM(CASE WHEN event_action = 'metadata-scroll-close' THEN event_samplingFactor ELSE 0 END) AS 'metadata-scroll-close' FROM ( SELECT timestamp, event_action, 1 AS event_samplingFactor FROM MediaViewer_7670440 @@ -89,6 +91,9 @@ SELECT timestamp, event_action, event_samplingFactor FROM MediaViewer_10536413 WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) UNION ALL + SELECT timestamp, event_action, event_samplingFactor FROM MediaViewer_10606177 + WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) + UNION ALL SELECT timestamp, (CASE WHEN event_value = 0 THEN 'pref-optout-loggedin' ELSE 'pref-optin-loggedin' END) AS event_action, 1 AS event_samplingFactor FROM PrefUpdate_5563398 WHERE event_property = 'multimediaviewer-enable' AND timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) ) AS MediaViewerUnioned diff --git a/geoperf/template.sql b/geoperf/template.sql index 60309d2..0ec307c 100644 --- a/geoperf/template.sql +++ b/geoperf/template.sql @@ -21,4 +21,7 @@ FROM ( SELECT timestamp, event_total, event_country, event_type, wiki FROM MultimediaViewerNetworkPerformance_7917896 WHERE %wiki% %metricwhere% AND timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 DAY)) AND event_total > 20 AND event_country != '' +UNION ALL +SELECT timestamp, event_total, event_country, event_type, wiki FROM MultimediaViewerNetworkPerformance_10596581 +WHERE %wiki% %metricwhere% AND timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 DAY)) AND event_total > 20 AND event_country != '' ) MultimediaViewerNetworkPerformanceUnioned GROUP BY country HAVING sample_size > 10 ORDER BY mean ASC \ No newline at end of file diff --git a/perf/template.sql b/perf/template.sql index 54e2ad4..5401fbe 100644 --- a/perf/template.sql +++ b/perf/template.sql @@ -27,4 +27,7 @@ ',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS %metricname%_time_99th_percentile FROM ( SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_7917896 WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND event_total > 20 AND %metricwhere% +UNION ALL +SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_10596581 +WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND event_total > 20 AND %metricwhere% ) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC ) stats USING (datestring) \ No newline at end of file -- To view, visit https://gerrit.wikimedia.org/r/179159 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I20abf584c4862a98c30ae037fe11bc81391bb40d Gerrit-PatchSet: 1 Gerrit-Project: analytics/multimedia Gerrit-Branch: master Gerrit-Owner: Gilles ___ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
