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 |
|