DXcUKXH3

1use buchnevva;
2
3ADD JAR /opt/cloudera/parcels/CDH/lib/hive/lib/hive-contrib.jar;
4ADD jar hdfs:/opt/cloudera/parcels/CDH/lib/hive/lib/json-serde-1.3.8-jar-with-dependencies.jar;
5
6drop table if exists incomes;
7create table incomes as select content.userInn as userinn, coalesce(content.totalSum, 0) as totalsum, split(content.DateTime.date, '[\\s]')[0] as date from kkt;
8
9drop table if exists days_incomes;
10create table days_incomes as select userinn, date, SUM(totalsum) as totalsum from incomes group by userinn, date;
11
12drop table if exists max_incomes;
13create table max_incomes as select userinn, max(totalsum) as totalsum from days_incomes group by userinn;
14
15drop table if exists max_days_incomes_tmp;
16create table max_days_incomes_tmp as select max_incomes.userinn, max_incomes.totalsum, days_incomes.date from max_incomes left join days_incomes on (max_incomes.totalsum == days_incomes.totalsum and max_incomes.userinn == days_incomes.userinn);
17
18drop table if exists max_days_incomes;
19create table max_days_incomes as select userinn, split(collect_list(date)[0], '[-]')[2] as day, collect_list(totalsum)[0] as totalsum from max_days_incomes_tmp group by userinn;
20
21select * from max_days_incomes where userinn is not NULL;
22