diff options
author | Yury Melnichek <what@hydrogen.local> | 2014-12-24 22:23:50 +0300 |
---|---|---|
committer | Alex Zolotarev <alex@maps.me> | 2015-09-23 02:35:21 +0300 |
commit | 2898c8b720806d86b8ba6e0ab2c14f6ab3c48577 (patch) | |
tree | d3df8d9541969029737fa51e1ecefa5024afd2ca /tools/download_statistics | |
parent | d73d359c13b19011fee2e5dec6b5e1d2c2876265 (diff) |
[sql] Script to calculate download statistics.
Diffstat (limited to 'tools/download_statistics')
-rw-r--r-- | tools/download_statistics/calc_download_stats.sql | 41 |
1 files changed, 41 insertions, 0 deletions
diff --git a/tools/download_statistics/calc_download_stats.sql b/tools/download_statistics/calc_download_stats.sql new file mode 100644 index 0000000000..8f31266421 --- /dev/null +++ b/tools/download_statistics/calc_download_stats.sql @@ -0,0 +1,41 @@ +drop table if exists downloads; +create table downloads(year int, month int, country_from varchar(255), country_to varchar(255), type char(1), count int); + +load data infile '/tmp/aggregated_download_info.csv' into table downloads fields terminated by ';' lines terminated by '\n'; + +delete from downloads where country_to in (select country_to from (select country_to, SUM(count) as cnt from downloads group by country_to having cnt < 1000 order by cnt asc limit 100) as z); + +delete from downloads where country_from in ('None', 'Unknown'); + +update downloads set country_from='USA' where country_from='United States'; +update downloads set country_from='UK' where country_from='United Kingdom'; +update downloads set country_from='Moldova' where country_from='Republic of Moldova'; +update downloads set country_from='Lithuania' where country_from='Republic of Lithuania'; +update downloads set country_from='Burma' where country_from='Myanmar [Burma]'; +update downloads set country_from='China' where country_from='Hong Kong'; +update downloads set country_from='China' where country_from='Macao'; +update downloads set country_from='South Korea' where country_from='Republic of Korea'; +update downloads set country_from='Jordan' where country_from='Hashemite Kingdom of Jordan'; +update downloads set country_from='Italy' where country_from='San Marino'; +update downloads set country_from='Micronesia' where country_from='Federated States of Micronesia'; +update downloads set country_from='United States Virgin Islands' where country_from='U.S. Virgin Islands'; +update downloads set country_from='Congo-Kinshasa' where country_from='Congo'; +update downloads set country_from='Congo-Brazzaville' where country_from='Republic of the Congo'; +update downloads set country_from='Italy' where country_from='Vatican City'; +update downloads set country_from='Saint Martin' where country_from='Sint Maarten'; +update downloads set country_from='Netherlands Antilles' where country_from='Bonaire'; + +select distinct country_from as country_from_that_never_to from downloads where country_from not in (select country_to from downloads); +select distinct country_to as country_to_that_never_from from downloads where country_to not in (select country_from from downloads); + +select country_to as most_downloaded_country, SUM(count) as cnt from downloads where type='M' group by country_to order by cnt desc limit 20; +select country_to as most_downloaded_country_from_outside, SUM(count) as cnt from downloads where type='M' and country_from <> country_to group by country_to order by cnt desc limit 20; +select country_from as most_downloading_country, SUM(count) as cnt from downloads where type='M' group by country_from order by cnt desc limit 20; + + +select country_from, in_cnt, out_cnt, out_cnt * 1.0 / in_cnt as ratio from \ + (select country_from, SUM(in_cnt) as in_cnt, SUM(out_cnt) as out_cnt from ( \ + select country_from, SUM(count) as in_cnt, 0 as out_cnt from downloads where type='M' and country_from=country_to group by country_from \ + union select country_from, 0 as in_cnt, SUM(count) as out_cnt from downloads where type='M' and country_from<>country_to group by country_from \ + ) as z group by country_from \ +) as zz order by ratio desc; |