Hi Everyone,
I’m in need of some guidance on how to properly write the correct query from the metrics table for download stats for articles site-wide, during 2017 - 2018, split by top ten countries.
So far I’ve gotten as far as getting a sum of metric for a certain time period for assoc_type=259 which I’m not sure is correct for article/file downloads. I have checked the old archived wiki for stats framework and have confused myself. Could someone please explain the components of a query such as this example from this thread for user view count. How do the columns assoc_id, assoc_type, context_id and metric_type affect the query? And why are we using SUM() over COUNT()?
SELECT SUM(metric) AS metric FROM metrics WHERE assoc_id = ‘1983’ AND assoc_type = 257 AND context_id = ‘1’ AND metric_type = ‘ojs::counter’ LIMIT 5000;
Our Application.inc.php has these defined constants:
- define(‘ASSOC_TYPE_ARTICLE’, ASSOC_TYPE_SUBMISSION); // DEPRECATED but needed by filter framework
- define(‘ASSOC_TYPE_GALLEY’, ASSOC_TYPE_REPRESENTATION);
- define(‘ASSOC_TYPE_JOURNAL’, 0x0000100); // 256
- define(‘ASSOC_TYPE_ISSUE’, 0x0000103); //259
- define(‘ASSOC_TYPE_ISSUE_GALLEY’, 0x0000105); //261
- define(‘CONTEXT_JOURNAL’, 1);
Query I’ve got up to this point:
mysql> select sum(metric) from metrics where assoc_type=259 and (MONTH BETWEEN 201705 AND 201804) ;
±------------+
| sum(metric) |
±------------+
| 297876 |
±------------+
1 row in set (6.75 sec)
Thank you so much for reading, appreciate any assistance you can provide!
Best,
Rachel