KT4236i8

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 avg_income_first;
11
12CREATE TABLE avg_income_first AS
13SELECT content.userInn AS userInn, AVG(content.totalSum) AS avg_income
14FROM kkt
15WHERE subtype = "receipt" AND HOUR(content.dateTime.date) < 13
16GROUP BY content.userInn;
17
18DROP TABLE IF EXISTS avg_income_second;
19
20CREATE TABLE avg_income_second AS
21SELECT content.userInn AS userInn, AVG(content.totalSum) AS avg_income
22FROM kkt
23WHERE subtype = "receipt" AND HOUR(content.dateTime.date) >= 13
24GROUP BY content.userInn;
25
26SELECT f.userInn AS userInn, ROUND(f.avg_income, 0) AS morning_income, ROUND(s.avg_income, 0) AS evening_income
27FROM avg_income_first AS f
28JOIN avg_income_second s 
29ON f.userInn = s.userInn
30WHERE f.avg_income > s.avg_income AND f.userInn IS NOT NULL
31ORDER BY morning_income  
32LIMIT 50;
33