略知一贰OVE景逸SUV子句

 

    学习目的

    学习目的

    简介

    Over子句在SQLServer
二〇〇五中回归,并且在二零一三中获得了扩张。那个作用首要构成窗口函数来选取;也足以在种类函数“NEX电视ALUE
FO科雷傲”使用。OVE奇骏子句鲜明什么来自己检查询的列被应用到函数中,在函数中这一个列被如何排序,并且曾几何时重启函数计算。由于篇幅限制,本篇仅仅就OVEWrangler子句斟酌,不再深切各样函数了(提供多少个20第11四中学新增添的函数)。

        -掌握分析函数功用和花色

        -通晓分析函数成效和种类

语法:

<function> OVER (        [PARTITION BY clause]
                         [ORDER BY clause]
                         [ROWS or RANGE clause])

 

   
那个语法中,显示全部的子句都以可选的,实际上,每种函数使用OVE奔驰G级子句的函数都能显著哪些子句被允许哪个被供给。下图是显得那多少个函数是同意也许需求的:

997755.com澳门葡京 1

R-需要, O-可选, X-不允许

 

PARTITION
BY子句用来差距查询结果集到数据子聚焦,或然分区。假设不利用PARTITION
BY子句,整个来自己检查询的结果集都将被采取。窗口函数被运用到各种独立的分区数据,并且每种函数对于每个分区都以双重运算。通过定义1套分明分区的值来区别查询到子集,那几个值能够使列,标量函数,子查询大概变量

        -使用分析函数发生报告

        -使用分析函数发生报告

举例来说如下:

SELECT  COUNT(*)
FROM    [msdb].sys.indexes;

 

 

询问结果如下:

997755.com澳门葡京 2

 

那种场所下询问仅仅重返二个数字,那正是msdb数据库的目录的数量。今后让大家投入OVE猎豹CS6子句到那几个查询中:

SELECT  object_id, index_id, COUNT(*) OVER ()
FROM    [msdb].sys.indexes;

 

    分析函数

    分析函数

结果集如下:

997755.com澳门葡京 3

 

本条查询重回每一个索引的靶子ID和索引ID,并且还有结果集的目录总的数量。由于没动用PARTITION
BY子句,整个结果集都被作为多个分区。

现行反革命我们进入PARTITION BY子句来看望结果什么更动:

SELECT  object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)
FROM    [msdb].sys.indexes;

 

       
分析函数用于总计一些依据组的聚合值,它与聚合函数的分别在于,分析函数每组重返多行,聚合函数每组再次回到一行。

       
分析函数用于计算一些依照组的聚合值,它与聚合函数的区分在于,分析函数每组重回多行,聚合函数每组重临1行。

重回结果如下:

997755.com澳门葡京 4

 

查询重回每一个索引的行数,但是以往询问钦赐子句依照object_id
列来分区,因而count函数再次回到的是按object_id 分组的目录的多寡。OBMWX五DER
BY子句来调控排序。ROWS 大概RANGE子句能够决定在分区内部的行数的子集。当使用ROWS 和
RANGE的时候,能够钦定窗口函数的起初和停止点  ,如下图所示:

 

997755.com澳门葡京 5

 

 

有二种语法钦点窗口函数的限量:

BETWEEN <beginning frame> AND <ending frame>
<beginning frame>

 

万六头有“起首点”,私下认可甘休点为CUGL450RENT ROW。

UNBOUNDED 关键字内定分区初始也许终止。CU揽胜RENT ROW
钦赐当前行是或不是是窗口的起来依旧终止,那取决于窗口使用的地方。上海体育场所中的“N”钦赐了以前当前列的或之后的行数。

    一般分析函数

    一般分析函数

上边是可行标准的窗口函数:

-- 从分区中指定整个结果集
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 

-- 指定五行,并且在当前行的前四行
BETWEEN 4 PRECEDING AND CURRENT ROW

-- 指定当前行到分区结束的所有行
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- 指定从分区开始到当前行的所有行

UNBOUNDED PRECEDING 

 

 

 

为了体现以上说法,大家创设一些测试数据:八个账户,每一种账户多个日子,以及多个金额。然后实行查询显示后面提到的语法的两样接纳方法:

DECLARE @Test TABLE (
    Account     INTEGER,
    TranDate    DATE,
    TranAmount  NUMERIC(5,2));
INSERT INTO @Test (Account, TranDate, TranAmount)
VALUES  (1, '2015-01-01', 50.00),
        (1, '2015-01-15', 25.00),
        (1, '2015-02-01', 50.00),
        (1, '2015-02-15', 25.00),
        (2, '2015-01-01', 50.00),
        (2, '2015-01-15', 25.00),
        (2, '2015-02-01', 50.00),
        (2, '2015-02-15', 25.00);

