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 <gdu...@wikimedia.org>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
