灰谷

sql:over() 开窗函数,一个第一次知道的函数

前情:一个看板,在不同的表里连续获取某条件下最大时间的某个字段值。

table:bitcron_in

Item Value Qty
1 aline 3
2 aline 6
3 bline 6
4 bline 9

问题1描述:获取qty每一个value条件下qty值最小的那一行信息,无论是搜索或是当时自己下意识的写法都是:select * from bitcron_over where qty in (select max(qty) from bitcron_over group by value);

结果如下:

Item Value Qty
2 aline 6
3 bline 6
4 bline 9

从结果上可知该写法因“6”这个重叠值的存在而为错误写法;
问题1解法:在in中同时匹配两个条件,即:select * from bitcron_over where (value,qty) in (select value,max(qty) from bitcron_over group by value)

结果如下:

Item Value Qty
2 aline 6
4 bline 9

结论:in用了也蛮久的了,但是一直在基础的单一条件下使用, select * from table where (a,b) in (select a,b from table)这个用法一直属于知道,但不会下意识想起来的东西。

=== 9/9 准备在模拟情景时发现源代码一段漏洞,关于over()等 9/10 再更新===

=== 9/21 完美符合我拖延症的时间分割线,凑好看瞎编点字进来吧吧吧吧吧===

情景模拟:(其实懒的想啊,但是还是想一个吧,想得好像有点扯)

个人信息开放的时代,每一个人的信息都可以被权利机构通过系统查询,个人独立ID号的存在,面部识别系统的快速发展让人们无所遁形。
在其中,为了保证公共交通的安全,公交系统会以安保的名义记录每辆交通工具上乘客的ID搭乘时间,形成监控看板在中控机构监察,记录ID以log形式随时随地添加在数据库中。

以此情景下,目前会形成两个表,一个是以个人信息存在的表,一个是交通信息log表,设置表内容如下:

bitcron_over_people:

ID NAME GENSDER
001 Abbey F
002 Fair M
003 Cox M
004 Ileana F

bitcron_over_car:

CARNUM PPID STAUS TIME
8路车 001 IN 8012/1/1
8路车 001 ONWAY 8012/1/2
8路车 001 ARRIVE 8012/1/2
8路车 001 END 8012/1/3
A888航 002 IN 8012/1/1
A888航 002 ONWAY 8012/1/1
A888航 002 ARRIVE 8012/1/2
D0008 003 IN 8012/1/1
D0008 003 ONWAY 8012/1/3
D0008 004 IN 8012/1/1
D0008 004 ONWAY 8012/1/3

===想情景要死人的分割线,造数据死得更惨的分割线,其实是凑字数的分割线===

首先要获取的是每个交通工具上未下车的信息(这个场景设计和我实际的场景有差别在于我的实际数据是不会有重复乘客的设定,暂时我们先默认它不重复先),即有非END状态的所有乘客。
select * from bitcron_over_car where PPID not in (select PPID from bitcron_over_car where STAUS = 'END')

结果如下:

CARNUM PPID STAUS TIME
A888航 002 IN 8012/1/1
A888航 002 ONWAY 8012/1/1
A888航 002 ARRIVE 8012/1/2
D0008 003 IN 8012/1/1
D0008 003 ONWAY 8012/1/3
D0008 004 IN 8012/1/1
D0008 004 ONWAY 8012/1/3

获取最后状态:
select CARNUM,PPID,max(TIME) as TIME from bitcron_over_car where PPID not in (select PPID from bitcron_over_car where STAUS = 'END') group by ppid

结果如下:

CARNUM PPID TIME
A888航 002 8012/1/2
D0008 003 8012/1/3
D0008 004 8012/1/3

在这一步中我们发现,带不出来staus的状态,在group by中它的存在会影响我们的最终值(搞到这边突然知道这个场景和实际我会迫切用到over()之间的差异……好像只能硬着头皮往下写了)

所以用上over()这个能带出值的函数:ROW_NUMBER()OVER()

SELECT row_number()over(PARTITION BY ppid, carnum
ORDER BY TIME) AS rownum,
carnum,
ppid,
staus
FROM bitcron_over_car
WHERE ppid NOT IN
(SELECT ppid
FROM bitcron_over_car
WHERE staus = 'END')

===9/21最后发现自己电脑没oracle只有mysql的超尴尬等我想个法子的分割线===

| 2018-09-09 | | 基础知识2 sql2 |
Comments
Write a Comment