Hive SQL常考面试题

多列转多行

test:
result:

select
	a.name,
	b.subject,
	b.score
from test as a
lateral view explode(
	map(
		'语文',chinese,
		'数学',math,
		'英语',english
	)
) b as subject, score;

select
	a.name,
	b.subject,
	b.score
from test as a
lateral view explode(
	str_to_map(concat(
		'语文=',chinese,
		'&数学=',math,
		'&英语=',english
	), '&', '=')
) b as subject, score;

多行转多列

test:

result:

select
	name,
	sum(case when subject='语文' then score else 0 end) chinese,
	sum(case when subject='数学' then score else 0 end) as math,
	sum(case when subject='英语' then score else 0 end) english,
from test
group by name;

topN

select *
from(
		select 
			name,
			subject,
			score,
			row_number() over(partition by subject order by score desc) as rank
		from test
	) tmp
where tmp.rank <= 3;

求连续3天登录用户

wang,2020-05-01
wang,2020-05-02
wang,2020-05-03
wang,2020-05-04
wang,2020-05-07
zhao,2020-04-01
zhao,2020-04-01
zhao,2020-04-07
zhao,2020-05-09
zhao,2020-05-10
li,2020-01-10
li,2020-01-12
li,2020-01-13
li,2020-01-14
li,2020-02-12
li,2020-02-13

select
	t.id,
	date_sub(t.login_date, t.rank) as flag_date,
	count(1) as continuous_days
from (
	select
		id,
		login_date,
		row_number() over(partition by id order by login_date) as rank
	from login
) t
group by t.id, date_sub(t.login_date, t.rank)
having count(1) >= 3;

select
	m.id
from(
	select
		t.id,
		date_sub(t.login_date, t.rank) as flag_date,
		count(1) as continuous_days
	from (
		select
			id,
			login_date,
			row_number() over(partition by id order by login_date) as rank
		from login
	) t
	group by t.id, date_sub(t.login_date, t.rank)
	having count(1) >= 3;
) m
group by m.id;

求连续n天登录用户

select t.user_id
from
(select
    user_id,
    datediff(login_date, lag(login_date, n-1, -1) over(partition by user_id order by login_date)) 
    as diff
from login) t
where diff = n-1
group by t.user_id;

select m.user_id
from
    (select t.user_id, count(1)
    from
        (select user_id, login_date,
            row_number() over(partition by user_id order by login_date) as rank
        from login) t
    group by t.user_id, date_sub(t.login_date, t.rank)
    where count(1) >= n
    ) m
group by m.user_id;