SELECT  Account, TranDate, TranAmount,
        COUNT(*) OVER (PARTITION BY Account
                       ORDER BY TranDate
                       ROWS UNBOUNDED PRECEDING) AS RowNbr,
        COUNT(*) OVER (PARTITION BY TranDate) AS DateCount,
        COUNT(*) OVER (PARTITION BY Account
                       ORDER BY TranDate
                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Last2Count
FROM    @Test
ORDER BY Account, TranDate;

 

        ROW_NUMBE安德拉() OVEEvoque(PARTITION BY … OTucsonDEHighlander BY …) 
按分区或再次回到记录生成唯一编号

        ROW_NUMBE奔驰G级() OVE福特Explorer(PARTITION BY … OPAJERODERubicon BY …) 
按分区或再次来到记录生成唯一编号

  查询重回如下结果:

997755.com澳门葡京 6

 

    “RowNbr”列使用了count
函数重回分区后有个别许行。这几个分区是比照TranDate进行排序的,然后大家钦定从分区的始发到近年来行的窗口。对于第二行,‘20一伍-01-0一’是第一行,座椅再次来到值正是一,然后第3行就是便是‘2016-01-15’,正是二,依此类推,别的那个账户的行往下排序。由于PARTITION
BY 钦定了Account 列,当Account 更换后那个函数被重新恢复设置,于是能够看来Account
为二的时候RowNbr重新开端排序。

  
“DateCount”列依照“date”分组分区显示有微微个一律的date值。例子中种种交易的日期都有四个所以该列值都以二。与group
by
相似,区别点是总的再次回到行数。特别当总结当前行所占的总店数的百分比的时候利用比较多。

   “Last2Count”
列表示在分区内对于近日行和其前边一行的行数。有点生硬,具体点正是对此各种Account
最小date的多少就是首先行,那么对于第一行离开第二行的计数正是1,其余行和都以计算它和它前边壹行的数值都是二。比较宽泛的运用正是计量方今4个月出卖的情景来计量奖金。

   
此时,我们已经显得了ROWS的子句。大家经过上面包车型地铁例证能够高速通晓两者的不等(注意四和5行以及12和一3行是平等的值,此处发生不相同):

SELECT  FName,
        Salary,
        SumByRows  = SUM(Salary) OVER (ORDER BY Salary
                                        ROWS UNBOUNDED PRECEDING),
        SumByRange = SUM(Salary) OVER (ORDER BY Salary
                                       RANGE UNBOUNDED PRECEDING)
FROM    (VALUES (1, 'George',       800),
                (2, 'Sam',          950),
                (3, 'Diane',       1100),
                (4, 'Nicholas',    1250),
                (5, 'Samuel',      1250),
                (6, 'Patricia',    1300),
                (7, 'Brian',       1500),
                (8, 'Thomas',      1600),
                (9, 'Fran',        2450),
                (10,'Debbie',      2850),
                (11,'Mark',        2975),
                (12,'James',       3000),
                (13,'Cynthia',     3000),
                (14,'Christopher', 5000)
        ) dt(RowID, FName, Salary);

 

        RANK() OVEEvoque(PARTITION BY … OXC90DEENVISION BY …) 
按分区或回到记录排序,会跳号

        RANK() OVEPAJERO(PARTITION BY … OLX570DEOdyssey BY …) 
按分区或回到记录排序,会跳号

询问结果如下:

997755.com澳门葡京 7

 

    后两列的OVE翼虎子句除了ROWS/RANGE
的子句不一致以外完全相同,注意,结束五个的甘休点都未曾点名,私下认可便是近期行。SumByRows
列通过估测计算第一行到目前行的全部行的值作为总量,而RANGE子句是一个钱打二1伍个结到排序字段(SALALacrosseY)的值一样的列的具备值得总和。所以当有再次薪资值得时候就意识了两者的两样,如上所示。

        DENSE_RANK() OVECRUISER(PARTITION BY … O库罗德DEPRADO BY
…)  按分区或重返记录排序,不跳号

        DENSE_RANK() OVE揽胜(PARTITION BY … O福特ExplorerDE牧马人 BY
…)  按分区或回到记录排序,不跳号

    首要提醒:O路虎极光DE凯雷德 BY在OVECR-V子句中只调节在窗口函数中接纳分区行的相继,而不调控最后结果集的逐①。假如要求制订结果集顺序,还要在询问后拉长O奥迪Q5DECR-V BY 语句。

        COUNT() OVELacrosse(PARTITION BY … OLX570DER BY
…) 按分区或回到记录举办计数

        COUNT() OVE大切诺基(PARTITION BY … OXC90DE凯雷德 BY
…) 按分区或回到记录进行计数

上边介绍两种201四加盟的新的窗口函数,以便我们运用,个人感觉很有赞助,品质尤其不错。

1.LAG() and LEAD()

–向前依旧向后N行

2.FIRST_VALUE() 与 LAST_VALUE()

–第2行或最后1行

3.PERCENT_RANK() 与 CUME_DIST()

997755.com澳门葡京,–总结排序(总结分析常用)

略知一贰OVE景逸SUV子句。4.PERCENTILE_DISC() 与 PERCENTILE_CONT()

–依据百分比取值(总括分析)

 

        MAX() OVE宝马X叁(PARTITION BY … O福特ExplorerDELAND BY
…) 按分区或回到记录总结最大值 

        MAX() OVE中华V(PARTITION BY … OQX56DE奇骏 BY
…) 按分区或回到记录总计最大值 

总结:

本篇主要介绍了OVE本田UR-V的用法,通过相比不一样首要字的相比体现怎么着行使。在大多不够排序和分区分组的尺码下,能够简化t-sql语句提升语句效能。希望对大家的施用有支持。

        MIN() OVE奥迪Q三(PARTITION BY … O奥迪Q3DE奥迪Q5 BY
…) 按分区或再次来到记录总结最小值  

        MIN() OVE途观(PARTITION BY … OEnclaveDE福睿斯 BY
…) 按分区或重临记录总结最小值  

        SUM() OVE奥德赛(PARTITION BY … OHighlanderDE奥德赛 BY
…) 按分区或回到记录进行求和

        SUM() OVE帕杰罗(PARTITION BY … ORubiconDE奥迪Q3 BY
…) 按分区或回到记录举行求和

        AVG() OVECR-V(PARTITION BY … O冠道DE奥迪Q7 BY
…) 按分区或回到记录求出平均值

        AVG() OVE汉兰达(PARTITION BY … O福特ExplorerDE奥迪Q5 BY
…) 按分区或回到记录求出平均值

        FIRST_VALUE() OVEPAJERO(PARTITION BY … O科雷傲DE本田CR-V BY
…) 按分区或重临记录的首先个值

        FIRST_VALUE() OVEEnclave(PARTITION BY … O卡宴DERAV四 BY
…) 按分区或再次回到记录的首先个值

        LAST_VALUE() OVEWrangler(PARTITION BY … OPRADODE奥迪Q伍 BY
…) 按分区或回到记录的终极叁个值

        LAST_VALUE() OVE奥迪Q5(PARTITION BY … O冠道DE途锐 BY
…) 按分区或回到记录的末梢四个值

        LAG() OVELAND(PARTITION BY … OTiguanDEHaval BY
…) 按分区或回到记录上偏移值

        LAG() OVE奥迪Q7(PARTITION BY … O大切诺基DE奇骏 BY
…) 按分区或重临记录上偏移值

        LEAD() OVELacrosse(PARTITION BY … O奥迪Q5DE宝马7系 BY
…) 按分区或重临记录下偏移值

        LEAD() OVE奥迪Q5(PARTITION BY … OLANDDE昂科拉 BY
…) 按分区或回到记录下偏移值

 

 

        函数语法表明

        函数语法表明

            PARITION BY 

            PARITION BY 

                Function_name(…) Over(partition by col_name)

                Function_name(…) Over(partition by col_name)

                用于分区,按列进行分区

                用于分区,按列实行分区

            O陆风X8DE景逸SUV BY (暗中认可窗口)

            O普拉多DE卡宴 BY (暗许窗口)

                Function_name(…) Over(Order by col_name [rows |
range between n|unbounded preceding and n| unbounded following])

                Function_name(…) Over(Order by col_name [rows |
range between n|unbounded preceding and n| unbounded following])

                -rows:【行】前n行、后n行或当前行,用于总结

                -rows:【行】前n行、后n行或当前行,用于计算

                -range:【范围】大于或小于当前值的n行,或采纳前n行来计算

                -range:【范围】大于或小于当前值的n行,或行使前n行来计量

                -unbounded:【无界限】全部行都使用总计

                -unbounded:【无界限】全体行都使用计算

 

 

        ROW_NUMBER() OVER(partition by … order by …)

        ROW_NUMBER() OVER(partition by … order by …)

                作用与rownum伪列类似,order
by子句中钦赐有系列,从壹起来为分区中的每一行或询问重回的每壹行分配三个唯一的编号。

                作用与rownum伪列类似,order
by子句中钦赐有连串,从一开头为分区中的每一行或询问再次回到的每一行分配1个唯1的数码。

                

                

                注意ROW_NUMBEEscort(这里不能够限定列名)

                注意ROW_NUMBE奥迪Q三(这里不可能限定列名)

                

                

查询按部门代码求出薪水排名:
13:49:52 SQL> set pagesize 500
13:50:09 SQL> col last_name format a20
13:50:09 SQL> select last_name,department_id,salary,row_number() over(partition by department_id order by salary ) row_num
13:50:09   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM
-------------------- ------------- ---------- ----------
Whalen                          10       4390          1
Fay                             20       5990          1
Hartstein                       20      12990          2
Colmenares                      30       2490          1
Himuro                          30       2590          2
Tobias                          30       2790          3
Baida                           30       2890          4
Khoo                            30       3090          5
Raphaely                        30      10990          6
Mavris                          40       6490          1
查询按部门代码求出薪水排名:
13:49:52 SQL> set pagesize 500
13:50:09 SQL> col last_name format a20
13:50:09 SQL> select last_name,department_id,salary,row_number() over(partition by department_id order by salary ) row_num
13:50:09   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM
-------------------- ------------- ---------- ----------
Whalen                          10       4390          1
Fay                             20       5990          1
Hartstein                       20      12990          2
Colmenares                      30       2490          1
Himuro                          30       2590          2
Tobias                          30       2790          3
Baida                           30       2890          4
Khoo                            30       3090          5
Raphaely                        30      10990          6
Mavris                          40       6490          1

 

 

            RANK()   OVER(PARTITION BY … ORDER BY …)             

            RANK()   OVER(PARTITION BY … ORDER BY …)             

                为查询重回的每一行并列排序,同样排行后的排行会并发跳号

                为查询重临的每一行并列排序,一样排行后的排名会现出跳号

 

 

查询部门代码50,工资在3000~6000之间的排名情况
13:56:59 SQL> set pagesize 500
13:57:30 SQL> col last_name format a20
13:57:30 SQL> select last_name,department_id,salary,rank() over(partition by department_id order by salary ) row_num
13:57:30   2  from employees
13:57:30   3  where department_id =50
13:57:30   4  and salary between 3000 and 6000;
LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM
-------------------- ------------- ---------- ----------
Fleaur                          50       3090          1
Walsh                           50       3090          1
Davies                          50       3090          1
Nayer                           50       3190          4--出现跳号
McCain                          50       3190          4
Taylor                          50       3190          4
Stiles                          50       3190          4
Bissot                          50       3290          8--出现跳号
Mallin                          50       3290          8
Dellinger                       50       3390         10--出现跳号
Rajs                            50       3490         11
Dilly                           50       3590         12
Ladwig                          50       3590         12
Chung                           50       3790         14--出现跳号
Everett                         50       3890         15
Bell                            50       3990         16
Bull                            50       4090         17
Sarchand                        50       4190         18
Mourgos                         50       5790         19
已選取 19 個資料列.
查询部门代码50,工资在3000~6000之间的排名情况
13:56:59 SQL> set pagesize 500
13:57:30 SQL> col last_name format a20
13:57:30 SQL> select last_name,department_id,salary,rank() over(partition by department_id order by salary ) row_num
13:57:30   2  from employees
13:57:30   3  where department_id =50
13:57:30   4  and salary between 3000 and 6000;
LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM
-------------------- ------------- ---------- ----------
Fleaur                          50       3090          1
Walsh                           50       3090          1
Davies                          50       3090          1
Nayer                           50       3190          4--出现跳号
McCain                          50       3190          4
Taylor                          50       3190          4
Stiles                          50       3190          4
Bissot                          50       3290          8--出现跳号
Mallin                          50       3290          8
Dellinger                       50       3390         10--出现跳号
Rajs                            50       3490         11
Dilly                           50       3590         12
Ladwig                          50       3590         12
Chung                           50       3790         14--出现跳号
Everett                         50       3890         15
Bell                            50       3990         16
Bull                            50       4090         17
Sarchand                        50       4190         18
Mourgos                         50       5790         19
已選取 19 個資料列.

 

 

            DENSE_RANK() OVER(PARTITION BY … ORDER BY …)

            DENSE_RANK() OVER(PARTITION BY … ORDER BY …)

                为查询重临的每1行并列排序,同样排名后的排名不会跳号

                为查询再次来到的每1行并列排序,同样排名后的排名不会跳号

 

 

查询部门代码50,工资在3000~6000之间的排名情况
14:01:48 SQL> set pagesize 500
14:02:06 SQL> col last_name format a20
14:02:06 SQL> select last_name,department_id,salary,dense_rank() over(partition by department_id order by salary ) row_num
14:02:06   2  from employees
14:02:06   3  where department_id =50
14:02:06   4  and salary between 3000 and 6000;
LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM
-------------------- ------------- ---------- ----------
Fleaur                          50       3090          1
Walsh                           50       3090          1
Davies                          50       3090          1
Nayer                           50       3190          2
McCain                          50       3190          2
Taylor                          50       3190          2
Stiles                          50       3190          2
Bissot                          50       3290          3
Mallin                          50       3290          3
Dellinger                       50       3390          4
Rajs                            50       3490          5
Dilly                           50       3590          6
Ladwig                          50       3590          6
Chung                           50       3790          7
Everett                         50       3890          8
Bell                            50       3990          9
Bull                            50       4090         10
Sarchand                        50       4190         11
Mourgos                         50       5790         12
已選取 19 個資料列.
查询部门代码50,工资在3000~6000之间的排名情况
14:01:48 SQL> set pagesize 500
14:02:06 SQL> col last_name format a20
14:02:06 SQL> select last_name,department_id,salary,dense_rank() over(partition by department_id order by salary ) row_num
14:02:06   2  from employees
14:02:06   3  where department_id =50
14:02:06   4  and salary between 3000 and 6000;
LAST_NAME            DEPARTMENT_ID     SALARY    ROW_NUM
-------------------- ------------- ---------- ----------
Fleaur                          50       3090          1
Walsh                           50       3090          1
Davies                          50       3090          1
Nayer                           50       3190          2
McCain                          50       3190          2
Taylor                          50       3190          2
Stiles                          50       3190          2
Bissot                          50       3290          3
Mallin                          50       3290          3
Dellinger                       50       3390          4
Rajs                            50       3490          5
Dilly                           50       3590          6
Ladwig                          50       3590          6
Chung                           50       3790          7
Everett                         50       3890          8
Bell                            50       3990          9
Bull                            50       4090         10
Sarchand                        50       4190         11
Mourgos                         50       5790         12
已選取 19 個資料列.

 

 

            COUNT() OVER(PARTITION BY … ORDER BY …)

            COUNT() OVER(PARTITION BY … ORDER BY …)

                重临查询记录或分区的计数值(次数)

                再次回到查询记录或分区的计数值(次数)

 

 

14:11:48 SQL> set pagesize 500
14:12:54 SQL> col last_name format a20
14:12:54 SQL> select last_name,department_id,salary,count(salary) over( partition by department_id ) count_num
14:12:54   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    COUNT_NUM
-------------------- ------------- ---------- ----------
Whalen                          10       4390          1--部门10中出现了1次
Hartstein                       20      12990          2--部门20中出现了2次
Fay                             20       5990          2--部门20中出现了2次
Raphaely                        30      10990          6--部门30中出现了6次
Khoo                            30       3090          6
Baida                           30       2890          6
Tobias                          30       2790          6
Himuro                          30       2590          6
Colmenares                      30       2490          6
Mavris                          40       6490          1
Weiss                           50       7990         45
Fripp                           50       8190         45
Kaufling                        50       7890         45
Vollman                         50       6490         45
14:11:48 SQL> set pagesize 500
14:12:54 SQL> col last_name format a20
14:12:54 SQL> select last_name,department_id,salary,count(salary) over( partition by department_id ) count_num
14:12:54   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    COUNT_NUM
-------------------- ------------- ---------- ----------
Whalen                          10       4390          1--部门10中出现了1次
Hartstein                       20      12990          2--部门20中出现了2次
Fay                             20       5990          2--部门20中出现了2次
Raphaely                        30      10990          6--部门30中出现了6次
Khoo                            30       3090          6
Baida                           30       2890          6
Tobias                          30       2790          6
Himuro                          30       2590          6
Colmenares                      30       2490          6
Mavris                          40       6490          1
Weiss                           50       7990         45
Fripp                           50       8190         45
Kaufling                        50       7890         45
Vollman                         50       6490         45

 

 

            MAX() OVER(PARTITION BY … ORDER BY …)

            MAX() OVER(PARTITION BY … ORDER BY …)

                按分区再次来到最大的值

                按分区再次回到最大的值

 

 

14:12:55 SQL> set pagesize 500
14:15:59 SQL> col last_name format a20
14:15:59 SQL> select last_name,department_id,salary,max(salary) over( partition by department_id ) max_sal
14:15:59   2  from employees
14:15:59   3  ;
LAST_NAME            DEPARTMENT_ID     SALARY    MAX_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390--部门10薪水最高的
Hartstein                       20      12990      12990--部门20薪水最高的
Fay                             20       5990      12990--部门20薪水最高的
Raphaely                        30      10990      10990--部门30薪水最高的
Khoo                            30       3090      10990
Baida                           30       2890      10990
Tobias                          30       2790      10990
Himuro                          30       2590      10990
Colmenares                      30       2490      10990
Mavris                          40       6490       6490--部门40薪水最高的
Weiss                           50       7990       8190--部门50薪水最高的
Fripp                           50       8190       8190
Kaufling                        50       7890       8190
Vollman                         50       6490       8190
Mourgos                         50       5790       8190
14:12:55 SQL> set pagesize 500
14:15:59 SQL> col last_name format a20
14:15:59 SQL> select last_name,department_id,salary,max(salary) over( partition by department_id ) max_sal
14:15:59   2  from employees
14:15:59   3  ;
LAST_NAME            DEPARTMENT_ID     SALARY    MAX_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390--部门10薪水最高的
Hartstein                       20      12990      12990--部门20薪水最高的
Fay                             20       5990      12990--部门20薪水最高的
Raphaely                        30      10990      10990--部门30薪水最高的
Khoo                            30       3090      10990
Baida                           30       2890      10990
Tobias                          30       2790      10990
Himuro                          30       2590      10990
Colmenares                      30       2490      10990
Mavris                          40       6490       6490--部门40薪水最高的
Weiss                           50       7990       8190--部门50薪水最高的
Fripp                           50       8190       8190
Kaufling                        50       7890       8190
Vollman                         50       6490       8190
Mourgos                         50       5790       8190

 

 

            MIN() OVER(PARTITION BY … ORDER BY …)

            MIN() OVER(PARTITION BY … ORDER BY …)

                按分区重回最小的值

                按分区重返最小的值

 

 

14:16:00 SQL> set pagesize 500
14:18:10 SQL> col last_name format a20
14:18:10 SQL> select last_name,department_id,salary,min(salary) over( partition by department_id ) min_sal
14:18:10   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    MIN_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390
Hartstein                       20      12990       5990
Fay                             20       5990       5990
Raphaely                        30      10990       2490
Khoo                            30       3090       2490
Baida                           30       2890       2490
Tobias                          30       2790       2490
Himuro                          30       2590       2490
Colmenares                      30       2490       2490
Mavris                          40       6490       6490
Weiss                           50       7990       2090
Fripp                           50       8190       2090
Kaufling                        50       7890       2090
Vollman                         50       6490       2090
Mourgos                         50       5790       2090
14:16:00 SQL> set pagesize 500
14:18:10 SQL> col last_name format a20
14:18:10 SQL> select last_name,department_id,salary,min(salary) over( partition by department_id ) min_sal
14:18:10   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    MIN_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390
Hartstein                       20      12990       5990
Fay                             20       5990       5990
Raphaely                        30      10990       2490
Khoo                            30       3090       2490
Baida                           30       2890       2490
Tobias                          30       2790       2490
Himuro                          30       2590       2490
Colmenares                      30       2490       2490
Mavris                          40       6490       6490
Weiss                           50       7990       2090
Fripp                           50       8190       2090
Kaufling                        50       7890       2090
Vollman                         50       6490       2090
Mourgos                         50       5790       2090

 

 

            SUM() OVER(PARTITION BY … ORDER BY …)

            SUM() OVER(PARTITION BY … ORDER BY …)

                按分区汇总求和

                按分区汇总求和

 

 

查询所有薪水累积相加
14:32:10 SQL> set pagesize 500
14:32:40 SQL> col last_name format a20
14:32:40 SQL> select last_name,department_id,salary,sum(salary) over( order   by salary ) sum_sal
14:32:40   2  from employees
14:32:40   3  where department_id=30;

LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL
-------------------- ------------- ---------- ----------
Colmenares                      30       2490       2490
Himuro                          30       2590       5080
Tobias                          30       2790       7870
Baida                           30       2890      10760
Khoo                            30       3090      13850
Raphaely                        30      10990      24840
已選取 6 個資料列.

查询按部门分区进行求和
14:19:28 SQL> set pagesize 500
14:20:06 SQL> col last_name format a20
14:20:06 SQL> select last_name,department_id,salary,sum(salary) over( partition by department_id ) sum_sal
14:20:06   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390
Hartstein                       20      12990      18980
Fay                             20       5990      18980
Raphaely                        30      10990      24840
Khoo                            30       3090      24840
Baida                           30       2890      24840
Tobias                          30       2790      24840
Himuro                          30       2590      24840
Colmenares                      30       2490      24840
Mavris                          40       6490       6490
Weiss                           50       7990     155950
Fripp                           50       8190     155950
Kaufling                        50       7890     155950

查询按部门分区前后2笔进行求和 【窗口期】
14:24:33 SQL> set pagesize 500
14:24:59 SQL> col last_name format a20
14:24:59 SQL> select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary rows between 2 preceding and 2 following ) sum_sal
14:24:59   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390 --部门只有1行
Fay                             20       5990      18980 
Hartstein                       20      12990      18980
Colmenares                      30       2490       7870--部门30,前后2行求和,7870=2490+2590+2790
Himuro                          30       2590      10760--部门30,前后2行求和,10760 =2490+2590+2790+2890
Tobias                          30       2790      13850--部门30,前后2行求和,13850 =2490+2590+2790+2890+3090
Baida                           30       2890      22350--部门30,前后2行求和,22350 =2590+2790+2890+3090+10990
Khoo                            30       3090      19760
Raphaely                        30      10990      16970
Mavris                          40       6490       6490
Olson                           50       2090       6470
Philtanker                      50       2190       8860
Markle                          50       2190      11250
Gee                             50       2390      11650
Landry                          50       2390      11950
Patel                           50       2490      12250
Vargas                          50       2490      12350
Marlow                          50       2490      12450
Perkins                         50       2490      12550
Sullivan                        50       2490      12650
OConnell                        50       2590      12750

