[MediaWiki-commits] [Gerrit] Take sampling factor into account - change (analytics/multimedia)

2014-05-22 Thread MarkTraceur (Code Review) 
MarkTraceur has submitted this change and it was merged.

Change subject: Take sampling factor into account
..


Take sampling factor into account

Change-Id: I3808cc07c44f1f0d4f886bebbc65e213bd0d1315
Mingle: https://wikimedia.mingle.thoughtworks.com/projects/multimedia/cards/619
---
M actions/template.sql
1 file changed, 23 insertions(+), 20 deletions(-)

Approvals:
  MarkTraceur: Looks good to me, approved
  Gergő Tisza: Looks good to me, but someone else must approve
  jenkins-bot: Verified



diff --git a/actions/template.sql b/actions/template.sql
index 49eb727..5e0d918 100644
--- a/actions/template.sql
+++ b/actions/template.sql
@@ -1,30 +1,33 @@
 -- Get statistics, per day, for various actions in Media Viewer.
 
 SELECT CONCAT(SUBSTRING(timestamp, 1, 4), '-', SUBSTRING(timestamp, 5, 2), 
'-', SUBSTRING(timestamp, 7, 2)) AS datestring,
-   SUM(CASE WHEN event_action IN ('thumbnail-link-click', 'thumbnail') 
THEN 1 ELSE 0 END) AS 'thumbnail',
-   SUM(CASE WHEN event_action IN ('enlarge-link-click', 'enlarge') THEN 1 
ELSE 0 END) AS 'enlarge',
-   SUM(CASE WHEN event_action IN ('fullscreen-link-click', 'fullscreen') 
THEN 1 ELSE 0 END) AS 'fullscreen',
-   SUM(CASE WHEN event_action IN ('defullscreen-link-click', 
'defullscreen') THEN 1 ELSE 0 END) AS 'defullscreen',
-   SUM(CASE WHEN event_action IN ('close-link-click', 'close') THEN 1 ELSE 
0 END) AS 'close',
-   SUM(CASE WHEN event_action IN ('site-link-click', 
'file-description-page') THEN 1 ELSE 0 END) AS 'file-description-page',
-   SUM(CASE WHEN event_action IN ('use-this-file-link-click', 
'use-this-file-open') THEN 1 ELSE 0 END) AS 'use-this-file-open',
-   SUM(CASE WHEN event_action = 'image-view' THEN 1 ELSE 0 END) AS 
'image-view',
-   SUM(CASE WHEN event_action = 'metadata-open' THEN 1 ELSE 0 END) AS 
'metadata-open',
-   SUM(CASE WHEN event_action = 'metadata-close' THEN 1 ELSE 0 END) AS 
'metadata-close',
-   SUM(CASE WHEN event_action = 'next-image' THEN 1 ELSE 0 END) AS 
'next-image',
-   SUM(CASE WHEN event_action = 'prev-image' THEN 1 ELSE 0 END) AS 
'prev-image',
-   SUM(CASE WHEN event_action = 'terms-open' THEN 1 ELSE 0 END) AS 
'terms-open',
-   SUM(CASE WHEN event_action = 'license-page' THEN 1 ELSE 0 END) AS 
'license-page',
-   SUM(CASE WHEN event_action = 'author-page' THEN 1 ELSE 0 END) AS 
'author-page',
-   SUM(CASE WHEN event_action = 'source-page' THEN 1 ELSE 0 END) AS 
'source-page',
-   SUM(CASE WHEN event_action = 'hash-load' THEN 1 ELSE 0 END) AS 
'hash-load',
-   SUM(CASE WHEN event_action = 'history-navigation' THEN 1 ELSE 0 END) AS 
'history-navigation'
+   SUM(CASE WHEN event_action IN ('thumbnail-link-click', 'thumbnail') 
THEN event_samplingFactor ELSE 0 END) AS 'thumbnail',
+   SUM(CASE WHEN event_action IN ('enlarge-link-click', 'enlarge') THEN 
event_samplingFactor ELSE 0 END) AS 'enlarge',
+   SUM(CASE WHEN event_action IN ('fullscreen-link-click', 'fullscreen') 
THEN event_samplingFactor ELSE 0 END) AS 'fullscreen',
+   SUM(CASE WHEN event_action IN ('defullscreen-link-click', 
'defullscreen') THEN event_samplingFactor ELSE 0 END) AS 'defullscreen',
+   SUM(CASE WHEN event_action IN ('close-link-click', 'close') THEN 
event_samplingFactor ELSE 0 END) AS 'close',
+   SUM(CASE WHEN event_action IN ('site-link-click', 
'file-description-page') THEN event_samplingFactor ELSE 0 END) AS 
'file-description-page',
+   SUM(CASE WHEN event_action IN ('use-this-file-link-click', 
'use-this-file-open') THEN event_samplingFactor ELSE 0 END) AS 
'use-this-file-open',
+   SUM(CASE WHEN event_action = 'image-view' THEN event_samplingFactor 
ELSE 0 END) AS 'image-view',
+   SUM(CASE WHEN event_action = 'metadata-open' THEN event_samplingFactor 
ELSE 0 END) AS 'metadata-open',
+   SUM(CASE WHEN event_action = 'metadata-close' THEN event_samplingFactor 
ELSE 0 END) AS 'metadata-close',
+   SUM(CASE WHEN event_action = 'next-image' THEN event_samplingFactor 
ELSE 0 END) AS 'next-image',
+   SUM(CASE WHEN event_action = 'prev-image' THEN event_samplingFactor 
ELSE 0 END) AS 'prev-image',
+   SUM(CASE WHEN event_action = 'terms-open' THEN event_samplingFactor 
ELSE 0 END) AS 'terms-open',
+   SUM(CASE WHEN event_action = 'license-page' THEN event_samplingFactor 
ELSE 0 END) AS 'license-page',
+   SUM(CASE WHEN event_action = 'author-page' THEN event_samplingFactor 
ELSE 0 END) AS 'author-page',
+   SUM(CASE WHEN event_action = 'source-page' THEN event_samplingFactor 
ELSE 0 END) AS 'source-page',
+   SUM(CASE WHEN event_action = 'hash-load' THEN event_samplingFactor ELSE 
0 END) AS 'hash-load',
+   SUM(CASE WHEN event_action = 'history-navigation' THEN 
event_samplingFactor ELSE 0 END) AS 'history-navigation'
 
