Overview
| SQL |
|---|
| rowStyles | background:#B8D1F3,background:#DAE5F4,background:#B8D1F3 |
|---|
| noDataError | true |
|---|
| enableSorting | false |
|---|
| columnStyles | font-size: 1.0em |
|---|
| rowOrientation | horizontal |
|---|
| expandArray | false |
|---|
| style | fumble:0 |
|---|
| convertNull | false |
|---|
| id | mydata3 |
|---|
| class | @default testing |
|---|
| dataSource | mars_apistats |
|---|
| enableHighlighting | false |
|---|
| retainRowStyleOrder | false |
|---|
|
SELECT rtrim(to_char(sum(delivered/1024^4), 'FM999999999999990.999'), '.') as "Delivered data volume [TB]", to_char(sum(fields),'FM999,999,999,999,999') as "Number of retrieved fields", to_char(sum(requests), 'FM999,999,999,999,999') as "Number of user requests"
FROM datasets_stats_monthly
WHERE datasets_stats_monthly.dataset='yopp' and datasets_stats_monthly.month>=date'20170701'; |
*valid for the whole archiving period since 2015-05-01
Monthly sums
Data [TB]
- delivered: data volume delivered to user after post-processing(e.g. interpolation) applied if asked for
- retrieved: data volume retrieved from MARS before post-processing
- retrieved data volume should be normally equal or bigger then the delivered one
- it could be also smaller when user asks for higher then archived grid resolution (resulting in artificially bigger interpolated files - situation which should be rather avoided)
| Chart |
|---|
| showShapes | false |
|---|
| timePeriod | Quarter |
|---|
| width | 1200 |
|---|
| categoryLabelPosition | up45 |
|---|
| dataOrientation | vertical |
|---|
| type | bar |
|---|
| yLabel | TB |
|---|
| height | 400 |
|---|
|
| SQL |
|---|
| id | mydata |
|---|
| dataSource | mars_apistats |
|---|
| select to_char(month, 'MM/YYYY') as monthyear, CAST(delivered AS FLOAT)/1024^4 as "delivered" from datasets_stats_monthly where dataset='yopp' and month>=date'20170101' order by month ;
select to_char(month, 'MM/YYYY') as monthyear, CAST(retrieved AS FLOAT)/1024^4 as "retrieved" from datasets_stats_monthly where dataset='yopp' and month>=date'20170101' order by month |
|
Users
- user requests: number of MARS retrieval requests based on original user request (each user request leads usually to more MARS retrieval requests depending on the required period, parameters, level types etc)
- active users: users with at least 3 requests in given month
| Chart |
|---|
| showShapes | false |
|---|
| width | 1200 |
|---|
| categoryLabelPosition | up45 |
|---|
| dataOrientation | vertical |
|---|
| type | bar |
|---|
| yLabel | Nr of requests |
|---|
| colors | #FFAB00 |
|---|
| height | 300 |
|---|
|
| SQL |
|---|
| id | mydata |
|---|
| dataSource | mars_apistats |
|---|
| select to_char(month, 'MM/YYYY') as monthyear, requests as "user requests" from datasets_stats_monthly where dataset='yopp' and month>=date'20170701' order by month; |
|
| Chart |
|---|
| showShapes | false |
|---|
| width | 1200 |
|---|
| categoryLabelPosition | up45 |
|---|
| dataOrientation | vertical |
|---|
| type | bar |
|---|
| yLabel | Nr of users |
|---|
| colors | #36B37E |
|---|
| height | 300 |
|---|
|
| SQL |
|---|
| id | mydata |
|---|
| dataSource | mars_apistats |
|---|
| SELECT to_char(month, 'MM/YYYY') as monthyear, count(DISTINCT username) as "active users"
FROM users_stats_monthly
WHERE dataset='yopp' AND month>=date'20170701'
AND username IN (
SELECT username
FROM users_stats_monthly
WHERE dataset='yopp'
GROUP BY username
HAVING sum(requests) >= 3
)
GROUP BY month
ORDER BY month; |
|
Users per country [%]
- as per 1.5. 2018
- only countries with more than XXX are shown
| Chart |
|---|
| stacked | true |
|---|
| width | 900 |
|---|
| dataOrientation | vertical |
|---|
| opacity | 75 |
|---|
| height | 700 |
|---|
|
| SQL |
|---|
| id | mydata |
|---|
| dataSource | mars_apistats |
|---|
| SELECT profile_country.name, count(DISTINCT auth_user.username) as total_users
FROM profile_country
RIGHT JOIN profile_profile ON profile_profile.country_id = profile_country.id
RIGHT JOIN auth_user ON auth_user.id = profile_profile.user_id
RIGHT JOIN users_stats_monthly ON users_stats_monthly.username = auth_user.username
WHERE users_stats_monthly.dataset='yopp' and users_stats_monthly.month>=date'20170701'
GROUP BY profile_country.name
HAVING sum(users_stats_monthly.requests) > 5000
ORDER BY total_users DESC |
|