范围在200以内的【窗口期】
set pagesize 500
col last_name format a20
select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between 200 preceding and 200 following ) sum_sal
from employees;

范围无限制【窗口期】
set pagesize 500
col last_name format a20
select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between unbounded preceding and unbounded following ) sum_sal
from employees;

set pagesize 500
col last_name format a20
select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary rows between unbounded preceding and unbounded following ) sum_sal
from employees;
查询所有薪水累积相加
14:32:10 SQL> set pagesize 500
14:32:40 SQL> col last_name format a20
14:32:40 SQL> select last_name,department_id,salary,sum(salary) over( order   by salary ) sum_sal
14:32:40   2  from employees
14:32:40   3  where department_id=30;

LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL
-------------------- ------------- ---------- ----------
Colmenares                      30       2490       2490
Himuro                          30       2590       5080
Tobias                          30       2790       7870
Baida                           30       2890      10760
Khoo                            30       3090      13850
Raphaely                        30      10990      24840
已選取 6 個資料列.

查询按部门分区进行求和
14:19:28 SQL> set pagesize 500
14:20:06 SQL> col last_name format a20
14:20:06 SQL> select last_name,department_id,salary,sum(salary) over( partition by department_id ) sum_sal
14:20:06   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390
Hartstein                       20      12990      18980
Fay                             20       5990      18980
Raphaely                        30      10990      24840
Khoo                            30       3090      24840
Baida                           30       2890      24840
Tobias                          30       2790      24840
Himuro                          30       2590      24840
Colmenares                      30       2490      24840
Mavris                          40       6490       6490
Weiss                           50       7990     155950
Fripp                           50       8190     155950
Kaufling                        50       7890     155950

