dcausse added a comment.
Yes it definitely can support such queries e.g (extract all api requests from
mediawiki.apiaction grouped by their action param and database where the avg
backend time is > 100ms over a 1 minute window).
SELECT
TUMBLE_START(dt, INTERVAL '1' MINUTE) as ts_win,
database,
params['action'],
count(1) as cnt,
avg(backend_time_ms) as avg_backend_time
FROM api_action
GROUP BY
TUMBLE(dt, INTERVAL '1' MINUTE),
database, params['action']
HAVING avg(backend_time_ms) > 100
Will output:
ts_win database EXPR$2
cnt avg_backend_time
2020-10-28T14:35 srwiki feedrecentchanges
4 228
2020-10-28T14:35 svwiki feedrecentchanges
2 467
2020-10-28T14:35 bgwiki stashedit
6 245
2020-10-28T14:35 igwiki languagesearch
2 146
2020-10-28T14:35 ruwiki cxsave
6 108
2020-10-28T14:35 fiwiki feedrecentchanges
4 702
2020-10-28T14:35 plwiktionary options
2 696
2020-10-28T14:35 astwiki parse
10 349
2020-10-28T14:35 bswiki parse
5 110
2020-10-28T14:35 jawikibooks query
8 115
2020-10-28T14:35 viwiki stashedit
8 479
2020-10-28T14:35 enwikisource feedrecentchanges
2 626
2020-10-28T14:35 bnwikisource purge
6 346
2020-10-28T14:35 zhwikisource query
11 260
2020-10-28T14:35 ukwiki feedrecentchanges
3 672
2020-10-28T14:35 elwiki feedrecentchanges
4 401
I'll work out some other examples using a python + the table API (possibly
doing joins), the SQL CLI is nice for quickly looking at results but rather
limited.
TASK DETAIL
https://phabricator.wikimedia.org/T262942
EMAIL PREFERENCES
https://phabricator.wikimedia.org/settings/panel/emailpreferences/
To: dcausse
Cc: Aklapper, Ottomata, Gehel, dcausse, CDanis, Zbyszko, CBogen, Akuckartz,
4748kitoko, Nandana, Namenlos314, Akovalyov, Lahi, Gq86,
Lucas_Werkmeister_WMDE, GoranSMilovanovic, QZanden, EBjune, merbst,
LawExplorer, _jensen, rosalieper, Scott_WUaS, Jonas, Xmlizer, JAllemandou,
terrrydactyl, jkroll, Wikidata-bugs, Jdouglas, aude, Tobias1984, Manybubbles,
Mbch331, jeremyb
_______________________________________________
Wikidata-bugs mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
