精华内容
下载资源
问答
  • Sql中的窗口函数

    2020-10-14 19:29:24
    基本格式: [window_name] [partition_clause] [order_clause][frame_clause] 各部分解读 window_name:预先定义的窗口函数 partition_clause:书写格式为partition by properties_name,即将全部数据划分按照字段名...

    案例

    原始数据

    在这里插入图片描述

    入门程序

    写法一:
    select 
    province,city,gdp,people, rank() over (partition by province order by gdp) as ranking 
    from windowfunc;
    
    写法二:
    select 
    province,city,gdp,people,rank() over  w1 as ranking 
    from windowfunc 
    window w1 as (partition by province order by gdp);
    
    大致解读:
    根据province字段进行分区,也就是将province字段相同的值划分到同一个窗口之中,在每一个窗口之中根据gdp字段进行排行,得到每个省份各自的城市的gdp排行。
    

    在这里插入图片描述

    入门总结

    窗口函数有两种:

    1. 直接在over关键字之后添加窗口函数的定义;
    2. 预先定义一个窗口函数,然后在over关键字之后直接使用定义的窗口函数即可。

    基本格式:

    [window_name] [partition_clause] [order_clause][frame_clause]
    

    各部分解读

    window_name:预先定义的窗口函数
    partition_clause:书写格式为partition by properties_name,即将全部数据划分按照字段名进行划分窗口,多个字段名之间用逗号进行分隔。如果没有指定字段,则所有数据属于一个窗口。
    order_clause:书写格式为order by properties_name.即在每一个窗口之内根据指定的字段名进行排序,多个字段之间用逗号进行分隔。同时可以desc和asc关键字来指定排序的顺序。
    frame_clause:用来指定窗口中的操作范围。
    如以上四部分都不存在,则窗口为所有查询行

    非聚合窗口函数

    名称 描述
    CUME_DIST() 累计分布值
    DENSE_RANK() 当前行在其窗口内的排名
    FIRST_VALUE() 窗口中的第一个值
    LAG() 窗口中当前行滞后的参数值
    LAST VALUE() 窗口中的最后一个值
    LEAD() 窗口内当前行的行的参数值
    NTH VALUE 窗口内的第N个值
    NTILE 窗口中当前行的存储桶号
    PRRCENT RANK() 百分比排名值
    RANK() 当前行在分区内的排名
    ROW NUMBER() 窗口中的数据行数

    演示:

    数据

    在这里插入图片描述

    执行语句

    select 
    sex,height,
    cume_dist() over win as 'cume_dist',
    first_value(height) over win as 'first',
    last_value(height) over win as 'last',
    nth_value(height,2) over win as 'second',
    rank() over win as 'rank',
    dense_rank() over win as 'dense_rank',
    percent_rank() over win as 'percent_rank',
    row_number() over win as 'row_number',
    ntile(4) over win as 'ntile',
    lag(height) over win as 'lag',
    lead(height) over win as 'lead'
     from man  
     window win as (partition by sex order by height);
    

    结果

    在这里插入图片描述

    CUME_DIST()

    返回窗口内小于等于当前值的比例。即小于等于当前值的数据个数/窗口内数据量。

    first_value(properties_name)

    返回窗口内指定字段名的第一个值

    last_value(properties_name)

    返回窗口内指定字段名的最后一个值

    nth_value(properties_name,N)

    返回窗口内指定字段名的第N个值

    rank()

    对窗口内的数据进行排序,返回其排名,相同的排名一致,同时占用后面的排名

    dense_rank()

    对窗口内的数据进行排序,返回其排名,相同的排名一致,但是不占用后面的排名

    percent_rank()

    在dense_rank()的前提,将排名转化为百分比。即当前值的排名/最后一名的排名

    row_number()

    返回窗口内当前行的编号,编号从1开始

    ntile(N)

    将窗口内的数据分成N个桶,同时返回每个数据所在的桶编号。桶可以更小的分区

    lag(properties_name,N,default_value)

    窗口内,当前行之前的第N个值,若不存在,则使用default_value。在不指定的情况下,N = 1,default_value=null。N必须大于等于0

    lead(properties_name,N,default_value)

    窗口内,当前行之后的第N个值,若不存在,则使用default_value。在不指定的情况下,N = 1,default_value=null。N必须大于等于0

    frame_clause

    用来在窗口之中划分一块更小的子集,然后进行运算。

    格式

    frame_units frame_extent
    
    frame_units:rows | range
    frame_extent:{frame_start | frame_between}
    	frame_between:between frame_start and frame_end
    	frame_start/frame_end:{
    		current row
    		unbounded preceding
    		unbounded following
    		express preceding
    		express following
    		}
    		
    在frame_between中,frame_start必须早于frame_end
    

    案例

    select sex, height, 
    min(height) over (partition by sex order by height) as 'min',
    min(height) over (partition by sex order by height rows between 1 preceding and 1 following) as 'min_row_1_preceing_1_following',
    max(height) over (partition by sex order by height range unbounded preceding) as 'range_max_unbounder_preceding' from man
    ;
    
    

    在这里插入图片描述

    默认情况

    在没有frame子句的情况下,默认框架取决于是否存在order by子句

    1. 使用order by:默认frame_clause是从窗口的开始到当前的所有行
    2. 不使用order by:默认frame_clause是包含窗口的所有数据

    案例

    select sex,height,
    max(height) over (partition by sex order by height) as 'max_with_order',
    max(height) over (partition by sex) as 'max_without_order' 
    from man;
    

    在这里插入图片描述

    展开全文
  • SQL中的窗口函数

    2010-06-28 20:09:00
    实际上,DB2称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,但ISO SQL标准把它们称为窗口函数窗口函数一般在OLAP分析、制作报表过程会使用到。 窗口函数: 聚合函数 over() 聚合函数 over...

    与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。

    窗口函数:

    聚合函数 over()

    聚合函数 over(partition by 字段)—分区

    聚合函数 over(order by 字段)--框架字句

     

    本文以Oracle11g中HR模式下的Employees表为例子来试着了解窗口函数,

    Employees表结构如下:

    SQL> desc employees
     名称                                      是否为空? 类型
     ----------------------------------------- -------- ----------------------------
     EMPLOYEE_ID                               NOT NULL NUMBER(6)
     FIRST_NAME                                         VARCHAR2(20)
     LAST_NAME                                 NOT NULL VARCHAR2(25)
     EMAIL                                     NOT NULL VARCHAR2(25)
     PHONE_NUMBER                                       VARCHAR2(20)
     HIRE_DATE                                 NOT NULL DATE
     JOB_ID                                    NOT NULL VARCHAR2(10)
     SALARY                                             NUMBER(8,2)
     COMMISSION_PCT                                     NUMBER(2,2)
     MANAGER_ID                                         NUMBER(6)
     DEPARTMENT_ID                                      NUMBER(4)

     

    计算部门号位20的员工总数:

    SQL> edit
      1  select first_name,department_id,count(*) over()
      2  from employees
      3* where department_id=20
    SQL> /
    
    FIRST_NAME           DEPARTMENT_ID COUNT(*)OVER()                               
    -------------------- ------------- --------------                               
    Michael                         20              2                               
    Pat                             20              2         

     

    窗口 ,函数 count(*) over() 对于查询返回的每一行,它返回了表中所有行的计数。

    在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。

     

    1. 分区

    使用Partiton by字句定义行的分区或组,可以用paritition by对定义的行组计算聚集(当遇到新组的时候复位),并返回每个值(每个组中的每个成员),而不是一个用一个组表示表中的这个值的所有实例。如:

    SQL> edit
      1  select first_name,department_id,count(*) over(partition by department_id) as cnt
      2  from employees
      3* order by 2
    SQL> /
    
    FIRST_NAME           DEPARTMENT_ID        CNT                                   
    -------------------- ------------- ----------                                   
    Jennifer                        10          1                                   
    Michael                         20          2                                   
    Pat                             20          2                                   
    Den                             30          6                                   
    Alexander                       30          6                                   
    Shelli                          30          6                                   
    Sigal                           30          6                                   
    Guy                             30          6                                   
    Karen                           30          6                                   
    Susan                           40          1                                   
    Matthew                         50         45    
    。。。。。。。。。。
    如上结果所示:对于同一个部门(同一个分区)的每个员工的cnt值相同,这是由于在遇到新部门之前不会重置聚集。      

     

    另外partition by字句的优点是:在同一个select语句中,一个窗口函数的计算独立于按其他列分区的其他窗口函数的计算。例如下面的查询,返回每个员工、他的部门、他的部门中的员工数、他的职位以及跟他相同职位的员工数:

     

     

     1  select first_name,department_id,count(*) over (partition by department_id) as dept_cnt,
      2  job_id,
      3  count(*) over(partition by job_id) as job_cnt
      4  from employees
      5* order by 2
    SQL> /
    
    FIRST_NAME           DEPARTMENT_ID   DEPT_CNT JOB_ID        JOB_CNT             
    -------------------- ------------- ---------- ---------- ----------             
    Jennifer                        10          1 AD_ASST             1             
    Michael                         20          2 MK_MAN              1             
    Pat                             20          2 MK_REP              1             
    Sigal                           30          6 PU_CLERK            5             
    Alexander                       30          6 PU_CLERK            5             
    Shelli                          30          6 PU_CLERK            5             
    Karen                           30          6 PU_CLERK            5             
    Den                             30          6 PU_MAN              1             
    Guy                             30          6 PU_CLERK            5             
    Susan                           40          1 HR_REP              1             
    Donald                          50         45 SH_CLERK           20     

     

     

    1. 框架字句:

    当在窗口函数over字句中使用order by 字句时,就指定了两件事:

    1、分区中的行如何排序

    2、在计算中包含哪些行

    请看下面的查询,它计算了30号员工的工资的累计和

     1  select department_id,first_name,hire_date,salary,
      2  sum(salary) over(partition by department_id) as total1,
      3  sum(salary) over() as total2,
      4  sum(salary) over(order by hire_date) as running_total
      5  from employees
      6* where department_id=30
    SQL> /
    
    DEPARTMENT_ID FIRST_NAME           HIRE_DATE          SALARY     TOTAL1         
    ------------- -------------------- -------------- ---------- ----------         
        TOTAL2 RUNNING_TOTAL                                                        
    ---------- -------------                                                        
               30 Den                  07-12月-02          11000      24900         
         24900         11000                                                        
                                                                                    
               30 Alexander            18-5月 -03           3100      24900         
         24900         14100                                                        
                                                                                    
               30 Sigal                24-7月 -05           2800      24900         
         24900         16900                                                        
                                                                                    
    
    DEPARTMENT_ID FIRST_NAME           HIRE_DATE          SALARY     TOTAL1         
    ------------- -------------------- -------------- ---------- ----------         
        TOTAL2 RUNNING_TOTAL                                                        
    ---------- -------------                                                        
               30 Shelli               24-12月-05           2900      24900         
         24900         19800                                                        
                                                                                    
               30 Guy                  15-11月-06           2600      24900         
         24900         22400                                                        
                                                                                    
               30 Karen                10-8月 -07           2500      24900         
         24900         24900                                                        
                                                                                    
    
    已选择6行。

    上面的查询语句相当于:


     

      1  select department_id,first_name,hire_date,salary,
      2  sum(salary) over(partition by department_id) as total1,
      3  sum(salary) over() as total2,
      4  sum(salary) over(order by hire_date range between unbounded preceding and current row) as running_total
      5  from employees
      6* where department_id=30

     

     

    也就说默认情况下会告诉查询:计算所有行的和,即从当前行开始、包括它前面的所有行。对从当前行开始、包括它前面的所有行进行求和,就可以得到累计和效果了。

     

    通过,框架字句允许定义数据的不同“子窗口”,以便在计算中使用,有很多方式可以指定这样的子窗口。如:

     1  select department_id,first_name,salary,
      2  sum(salary) over (order by hire_date range between unbounded preceding and current row) as run_total1,
      3  sum(salary) over(order by hire_date rows between 1 preceding and current row) as run_total2,
      4  sum(salary) over(order by hire_date range between current row and unbounded following) as run_total3,
      5  sum(salary) over(order by hire_date rows between current row and 1 following) as run_total4
      6  from employees
      7* where department_id=30
    SQL> /
    
    DEPARTMENT_ID FIRST_NAME               SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3  
    ------------- -------------------- ---------- ---------- ---------- ----------  
    RUN_TOTAL4                                                                      
    ----------                                                                      
               30 Den                       11000      11000      11000      24900  
         14100                                                                      
                                                                                    
               30 Alexander                  3100      14100      14100      13900  
          5900                                                                      
                                                                                    
               30 Sigal                      2800      16900       5900      10800  
          5700                                                                      
                                                                                    
    
    DEPARTMENT_ID FIRST_NAME               SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3  
    ------------- -------------------- ---------- ---------- ---------- ----------  
    RUN_TOTAL4                                                                      
    ----------                                                                      
               30 Shelli                     2900      19800       5700       8000  
          5500                                                                      
                                                                                    
               30 Guy                        2600      22400       5500       5100  
          5100                                                                      
                                                                                    
               30 Karen                      2500      24900       5100       2500  
          2500                                                                      
                                                                                    
    
    已选择6行。

     

    其中:

    range between unbounded preceding and current row 指定计算当前行开始、当前行之前的所有值;

    rows between 1 preceding and current row 指定计算当前行的前一行开始,其范围一直延续到当前行;

    range between current row and unbounded following 指定计算从当前行开始,包括它后面的所有行;

    rows between current row and 1 following 指定计算当前行和它后面的一行;

     

    最后一个例子,展示 了框架字句对查询输出的影响,请看下面查询:

     1  select first_name,salary,min(salary) over(order by salary) min1,
      2  max(salary) over(order by salary) max1,
      3  min(salary) over(order by salary range between unbounded preceding and unbounded following) min2,
      4  max(salary) over(order by salary range between unbounded preceding and unbounded following) max2,
      5  min(salary) over(order by salary range between current row and current row) min3,
      6  max(salary) over(order by salary range between current row and current row) max3,
      7  max(salary) over(order by salary rows between 3 preceding and 3 following) max4
      8* from employees
    SQL> /
    
    FIRST_NAME               SALARY       MIN1       MAX1       MIN2       MAX2     
    -------------------- ---------- ---------- ---------- ---------- ----------     
          MIN3       MAX3       MAX4                                                
    ---------- ---------- ----------                                                
    TJ                         2100       2100       2100       2100      24000     
          2100       2100       2400                                                
                                                                                    
    Steven                     2200       2100       2200       2100      24000     
          2200       2200       2400                                                
                                                                                    
    Hazel                      2200       2100       2200       2100      24000     
          2200       2200       2500      
     
    请仔细观察计算结果,领会子窗口的内涵;
     
    参见:SQL CookBook                                          
                                                                                    

    转载于:https://www.cnblogs.com/sirc/archive/2010/06/28/1766981.html

    展开全文
  • SQL中的窗口函数 OVER窗口函数

    万次阅读 2013-11-17 00:51:31
    SQL中的窗口函数 SQL/OR   与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP...

    SQL中的窗口函数

    SQL/OR

     

    与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。

    窗口函数:

    聚合函数 over()

    聚合函数 over(partition by 字段)—分区

    聚合函数 over(order by 字段)--框架字句

     

    本文以Oracle11g中HR模式下的Employees表为例子来试着了解窗口函数,

    Employees表结构如下:

    SQL> desc employees
     名称                                      是否为空? 类型
     ----------------------------------------- -------- ----------------------------
     EMPLOYEE_ID                               NOT NULL NUMBER(6)
     FIRST_NAME                                         VARCHAR2(20)
     LAST_NAME                                 NOT NULL VARCHAR2(25)
     EMAIL                                     NOT NULL VARCHAR2(25)
     PHONE_NUMBER                                       VARCHAR2(20)
     HIRE_DATE                                 NOT NULL DATE
     JOB_ID                                    NOT NULL VARCHAR2(10)
     SALARY                                             NUMBER(8,2)
     COMMISSION_PCT                                     NUMBER(2,2)
     MANAGER_ID                                         NUMBER(6)
     DEPARTMENT_ID                                      NUMBER(4)

     

    计算部门号位20的员工总数:

    SQL> edit
      1  select first_name,department_id,count(*) over()
      2  from employees
      3* where department_id=20
    SQL> /
    
    FIRST_NAME           DEPARTMENT_ID COUNT(*)OVER()                               
    -------------------- ------------- --------------                               
    Michael                         20              2                               
    Pat                             20              2         

     

    窗口 ,函数 count(*) over() 对于查询返回的每一行,它返回了表中所有行的计数。

    在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。

     

    1. 分区

    使用Partiton by字句定义行的分区或组,可以用paritition by对定义的行组计算聚集(当遇到新组的时候复位),并返回每个值(每个组中的每个成员),而不是一个用一个组表示表中的这个值的所有实例。如:

    SQL> edit
      1  select first_name,department_id,count(*) over(partition by department_id) as cnt
      2  from employees
      3* order by 2
    SQL> /
    
    FIRST_NAME           DEPARTMENT_ID        CNT                                   
    -------------------- ------------- ----------                                   
    Jennifer                        10          1                                   
    Michael                         20          2                                   
    Pat                             20          2                                   
    Den                             30          6                                   
    Alexander                       30          6                                   
    Shelli                          30          6                                   
    Sigal                           30          6                                   
    Guy                             30          6                                   
    Karen                           30          6                                   
    Susan                           40          1                                   
    Matthew                         50         45    
    。。。。。。。。。。
    如上结果所示:对于同一个部门(同一个分区)的每个员工的cnt值相同,这是由于在遇到新部门之前不会重置聚集。      

     

    另外partition by字句的优点是:在同一个select语句中,一个窗口函数的计算独立于按其他列分区的其他窗口函数的计算。例如下面的查询,返回每个员工、他的部门、他的部门中的员工数、他的职位以及跟他相同职位的员工数:

     

     

     1  select first_name,department_id,count(*) over (partition by department_id) as dept_cnt,
      2  job_id,
      3  count(*) over(partition by job_id) as job_cnt
      4  from employees
      5* order by 2
    SQL> /
    
    FIRST_NAME           DEPARTMENT_ID   DEPT_CNT JOB_ID        JOB_CNT             
    -------------------- ------------- ---------- ---------- ----------             
    Jennifer                        10          1 AD_ASST             1             
    Michael                         20          2 MK_MAN              1             
    Pat                             20          2 MK_REP              1             
    Sigal                           30          6 PU_CLERK            5             
    Alexander                       30          6 PU_CLERK            5             
    Shelli                          30          6 PU_CLERK            5             
    Karen                           30          6 PU_CLERK            5             
    Den                             30          6 PU_MAN              1             
    Guy                             30          6 PU_CLERK            5             
    Susan                           40          1 HR_REP              1             
    Donald                          50         45 SH_CLERK           20     

     

     

    1. 框架字句:

    当在窗口函数over字句中使用order by 字句时,就指定了两件事:

    1、分区中的行如何排序

    2、在计算中包含哪些行

    请看下面的查询,它计算了30号员工的工资的累计和

     1  select department_id,first_name,hire_date,salary,
      2  sum(salary) over(partition by department_id) as total1,
      3  sum(salary) over() as total2,
      4  sum(salary) over(order by hire_date) as running_total
      5  from employees
      6* where department_id=30
    SQL> /
    
    DEPARTMENT_ID FIRST_NAME           HIRE_DATE          SALARY     TOTAL1         
    ------------- -------------------- -------------- ---------- ----------         
        TOTAL2 RUNNING_TOTAL                                                        
    ---------- -------------                                                        
               30 Den                  07-12月-02          11000      24900         
         24900         11000                                                        
                                                                                    
               30 Alexander            18-5月 -03           3100      24900         
         24900         14100                                                        
                                                                                    
               30 Sigal                24-7月 -05           2800      24900         
         24900         16900                                                        
                                                                                    
    
    DEPARTMENT_ID FIRST_NAME           HIRE_DATE          SALARY     TOTAL1         
    ------------- -------------------- -------------- ---------- ----------         
        TOTAL2 RUNNING_TOTAL                                                        
    ---------- -------------                                                        
               30 Shelli               24-12月-05           2900      24900         
         24900         19800                                                        
                                                                                    
               30 Guy                  15-11月-06           2600      24900         
         24900         22400                                                        
                                                                                    
               30 Karen                10-8月 -07           2500      24900         
         24900         24900                                                        
                                                                                    
    
    已选择6行。

    上面的查询语句相当于:


     

      1  select department_id,first_name,hire_date,salary,
      2  sum(salary) over(partition by department_id) as total1,
      3  sum(salary) over() as total2,
      4  sum(salary) over(order by hire_date range between unbounded preceding and current row) as running_total
      5  from employees
      6* where department_id=30

     

     

    也就说默认情况下会告诉查询:计算所有行的和,即从当前行开始、包括它前面的所有行。对从当前行开始、包括它前面的所有行进行求和,就可以得到累计和效果了。

     

    通过,框架字句允许定义数据的不同“子窗口”,以便在计算中使用,有很多方式可以指定这样的子窗口。如:

     1  select department_id,first_name,salary,
      2  sum(salary) over (order by hire_date range between unbounded preceding and current row) as run_total1,
      3  sum(salary) over(order by hire_date rows between 1 preceding and current row) as run_total2,
      4  sum(salary) over(order by hire_date range between current row and unbounded following) as run_total3,
      5  sum(salary) over(order by hire_date rows between current row and 1 following) as run_total4
      6  from employees
      7* where department_id=30
    SQL> /
    
    DEPARTMENT_ID FIRST_NAME               SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3  
    ------------- -------------------- ---------- ---------- ---------- ----------  
    RUN_TOTAL4                                                                      
    ----------                                                                      
               30 Den                       11000      11000      11000      24900  
         14100                                                                      
                                                                                    
               30 Alexander                  3100      14100      14100      13900  
          5900                                                                      
                                                                                    
               30 Sigal                      2800      16900       5900      10800  
          5700                                                                      
                                                                                    
    
    DEPARTMENT_ID FIRST_NAME               SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3  
    ------------- -------------------- ---------- ---------- ---------- ----------  
    RUN_TOTAL4                                                                      
    ----------                                                                      
               30 Shelli                     2900      19800       5700       8000  
          5500                                                                      
                                                                                    
               30 Guy                        2600      22400       5500       5100  
          5100                                                                      
                                                                                    
               30 Karen                      2500      24900       5100       2500  
          2500                                                                      
                                                                                    
    
    已选择6行。

     

    其中:

    range between unbounded preceding and current row 指定计算当前行开始、当前行之前的所有值;

    rows between 1 preceding and current row 指定计算当前行的前一行开始,其范围一直延续到当前行;

    range between current row and unbounded following 指定计算从当前行开始,包括它后面的所有行;

    rows between current row and 1 following 指定计算当前行和它后面的一行;

     

    最后一个例子,展示 了框架字句对查询输出的影响,请看下面查询:

     1  select first_name,salary,min(salary) over(order by salary) min1,
      2  max(salary) over(order by salary) max1,
      3  min(salary) over(order by salary range between unbounded preceding and unbounded following) min2,
      4  max(salary) over(order by salary range between unbounded preceding and unbounded following) max2,
      5  min(salary) over(order by salary range between current row and current row) min3,
      6  max(salary) over(order by salary range between current row and current row) max3,
      7  max(salary) over(order by salary rows between 3 preceding and 3 following) max4
      8* from employees
    SQL> /
    
    FIRST_NAME               SALARY       MIN1       MAX1       MIN2       MAX2     
    -------------------- ---------- ---------- ---------- ---------- ----------     
          MIN3       MAX3       MAX4                                                
    ---------- ---------- ----------                                                
    TJ                         2100       2100       2100       2100      24000     
          2100       2100       2400                                                
                                                                                    
    Steven                     2200       2100       2200       2100      24000     
          2200       2200       2400                                                
                                                                                    
    Hazel                      2200       2100       2200       2100      24000     
          2200       2200       2500      
     
    请仔细观察计算结果,领会子窗口的内涵;
     
    参见:SQL CookBook                           
     
     
     
     
     
     
     
     
    SYBASE ASA :
     
     
     
     
     
     
     
     

    窗口定义:内置使用 OVER 子句和 WINDOW 子句

    定义窗口的方法有三种:

    • 内置(在窗口函数的 OVER 子句中)

    • 在 WINDOW 子句中

    • 部分内置,部分在 WINDOW 子句中

    然而,有些方法存在限制,如以下各节中所述。

     内置定义(在窗口函数的 OVER 子句中)
     WINDOW 子句定义
     内置与 WINDOW 子句定义相结合
    展开全文
  • 实际上,DB2称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,但ISO SQL标准把它们称为窗口函数窗口函数一般在OLAP分析、制作报表过程会使用到。 窗口函数: 聚合函数 over() 聚合函数 over...

    SQL/OR

     

    与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,但ISO SQL标准把它们称为窗口函数。窗口函数一般在OLAP分析、制作报表过程中会使用到。

    窗口函数:

    聚合函数 over()

    聚合函数 over(partition by 字段)—分区

    聚合函数 over(order by 字段)--框架字句

     

    本文以Oracle11g中HR模式下的Employees表为例子来试着了解窗口函数,

    Employees表结构如下:

    SQL> desc employees
     名称                                      是否为空? 类型
     ----------------------------------------- -------- ----------------------------
     EMPLOYEE_ID                               NOT NULL NUMBER(6)
     FIRST_NAME                                         VARCHAR2(20)
     LAST_NAME                                 NOT NULL VARCHAR2(25)
     EMAIL                                     NOT NULL VARCHAR2(25)
     PHONE_NUMBER                                       VARCHAR2(20)
     HIRE_DATE                                 NOT NULL DATE
     JOB_ID                                    NOT NULL VARCHAR2(10)
     SALARY                                             NUMBER(8,2)
     COMMISSION_PCT                                     NUMBER(2,2)
     MANAGER_ID                                         NUMBER(6)
     DEPARTMENT_ID                                      NUMBER(4)

     

    计算部门号位20的员工总数:

    SQL> edit
      1  select first_name,department_id,count(*) over()
      2  from employees
      3* where department_id=20
    SQL> /

    FIRST_NAME           DEPARTMENT_ID COUNT(*)OVER()                               
    -------------------- ------------- --------------                               
    Michael                         20              2                               
    Pat                             20              2         

     

    窗口 ,函数 count(*) over() 对于查询返回的每一行,它返回了表中所有行的计数。

    在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。

     

        分区

    使用Partiton by字句定义行的分区或组,可以用paritition by对定义的行组计算聚集(当遇到新组的时候复位),并返回每个值(每个组中的每个成员),而不是一个用一个组表示表中的这个值的所有实例。如:

    SQL> edit
      1  select first_name,department_id,count(*) over(partition by department_id) as cnt
      2  from employees
      3* order by 2
    SQL> /

    FIRST_NAME           DEPARTMENT_ID        CNT                                   
    -------------------- ------------- ----------                                   
    Jennifer                        10          1                                   
    Michael                         20          2                                   
    Pat                             20          2                                   
    Den                             30          6                                   
    Alexander                       30          6                                   
    Shelli                          30          6                                   
    Sigal                           30          6                                   
    Guy                             30          6                                   
    Karen                           30          6                                   
    Susan                           40          1                                   
    Matthew                         50         45    

    。。。。。。。。。。

    如上结果所示:对于同一个部门(同一个分区)的每个员工的cnt值相同,这是由于在遇到新部门之前不会重置聚集。      

     

    另外partition by字句的优点是:在同一个select语句中,一个窗口函数的计算独立于按其他列分区的其他窗口函数的计算。例如下面的查询,返回每个员工、他的部门、他的部门中的员工数、他的职位以及跟他相同职位的员工数:

     

     

     1  select first_name,department_id,count(*) over (partition by department_id) as dept_cnt,
      2  job_id,
      3  count(*) over(partition by job_id) as job_cnt
      4  from employees
      5* order by 2
    SQL> /

    FIRST_NAME           DEPARTMENT_ID   DEPT_CNT JOB_ID        JOB_CNT             
    -------------------- ------------- ---------- ---------- ----------             
    Jennifer                        10          1 AD_ASST             1             
    Michael                         20          2 MK_MAN              1             
    Pat                             20          2 MK_REP              1             
    Sigal                           30          6 PU_CLERK            5             
    Alexander                       30          6 PU_CLERK            5             
    Shelli                          30          6 PU_CLERK            5             
    Karen                           30          6 PU_CLERK            5             
    Den                             30          6 PU_MAN              1             
    Guy                             30          6 PU_CLERK            5             
    Susan                           40          1 HR_REP              1             
    Donald                          50         45 SH_CLERK           20     

     

     

        框架字句:

    当在窗口函数over字句中使用order by 字句时,就指定了两件事:

    1、分区中的行如何排序

    2、在计算中包含哪些行

    请看下面的查询,它计算了30号员工的工资的累计和

     1  select department_id,first_name,hire_date,salary,
      2  sum(salary) over(partition by department_id) as total1,
      3  sum(salary) over() as total2,
      4  sum(salary) over(order by hire_date) as running_total
      5  from employees
      6* where department_id=30
    SQL> /

    DEPARTMENT_ID FIRST_NAME           HIRE_DATE          SALARY     TOTAL1         
    ------------- -------------------- -------------- ---------- ----------         
        TOTAL2 RUNNING_TOTAL                                                        
    ---------- -------------                                                        
               30 Den                  07-12月-02          11000      24900         
         24900         11000                                                        
                                                                                    
               30 Alexander            18-5月 -03           3100      24900         
         24900         14100                                                        
                                                                                    
               30 Sigal                24-7月 -05           2800      24900         
         24900         16900                                                        
                                                                                    

    DEPARTMENT_ID FIRST_NAME           HIRE_DATE          SALARY     TOTAL1         
    ------------- -------------------- -------------- ---------- ----------         
        TOTAL2 RUNNING_TOTAL                                                        
    ---------- -------------                                                        
               30 Shelli               24-12月-05           2900      24900         
         24900         19800                                                        
                                                                                    
               30 Guy                  15-11月-06           2600      24900         
         24900         22400                                                        
                                                                                    
               30 Karen                10-8月 -07           2500      24900         
         24900         24900                                                        
                                                                                    

    已选择6行。

    上面的查询语句相当于:


     

      1  select department_id,first_name,hire_date,salary,
      2  sum(salary) over(partition by department_id) as total1,
      3  sum(salary) over() as total2,
      4  sum(salary) over(order by hire_date range between unbounded preceding and current row) as running_total
      5  from employees
      6* where department_id=30

     

     

    也就说默认情况下会告诉查询:计算所有行的和,即从当前行开始、包括它前面的所有行。对从当前行开始、包括它前面的所有行进行求和,就可以得到累计和效果了。

     

    通过,框架字句允许定义数据的不同“子窗口”,以便在计算中使用,有很多方式可以指定这样的子窗口。如:

     1  select department_id,first_name,salary,
      2  sum(salary) over (order by hire_date range between unbounded preceding and current row) as run_total1,
      3  sum(salary) over(order by hire_date rows between 1 preceding and current row) as run_total2,
      4  sum(salary) over(order by hire_date range between current row and unbounded following) as run_total3,
      5  sum(salary) over(order by hire_date rows between current row and 1 following) as run_total4
      6  from employees
      7* where department_id=30
    SQL> /

    DEPARTMENT_ID FIRST_NAME               SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3  
    ------------- -------------------- ---------- ---------- ---------- ----------  
    RUN_TOTAL4                                                                      
    ----------                                                                      
               30 Den                       11000      11000      11000      24900  
         14100                                                                      
                                                                                    
               30 Alexander                  3100      14100      14100      13900  
          5900                                                                      
                                                                                    
               30 Sigal                      2800      16900       5900      10800  
          5700                                                                      
                                                                                    

    DEPARTMENT_ID FIRST_NAME               SALARY RUN_TOTAL1 RUN_TOTAL2 RUN_TOTAL3  
    ------------- -------------------- ---------- ---------- ---------- ----------  
    RUN_TOTAL4                                                                      
    ----------                                                                      
               30 Shelli                     2900      19800       5700       8000  
          5500                                                                      
                                                                                    
               30 Guy                        2600      22400       5500       5100  
          5100                                                                      
                                                                                    
               30 Karen                      2500      24900       5100       2500  
          2500                                                                      
                                                                                    

    已选择6行。

     

    其中:

    range between unbounded preceding and current row 指定计算当前行开始、当前行之前的所有值;

    rows between 1 preceding and current row 指定计算当前行的前一行开始,其范围一直延续到当前行;

    range between current row and unbounded following 指定计算从当前行开始,包括它后面的所有行;

    rows between current row and 1 following 指定计算当前行和它后面的一行;

     

    最后一个例子,展示 了框架字句对查询输出的影响,请看下面查询:

     1  select first_name,salary,min(salary) over(order by salary) min1,
      2  max(salary) over(order by salary) max1,
      3  min(salary) over(order by salary range between unbounded preceding and unbounded following) min2,
      4  max(salary) over(order by salary range between unbounded preceding and unbounded following) max2,
      5  min(salary) over(order by salary range between current row and current row) min3,
      6  max(salary) over(order by salary range between current row and current row) max3,
      7  max(salary) over(order by salary rows between 3 preceding and 3 following) max4
      8* from employees
    SQL> /

    FIRST_NAME               SALARY       MIN1       MAX1       MIN2       MAX2     
    -------------------- ---------- ---------- ---------- ---------- ----------     
          MIN3       MAX3       MAX4                                                
    ---------- ---------- ----------                                                
    TJ                         2100       2100       2100       2100      24000     
          2100       2100       2400                                                
                                                                                    
    Steven                     2200       2100       2200       2100      24000     
          2200       2200       2400                                                
                                                                                    
    Hazel                      2200       2100       2200       2100      24000     
          2200       2200       2500      

     

    请仔细观察计算结果,领会子窗口的内涵;
    ---------------------
    作者:mfkpie
    来源:CSDN
    原文:https://blog.csdn.net/mfkpie/article/details/16364513
    版权声明:本文为博主原创文章,转载请附上博文链接!

    展开全文
  • Hive--sql中的窗口函数

    2019-09-03 00:07:56
    今天对Hive–sql中的窗口函数做一个简单的总结。 在Hive命令行中可以进行如下查看 show functions; //查看自带的所有的内置函数 desc function upper; //查看具体的某个函数的用法 desc function extended upper; ...
  • Flink SQL中的窗口函数

    千次阅读 2020-11-25 20:08:43
    窗口函数Flink SQL支持基于无限大窗口的聚合(无需在SQL Query,显式定义任何窗口)以及对一个特定的窗口的聚合。例如,需要统计在过去的1分钟内有多少用户点击了某个的网页,可以通过定义一个窗口来收集最近1分钟...
  • 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 语法 <窗口函数> over (partition by <用于分组列名> order by <用于排序列名>...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,551
精华内容 620
关键字:

sql中的窗口函数