查询按部门分区前后2笔进行求和 【窗口期】
14:24:33 SQL> set pagesize 500
14:24:59 SQL> col last_name format a20
14:24:59 SQL> select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary rows between 2 preceding and 2 following ) sum_sal
14:24:59   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    SUM_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390 --部门只有1行
Fay                             20       5990      18980 
Hartstein                       20      12990      18980
Colmenares                      30       2490       7870--部门30,前后2行求和,7870=2490+2590+2790
Himuro                          30       2590      10760--部门30,前后2行求和,10760 =2490+2590+2790+2890
Tobias                          30       2790      13850--部门30,前后2行求和,13850 =2490+2590+2790+2890+3090
Baida                           30       2890      22350--部门30,前后2行求和,22350 =2590+2790+2890+3090+10990
Khoo                            30       3090      19760
Raphaely                        30      10990      16970
Mavris                          40       6490       6490
Olson                           50       2090       6470
Philtanker                      50       2190       8860
Markle                          50       2190      11250
Gee                             50       2390      11650
Landry                          50       2390      11950
Patel                           50       2490      12250
Vargas                          50       2490      12350
Marlow                          50       2490      12450
Perkins                         50       2490      12550
Sullivan                        50       2490      12650
OConnell                        50       2590      12750

范围在200以内的【窗口期】
set pagesize 500
col last_name format a20
select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between 200 preceding and 200 following ) sum_sal
from employees;

