Case 1:

Demand: there is such a batch of data, now it requires: the maximum number of single month visits by each user up to the end of each month and the total number of visits accumulated to that month.
Data:

User name, month, number of visits
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,11

Final result:

User month      Maximum visits total visits       Number of visits in the month
A     2015-01          33              33               33
A     2015-02          33              43               10
A     2015-03          38              81               38
B     2015-01          30              30               30
B     2015-02          30              45               15
B     2015-03          44              89                44

Solve:

#step01 counts the total number of visits per user per month
create view view_step01 as select name,month,sum(visitCount) total from t_user  group by name,month;
#step02 (self connection, connection condition is name)
create view view_step02 as
    select t1.name aname,t1.month amonth,t1.total atotal,t2.name bname,t2.month bmonth,t2.total btotal
    from view_step01 t1 join view_step01  t2 on t1.name =t2.name 
#step03 remove useless data, each group finds data less than or equal to its own month
select bname,bmonth,max(btotal),sum(btotal),btotal
from view_step02
where unix_timestamp(amonth,'yyyy-MM')>=unix_timestamp(bmoth,'yyyy-MM')
group by aname,amonth,atotal;

Case two:

#Create table statement:

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sid` int(11) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#insert data
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);


Demand: student number of all students whose math course scores are higher than Chinese course scores
Solution: (row and column conversion)

SELECT
    t1.sid 
FROM
    (
SELECT
    sid,
    max( CASE `course` WHEN "yuwen" THEN score ELSE 0 END ) AS "yuwen",
    max( CASE `course` WHEN "shuxue" THEN score ELSE 0 END ) AS "shuxue" 
FROM
    `course` 
GROUP BY
    sid 
    ) t1 
WHERE
    t1.yuwen < t1.shuxue;

Case three:

Demand: for example, 2010012325 indicates that the temperature on January 23, 2010 is 25 ℃. Now it is required to use hive to calculate the date + temperature of the maximum temperature in each year.
Data:
Annual temperature
20140101 14
20140102 16
20140103 17
20140104 10
20140105 06
20120106 09
20120107 32
20120108 12
20120109 19
20120110 23
20010101 16
20010102 12
20010103 10
20010104 11
20010105 29
20130106 19
20130107 22
20130108 12
20130109 29
20130110 23
20080101 05

Now we need to perform group by according to the month and year, but the final result needs to be 20080101 05. That is to say, the group field and the last reserved field are different. What should we do?

Solve:

#Step1:
CREATE VIEW view_step1 AS SELECT
substr( tmp, 1, 4 ) AS YEAR,
max( substr( tmp, 9, 2 ) ) AS tmp 
FROM
    tmp 
GROUP BY
    substr( tmp, 1, 4 );

#Step2:
SELECT
    b.tmp,
    a.tmp 
FROM
    view_step1 a
    JOIN tmp b ON a.YEAR = substr( b.tmp, 1, 4 ) 
    AND a.tmp = substr( b.tmp, 9, 2 );

Case four:

data

#It means that some students with id 1,2,3 have taken courses a,b,c,d,e,f, including:
id course 
1,a 
1,b 
1,c 
1,e 
2,a 
2,c 
2,d 
2,f 
3,a 
3,b 
3,c 
3,e

Requirement: write Hive's HQL statement to achieve the following results: 1 in the table indicates elective, and 0 in the table indicates no elective.

Solution (solution 1):

#Row and column transformation
select id 
max(case when course='a' then 1 else 0 and ) as a ,
max(case when course='b' then 1 else 0 and ) as b ,
max(case when course='c' then 1 else 0 and ) as c ,
max(case when course='d' then 1 else 0 and ) as d ,
max(case when course='e' then 1 else 0 and ) as e ,
max(case when course='f' then 1 else 0 and ) as f
from course  group by id;

Solution (solution 2):

#Collect set function
#step01
create view id_courses as 
select a.course acourse,b.course bcourse,b.id id
(select collect_set(course) as course from course) a 
    join 
(selecet id ,colect_set(course) as course from course group by id) b

#step02
select id,
case when array_contains(bcourse,acourse[0]) then 1 else 0 end as a ,
case when array_contains(bcourse,acourse[1]) then 1 else 0 end as b ,
case when array_contains(bcourse,acourse[2]) then 1 else 0 end as c ,
case when array_contains(bcourse,acourse[3]) then 1 else 0 end as d ,
case when array_contains(bcourse,acourse[4]) then 1 else 0 end as e ,
case when array_contains(bcourse,acourse[5]) then 1 else 0 end as f
from id_courses;