DLjAJKwr

1SET hive.exec.dynamic.partition.mode=nonstrict;
2SET hive.exec.max.dynamic.partitions=2000;
3SET hive.exec.max.dynamic.partitions.pernode=500;
4USE kohnkeklau;
5
6DROP TABLE IF EXISTS temp_Athletes PURGE; 
7CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS temp_Athletes(
8  AthleteID			BIGINT,
9  Name				STRING,
10  Nationality		STRING,
11  Sex				STRING,
12  DoB				SMALLINT,
13  Height			FLOAT,
14  Weight			INT,
15  Sport				STRING,
16  Gold				INT,
17  Silver			INT,
18  Bronze			INT)
19  
20  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
21with serdeproperties (
22  "separatorChar" = ",",
23  "quoteChar"     = "\"")
24  STORED AS TEXTFILE
25  LOCATION '/user/kohnkeklau'
26  TBLPROPERTIES ('skip.header.line.count'='1');
27  
28  DROP TABLE IF EXISTS Athletes PURGE;
29  
30  CREATE TABLE Athletes (
31    AthleteID BIGINT,
32    Name STRING,
33    Height FLOAT,
34	Medals INT
35)
36    PARTITIONED BY(Sex STRING);
37  
38  INSERT OVERWRITE TABLE Athletes PARTITION(Sex)
39  	SELECT temp.AthleteID, temp.Name, temp.Height, temp.Gold+temp.Silver+temp.Bronze as Medals,temp.Sex
40	FROM temp_Athletes temp;
41
42########################################################################################## 
43## Wyznaczyc 5 zawodników, którzy zdobyli medale i byli o wzroscie
44## poniżej średniej wzrostu dla zawodników zdobywających medale, z podziałem na płeć.
45##########################################################################################  
46
47USE kohnkeklau;
48(SELECT sex, name, height, medals FROM (
49    SELECT Name, Height, Sex, Medals, AVG(Height) OVER (PARTITION BY Sex) AS Average_Height
50        FROM athletes WHERE Medals > 0 ) t
51WHERE height < Average_Height AND Sex='female'
52ORDER BY height
53LIMIT 5)
54UNION ALL
55(SELECT sex, name, height, medals FROM (
56    SELECT Name, Height, Sex, Medals, AVG(Height) OVER (PARTITION BY Sex) AS Average_Height
57        FROM athletes WHERE Medals > 0 ) t
58WHERE height < Average_Height AND Sex='male'
59ORDER BY height
60LIMIT 5)