[MediaWiki-commits] [Gerrit] Fix unique-users query - change (analytics/limn-flow-data)

2015-07-28 Thread Matthias Mullie (Code Review) 
Matthias Mullie has submitted this change and it was merged.

Change subject: Fix unique-users query
..


Fix unique-users query

The way it was using GROUP BY was breaking things somehow.
This may have been related to rev_user_ip being NULL for
logged-in users, or maybe GROUP BY is just weird.

COUNT(DISTINCT rev_user_id, rev_user_ip, rev_user_wiki) also doesn't
work because that drops any row where any of those fields is NULL;
but COALESCE()ing the nullable field fixes that.

Bonus: Renamed fields to readable names, and fixed
weekstart value to be the actual day the week starts (Sunday),
rather than the day before (Saturday) which isn't even part of
the week in question!

Bug: T106564
Change-Id: I3d08c7d6b7367103aacf1c906ebe62da9dc99759
---
M flow/unique-users.sql
1 file changed, 11 insertions(+), 12 deletions(-)

Approvals:
  Matthias Mullie: Verified; Looks good to me, approved



diff --git a/flow/unique-users.sql b/flow/unique-users.sql
index 4b933fa..e5ebef7 100644
--- a/flow/unique-users.sql
+++ b/flow/unique-users.sql
@@ -1,12 +1,11 @@
-SELECT weekstart,
-   SUM(user) as unique_users
-  FROM (
-SELECT 1 as user,
-   
DATE(DATE_SUB(FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)2)/1000), 
interval DAYOFWEEK( 
FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)2)/1000)) day)) as 
weekstart
-  FROM flow_revision
- WHERE rev_user_wiki NOT IN ( 'testwiki', 'test2wiki' )
- GROUP BY rev_user_wiki, rev_user_id, rev_user_ip
-   ) x
- WHERE weekstart  DATE_SUB(CURDATE(), interval DAYOFWEEK(NOW()) day)
- GROUP BY weekstart;
-
+SELECT
+   DATE_SUB(d, interval DAYOFWEEK(d)-1 day) AS Week,
+   COUNT(DISTINCT rev_user_id, COALESCE(rev_user_ip, ''), rev_user_wiki) 
AS Unique users
+FROM (
+   SELECT 
DATE(FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)2)/1000)) AS d, 
rev_user_id, rev_user_ip, rev_user_wiki
+   FROM flow_revision
+   WHERE rev_user_wiki NOT IN ('testwiki', 'test2wiki')
+) AS x
+GROUP BY Week
+HAVING Week  DATE_SUB(CURDATE(), interval DAYOFWEEK(NOW())-1 day)
+ORDER BY Week;

-- 
To view, visit https://gerrit.wikimedia.org/r/227398
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings

Gerrit-MessageType: merged
Gerrit-Change-Id: I3d08c7d6b7367103aacf1c906ebe62da9dc99759
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-flow-data
Gerrit-Branch: master
Gerrit-Owner: Catrope roan.katt...@gmail.com
Gerrit-Reviewer: Matthias Mullie mmul...@wikimedia.org

___
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits

[MediaWiki-commits] [Gerrit] Fix unique-users query - change (analytics/limn-flow-data)

2015-07-27 Thread Catrope (Code Review) 
Catrope has uploaded a new change for review.

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

Change subject: Fix unique-users query
..

Fix unique-users query

The way it was using GROUP BY was breaking things somehow.
This may have been related to rev_user_ip being NULL for
logged-in users, or maybe GROUP BY is just weird.

COUNT(DISTINCT rev_user_id, rev_user_ip, rev_user_wiki) also doesn't
work because that drops any row where any of those fields is NULL;
but COALESCE()ing the nullable field fixes that.

Bonus: Renamed fields to readable names, and fixed
weekstart value to be the actual day the week starts (Sunday),
rather than the day before (Saturday) which isn't even part of
the week in question!

Bug: T106564
Change-Id: I3d08c7d6b7367103aacf1c906ebe62da9dc99759
---
M flow/unique-users.sql
1 file changed, 11 insertions(+), 12 deletions(-)


  git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-flow-data 
refs/changes/98/227398/1

diff --git a/flow/unique-users.sql b/flow/unique-users.sql
index 4b933fa..e5ebef7 100644
--- a/flow/unique-users.sql
+++ b/flow/unique-users.sql
@@ -1,12 +1,11 @@
-SELECT weekstart,
-   SUM(user) as unique_users
-  FROM (
-SELECT 1 as user,
-   
DATE(DATE_SUB(FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)2)/1000), 
interval DAYOFWEEK( 
FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)2)/1000)) day)) as 
weekstart
-  FROM flow_revision
- WHERE rev_user_wiki NOT IN ( 'testwiki', 'test2wiki' )
- GROUP BY rev_user_wiki, rev_user_id, rev_user_ip
-   ) x
- WHERE weekstart  DATE_SUB(CURDATE(), interval DAYOFWEEK(NOW()) day)
- GROUP BY weekstart;
-
+SELECT
+   DATE_SUB(d, interval DAYOFWEEK(d)-1 day) AS Week,
+   COUNT(DISTINCT rev_user_id, COALESCE(rev_user_ip, ''), rev_user_wiki) 
AS Unique users
+FROM (
+   SELECT 
DATE(FROM_UNIXTIME((conv(substring(hex(rev_id),1,12),16,10)2)/1000)) AS d, 
rev_user_id, rev_user_ip, rev_user_wiki
+   FROM flow_revision
+   WHERE rev_user_wiki NOT IN ('testwiki', 'test2wiki')
+) AS x
+GROUP BY Week
+HAVING Week  DATE_SUB(CURDATE(), interval DAYOFWEEK(NOW())-1 day)
+ORDER BY Week;

-- 
To view, visit https://gerrit.wikimedia.org/r/227398
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings

Gerrit-MessageType: newchange
Gerrit-Change-Id: I3d08c7d6b7367103aacf1c906ebe62da9dc99759
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-flow-data
Gerrit-Branch: master
Gerrit-Owner: Catrope roan.katt...@gmail.com

___
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits