开窗函数中partition应该放什么？

开窗函数row_number()over(partition order by)中partition应该放什么？

我们现在一个dt每天的分区表，简单化来设计，有原始数据如下，一共14条数据：

题目一：现在求每个用户，每天，每个APP， 使用时长最长的那次。

我们能发现一天中一个用户一个APP会打开关闭打开关闭好几次，所以业务主键每天每个用户每个APP对应多条数据，close_time-open_time就得到停留时间，因此现在是对一天一个用户一个APP取最大值。我们第一反应就是分组 max ，看看能否实现：

select uuid ,appid,dt, round(max((close_time-open_time)2460),0)
from oppo_entreview
group by uuid ,appid,dt
order by 1,2,3;

得到11行数据，的确是我们需求的。

那用row_number()over() 怎么做？
with oppo_entreview_cum as (
select
stay_time
from oppo_entreview

)
select * from (
select dt, uuid ,appid, stay_time, row_number()over(partition by dt, uuid，appid order by stay_time desc) rn
from oppo_entreview_cum
) t
where rn=1
order by dt,uuid,appid, stay_time

这是在一线城市，月薪2万以下，基本每个公司都会问的必考题，关键在于电话面试的时候你头脑是否清晰的理解题目。

row_number()over(partition by xxxx  order by xxxx desc) rn 
rank()over(partition by xxxx  order by xxxx desc) rn 
dense_rank()over(partition by xxxx  order by xxxx desc) rn 


还有一种情况 不需要分组，直接拿到排序  

row_number()over(order by xxxx desc) rn 
rank()over(order by xxx desc) rn 

DROP TABLE student_score;

SELECT * FROM student_score;

CREATE TABLE student_score (
name varchar2(8)  
,sub varchar2(8) 
,score number(18,2)
);



INSERT INTO  student_score VALUES ('k001','math',99);
INSERT INTO  student_score VALUES ('k001','english',80);
INSERT INTO  student_score VALUES ('k001','chinese',95);
INSERT INTO  student_score VALUES ('k002','math',80);
INSERT INTO  student_score VALUES ('k002','chinese',91);
INSERT INTO  student_score VALUES ('k002','english',93);
INSERT INTO  student_score VALUES ('k003','math',100);
INSERT INTO  student_score VALUES ('k003','chinese',89);
INSERT INTO  student_score VALUES ('k003','english',90);
INSERT INTO  student_score VALUES ('k004','chinese',95);


第一步先行转列 
NULL 会排在第一位 

WITH tmp1 AS (
SELECT 
      name 
     ,max(CASE WHEN sub='math' THEN score ELSE 0 END) AS sub_math
     ,max(CASE WHEN sub='english' THEN score ELSE 0 END) AS sub_english
     ,max(CASE WHEN sub='chinese' THEN score ELSE 0 END) AS sub_chinese
 FROM student_score
 GROUP BY 
       name 
) 
,tmp2 AS (
SELECT 
     name
     ,sub_math
     ,sub_english
     ,sub_chinese
     ,rank()over(ORDER BY sub_math DESC) AS rn 
FROM tmp1 
) 

SELECT *
-- sum(sub_math+ sub_english+sub_chinese)
FROM tmp2 
WHERE rn=1 




第二种情况  不  行转列 
-- 数学成绩排第一的
SELECT 
name 
,sub
,score
FROM (
SELECT 
name 
,sub
,score
,rank()over(ORDER BY score desc) rn 
FROM student_score
WHERE sub='math' ) t 
WHERE rn=1


-- -- 数学成绩排第一的学生的总成绩  



SELECT 
name , sum(score)
FROM student_score
WHERE name IN (
SELECT 
name 
FROM (
SELECT 
name 
,sub
,score
,rank()over(ORDER BY score desc) rn 
FROM student_score
WHERE sub='math' ) t 
WHERE rn=1
)  
GROUP BY name 

lag() 函数