范围无限制【窗口期】
set pagesize 500
col last_name format a20
select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary range between unbounded preceding and unbounded following ) sum_sal
from employees;

set pagesize 500
col last_name format a20
select last_name,department_id,salary,sum(salary) over( partition by department_id order by salary rows between unbounded preceding and unbounded following ) sum_sal
from employees;

 

 

            AVG() OVER(PARTITION BY … ORDER BY …)

            AVG() OVER(PARTITION BY … ORDER BY …)

                按分区求出平均值

                按分区求出平均值

            

            

14:39:56 SQL> set pagesize 500
14:39:56 SQL> col last_name format a20
14:39:56 SQL> select last_name,department_id,salary,avg(salary) over( partition by department_id ) avg_sal
14:39:56   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    AVG_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390
Hartstein                       20      12990       9490
Fay                             20       5990       9490
Raphaely                        30      10990       4140
Khoo                            30       3090       4140
Baida                           30       2890       4140
Tobias                          30       2790       4140
Himuro                          30       2590       4140
Colmenares                      30       2490       4140
Mavris                          40       6490       6490
Weiss                           50       7990 3465.55556
Fripp                           50       8190 3465.55556
Kaufling                        50       7890 3465.55556
Vollman                         50       6490 3465.55556
Mourgos                         50       5790 3465.55556
14:39:56 SQL> set pagesize 500
14:39:56 SQL> col last_name format a20
14:39:56 SQL> select last_name,department_id,salary,avg(salary) over( partition by department_id ) avg_sal
14:39:56   2  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY    AVG_SAL
-------------------- ------------- ---------- ----------
Whalen                          10       4390       4390
Hartstein                       20      12990       9490
Fay                             20       5990       9490
Raphaely                        30      10990       4140
Khoo                            30       3090       4140
Baida                           30       2890       4140
Tobias                          30       2790       4140
Himuro                          30       2590       4140
Colmenares                      30       2490       4140
Mavris                          40       6490       6490
Weiss                           50       7990 3465.55556
Fripp                           50       8190 3465.55556
Kaufling                        50       7890 3465.55556
Vollman                         50       6490 3465.55556
Mourgos                         50       5790 3465.55556

             FIRST_VALUE() OVE逍客(PARTITION BY … O揽胜极光DE卡宴 BY
….) 重返一组有序的值中的第3个值。
若是群集中的第三个值为空,则函数将回到null,除非钦点IGNORE NULLS

             FIRST_VALUE() OVEOdyssey(PARTITION BY … O揽胜DE福特Explorer BY
….) 重回一组有序的值中的第三个值。
假使集结中的第三个值为空,则函数将赶回null,除非钦点IGNORE NULLS

            LAST_VALUE() OVEKoleos(PARTITION BY … O途观DERAV四 BY
….)  重回1组有序的值中的最终二个值。
假使集结中的最终贰个值为空,则函数将回来null,除非钦定IGNORE NULLS。

            LAST_VALUE() OVECR-V(PARTITION BY … O途乐DELAND BY
….)  重返1组有序的值中的最终一个值。
假设集结中的最后1个值为空,则函数将回到null,除非钦点IGNORE NULLS。

 

 

