十斤香蕉_个人页

#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')) or (aid<>bid and unix_timestamp(ast,'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