精华内容
下载资源
问答
  • Oracle的分析函数,对我们进行统计有很大的帮助,可以避免一些子查询等操作,在统计中,我们对窗函数的接触较少,下面主要介绍下窗函数的使用; http://www.itpub.net/thread-1241311-1-1.html ...

    Oracle的分析函数,对我们进行统计有很大的帮助,可以避免一些子查询等操作,在统计中,我们对开窗函数的接触较少,下面主要介绍下开窗函数的使用;

    http://www.itpub.net/thread-1241311-1-1.html

    http://www.oracle-base.com/articles/misc/analytic-functions.php#windowing_clause

    http://blog.sina.com.cn/s/blog_70cea94b0100xi46.html

    首先我们介绍下分析函数的语义

    (分为range和row):缺省时相当于RANGE UNBOUNDED PRECEDING
       值域窗(RANGE WINDOW)  如:RANGE N PRECEDING, 仅对数值或日期类型有效,选定窗为排序后当前行之前,某列(即排序列)值大于/小于(当 前 行该列值 –/+ N)的所有行,因此与ORDER BY子句有关系。
       行窗(ROW WINDOW)如:ROWS N PRECEDING ,选定窗为当前行及之前N行。还可以加上BETWEEN AND 形式,例如RANGE BETWEEN m PRECEDING AND n FOLLOWING,表示每行对应的数据窗口是之前m行与之后n行内。

    1 SELECT empno,
    2        sal,
    3        mgr,
    4        deptno,
    5        SUM(sal) over(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND 100 FOLLOWING) dd
    6   FROM emp;

    其中:上面代表按DEPARTMENT_ID分区,按SALARY升序排序,汇总当前SALARY到比当前SALARY大100之间的SALARY总和。、

    按DEPARTMENT_ID分区,按SALARY升序排序,汇总当前SALARY到比当前SALARY大100之间的SALARY总和。

    Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

    analytic_function::=

    Description of analytic_function.gif follows
    Description of the illustration analytic_function.gif

    analytic_clause::=

    Description of analytic_clause.gif follows
    Description of the illustration analytic_clause.gif

    query_partition_clause::=

    Description of query_partition_clause.gif follows
    Description of the illustration query_partition_clause.gif

    order_by_clause::=

    Description of order_by_clause.gif follows
    Description of the illustration order_by_clause.gif

    windowing_clause ::=

    Description of windowing_clause.gif follows
    Description of the illustration windowing_clause.gif

    上面的这张图片是开窗函数的具体语法,我们可以参照这个语法。

    值的开窗,该值只能是日期和数字

    我有这样一个要求:
    1、查询的结果按照值排序,如sql:select value from t;
    结果示例如下:

    50
    70
    90
    130
    160
    190

    2、对数据进行分组。从上述数组第一个值开始,+50之内的值作为同一组值,如果超出50了,则开始一个新的分组。示例如下
    50    50
    70    50
    90    50
    130  130
    160  130
    190  190

    3、最终结果是统计每组的个数。结果示例:
    50    3
    130  2
    190  1

    原帖见:http://www.itpub.net/thread-985707-1-1.html

     

     1 WITH T AS (
     2 SELECT 50 N FROM DUAL UNION ALL 
     3 SELECT 70 N FROM DUAL UNION ALL 
     4 SELECT 90 N FROM DUAL UNION ALL 
     5 SELECT 130 N FROM DUAL UNION ALL 
     6 SELECT 160 N FROM DUAL UNION ALL 
     7 SELECT 190 N FROM DUAL
     8 )
     9 SELECT *
    10   FROM (SELECT n,
    11                row_number() OVER(ORDER BY n) rn,
    12                COUNT(*) OVER(ORDER BY n RANGE BETWEEN CURRENT ROW AND 50 FOLLOWING) cn
    13           FROM t)
    14  START WITH rn = 1
    15 CONNECT BY RN = PRIOR CN + PRIOR RN;

    在这里,我们通过数值开窗函数,统计了每个范围内的值,然后,通过构造条件,去进行connect by,

    在这里,通过让cn和rn去相加,作为connect by的条件,这个思路非常的好,很值得我们思考

    在统计的过程,我们往往只是需要去构造一个场景,条件。

    我有这样一个要求:
    1、查询的结果按照值排序,如sql:select value from t;
    结果示例如下:

    50
    70
    90
    130
    160
    190

    2、对数据进行分组。从上述数组第一个值开始,+50之内的值作为同一组值,如果超出50了,则开始一个新的分组。示例如下
    50    50
    70    50
    90    50
    130  130
    160  130
    190  190

    3、最终结果是统计每组的个数。结果示例:
    50    3
    130  2
    190  1

    这样一个要求,怎么用一个sql语句实现呢。
    谢谢大家!
    原帖见:http://www.itpub.net/thread-985707-1-1.html

    通过如下的SQL可以实现上面的要求:

     

     1 WITH T AS (
     2 SELECT 1 N FROM DUAL UNION ALL 
     3 SELECT 3 N FROM DUAL UNION ALL 
     4 SELECT 4 N FROM DUAL UNION ALL 
     5 SELECT 7 N FROM DUAL UNION ALL 
     6 SELECT 10 N FROM DUAL UNION ALL 
     7 SELECT 11 N FROM DUAL UNION ALL 
     8 SELECT 12 N FROM DUAL UNION ALL 
     9 SELECT 12 N FROM DUAL UNION ALL 
    10 SELECT 19 N FROM DUAL UNION ALL 
    11 SELECT 20 N FROM DUAL 
    12 )
    13 SELECT T2.N
    14        ,DENSE_RANK() OVER(ORDER BY T2.G) G
    15 FROM (
    16       SELECT T.N
    17              ,MAX(T1.N)OVER(ORDER BY T.N) G
    18       FROM (
    19             SELECT N 
    20             FROM (
    21                   SELECT N 
    22                          ,COUNT(*) OVER(ORDER BY N RANGE BETWEEN CURRENT ROW AND 4 FOLLOWING) CNT
    23                          ,ROW_NUMBER()OVER(ORDER BY N) RN
    24                   FROM T
    25                  )
    26             CONNECT BY RN = PRIOR RN + PRIOR CNT 
    27             START WITH RN = 1
    28            ) T1 , T
    29       WHERE T1.N(+) = T.N
    30       ) T2;

    在这里,我们需要关注connect by,dense rank函数和 ,MAX(T1.N)OVER(ORDER BY T.N) G这个用法。

    下面是高手用with递归解决的例子,当前也可以用我们熟悉的connect by解决该问题

     1 WITH T AS
     2  (SELECT 1 N
     3     FROM DUAL
     4   UNION ALL
     5   SELECT 4 N
     6     FROM DUAL
     7   UNION ALL
     8   SELECT 3 N
     9     FROM DUAL
    10   UNION ALL
    11   SELECT 7 N
    12     FROM DUAL
    13   UNION ALL
    14   SELECT 10 N
    15     FROM DUAL
    16   UNION ALL
    17   SELECT 11 N
    18     FROM DUAL
    19   UNION ALL
    20   SELECT 12 N
    21     FROM DUAL
    22   UNION ALL
    23   SELECT 12 N
    24     FROM DUAL
    25   UNION ALL
    26   SELECT 19 N
    27     FROM DUAL
    28   UNION ALL
    29   SELECT 20 N FROM DUAL),
    30 v AS
    31  (SELECT n, row_number() over(ORDER BY n) rn FROM t),
    32 v1(flag,
    33 n,
    34 rn) AS
    35  (SELECT n, n, rn
    36     FROM v
    37    WHERE rn = 1
    38   UNION ALL
    39   SELECT CASE
    40            WHEN v.n - v1.flag >= 5 THEN
    41             v.n
    42            ELSE
    43             v1.flag
    44          END,
    45          v.n,
    46          v.rn
    47     FROM v, v1
    48    WHERE v.rn = v1.rn + 1)
    49 SELECT * FROM v1

    当然也有高手用MODEL语句实现了该功能,请查看原帖。

    转载于:https://www.cnblogs.com/caroline/p/3501097.html

    展开全文
  • 分析函数和窗函数

    2021-05-07 14:20:23
    使用形式为:分析函数() over() 窗函数(over()) 窗函数(over())包含三个分析子句: 分组子句(partition by) 排序子句(order by) 窗口子句(rows) 窗函数(over())使用形式如下: over(partition by xxx order by...

    分析函数

    分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值

    分析函数和聚合函数的区别

    普通的聚合函数用group by分组,每个分组返回一个统计值

    分析函数采用partition by分组,并且每组每行都可以返回一个统计值

    显而易见的区别是,从返回结果上看

    聚合后的记录数可能会减少,因为它对数据源进行了group by操作,所以对结果是有影响的,即返回的结果里去掉聚合的列后,跟聚合前的结果不一样

    而分析后的记录数是不变的,因为它的作用仅仅在于分析,所以不会对结果有影响,即返回的结果里去掉分析的列后,跟分析前的结果是一样的

    分析函数的形式

    分析函数要与开窗函数(over())一起使用,使用形式为:分析函数() over()

    开窗函数(over())

    开窗函数(over())包含三个分析子句:

    分组子句(partition by)

    排序子句(order by)

    窗口子句(rows)

    开窗函数(over())使用形式如下:

    over(partition by xxx order by yyy rows between zzz)

    开窗函数里的"窗",即"窗口",表示分析函数分析时要处理的数据范围

    比如分析函数sum(),它所针对的数据范围为窗口中的记录,而不是整个表的记录

    要获取某个范围的sum值,则需在窗口指定具体的数据范围

    比如指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样,即整个组的总和

    窗口子句除了rows方式的窗口,还包括range方式和滑动窗口

    排序子句(order by)

    开窗函数的order by和sql语句的order by的执行时机

    分析及开窗函数(over())是在整个sql查询结束后再进行的, 即sql语句的order by也会影响分析函数的执行结果,有以下两种情况:

    1) 两者一致,即sql语句中的order by语句与开窗函数的order by一致,则sql语句中的order by先执行,分析函数在分析时就不必再排序

    2) 两者不一致,即sql语句中的order by语句与开窗函数的order by不一致,则分析及开窗函数先分析排序,sql语句中的order by再最后执行

    窗口子句(rows)

    如果没有窗口子句(rows),则默认当前组的第一行到当前行

    无论是否省略分组子句,都有:

    窗口子句(rows)不能单独存在,必须有order by子句时才能出现

    相反,有order by子句,可以没有窗口子句(rows)

    当省略窗口子句时

    如果存在order by,则默认的窗口是unbounded preceding and current row,即当前组的第一行到当前行

    如果不存在order by,则默认的窗口是unbounded preceding and unbounded following,即整个组

    例如:lag(sal) over(order by sal)

    over(order by salary)表示意义如下:

    1) 由于省略分组子句,所以当前组的范围为整个表的数据行

    2) 在当前组(此时为整个表的数据行)这个范围里执行排序,即order by salary

    3) 分析函数lag(sal)在当前组(此时为整个表的数据行)这个范围里的窗口范围为当前组的第一行到当前行,即分析函数lag(sal)在这个窗口范围执行

    窗口子句(rows)的相关关键字:

    preceding:表示在...之前

    1 preceding:表示当前记录的前1条记录

    2 preceding:表示当前记录的前2条记录

    n preceding:表示当前记录的前n条记录

    unbounded preceding:不受控制的,无限的,

    若无分组,则表示所有记录的第1条记录

    若有分组,则表示分组后,组内的第1条记录

    following:表示在...之后

    1 following:表示当前记录的后一条记录

    2 following:表示当前记录的后两条记录

    n following:表示当前记录的后n条记录

    unbounded following:不受控制的,无限的,

    若无分组,则表示所有记录的最后一条记录

    若有分组,则表示分组后,组内的最后一条记录

    相关用例:

    rows between unbounded preceding and unbounded following:针对所有记录

    rows between unbounded preceding and current row:针对第一条记录到当前记录

    rows between current row and unbounded following:针对当前记录到最后一条记录

    rows between 1 preceding and current row:针对当前记录的上一行记录到当前行记录

    rows between current row and 3 following:针对当前记录到当前行记录的后三条记录

    rows between 1 preceding and 2 following:针对当前记录的上一条记录 ~~ 当前行记录的后两条记录

    当开窗函数over()出现分组(partition by)子句时

    unbounded preceding即表中一个分组里的第一行,unbounded following即表中一个分组里的最后一行

    当开窗函数over()省略了分组(partition by)子句时

    unbounded preceding即表中的第一行, unbounded following即表中的最后一行

    使用示例:

    selectt.id,

    t.name,

    t.city,

    t.sales,--按城市分组,且组内销量升序排列,并获取当前记录针对上一条记录到下一条记录范围内的最小销量值

    min(t.sales) over(partition by t.city order by t.sales rows between 1 preceding and 1 following) ascol1,--按城市分组,且组内销量升序排列,并获取当前记录针对第一条记录到当前记录范围内的最小销量值

    min(t.sales) over(partition by t.city order by t.sales rows between unbounded preceding and current row) ascol2,--按城市分组,且组内销量升序排列,并获取当前记录针对上三条记录到当前记录范围内的最小销量值

    min(t.sales) over(partition by t.city order by t.sales rows between 3 preceding and current row) ascol3from dataset t

    原文:https://www.cnblogs.com/shiliye/p/12363300.html

    展开全文
  • 窗函数有浅入深详解(一)2019-01-04编程之家收集整理的这篇文章主要介绍了窗函数有浅入深详解(一),编程之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。在窗函数出现之前存在着很多用 sql 语句很难...

    开窗函数有浅入深详解(一)

    2019-01-04

    编程之家收集整理的这篇文章主要介绍了开窗函数有浅入深详解(一),编程之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

    在开窗函数出现之前存在着很多用 sql 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在2003年ISO sql标准加入了开窗函数,开窗函数的使用使得这些经典的难题可以被轻松的解决。

    目前在 MSsqlServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,不过非常遗憾的是 MysqL 暂时还未对开窗函数给予支持。

    为了更加清楚地理解,我们来建表并进行相关的查询(截图为MSsqlServer中的结果)

    MysqL,MSsqlServer,DB2:

    sql;">

    CREATE TABLE T_Person

    (

    FName VARCHAR(20),FCity VARCHAR(20),FAge INT,FSalary INT

    )

    Oracle:

    代码如下:

    CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20),FSalary INT)

    sqlServer中演示:

    T_Person 表保存了人员信息,FName 字段为人员姓名,FCity 字段为人员所在的城市名,

    FAge 字段为人员年龄,FSalary 字段为人员工资。

    然后执行下面的sql语句向 T_Person表中插入一些演示数据:

    sql;">

    INSERT INTO T_Person(FName,FCity,FAge,FSalary)

    VALUES('Tom','BeiJing',20,3000);

    INSERT INTO T_Person(FName,FSalary)

    VALUES('Tim','ChengDu',21,4000);

    INSERT INTO T_Person(FName,FSalary)

    VALUES('Jim',22,3500);

    INSERT INTO T_Person(FName,FSalary)

    VALUES('Lily','London',2000);

    INSERT INTO T_Person(FName,FSalary)

    VALUES('John','NewYork',1000);

    INSERT INTO T_Person(FName,FSalary)

    VALUES('YaoMing',FSalary)

    VALUES('Swing',FSalary)

    VALUES('Guo',2800);

    INSERT INTO T_Person(FName,FSalary)

    VALUES('YuQian',24,8000);

    INSERT INTO T_Person(FName,FSalary)

    VALUES('Ketty',25,8500);

    INSERT INTO T_Person(FName,FSalary)

    VALUES('Kitty',FSalary)

    VALUES('Merry',23,FSalary)

    VALUES('Smith',30,FSalary)

    VALUES('Bill',FSalary)

    VALUES('Jerry',3300);

    查看表中的内容:

    @L_403_1@ 代码如下:

    select * from T_Person

    开窗函数简介

    与 聚 合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值,开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计算的行集组是窗口。

    在ISO sql规定了这样的函数为开窗函数,在 Oracle中则被称为分析函数,而在DB2中则被称为OLAP函数。

    要计算所有人员的总数,我们可以执行下面的 sql语句:

    代码如下:

    SELECT COUNT(*) FROM T_Person

    除了这种较简单的使用方式,有时需要从不在聚合函数中的行中访问这些聚合计算的值。比如我们想查询每个工资小于 5000元的员工信息(城市以及年龄) ,并且在每行中都显示所有工资小于5000元的员工个数,尝试编写下面的 sql语句:

    sql;">

    SELECT FCITY,FAGE,COUNT(*)

    FROM T_Person

    HERE FSALARY<5000

    执行上面的sql以后我们会得到下面的错误信息:

    选择列表中的列 'T_Person.FCity' 无效,因为该列没有包含在聚合函数或GROUP BY 子句中。

    这是因为所有不包含在聚合函数中的列必须声明在GROUP BY 子句中,

    可以进行如下修改:

    sql;">

    SELECT FCITY,COUNT(*)

    FROM T_Person

    WHERE FSALARY<5000

    GROUP BY FCITY,FAGE

    执行完毕我们就能在输出结果中看到下面的执行结果:

    这个执行结果与我们想像的是完全不同的,这是因为GROUP BY子句对结果集进行了分组,所以聚合函数进行计算的对象不再是所有的结果集,而是每一个分组。

    可以通过子查询来解决这个问题,sql如下:

    sql;">

    SELECT FCITY,(

    SELECT COUNT(* ) FROM T_Person

    WHERE FSALARY<5000

    )

    FROM T_Person

    WHERE FSALARY<5000

    执行完毕我们就能在输出结果中看到下面的执行结果:

    虽然使用子查询能够解决这个问题,但是子查询的使用非常麻烦,使用开窗函数则可以大大简化实现,下面的sql语句展示了如果使用开窗函数来实现同样的效果:

    sql;">

    SELECT FCITY,COUNT(*) OVER()

    FROM T_Person

    WHERE FSALARY<5000

    执行完毕我们就能在输出结果中看到下面的执行结果:

    可以看到与聚合函数不同的是,开窗函数在聚合函数后增加了一个

    OVER 关键字。

    开窗函数的调用格式为:

    函数名(列) OVER(选项)

    OVER 关键字表示把函数当成开窗函数而不是聚合函数。sql 标准允许将所有聚合函数用做开窗函数,使用OVER 关键字来区分这两种用法。

    在上边的例子中,开窗函数

    COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。

    如果OVER关键字后的括号中的选项为空,则开窗函数会对结果集中的所有行进行聚合运算。

    总结:上述讲述的是开窗函数的基本用法,希望对大家有所帮助!

    总结

    以上是编程之家为你收集整理的开窗函数有浅入深详解(一)全部内容,希望文章能够帮你解决开窗函数有浅入深详解(一)所遇到的程序开发问题。

    如果觉得编程之家网站内容还不错,欢迎将编程之家网站推荐给程序员好友。

    本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。

    展开全文
  • 窗口函数(窗函数)及OVER子句(3):OVER子句完整语法及各选项详解 若觉得本文写得还可以,请多多关注本人所作书籍《C++语法详解》电子工业出版社出版,作者 黄勇,网盘地址: ... 本文为原创文章,转载请注明出处,...

    窗口函数(开窗函数)及OVER子句(3):OVER子句完整语法及各选项详解

    若觉得本文写得还可以,请多多关注本人所作书籍《C++语法详解》电子工业出版社出版,作者 黄勇,网盘地址:
    https://pan.baidu.com/s/1dIxLMN5b91zpJN2sZv1MNg

    本文为原创文章,转载请注明出处,或注明转载自“黄邦勇帅(原名:黄勇)

    四、OVER子句完整语法
    OVER子句完整语法如下:
    在这里插入图片描述
    为避免混淆,本小节会把分区子句称为PARTITION BY从属子句,排序子句称为ORDER BY从属子句

    分区子句< PARTITION BY clause >
    1、所有窗口函数都支持分区子句。
    2、分区子句用于对查询结果集进行分区,其规则为:只有在结果集的分区列中与当前行的分区列有相同值的行才进入分区,其余行被过滤掉,若未指定PARTITION BY子句,则表示未分区,此时默认分区为整个查询结果集,若未指定框架子句,则由分区子句划分的分区就是最终定义的窗口。比如PARTITION BY a,若当前行列a的值为1,则只有查询结果集中所有列a的值为1的行才进入分区,其余行被过滤掉。该子句的示例在前文已讲解过,此处不再重述。

    排序子句< ORDER BY clause >
    1、所有窗口函数都支持排序子句,注意:SQL Server2012之后才开始在聚合窗口函数中支持ORDER BY从属子句。
    2、ORDER BY从属子句用于定义在窗口内的顺序,不同类型的窗口函数,ORDER BY从属子句的意义会有一些不相同,具体规则详见对相应窗口函数的讲解,另外,排序子句还会帮助框架子句对窗口进行进一步的限定或过滤
    3、窗口函数的排序规范与显示(或输出)时的排序规范不同,窗口函数的排序子句,只影响窗口内的数据,若指定了显示排序,则最后输出的排序以显示排序为准,而不是以窗口函数的排序为准,若需要按一定顺序显示,则需要指定显示排序。显示排序指的是SELECT语句中的ORDER BY子句,比如SELECT * FROM T ORDER BY a,其中的ORDER BY就是显示排序
    4、窗口函数、NEXT VALUE FOR 函数不支持将整数索引作为 ORDER BY 子句表达式。但可以使用子查询返回一个常量作为排序子句的元素,比如ORDER BY (SELECT 3)就是正确的,同理ORDER BY (select a from T4 where a=1),若子查询返回的值是唯一的,也是正确的

    框架子句< ROW or RANGE clause >
    1、框架子句适用于聚合窗口函数和3个偏移函数(FIRSST_VALUE、LAST_VALUE、NTH_VALUE),注意:排名函数不支持框架子句。
    2、框架子句是在一个分区内对行的进一步限制或过滤。也就是说,PARTITION BY从属子句是对窗口的第一次过滤,框架子句是在第一次过滤的基础上进行的第二次过滤,由框架子句过滤后的窗口,将成为最终的窗口。
    3、框架子句过滤窗口的基本规则是:基于排序子句的顺序,在当前行所在分区中定义两个点,在这两点之间能形成框架的行才会进入窗口,其余行被再次过滤掉。
    4、SQL Server使用ROWS或RANGE选项来定义框架的开始行和结束行,这两个选项的基本规则如下:
     ROWS选项使用相对于当前行的偏移行数来定义框架的起点和终点。
     RANGE选项可以使用框架起/终点的值与当前行的值的差异来定义偏移的行数,SQL Server2017只实现了该选项的部分功能。因此本文主要对ROWS选项进行讲解,在SQL Server中,RANGE与ROWS的原理是类似的,但是排序子句会对RANGE有影响,详见后文
     ROWS/RANGE选项各参数的意义,可参阅语法的注释,现在应该容易理解了
    5、框架子句过滤分区的基本规则
    1)、当使用< window frame preceding > 子句定义框架区域时,该子句中的参数只能用于指定窗口的起点,窗口的终点此时为CURRENT ROW(即当前行),比如ROWS 2 PRECEDING与ROWS BETWEEN 2 PRECEDING AND CURRENT ROW相同,表示窗口总共有3行,包括当前行的前2行和当前行,假设当前行为第5行且当前行所在分区足够大,则该行的窗口为第3,4,5行
    2)、注意:不能只使用< window frame following > 子句定义框架区域,比如ROWS 2 FOLLOWING是错误的语句。
    3)、当< 整数值> FOLLOWING 指定为窗口起点时,终点不能是< 整数值> PRECEDING 或CURRENT ROW,比如,ROWS BETWEEN 1 FOLLOWING AND CURRENT ROW,或ROWS BETWEEN 1 FOLLOWING AND 1 PRECEDING都是错误的,
    4)、RANGE 不能用于 < 整数值> PRECEDING 或 < 整数值> FOLLOWING。也就是说,使用RANGE时不能使用数值指定行数
    5)、下面列举几个示例
    在这里插入图片描述
    6、RANGE选项定义框架的原理
    RANGE选项定义的窗口除按照ROWS规则确定的行外,还包含ORDER BY从属子句列表中列的值与当前行相应列具有相同值的所有行,这主要影响当使用CURRENT ROW确定窗口的起点或终点时。比如表有(a, b, c, d)四列,假设有(5, 2, 8, 9),(5, 6, 1, 2),(5, 6, 3, 4)三行,有语句

    PARTITION BY a ORDER BY a, b RANGE UNBOUNDED PRECEDING
    

    那么,行(5, 6, 1, 2)的窗口为(5, 2, 8, 9),(5, 6, 1, 2),(5, 6, 3, 4)三行。按照ROWS规则确定的行有(5, 2, 8, 9)、(5, 6, 1, 2),另外,还包含(a, b)列的值为(5, 6)的行(5, 6, 3, 4),若把RANGE修改为ROWS,则当前行(5, 6, 1, 2)的窗口只有(5, 2, 8, 9),(5, 6, 1, 2)两行。
    7、ORDER BY从属子句对框架子句的影响
    在这里插入图片描述

    示例5.3:ROWS定义框架的原理(输出结果见图5.3)
    select *,sum(c)over(partition by a order by a,b,c ROWS 2 PRECEDING ) as 框架1
    		,sum(c)over(partition by a order by a,b,c ROWS UNBOUNDED PRECEDING) as 框架2
    ,sum(c)over(partition by a order by a,b,c
    ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) as 框架3	
    		from T4 order by a,b,c;	--与框架子句排序一致,以免输出时顺序不一致
    

    在这里插入图片描述
    在这里插入图片描述

    示例5.4:RANGE定义框架的原理(输出结果见图5.4)
    select *,sum(c)over(partition by a order by a,b ROWS CURRENT ROW ) as 框架1
    		,sum(c)over(partition by a order by a,b RANGE CURRENT ROW) as 框架2
    		,sum(c)over(partition by a order by a,b ROWS UNBOUNDED PRECEDING) as 框架3
    		,sum(c)over(partition by a order by a,b RANGE UNBOUNDED PRECEDING) as 框架4
    		,sum(c)over(partition by a order by a,b) as 框架5	--框架5与框架4相同
    		,sum(c)over(order by a,b RANGE UNBOUNDED PRECEDING) as 框架6  --注意该框架
    		from T4 order by a,b;
    

    在这里插入图片描述
    示例说明:
    1、框架1的过滤条件为ROWS CURRENT ROW,因此窗口只有当前行一行,对窗口内c列进行求和,就是该行c列的值
    2、框架2的过滤条件为RANGE CURRENT ROW,表示窗口包含ORDER BY从属子句列表中列的值与当前行相应列具有相同值的所有行,因此,行(5, 6, 11, 12)的窗口为(a, b)列的值为(5, 6)的所有行,即窗口为(5, 6, 11, 12)、(5, 6, 3, 1)、(5, 6, 5, 4),对该窗口内的c列进行求和,其结果为19(即11+3+5),从这里可以明显看到RANGE与ROWS的区别。行(5, 6, 3, 1)、(5, 6, 5, 4)的原理与行(5, 6, 11, 12)相同,其余行的窗口都仅为当前行一行。
    3、框架3的过滤条件为ROWS UNBOUNDED ROW,其窗口为所在分区的开头直至当前行,具体从略,详见正文及上一示例的讲解
    4、框架4的过滤条件为RANGE UNBOUNDED ROW,表示窗口除包含按照ROWS规则确定的行外,还包含ORDER BY从属子句列表中列的值与当前行相应列具有相同值的所有行,因此,行(5, 6, 11, 12)的窗口为,按照ROWS规则确定的行(5, 1, 2, 3)、(5, 6, 11, 12),还包含(a, b)列的值为(5, 6)的行(5, 6, 3, 1)、(5, 6, 5, 4),对窗口内的c列进行求和,其结果为21(即2+11+3+5)。行(5, 6, 3, 1)、(5, 6, 5, 4)的原理与行(5, 6, 11, 12)相同,(5, 9, 10, 11)、(5, 11, 22, 33)、(4, 10, 1, 8)的窗口为所在分区的开头直至当前行,除此之外的其余行的窗口都仅为当前行一行。
    5、框架5与框架4是相同的,从略
    6、框架6没有PARTITION BY子句,因此,框架6的当前行所在的分区将是整个查询结果集,RANGE UNBOUNDED ROW将在整个查询结果集的基础上进行过滤,比如,行(4, 10, 1, 8),因整个查询结果集为该行所在分区,所以,其窗口为该行所在分区的开头直至当前行,即窗口为(1, 2, 3, 4)、(2, 3, 4, 5)、(4, 1, 2, 3)、(4, 10, 1, 8),对窗口内的c列进行求和,其结果为10(即3+4+2+1);行(5, 6, 11, 12)的窗口共有8行,即(1, 2, 3, 4)到(5, 6, 5, 4)之间的所有行(含起点和终点),对窗口内的c列进行求和,其结果为31(即3+4+2+1+2+11+3+5)

    作者:黄邦勇帅(原名:黄勇)

    在这里插入图片描述

    主要参考文献:
    1、C++语法详解 黄勇 编著 电子工业出版社 2017年7月
    2、SQL Server 2012 T-SQL基础教程 [美] Itzik Ben-Gan著 张洪举 李联国 张昊天 译 人民邮电出版社 2013年12月
    3、T-SQL性能调优秘笈------基于SQL Server 2012窗口函数 [美] Itzik Ben-Gan著 林德玲 方鑫译 人民邮电出版社 2014年8月
    4、SQL Server 2005技术内幕:T-SQL查询 [美] Itzik Ben-Gan , Lubor Kollar , Dejan Sarka著 赵立东 唐灿 刘波 译 赵立东 审校 电子工业出版社 出版日期不详
    4、SQL Server 2005技术内幕:T-SQL程序设计 [美] Itzik Ben-Gan , Dejan Sarka , Roger Wolter著 赵立东 译 电子工业出版社 出版日期不详
    5、Microsoft SQL Server 2008技术内幕:T-SQL语言基础 [美] Itzik Ben-Gan著 成保栋 张昱 译 电子工业出版社 2009年10月
    6、Microsoft SQL Server 2008技术内幕:T-SQL查询 [美] Itzik Ben-Gan , Lubor Kollar , Dejan Sarka , Steve Kass著 成保栋 李保强 译 电子工业出版社 2010年9月

    展开全文
  • HIVE 窗函数

    2020-04-18 14:40:04
    窗函数出现之前,存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在2003年ISO SQL标准加入了窗函数,窗函数的使用使得这些经典的难题可以被轻松...
  • 一、分析函数语法 function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);...over( ):窗函数 partition_C...
  • Hive窗函数总结

    万次阅读 多人点赞 2018-08-05 12:56:25
    背景: ...注:hive、oracle提供窗函数,mysql不提供 版本:Hive 1.1.0 + cdh5.13.0 一、介绍 分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而...
  • hive窗函数使用,用案例演示

    千次阅读 2021-03-07 00:27:14
    hive的窗函数的使用 hive窗函数,大致就是row_number()、sum()、coun()、avg()、max()、min()、first_value()、last_value()、lag()、lead()、cume_dist()、rank()、dense_rank()、ntile()这些接over窗口,...
  • 关于SparkSQL的窗函数,你应该知道这些!

    千次阅读 多人点赞 2020-04-12 12:49:08
    概述介绍聚合函数和窗函数窗函数分类2. 准备工作3. 聚合窗函数4. 排序窗函数4.1 ROW_NUMBER顺序排序4.2 RANK跳跃排序4.3 DENSE_RANK连续排序4.4 NTILE分组排名[了解]结语 1.概述 介绍    ...
  • presto剖析

    2015-09-17 11:03:47
    treasure data presto学习资料,presto剖析(ppt)
  • 一、窗类 1、求截止到当前行不为空最后一个值 select calendar_day, if_trade_dt, mkt_code, last_value(tmp_trade_dt, true) over(partition by mkt_code order by calendar_day desc) as gt_trade_dt, last_...
  • oracle分析函数技术详解(配上窗函数over())

    万次阅读 多人点赞 2013-11-11 11:36:00
    分析函数(以及与其配合的窗函数over())是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果: a) 两者一致:如果sql语句中的...
  • SQL Server中的窗函数是什么?窗函数和over是一个东西吗?怎么用?case when能和窗函数一起用吗CASE WHEN wst.score - 60 < 0 THEN -当wst.score能说一下oracle中的窗函数 聚合函数 分析函数都他们之间有啥...
  • 你在这里因为你有,有一个文件扩展名结尾...窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 发表评论
  • 你在这里因为你有,有一个文件扩展名结尾...窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 发表评论
  • 分析函数ratio_to_report的使用

    千次阅读 2009-06-24 09:10:00
    Ratio_to_report函数Syntax 分析函数RATIO_TO_REPORT 用来计算当前记录的指标expr占窗函数over中包含记录的所有同一指标的百分比. 这里如果窗函数的统计结果为null或者为0,就是说占用比率的被除数为0或者为null,...
  • 2、您讲的课,是那样丰富多采,每一个章节都仿佛在我面前打开了一扇窗户,让我看到了一个斑斓的新世界……3、老师,还记得高考(微博)那天您递给过我一瓶绿茶,要我备足精神考试吗?今天我想敬您一杯清茶,说上一...
  • oracle分析函数之windowing_clause--rows

    千次阅读 2013-10-30 09:32:11
    Some analytic functions allow the windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*)...
  • 你在这里因为你有,有一个文件扩展名结尾...窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 发表评论
  • 你在这里因为你有,有一个文件扩展名结尾...窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 发表评论
  • 你在这里因为你有,有一个文件扩展名结尾...窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 发表评论
  • 你在这里因为你有,有一个文件扩展名结尾...窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 发表评论
  • 你在这里因为你有,有一个文件扩展名结尾...窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 发表评论
  • 你在这里因为你有,有一个文件扩展名结尾...窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 发表评论
  • php后缀文件怎么打开

    2021-03-23 21:20:26
    窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 以上就是php后缀文件怎么打开的详细内容,更多请...
  • 给老师的一

    2021-07-30 01:59:20
    给老师的一话1、一日为师,终身为父。2、是谁把雨露撒遍大地?是谁把幼苗辛勤哺育?是您,老师,您是一位伟大的园丁!看这遍地怒放的鲜花,哪一朵上没有您的心血,哪一朵上没有您的笑影!3、假如我是诗人,我将以...
  • js打开新窗口的方法

    2017-07-04 15:40:08
    这一的作用是调用关闭窗口的代码,10秒钟后就自行关闭该窗口。)  【7、在弹出窗口中加上一个关闭按钮】    关闭' onClick='window.close()'>    呵呵,现在更加完美了! 【8...
  • 改写句子:肯定.doc

    2021-07-16 01:35:12
    改写句子:肯定肯定、否定、双重否定练习题一、把肯定改为否定,把否定改为肯定。1、街上的人真多。2、说起地道战,谁也不能不承认是个奇迹。3、谁也不能否认地球不是绕太阳运行的。4、他是一个听话...
  • 你在这里因为你有,有一个文件扩展名结尾...窗户经常关联一个默认程序的每个文件的扩展名,这样,当你双击该文件,程序会自动启动。当该程序不再是您的PC上,有时可以得到一个错误,当您试图打开相关的文件。 发表评论

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 8,440
精华内容 3,376
关键字:

开窗子句