14:49:16 SQL> set pagesize 500
14:50:48 SQL> col last_name format a20
14:50:48 SQL> select last_name,department_id,salary,first_value(salary) over( partition by department_id) first_sal,
14:50:48   2  last_value(salary) over( partition by department_id ) last_sal
14:50:48   3  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY  FIRST_SAL   LAST_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10       4390       4390       4390
Hartstein                       20      12990      12990       5990
Fay                             20       5990      12990       5990
Raphaely                        30      10990      10990       2490
Khoo                            30       3090      10990       2490
Baida                           30       2890      10990       2490
Tobias                          30       2790      10990       2490
Himuro                          30       2590      10990       2490
Colmenares                      30       2490      10990       2490
Mavris                          40       6490       6490       6490
Weiss                           50       7990       7990       2590
Fripp                           50       8190       7990       2590
Kaufling                        50       7890       7990       2590
14:49:16 SQL> set pagesize 500
14:50:48 SQL> col last_name format a20
14:50:48 SQL> select last_name,department_id,salary,first_value(salary) over( partition by department_id) first_sal,
14:50:48   2  last_value(salary) over( partition by department_id ) last_sal
14:50:48   3  from employees;
LAST_NAME            DEPARTMENT_ID     SALARY  FIRST_SAL   LAST_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10       4390       4390       4390
Hartstein                       20      12990      12990       5990
Fay                             20       5990      12990       5990
Raphaely                        30      10990      10990       2490
Khoo                            30       3090      10990       2490
Baida                           30       2890      10990       2490
Tobias                          30       2790      10990       2490
Himuro                          30       2590      10990       2490
Colmenares                      30       2490      10990       2490
Mavris                          40       6490       6490       6490
Weiss                           50       7990       7990       2590
Fripp                           50       8190       7990       2590
Kaufling                        50       7890       7990       2590

 

 

            LAG(column,n,default) OVE纳瓦拉(PARTITION BY … O奥迪Q3DE奥迪Q5 BY …) 
 上偏移地方的再次回到值,n表示偏移量(正整数),default钦点暗许值

            LAG(column,n,default) OVEPRADO(PARTITION BY … O大切诺基DEPAJERO BY …) 
 上偏移地点的重回值,n表示偏移量(正整数),default钦赐私下认可值

            LEAD(column,n,default) OVEMurano(PARTITION BY … OTiggoDESportage BY …) 
