Jforrester has uploaded a new change for review. (
https://gerrit.wikimedia.org/r/397626 )
Change subject: Update for new Edit schema version 17520312
......................................................................
Update for new Edit schema version 17520312
New action state (loaded, after ready and before saveIntent), which
means some changes to the queries, and editor type (wikitext), which
doesn't..
Change-Id: I9bf52dcbaa175ef52dcc9a4b615e14785be753ee
---
M edit/failure_rates_by_type.sql
M edit/failure_types_by_user_type.sql
M edit/rates.sql
M edit/sessions.sql
M edit/success_by_user_type.sql
5 files changed, 133 insertions(+), 32 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/limn-edit-data
refs/changes/26/397626/1
diff --git a/edit/failure_rates_by_type.sql b/edit/failure_rates_by_type.sql
index 05e2500..a540e82 100644
--- a/edit/failure_rates_by_type.sql
+++ b/edit/failure_rates_by_type.sql
@@ -63,20 +63,38 @@
timestamp < '{to_timestamp}'
) raw_events
+ union all
+
+ select
+ timestamp,
+ event_action,
+ `event_action.saveFailure.type`,
+ clientIp,
+ event_editingSessionId
+ from Edit_17520312
+ where
+ event_editor = '{editor}' and
+ ('{wiki}' = 'all' or wiki = '{wiki}') and
+ timestamp >= '{from_timestamp}' and
+ timestamp < '{to_timestamp}'
+ ) raw_events
+
inner join
(select 'init' as action, 0 as ord
union all
select 'ready' as action, 1 as ord
union all
- select 'saveIntent' as action, 2 as ord
+ select 'loaded' as action, 2 as ord
union all
- select 'saveAttempt' as action, 3 as ord
+ select 'saveIntent' as action, 3 as ord
union all
- select 'saveFailure' as action, 4 as ord
+ select 'saveAttempt' as action, 4 as ord
union all
- select 'saveSuccess' as action, 5 as ord
+ select 'saveFailure' as action, 5 as ord
union all
- select 'abort' as action, 6 as ord
+ select 'saveSuccess' as action, 6 as ord
+ union all
+ select 'abort' as action, 7 as ord
) action_order
on action = event_action
diff --git a/edit/failure_types_by_user_type.sql
b/edit/failure_types_by_user_type.sql
index 6fbfcfc..22c294f 100644
--- a/edit/failure_types_by_user_type.sql
+++ b/edit/failure_types_by_user_type.sql
@@ -42,6 +42,19 @@
timestamp < '{to_timestamp}' and
`event_action` = 'saveFailure' and
`event_action.saveFailure.type` is not null )
+
+ union all
+
+ ( select timestamp, `event_user.class`,
+ `event_user.editCount`, `event_action.saveFailure.type`
+ from Edit_17520312
+ where
+ event_editor = '{editor}' and
+ ('{wiki}' = 'all' or wiki = '{wiki}') and
+ timestamp >= '{from_timestamp}' and
+ timestamp < '{to_timestamp}' and
+ `event_action` = 'saveFailure' and
+ `event_action.saveFailure.type` is not null )
) saveFailures
group by 1, 2
diff --git a/edit/rates.sql b/edit/rates.sql
index eef80db..f41906a 100644
--- a/edit/rates.sql
+++ b/edit/rates.sql
@@ -1,4 +1,5 @@
select day,
+ sum(ready and not loaded) / sum(ready) as `load-failure`,
sum(ready and not saving) / sum(ready) as `bounce-rate`,
sum(ready and not attempt) / sum(ready) as `not-attempted-rate`,
sum(attempt and success) / sum(attempt) as `success-rate`,
@@ -6,6 +7,7 @@
from (select day,
actions like '%ready%' as ready,
+ actions like '%loaded%' as loaded,
actions like '%save%' as saving,
actions like '%Failure%' as failure,
actions like '%Attempt%' as attempt,
@@ -44,20 +46,37 @@
timestamp < '{to_timestamp}'
) raw_events
+ union all
+
+ select
+ timestamp,
+ event_action,
+ clientIp,
+ event_editingSessionId
+ from Edit_17520312
+ where
+ event_editor = '{editor}' and
+ ('{wiki}' = 'all' or wiki = '{wiki}') and
+ timestamp >= '{from_timestamp}' and
+ timestamp < '{to_timestamp}'
+ ) raw_events
+
inner join
(select 'init' as action, 0 as ord
union all
select 'ready' as action, 1 as ord
union all
- select 'saveIntent' as action, 2 as ord
+ select 'loaded' as action, 2 as ord
union all
- select 'saveAttempt' as action, 3 as ord
+ select 'saveIntent' as action, 3 as ord
union all
- select 'saveFailure' as action, 4 as ord
+ select 'saveAttempt' as action, 4 as ord
union all
- select 'saveSuccess' as action, 5 as ord
+ select 'saveFailure' as action, 5 as ord
union all
- select 'abort' as action, 6 as ord
+ select 'saveSuccess' as action, 6 as ord
+ union all
+ select 'abort' as action, 7 as ord
) action_order
on action = event_action
diff --git a/edit/sessions.sql b/edit/sessions.sql
index 130f090..e84f56e 100644
--- a/edit/sessions.sql
+++ b/edit/sessions.sql
@@ -2,36 +2,68 @@
actions,
count(*) as repeated
from (select group_concat(event_action order by timestamp, action_order.ord
separator '-') as actions
- from (select timestamp,
- event_action,
- clientIp,
- event_editingSessionId
+ from (
+ select
+ timestamp,
+ event_action,
+ clientIp,
+ event_editingSessionId
+ from Edit_11448630
+ where
+ event_editor = '{editor}' and
+ ('{wiki}' = 'all' or wiki = '{wiki}') and
+ timestamp >= '{from_timestamp}' and
+ timestamp < '{to_timestamp}'
- from Edit_13457736
- # NOTE: used to union all with Edit_11448630
- # That schema is still used apparently, but very little
+ # Add in events using the new schema. To be removed once
everything
+ # is switched over.
+ union all
- where event_editor = '{editor}'
- and ('{wiki}' = 'all' or wiki = '{wiki}')
- and timestamp >= '{from_timestamp}'
- and timestamp < '{to_timestamp}'
+ select
+ timestamp,
+ event_action,
+ clientIp,
+ event_editingSessionId
+ from Edit_13457736
+ where
+ event_editor = '{editor}' and
+ ('{wiki}' = 'all' or wiki = '{wiki}') and
+ timestamp >= '{from_timestamp}' and
+ timestamp < '{to_timestamp}'
) raw_events
- inner join
+ union all
+
+ select
+ timestamp,
+ event_action,
+ clientIp,
+ event_editingSessionId
+ from Edit_17520312
+ where
+ event_editor = '{editor}' and
+ ('{wiki}' = 'all' or wiki = '{wiki}') and
+ timestamp >= '{from_timestamp}' and
+ timestamp < '{to_timestamp}'
+ ) raw_events
+
+ inner join
(select 'init' as action, 0 as ord
union all
select 'ready' as action, 1 as ord
union all
- select 'saveIntent' as action, 2 as ord
+ select 'loaded' as action, 2 as ord
union all
- select 'saveAttempt' as action, 3 as ord
+ select 'saveIntent' as action, 3 as ord
union all
- select 'saveFailure' as action, 4 as ord
+ select 'saveAttempt' as action, 4 as ord
union all
- select 'saveSuccess' as action, 5 as ord
+ select 'saveFailure' as action, 5 as ord
union all
- select 'abort' as action, 6 as ord
+ select 'saveSuccess' as action, 6 as ord
+ union all
+ select 'abort' as action, 7 as ord
) action_order on action = event_action
diff --git a/edit/success_by_user_type.sql b/edit/success_by_user_type.sql
index 587944c..6a09f0a 100644
--- a/edit/success_by_user_type.sql
+++ b/edit/success_by_user_type.sql
@@ -60,20 +60,39 @@
timestamp < '{to_timestamp}'
) raw_events
+ union all
+
+ select
+ timestamp,
+ event_action,
+ clientIp,
+ event_editingSessionId,
+ `event_user.class`,
+ `event_user.editCount` as edit_count
+ from Edit_17520312
+ where
+ event_editor = '{editor}' and
+ ('{wiki}' = 'all' or wiki = '{wiki}') and
+ timestamp >= '{from_timestamp}' and
+ timestamp < '{to_timestamp}'
+ ) raw_events
+
inner join
(select 'init' as action, 0 as ord
union all
select 'ready' as action, 1 as ord
union all
- select 'saveIntent' as action, 2 as ord
+ select 'loaded' as action, 2 as ord
union all
- select 'saveAttempt' as action, 3 as ord
+ select 'saveIntent' as action, 3 as ord
union all
- select 'saveFailure' as action, 4 as ord
+ select 'saveAttempt' as action, 4 as ord
union all
- select 'saveSuccess' as action, 5 as ord
+ select 'saveFailure' as action, 5 as ord
union all
- select 'abort' as action, 6 as ord
+ select 'saveSuccess' as action, 6 as ord
+ union all
+ select 'abort' as action, 7 as ord
) action_order
on action = event_action
--
To view, visit https://gerrit.wikimedia.org/r/397626
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I9bf52dcbaa175ef52dcc9a4b615e14785be753ee
Gerrit-PatchSet: 1
Gerrit-Project: analytics/limn-edit-data
Gerrit-Branch: master
Gerrit-Owner: Jforrester <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
