v0D3dkP4

1SET hive.exec.dynamic.partition.mode=nonstrict;
2SET hive.exec.max.dynamic.partitions=2000;
3SET hive.exec.max.dynamic.partitions.pernode=500;
4USE szczepanbart;
5
6DROP TABLE IF EXISTS testing PURGE;
7
8CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS testing(
9  Id				INT,
10  UserId			INT,
11  Name				VARCHAR(20),
12  Datee				date,
13  Class				TINYINT,
14  TagBased			VARCHAR(5))
15  ROW FORMAT DELIMITED
16  FIELDS TERMINATED BY ','
17  STORED AS TEXTFILE
18  LOCATION '/user/szczepanbart/test'
19  TBLPROPERTIES ('skip.header.line.count'='1');
20  
21DROP TABLE IF EXISTS fullr PURGE;
22
23CREATE TABLE fullr (
24  Id				INT,
25  UserId			INT,
26  Name				VARCHAR(20),
27  Class				TINYINT,
28  TagBased			VARCHAR(5),
29  Datee 			date)
30  COMMENT 'openstack badges'
31  PARTITIONED BY(Month Varchar(7))
32  STORED AS ORC;
33
34INSERT OVERWRITE TABLE fullr PARTITION(Month)
35  SELECT Id,UserId,Name,Class,TagBased,Datee,CAST(Datee as VARCHAR(7)) FROM testing;
36
37DROP TABLE IF EXISTS ranking PURGE;
38
39  CREATE TABLE ranking (
40  Datee				date,
41  Name				VARCHAR(20),
42  liczba_odznak		INT)
43  COMMENT 'openstack badges'
44  PARTITIONED BY(rankowani INT)
45  STORED AS ORC;
46
47INSERT OVERWRITE TABLE ranking PARTITION(rankowani) 
48  select tmppp.datee,
49tmppp.name,
50tmppp.liczba_odznak,
51dense_rank() over (partition by datee order by liczba_odznak DESC) as rankowanie
52from (
53  select distinct * 
54  from (
55	select datee,
56	name,
57	count(name2) over (partition by datee,name2) as LICZBA_ODZNAK
58	from fullr ) tmp 
59  WHERE tmp.datee in (
60	select tmpp.datee 
61	from (
62	  select datee,count(name) as LICZBA_ODZNAK 
63	  from fullr 
64	  group by datee 
65	  order by LICZBA_ODZNAK DESC 
66	  limit 10) tmpp)
67  ORDER BY TMP.datee,TMP.LICZBA_ODZNAK DESC) tmppp;
68
69  select * from ranking
70where rankowani < 4
71order by datee,rankowani