eGzcxQTu

1ADD JAR /opt/cloudera/parcels/CDH/lib/hive/lib/hive-contrib.jar;
2ADD JAR /opt/cloudera/parcels/CDH/lib/hive/lib/json-serde-1.3.8-jar-with-dependencies.jar;
3
4SET hive.cli.print.header=FALSE;
5SET mapred.input.dir.recursive=TRUE;
6SET hive.mapred.supports.subdirectories=TRUE;
7
8USE martynenkoni;
9
10DROP TABLE IF EXISTS user_sum_income;
11
12CREATE TABLE user_sum_income AS
13SELECT content.userInn AS userInn,
14              COALESCE(SUM(content.totalSum), 0) AS total_sum,
15              TO_DATE(content.dateTime.date) AS user_date
16      FROM kkt
17      GROUP BY content.userInn,
18               TO_DATE(content.dateTime.date);
19
20
21SELECT user_sum_income.userInn AS Inn,
22        DAY(user_sum_income.user_date) AS day_of_month,
23        user_max_income.income AS max_income
24FROM
25  ( SELECT userInn AS inn,
26           MAX(total_sum) AS income
27   FROM user_sum_income
28   GROUP BY userInn) AS user_max_income
29JOIN user_sum_income 
30ON user_sum_income.userInn = user_max_income.Inn
31AND user_sum_income.total_sum = user_max_income.income
32WHERE user_max_income.inn IS NOT NULL
33ORDER BY Inn;
34
35
36