第一题:获取拥有最新登陆时间的设备ID
Activity表如下:图-1
需要获取的数据为player首次登陆设备的device_id,得到结果如下:图-2
代码如下:
select player_id , device_id from
(select row_number()over(partition by player_id order by event_date) rowid , activity.* from activity) as A1
where rowid=1;
使用到的函数:
row_number()、over()、partition by、order by
开窗函数:
OVER(PARTITION BY)函数
开窗函数,Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:分析函数对于每个组返回多行,而聚合函数对于每个组只返回一行。
over函数的写法:
上题中 over(partition by player_id order by event_date)
按照event_date排序进行累计,order by是个默认的开窗函数,按照player_id分区。
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。(可使用下面的代码建立T2_TEMP表,以下解释基于该表)SQL 例题表代码(第一题)www.jianshu.com
开窗的窗口范围:
over(order by sroce range between 5 preceding and 5 following):窗口范围为当前行数据幅度减5加5后的范围内的。
over(order by sroce rows between 5 preceding and 5 following):窗口范围为当前行前后各移动5行。OVER(PARTITION BY)函数介绍www.cnblogs.com
第二题:找出各分组中某属性最大的值
project表:
employee表:
查询每个项目中经验最丰富的(employee_years最大的)员工,结果如下:
代码如下:
select t.project_id,t.employee_id from(
select p.project_id,p.employee_id,rank()over(partition by p.project_id order by experience_years desc) qa from project p
left outer join employee e
on p.employee_id=e.employee_id) t
where qa=1;
使用的依然是开窗函数,相较于第一题,第二题是两表联立,需要使用到JOIN。
两题中使用的row_number()和rank()函数的差别在于rownumber()只返回一个结果,比如有两人并列第一则只会返回一个结果,根据employee表可以看出experience_years中有相同经验的员工,因此这里使用rank()。
待续……