FROM (
-   SELECT timestamp, event_action FROM MediaViewer_7670440
+   SELECT

[MediaWiki-commits] [Gerrit] Take sampling factor into account - change (analytics/multimedia)

2014-05-19 Thread Gilles (Code Review) 
Gilles has uploaded a new change for review.

  https://gerrit.wikimedia.org/r/134065

Change subject: Take sampling factor into account
..

Take sampling factor into account

Change-Id: I3808cc07c44f1f0d4f886bebbc65e213bd0d1315
Mingle: https://wikimedia.mingle.thoughtworks.com/projects/multimedia/cards/619
---
M actions/template.sql
1 file changed, 23 insertions(+), 20 deletions(-)


  git pull ssh://gerrit.wikimedia.org:29418/analytics/multimedia 
refs/changes/65/134065/1

diff --git a/actions/template.sql b/actions/template.sql
index 49eb727..5e0d918 100644
--- a/actions/template.sql
+++ b/actions/template.sql
@@ -1,30 +1,33 @@
 -- Get statistics, per day, for various actions in Media Viewer.
 
 SELECT CONCAT(SUBSTRING(timestamp, 1, 4), '-', SUBSTRING(timestamp, 5, 2), 
'-', SUBSTRING(timestamp, 7, 2)) AS datestring,
-   SUM(CASE WHEN event_action IN ('thumbnail-link-click', 'thumbnail') 
THEN 1 ELSE 0 END) AS 'thumbnail',
-   SUM(CASE WHEN event_action IN ('enlarge-link-click', 'enlarge') THEN 1 
ELSE 0 END) AS 'enlarge',
-   SUM(CASE WHEN event_action IN ('fullscreen-link-click', 'fullscreen') 
THEN 1 ELSE 0 END) AS 'fullscreen',
-   SUM(CASE WHEN event_action IN ('defullscreen-link-click', 
'defullscreen') THEN 1 ELSE 0 END) AS 'defullscreen',
-   SUM(CASE WHEN event_action IN ('close-link-click', 'close') THEN 1 ELSE 
0 END) AS 'close',
-   SUM(CASE WHEN event_action IN ('site-link-click', 
'file-description-page') THEN 1 ELSE 0 END) AS 'file-description-page',
-   SUM(CASE WHEN event_action IN ('use-this-file-link-click', 
'use-this-file-open') THEN 1 ELSE 0 END) AS 'use-this-file-open',
-   SUM(CASE WHEN event_action = 'image-view' THEN 1 ELSE 0 END) AS 
'image-view',
-   SUM(CASE WHEN event_action = 'metadata-open' THEN 1 ELSE 0 END) AS 
'metadata-open',
-   SUM(CASE WHEN event_action = 'metadata-close' THEN 1 ELSE 0 END) AS 
'metadata-close',
-   SUM(CASE WHEN event_action = 'next-image' THEN 1 ELSE 0 END) AS 
'next-image',
-   SUM(CASE WHEN event_action = 'prev-image' THEN 1 ELSE 0 END) AS 
'prev-image',
-   SUM(CASE WHEN event_action = 'terms-open' THEN 1 ELSE 0 END) AS 
'terms-open',
-   SUM(CASE WHEN event_action = 'license-page' THEN 1 ELSE 0 END) AS 
'license-page',
-   SUM(CASE WHEN event_action = 'author-page' THEN 1 ELSE 0 END) AS 
'author-page',
-   SUM(CASE WHEN event_action = 'source-page' THEN 1 ELSE 0 END) AS 
'source-page',
-   SUM(CASE WHEN event_action = 'hash-load' THEN 1 ELSE 0 END) AS 
'hash-load',
-   SUM(CASE WHEN event_action = 'history-navigation' THEN 1 ELSE 0 END) AS 
'history-navigation'
+   SUM(CASE WHEN event_action IN ('thumbnail-link-click', 'thumbnail') 
THEN event_samplingFactor ELSE 0 END) AS 'thumbnail',
+   SUM(CASE WHEN event_action IN ('enlarge-link-click', 'enlarge') THEN 
event_samplingFactor ELSE 0 END) AS 'enlarge',
+   SUM(CASE WHEN event_action IN ('fullscreen-link-click', 'fullscreen') 
THEN event_samplingFactor ELSE 0 END) AS 'fullscreen',
+   SUM(CASE WHEN event_action IN ('defullscreen-link-click', 
'defullscreen') THEN event_samplingFactor ELSE 0 END) AS 'defullscreen',
+   SUM(CASE WHEN event_action IN ('close-link-click', 'close') THEN 
event_samplingFactor ELSE 0 END) AS 'close',
+   SUM(CASE WHEN event_action IN ('site-link-click', 
'file-description-page') THEN event_samplingFactor ELSE 0 END) AS 
'file-description-page',
+   SUM(CASE WHEN event_action IN ('use-this-file-link-click', 
'use-this-file-open') THEN event_samplingFactor ELSE 0 END) AS 
'use-this-file-open',
+   SUM(CASE WHEN event_action = 'image-view' THEN event_samplingFactor 
ELSE 0 END) AS 'image-view',
+   SUM(CASE WHEN event_action = 'metadata-open' THEN event_samplingFactor 
ELSE 0 END) AS 'metadata-open',
+   SUM(CASE WHEN event_action = 'metadata-close' THEN event_samplingFactor 
ELSE 0 END) AS 'metadata-close',
+   SUM(CASE WHEN event_action = 'next-image' THEN event_samplingFactor 
ELSE 0 END) AS 'next-image',
+   SUM(CASE WHEN event_action = 'prev-image' THEN event_samplingFactor 
ELSE 0 END) AS 'prev-image',
+   SUM(CASE WHEN event_action = 'terms-open' THEN event_samplingFactor 
ELSE 0 END) AS 'terms-open',
+   SUM(CASE WHEN event_action = 'license-page' THEN event_samplingFactor 
ELSE 0 END) AS 'license-page',
+   SUM(CASE WHEN event_action = 'author-page' THEN event_samplingFactor 
ELSE 0 END) AS 'author-page',
+   SUM(CASE WHEN event_action = 'source-page' THEN event_samplingFactor 
ELSE 0 END) AS 'source-page',
+   SUM(CASE WHEN event_action = 'hash-load' THEN event_samplingFactor ELSE 
0 END) AS 'hash-load',
+   SUM(CASE WHEN event_action = 'history-navigation' THEN 
event_samplingFactor ELSE 0 END) AS 'history-navigation'
 
FROM (
-   SELECT timestamp, event_action FROM MediaViewer_7670440
+   SELECT timestamp, event_acti