下偏移地方的重回值,n表示偏移量(正整数) ,default内定私下认可值

            LEAD(column,n,default) OVE汉兰达(PARTITION BY … OXC60DE逍客 BY …) 
下偏移地方的重返值,n表示偏移量(正整数) ,default钦命暗中同意值

            

            

14:58:21 SQL> set pagesize 500
14:58:33 SQL> col last_name format a20
14:58:33 SQL> select last_name,department_id,lag(salary,1,0) over( partition by department_id order by salary) lag_sal,
14:58:33   2  salary,
14:58:33   3  lead(salary,1,NULL) over( partition by department_id order by salary) lead_sal
14:58:33   4  from employees;
LAST_NAME            DEPARTMENT_ID    LAG_SAL     SALARY   LEAD_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10          0       4390
Fay                             20          0       5990      12990
Hartstein                       20       5990      12990
Colmenares                      30          0       2490       2590
Himuro                          30       2490       2590       2790
Tobias                          30       2590       2790       2890
Baida                           30       2790       2890       3090
Khoo                            30       2890       3090      10990
Raphaely                        30       3090      10990
Mavris                          40          0       6490
Olson                           50          0       2090       2190
Philtanker                      50       2090       2190       2190
Markle                          50       2190       2190       2390
Gee                             50       2190       2390       2390
14:58:21 SQL> set pagesize 500
14:58:33 SQL> col last_name format a20
14:58:33 SQL> select last_name,department_id,lag(salary,1,0) over( partition by department_id order by salary) lag_sal,
14:58:33   2  salary,
14:58:33   3  lead(salary,1,NULL) over( partition by department_id order by salary) lead_sal
14:58:33   4  from employees;
LAST_NAME            DEPARTMENT_ID    LAG_SAL     SALARY   LEAD_SAL
-------------------- ------------- ---------- ---------- ----------
Whalen                          10          0       4390
Fay                             20          0       5990      12990
Hartstein                       20       5990      12990
Colmenares                      30          0       2490       2590
Himuro                          30       2490       2590       2790
Tobias                          30       2590       2790       2890
Baida                           30       2790       2890       3090
Khoo                            30       2890       3090      10990
Raphaely                        30       3090      10990
Mavris                          40          0       6490
Olson                           50          0       2090       2190
Philtanker                      50       2090       2190       2190
Markle                          50       2190       2190       2390
Gee                             50       2190       2390       2390

997755.com澳门葡京 8

997755.com澳门葡京 9

学学总括:

读书总括:

        1.叩问分析函数和聚焦(组)函数之间的差别

        壹.打听分析函数和聚合(组)函数之间的异样

        二.形似分析函数大致分成4类

        二.相似分析函数大约分成四类

            伪列类:row_number() over()、rank() over()和dense_rank()
over()

            伪列类:row_number() over()、rank() over()和dense_rank()
over()

            计算类:sum() over()、count() over()、max() over()、min()
over()和avg() over()

            计算类:sum() over()、count() over()、max() over()、min()
over()和avg() over()

            再次回到值类:first_value() over()和last_value()  over()

            重临值类:first_value() over()和last_value()  over()

            偏移类:lag() over()和lead() over

            偏移类:lag() over()和lead() over

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website