<!-- 1. 连续登录三天的用户有哪些 -->
select user_id from
(
select
user_id
,rdate
,lag(rdate,1) over(partition by user_id order by rdate) as rdate1
,lag(rdate,2) over(partition by user_id order by rdate) as rdate2
from
business.sqlexe1
where rstatus=1
) as a
where cast(rdate2 as int) = cast(rdate as int)-2
<!-- 2. 用户最新登录状态的前一天的日期和状态是什么 -->
elect user_id,rstatus,n_date from
(select * from business.sqlexe1
left join (
select user_id as uid,cast(cast(max(rdate) as int)-1 as string) as n_date
from business.sqlexe1
where rstatus=1
group by uid
order by uid) as a
on a.uid =sqlexe1.user_id) as b
where b.rdate = b.n_date
<!-- 3. 用户最新登录状态的前一天的日期和状态是什么 -->
<!-- Impala 不支持 hive COLLECT_SET函数的方式,只能另作它法 -->
select user_id,GROUP_CONCAT(cast(rstatus as string)) as status_cnt from
(select * from business.sqlexe1
left join (
select user_id as uid,cast(cast(max(rdate) as int) as string) as n_date
from business.sqlexe1
where rstatus=1
group by uid
order by uid) as a
on a.uid =sqlexe1.user_id) as b
where rdate<=n_date
group by user_id
order by user_id
由于row_number可以用在一二两题中,比较经典,遂作尝试:
<!-- 1. 连续登录三天的用户有哪些 -->
SELECT user_id from
(SELECT * from (
SELECT user_id,rdate,lag(rdate,2) OVER(PARTITION BY user_id ORDER BY rdate) as da FROM
(
SELECT row_number() over(PARTITION BY user_id ORDER BY user_id) as num,
user_id,rstatus,rdate FROM business.sqlexe1
where rstatus = 1
) as a) as b
where b.da IS NOT NULL) as c
WHERE cast(da as int) = cast(rdate as int)-2
<!-- 2. 用户最新登录状态的前一天的日期和状态是什么(这个用的不好,太冗余了) -->
SELECT user_id,rstatus,rdate from(
SELECT b.user_id,mm,c.rstatus,c.rdate from
(SELECT max(a.num)-1 as mm,a.user_id from
(SELECT row_number() over(PARTITION BY user_id ORDER BY user_id) as num,user_id,rstatus,rdate
FROM business.sqlexe1) as a
WHERE a.rstatus = 1
GROUP BY a.user_id
ORDER BY a.user_id) as b
RIGHT JOIN
(SELECT row_number() over(PARTITION BY user_id ORDER BY user_id) as num,user_id,rstatus,rdate
FROM business.sqlexe1) as c
on b.user_id = c.user_id and b.mm = c.num
ORDER BY b.user_id) as d
where user_id IS NOT NULL
ORDER BY user_id
以下是mentor的答案,重点是关注年份与row_number之差相等的关系:
<!-- 1. 连续登录三天的用户有哪些 -->
SELECT
T2.user_id,
T3.rstatus,
T3.rdate
FROM(
SELECT
T1.user_id,
T1.rstatus,
T1.rdate,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY rdate desc) rn
FROM business.sqlexe1 T1
WHERE T1.rstatus = 1
) T2
LEFT JOIN business.sqlexe1 T3
ON T3.user_id = T2.user_id
AND CAST(T3.rdate AS BIGINT) = CAST(T2.rdate AS BIGINT) - 1
WHERE T2.rn = 1;
<!-- 2. 用户最新登录状态的前一天的日期和状态是什么 -->
SELECT
T1.user_id
FROM(
SELECT
user_id,
rstatus,
rdate,
LEAD(rdate,2) OVER(PARTITION BY user_id ORDER BY rdate asc) rdate2
FROM business.sqlexe1
WHERE rstatus = 1
) as T1
HAVING CAST(T1.rdate2 AS BIGINT) - CAST(T1.rdate AS BIGINT)+1 = 3;
<!-- 3. 用户最新登录状态的前一天的日期和状态是什么 -->
SELECT
T2.user_id
FROM(
SELECT
T1.user_id,
CAST(rdate AS BIGINT)-CAST(rn AS BIGINT) rdate2
FROM(
SELECT
user_id,
rstatus,
rdate,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY rdate ASC) rn
FROM business.sqlexe1
WHERE rstatus = 1
) AS T1
) AS T2
GROUP BY user_id,rdate2
HAVING COUNT(1)=3;