十斤香蕉_个人页

#hive

1.

• CREATE table IF NOT EXISTS q1( id STRING, num STRING );

• insert into q1 values(1,1),(2,1),(3,1),(4,2),(5,1),(6,2),(7,2);

• SELECT num,count(1) c from (SELECT id,num,row_number()over(PARTITION BY num ORDER BY id) r, id-row_number()over(PARTITION BY num ORDER BY id) bz FROM q1 ) a GROUP BY num,bz having c>1

2.

• CREATE table IF NOT EXISTS q2( id STRING, time STRING );

• insert overwrite table q2 values(1,'2019/12/25 11:01'),(2,'2019/12/25 11:03'),(3,'2019/12/25 11:05'),(4,'2019/12/25 11:09'),(5,'2019/12/25 11:17'),(6,'2019/12/25 11:19'),(7,'2019/12/25 11:29'),(8,'2019/12/25 11:37');

• SELECT from_unixtime(floor(unix_timestamp(time,'yyyy/MM/dd hh:mm')/60/15)*60*15,'yyyy/MM/dd hh:mm'),count(1) FROM q2 GROUP BY floor(unix_timestamp(time,'yyyy/MM/dd hh:mm')/60/15);

3.同第一题

4. 对于相同的A列，C列相同则取该值，不同则置为1？

• CREATE TABLE q3( a STRING, b STRING, c STRING );

• insert into q3 values('aaa','1','x'),('aaa','2','y'), ('bbb','3','x'),('bbb','4','x'), ('ccc','5','y'),('ccc','6','y');

• select t.a,t.b+t2.b,if(t.c=t2.c,t.c,1) from( SELECT a,b,c FROM q3 where b%2=1 ) t left join (select a,b,c from q3 ) t2 where t.a=t2.a and t.b+1=t2.b

5.

• rank(),dense_rank()

• select project_id,employee_id,dense_rank() over(partition by project_id,employee_id order by experience_years desc) rk from (select project_id,employee_id from project) p left join (select employee_id,experience_years from employee ) e on p.employee_id=e.employee_id where rk=1

6.

• select actor_id,director_id from actorDirector group by actor_id,director_id having count(1)>=3

7.

• SELECT num from q4 GROUP BY num HAVING count(1)=1 ORDER BY num DESC LIMIT 1

8.

• create table if not exists q8( id string, starttime string, endtiem string);

• insert overwrite table q8 values(1,'08:00','09:15'),(2,'13:20','15:20'),(3,'10:00','14:00'),(4,'13:55','16:25'),(5,'14:00','17:45'),(6,'14:05','17:45'),(7,'18:05','19:45');

• SELECT DISTINCT bid FROM (SELECT id aid,starttime ast,endtiem aet from q8) a LEFT JOIN (SELECT id bid,starttime bst,endtiem bet from q8) b where (aid<>bid and unix_timestamp(ast,'hh:mm')<unix_timestamp(bst,'hh:mm') and unix_timestamp(aet,'hh:mm')>unix_timestamp(bst,'hh:mm')) or (aid<>bid and unix_timestamp(ast,'hh:mm')<unix_timestamp(bet,'hh:mm') and unix_timestamp(aet,'hh:mm')>unix_timestamp(bst,'hh:mm'))

9.

• CREATE TABLE q5( id int, name STRING );

• INSERT INTO q5 VALUES(1,'张三'),(2,'李四'),(3,'王五'),(4,'马六'),(5,'赵七');

• SELECT * from (SELECT id,lag(name,1,name)over() FROM q5) a WHERE a.id%2=1 UNION ALL SELECT * from (SELECT id,lead(name,1,name)over() FROM q5) b WHERE b.id%2=0

10.

• select a.name,b.name,c.name from (select * from city where parentid='0') a left join (select * from city where parentid='0') b
  a.id=b.parentid left join (select * from city where parentid='0') c b.id=c.parentid