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

    千次阅读 2018-06-20 09:48:14
    窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数, 使用的窗口函数不会导致行被分组到一个单一的输出行;行保留其独立的身份。 在后台,窗口函数...

    窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数, 使用的窗口函数不会导致行被分组到一个单一的输出行;行保留其独立的身份。 在后台,窗口函数能够访问的不止查询结果的当前行。

    这里是一个例子,说明如何比较每个员工的工资和在他或她的部门的平均工资:

    SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

      depname  | empno | salary |          avg          
    -----------+-------+--------+-----------------------
     develop   |    11 |   5200 | 5020.0000000000000000
     develop   |     7 |   4200 | 5020.0000000000000000
     develop   |     9 |   4500 | 5020.0000000000000000
     develop   |     8 |   6000 | 5020.0000000000000000
     develop   |    10 |   5200 | 5020.0000000000000000
     personnel |     5 |   3500 | 3700.0000000000000000
     personnel |     2 |   3900 | 3700.0000000000000000
     sales     |     3 |   4800 | 4866.6666666666666667
     sales     |     1 |   5000 | 4866.6666666666666667
     sales     |     4 |   4800 | 4866.6666666666666667
    (10 rows)

    前三输出列直接来自表empsalary,并有一个针对表中的每一行的输出行。 第四列将代表所有含有相同的depname值的表行的平均值作为当前值。 (这实际上与标准avg聚合函数的功能相同, 但是OVER子句使其被视为一个窗口函数并在一组合适的行上执行计算。)

    窗口函数的调用总是包含一个OVER子句,后面直接跟着窗口函数的名称和参数。 这是它在语法上区别于普通函数或聚合功能的地方。 OVER子句决定如何将查询的行进行拆分以便给窗口函数处理。 OVER子句内的PARTITION BY列表指定将行划分成组或分区, 组或分区共享相同的PARTITION BY表达式的值。 对于每一行,窗口函数在和当前行落在同一个分区的所有行上进行计算。

    你还可以使用窗口函数OVER内的ORDER BY来控制行的顺序。 (ORDER BY窗口甚至不需要与行的输出顺序相匹配。)下面是一个例子:

    SELECT depname, empno, salary,
           rank() OVER (PARTITION BY depname ORDER BY salary DESC)
    FROM empsalary;

      depname  | empno | salary | rank 
    -----------+-------+--------+------
     develop   |     8 |   6000 |    1
     develop   |    10 |   5200 |    2
     develop   |    11 |   5200 |    2
     develop   |     9 |   4500 |    4
     develop   |     7 |   4200 |    5
     personnel |     2 |   3900 |    1
     personnel |     5 |   3500 |    2
     sales     |     1 |   5000 |    1
     sales     |     4 |   4800 |    2
     sales     |     3 |   4800 |    2
    (10 rows)

    正如此处所示,rank函数按照由ORDER BY子句定义的顺序, 在当前行的分区里为每个不同的ORDER BY值产生了一个数值排名。 rank 不需要明确的参数,因为它的行为完全取决于OVER子句。

    窗口函数的行来自查询的FROM子句产生,并且如果有的话, 经过WHERE,GROUP BYHAVING子句过滤的"虚拟表"。 比如,被移除掉的行,因为不符合WHERE条件,所以是不能被任何窗口函数可见的。 一个查询可以包含多个窗口函数,通过不同的OVER子句用不同的方式分割数据, 但是他们都作用在这个虚拟表定义的同一个行集合。

    我们已经看到了,如果行排序并不重要,ORDER BY可以省略。 在只有一个包含了所有行的分区情况下,也可以省略PARTITION BY

    还有一个与窗口函数相关的重要的概念:对于每一行,有在其分区范围内的行集, 又称为它的window frame。许多(但不是全部)窗口函数,只作用于window frame中的行上, 而不是整个分区。默认情况下,如果使用ORDER BY, 那么这个frame包含从分区开始到当前行的所有行,以及那些当前行后面的,根据ORDER BY子句等于当前行的所有行,如果省略ORDER BY,那么,frame默认包含分区中的所有行。 [1] 下面是一个使用sum的例子:

    SELECT salary, sum(salary) OVER () FROM empsalary;
     salary |  sum  
    --------+-------
       5200 | 47100
       5000 | 47100
       3500 | 47100
       4800 | 47100
       3900 | 47100
       4200 | 47100
       4500 | 47100
       4800 | 47100
       6000 | 47100
       5200 | 47100
    (10 rows)

    如上,因为在OVER子句中没有使用ORDER BY,因此, window frame与分区(不使用PARTITION BY时即整个表)相同;换句话说, 每一次sum求和都是使用表中所有的salary,所以我们得到的每个输出行的结果相同。 但是,如果我们添加ORDER BY子句,我们会得到不同的结果:

    SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
     salary |  sum  
    --------+-------
       3500 |  3500
       3900 |  7400
       4200 | 11600
       4500 | 16100
       4800 | 25700
       4800 | 25700
       5000 | 30700
       5200 | 41100
       5200 | 41100
       6000 | 47100
    (10 rows)

    这里的总和是从第一个(最低)工资到当前一个,包括任何当前重复的(注意重复薪金的结果)。

    窗口函数仅允许在查询的SELECT列表和ORDER BY子句中使用。 在其他地方禁止使用,比如GROUP BY,HAVINGWHERE子句。 这是因为它们逻辑上在处理这些子句之后执行。此外,窗口函数在标准聚合函数之后执行。 这意味在一个窗口函数的参数中包含一个标准聚合函数的调用是有效的,但反过来不行。

    执行窗口计算后,如果有必要对行进行过滤或分组,你可以使用子查询。例如:

    SELECT depname, empno, salary, enroll_date
    FROM
      (SELECT depname, empno, salary, enroll_date,
              rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
         FROM empsalary
      ) AS ss
    WHERE pos < 3;

    上面的查询只显示内部查询结果中rank小于3的行。

    当查询涉及多个窗口函数时,可以写成每一个都带有单独的OVER子句, 但是,如果期待为多个窗口函数采用相同的窗口行为,这样做就会产生重复,并且容易出错。 作为代替,每个窗口行为可以在WINDOW子句中进行命名,然后再被OVER引用。 例如:

    SELECT sum(salary) OVER w, avg(salary) OVER w
      FROM empsalary
      WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

    有关窗口函数的更多详细信息请查阅第 4.2.8 节第 9.21 节,第 7.2.4 节,和 SELECT的参考页。

    展开全文
  • sql:窗口函数

    万次阅读 多人点赞 2018-08-17 10:29:42
    窗口函数可以进行排序,生成序列号等一般的聚合函数无法实现的高级操作。 窗口函数也称为OLAP函数,意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。 窗口函数语法:其中[]中...

    窗口函数可以进行排序,生成序列号等一般的聚合函数无法实现的高级操作。

    窗口函数也称为OLAP函数,意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

    窗口函数语法:其中[]中的内容可以省略

    <窗口函数> over ([partition by <列清单>]
                            order by <排序用列清单>)

    窗口函数大体可以分为以下两种:1.能够作为窗口函数的聚合函数(sum,avg,count,max,min)

                                                          2.rank,dense_rank。row_number等专用窗口函数。

    语法的基本使用方法:使用rank函数

    rank函数是用来计算记录排序的函数。

    select product_name, product_type, sale_price,
           rank () over (partition by product_type
    	                        order by sale_price) as ranking
    from Product;

    partition by 能够设定排序的对象范围,类似于group by语句,这里就是以product_type划分排序范围。

    order by能够指定哪一列,何种顺序进行排序。也可以通过asc,desc来指定升序降序。

    窗口函数兼具分组和排序两种功能。通过partition by分组后的记录集合称为窗口。

    然而partition by不是窗口函数所必须的:

    select product_name, product_type, sale_price,
           rank () over (order by sale_price) as ranking
    from Product;

    没有进行范围的划分,直接对全部的商品进行排序。

    专用函数的种类:1.rank函数:计算排序时,如果存在相同位次的记录,则会跳过之后的位次。

                                 2.dense_rank函数:同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。

                                 3.row_number函数:赋予唯一的连续位次。

    select product_name, product_type, sale_price,
           rank () over (order by sale_price) as ranking,
    	   dense_rank () over (order by sale_price) as dense_ranking,
    	   row_number () over (order by sale_price) as row_num
    from Product;

    由于窗口函数无需参数,因此通常括号里都是空的。

    窗口函数的适用范围:只能在select子句中使用。

    作为窗口函数使用的聚合函数:

    sum:

    select product_id, product_name, sale_price,
           sum(sale_price) over (order by product_id) as current_sum
    from Product;

    以累计的方式进行计算。

    计算出商品编号小于自己的商品的销售单价的合计值。

    avg:

    select product_id, product_name, sale_price,
           avg(sale_price) over (order by product_id) as current_sum
    from Product;

    作为的统计对象同样是排在自己之上的记录。

    1行:1000/1

    2行:(1000 + 500)/2

    3行:(1000+500+4000)/3

    ...

    计算移动平均

    窗口函数就是将表以窗口为单位进行分割,并在其中进行排序的函数。其中还包含在窗口中指定更加详细的汇总范围的备选功能,该备选功能中的汇总范围称为框架。

    指定最靠近的3行做为汇总对象:

    select product_id, product_name, sale_price,
           avg (sale_price) over (order by product_id
    	                          rows 2 preceding) as moving_avg
    from Product;

    指定框架(汇总范围):这里使用的rows(行)和preceding(之前)两个关键字,将框架指定为截止到之前?行,因此rows 2 preceding就是将框架指定为截止到之前2行,也就是将作为汇总对象的记录限定为如下的最靠近3行

    1.自身(当前记录)

    2.之前1行的记录

    3.之前2行的记录

    所以结果:

    假设当前行为3000,前1行记录为4000,前两行记录为500,所以(500+4000+3000)/3=2500

    这样的统计方法称为移动平均。

    使用关键字following(之后)替换preceding,就可以将框架改为截止到之后?行。

    将当前记录的前后行作为汇总对象:

    select product_id, product_name, sale_price,
           avg(sale_price) over (order by product_id
    	                          rows between 1 preceding and 1 following) as moving_avg--使用between规划范围,语句意思为rows 1 preceding
    																				     --到rows 1 following
    from Product;

    语句意思:1.之前1行的记录

                      2.自身(当前记录)

                      3.之后1行的记录

    整的框架就是这样

    还是假设3000为当前记录,框架计算4000为前一行记录,6800为后一行记录(4000+3000+6800)/3 = 4600

    总行数还是3.

    两个order by

    select product_name, product_type, sale_price,
           rank() over (order by product_name) as ranking
    from Product;

    这时候价格会显得混乱不堪

    可以在语句最后添加一个order by子句,来约束sale_price

    select product_name, product_type, sale_price,
           rank() over (order by product_name) as ranking
    from Product
    order by sale_price;

     

    展开全文
  • Hive窗口函数

    万次阅读 多人点赞 2017-02-08 13:34:15
    Hive窗口函数

    简介

    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析

    概念

    我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.

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

    数据准备

    我们准备一张order表,字段分别为name,orderdate,cost.数据内容如下:

    jack,2015-01-01,10
    tony,2015-01-02,15
    jack,2015-02-03,23
    tony,2015-01-04,29
    jack,2015-01-05,46
    jack,2015-04-06,42
    tony,2015-01-07,50
    jack,2015-01-08,55
    mart,2015-04-08,62
    mart,2015-04-09,68
    neil,2015-05-10,12
    mart,2015-04-11,75
    neil,2015-06-12,80
    mart,2015-04-13,94

    在hive中建立一张表t_window,将数据插入进去.

    实例

    聚合函数+over

    假如说我们想要查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现

    select name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'

    得到的结果如下:

    name    count_window_0
    mart    5
    mart    5
    mart    5
    mart    5
    jack    5

    可见其实在2015年4月一共有5次购买记录,mart购买了4次,jack购买了1次.事实上,大多数情况下,我们是只看去重后的结果的.针对于这种情况,我们有两种实现方式

    第一种:distinct

    select distinct name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'

    第二种:group by

    select name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'
    group by name

    执行后的结果如下:
    name count_window_0
    mart 2
    jack 2

    partition by子句

    Over子句之后第一个提到的就是Partition By.Partition By子句也可以称为查询分区子句,非常类似于Group By,都是将数据按照边界值分组,而Over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算.

    实例

    我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql

    select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
    from t_window

    执行结果如下:

    name    orderdate   cost    sum_window_0
    jack    2015-01-01  10  205
    jack    2015-01-08  55  205
    tony    2015-01-07  50  205
    jack    2015-01-05  46  205
    tony    2015-01-04  29  205
    tony    2015-01-02  15  205
    jack    2015-02-03  23  23
    mart    2015-04-13  94  341
    jack    2015-04-06  42  341
    mart    2015-04-11  75  341
    mart    2015-04-09  68  341
    mart    2015-04-08  62  341
    neil    2015-05-10  12  12
    neil    2015-06-12  80  80

    可以看出数据已经按照月进行汇总了.

    order by子句

    上述的场景,假如我们想要将cost按照月进行累加.这时我们引入order by子句.

    order by子句会让输入的数据强制排序(文章前面提到过,窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。Order By子句对于诸如Row_Number(),Lead(),LAG()等函数是必须的,因为如果数据无序,这些函数的结果就没有任何意义。因此如果有了Order By子句,则Count(),Min()等计算出来的结果就没有任何意义。

    我们在上面的代码中加入order by

    select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate )
    from t_window

    得到的结果如下:(order by默认情况下聚合从起始行当当前行的数据)

    name    orderdate   cost    sum_window_0
    jack    2015-01-01  10  10
    tony    2015-01-02  15  25
    tony    2015-01-04  29  54
    jack    2015-01-05  46  100
    tony    2015-01-07  50  150
    jack    2015-01-08  55  205
    jack    2015-02-03  23  23
    jack    2015-04-06  42  42
    mart    2015-04-08  62  104
    mart    2015-04-09  68  172
    mart    2015-04-11  75  247
    mart    2015-04-13  94  341
    neil    2015-05-10  12  12
    neil    2015-06-12  80  80

    window子句

    我们在上面已经通过使用partition by子句将数据进行了分组的处理.如果我们想要更细粒度的划分,我们就要引入window子句了.

    我们首先要理解两个概念:
    - 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合.
    - 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.

    当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的.每个窗口函数应用自己的规则.

    window子句:
    - PRECEDING:往前
    - FOLLOWING:往后
    - CURRENT ROW:当前行
    - UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点

    我们按照name进行分区,按照购物时间进行排序,做cost的累加.
    如下我们结合使用window子句进行查询

    select name,orderdate,cost,
    sum(cost) over() as sample1,--所有行相加
    sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加
    sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加
    sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,--和sample3一样,由起点到当前行的聚合
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, --当前行和前面一行做聚合
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,--当前行和前边一行及后面一行
    sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
    from t_window;

    得到查询结果如下:

    name    orderdate   cost    sample1 sample2 sample3 sample4 sample5 sample6 sample7
    jack    2015-01-01  10  661 176 10  10  10  56  176
    jack    2015-01-05  46  661 176 56  56  56  111 166
    jack    2015-01-08  55  661 176 111 111 101 124 120
    jack    2015-02-03  23  661 176 134 134 78  120 65
    jack    2015-04-06  42  661 176 176 176 65  65  42
    mart    2015-04-08  62  661 299 62  62  62  130 299
    mart    2015-04-09  68  661 299 130 130 130 205 237
    mart    2015-04-11  75  661 299 205 205 143 237 169
    mart    2015-04-13  94  661 299 299 299 169 169 94
    neil    2015-05-10  12  661 92  12  12  12  92  92
    neil    2015-06-12  80  661 92  92  92  92  92  80
    tony    2015-01-02  15  661 94  15  15  15  44  94
    tony    2015-01-04  29  661 94  44  44  44  94  79
    tony    2015-01-07  50  661 94  94  94  79  79  50

    窗口函数中的序列函数

    主要序列函数是不支持window子句的.

    hive中常用的序列函数有下面几个:

    NTILE

    • NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值

    • NTILE不支持ROWS BETWEEN,
      比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

    • 如果切片不均匀,默认增加第一个切片的分布

    这个函数用什么应用场景呢?假如我们想要每位顾客购买金额前1/3的交易记录,我们便可以使用这个函数.

    select name,orderdate,cost,
           ntile(3) over() as sample1 , --全局数据切片
           ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
           ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
           ntile(3) over(partition by name order by cost ) --按照name分组,在分组内按照cost升序排列,数据切成3份
    from t_window

    得到的数据如下:

    name    orderdate   cost    sample1 sample2 sample3 sample4
    jack    2015-01-01  10  3   1   1   1
    jack    2015-02-03  23  3   1   1   1
    jack    2015-04-06  42  2   2   2   2
    jack    2015-01-05  46  2   2   2   2
    jack    2015-01-08  55  2   3   2   3
    mart    2015-04-08  62  2   1   2   1
    mart    2015-04-09  68  1   2   3   1
    mart    2015-04-11  75  1   3   3   2
    mart    2015-04-13  94  1   1   3   3
    neil    2015-05-10  12  1   2   1   1
    neil    2015-06-12  80  1   1   3   2
    tony    2015-01-02  15  3   2   1   1
    tony    2015-01-04  29  3   3   1   2
    tony    2015-01-07  50  2   1   2   3

    如上述数据,我们去sample4 = 1的那部分数据就是我们要的结果

    row_number、rank、dense_rank

    这三个窗口函数的使用场景非常多
    - row_number()从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
    - RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位
    - DENSE_RANK() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位

    **注意:
    rank和dense_rank的区别在于排名相等时会不会留下空位.**

    举例如下:

    SELECT 
    cookieid,
    createtime,
    pv,
    RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
    DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
    FROM lxw1234 
    WHERE cookieid = 'cookie1';
    
    cookieid day           pv       rn1     rn2     rn3 
    
    cookie1 2015-04-12      7       1       1       1
    cookie1 2015-04-11      5       2       2       2
    cookie1 2015-04-15      4       3       3       3
    cookie1 2015-04-16      4       3       3       4
    cookie1 2015-04-13      3       5       4       5
    cookie1 2015-04-14      2       6       5       6
    cookie1 2015-04-10      1       7       6       7
    rn1: 15号和16号并列第3, 13号排第5
    rn2: 15号和16号并列第3, 13号排第4
    rn3: 如果相等,则按记录值排序,生成唯一的次序,如果所有记录值都相等,或许会随机排吧。

    LAG和LEAD函数

    这两个函数为常用的窗口函数,可以返回上下数据行的数据.
    以我们的订单表为例,假如我们想要查看顾客上次的购买时间可以这样去查询

    select name,orderdate,cost,
    lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1,
    lag(orderdate,2) over (partition by name order by orderdate) as time2
    from t_window;

    查询后的数据为:

    name    orderdate   cost    time1   time2
    jack    2015-01-01  10  1900-01-01  NULL
    jack    2015-01-05  46  2015-01-01  NULL
    jack    2015-01-08  55  2015-01-05  2015-01-01
    jack    2015-02-03  23  2015-01-08  2015-01-05
    jack    2015-04-06  42  2015-02-03  2015-01-08
    mart    2015-04-08  62  1900-01-01  NULL
    mart    2015-04-09  68  2015-04-08  NULL
    mart    2015-04-11  75  2015-04-09  2015-04-08
    mart    2015-04-13  94  2015-04-11  2015-04-09
    neil    2015-05-10  12  1900-01-01  NULL
    neil    2015-06-12  80  2015-05-10  NULL
    tony    2015-01-02  15  1900-01-01  NULL
    tony    2015-01-04  29  2015-01-02  NULL
    tony    2015-01-07  50  2015-01-04  2015-01-02

    time1取的为按照name进行分组,分组内升序排列,取上一行数据的值.

    time2取的为按照name进行分组,分组内升序排列,取上面2行的数据的值,注意当lag函数为设置行数值时,默认为1行.未设定取不到时的默认值时,取null值.

    lead函数与lag函数方向相反,取向下的数据.

    first_value和last_value

    first_value取分组内排序后,截止到当前行,第一个值
    last_value取分组内排序后,截止到当前行,最后一个值

    select name,orderdate,cost,
    first_value(orderdate) over(partition by name order by orderdate) as time1,
    last_value(orderdate) over(partition by name order by orderdate) as time2
    from t_window

    查询结果如下:

    name    orderdate   cost    time1   time2
    jack    2015-01-01  10  2015-01-01  2015-01-01
    jack    2015-01-05  46  2015-01-01  2015-01-05
    jack    2015-01-08  55  2015-01-01  2015-01-08
    jack    2015-02-03  23  2015-01-01  2015-02-03
    jack    2015-04-06  42  2015-01-01  2015-04-06
    mart    2015-04-08  62  2015-04-08  2015-04-08
    mart    2015-04-09  68  2015-04-08  2015-04-09
    mart    2015-04-11  75  2015-04-08  2015-04-11
    mart    2015-04-13  94  2015-04-08  2015-04-13
    neil    2015-05-10  12  2015-05-10  2015-05-10
    neil    2015-06-12  80  2015-05-10  2015-06-12
    tony    2015-01-02  15  2015-01-02  2015-01-02
    tony    2015-01-04  29  2015-01-02  2015-01-04
    tony    2015-01-07  50  2015-01-02  2015-01-07
    

    参考内容:
    - SQL Server中的窗口函数
    - 分析函数——排序排列(rank、dense_rank、row_number)
    - SQL中的窗口函数 OVER窗口函数

    展开全文
  • 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 子句定义相结合
    展开全文
  • hive窗口函数使用

    千次阅读 2020-09-24 11:14:24
    hive窗口函数的使用前言一、hive窗口函数语法1、over()窗口函数的语法结构1.1、over()函数中的三个函数讲解2、常与over()一起使用的分析函数2.1、聚合类2.2、排名类2.3、其他类3、窗口函数总结 前言 我们在学习hive...
  • Hive:窗口函数

    万次阅读 多人点赞 2018-09-20 00:01:35
    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分析处理)。 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、...
  • MySQL SQL窗口函数

    千次阅读 2019-11-25 13:08:19
    窗口函数解决的问题包括:1)排名问题2)top N问题 应用工作中, 面试中. 2.学习/操作 //注意,mysql版本8已至此窗口函数这个功能, 如果低于该版本, 会出现SQL报错! 一.窗口函数有什么用...
  • 本文通过案例来引出对窗口函数的认识,总结了窗口函数的用法及使用规律,该案例主要是对窗口函数在移动计算中的应用,类似于滑动窗口,所谓的滑动窗口也就是指每一行对应对应的数据窗口都不同,通过窗口子句类实现...
  • 窗口函数及OVER子句(4):聚合嵌套及窗口函数的其余问题 若觉得本文写得还可以,请多多关注本人所作书籍《C++语法详解》电子工业出版社出版,作者 黄勇 本文为原创文章,转载请注明出处,或注明转载自“黄邦勇帅(原名...
  • hive 窗口函数--常用函数介绍

    千次阅读 2020-07-19 00:25:01
    一、窗口函数概念 窗口函数是一组特殊函数,通过扫描多个输入行来计算每个输出值,为每行数据生成一行结果,可以通过窗口函数来实现复杂的计算和聚合。窗口函数也称为OLAP(Online Analytical Processing)函数,是...
  • 窗口函数的基本使用方法

    千次阅读 多人点赞 2019-07-08 12:16:18
    窗口函数简介 窗口函数又名开窗函数,属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组...
  • PostgreSQL中的窗口函数

    千次阅读 2018-04-24 11:22:10
    PostgreSQL中提供了窗口函数,一个窗口函数在一系列与当前行有某种关联的表行上进行一种计算。 一个窗口函数调用总是包含一个直接跟在窗口函数名及其参数之后的OVER子句。OVER子句决定究竟查询中的哪些行被分离出来...
  • Hive中窗口函数和侧写函数详解

    千次阅读 2020-08-23 10:04:56
     在hive中窗口函数是比较重要也是比较难理解的函数,窗口函数也叫开窗函数,意思为在数据上开一个窗来达到可以从一个大表中任何部分开始查询,而且想查几行就查几行,所以学会了窗口函数是很方便的,愿你在看到这篇...
  • SQL ——窗口函数简介

    万次阅读 2018-11-28 22:23:20
    1、窗口函数的描述 2、窗口函数中的元素 2.1、分区 2.2、排序 2.3、框架 3、支持窗口函数的查询元素 4、窗口函数类别 4.1、排名函数 4.2、分布函数 4.3、偏移函数   1、窗口函数的描述 窗口函数作用于...
  • SQL 窗口函数速查表

    千次阅读 多人点赞 2020-05-26 21:59:14
    SQL 窗口函数(分析函数)的速查表,包括窗口函数的语法、窗口函数列表(RANK、DENSE_RANK、ROW_NUMBER、LEAD、LAG、FIRST_VALUE、LAST_VALUE等)以及相关示例,内容适用于 MySQL、Oracle、SQL Server、PostgreSQL ...
  • MySQL窗口函数简介

    千次阅读 2019-01-13 21:25:54
    原文地址:https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_last-value 译文: 12.21.1Window Function Descriptions ...大多数聚合函数也可以用作窗口函数,...
  • [Hive]窗口函数与分析函数

    千次阅读 2017-02-22 14:24:52
    本文介绍了用于窗口函数和分析函数的Hive QL增强功能。所有窗口和分析函数操作都按照SQL标准。 当前版本支持以下窗口函数和分析函数:1 窗口函数LEAD 返回分区中当前行后面行(可以指定第几行)的值。 如果没有行,...
  • 数据库——窗口函数

    千次阅读 2018-10-03 12:49:55
    1、窗口函数又称为OLAP函数,主要是对数据进行实时处理,例如市场分析,创建财务报表。在oracle和SQL Server中称为分析函数。 2016年5月份,oracel,Sql Server DB2,等都支持了该功能,但是MySQL5.7还不支持。 2、...
  • SQL_窗口函数

    千次阅读 2020-03-19 22:22:31
    1.窗口函数与分析函数 看到一篇写的很好的总结了,就把链接附上把,我们主要是结合一些题目看一下具体的用法(https://blog.csdn.net/scgaliguodong123_/article/details/60135385) 1.1 窗口函数 FIRST_VALUE:取...
  • 如何理解窗口函数

    千次阅读 2017-08-22 14:06:31
    与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,...
  • spark sql窗口函数

    千次阅读 2018-09-28 22:18:42
    窗口函数是spark sql模块从1.4之后开始支持的,主要用于解决对一组数据进行操作,同时为每条数据返回单个结果,比如计算指定访问数据的均值、计算累进和或访问当前行之前行数据等,这些场景使用普通函数实现是比较...
  • 窗口函数之排名函数与分析函数

    千次阅读 2016-11-11 18:58:16
    窗口函数中的排名函数与分析函数实在是太好用了,尤其是row_number和lead 全局表如下: 排名函数  row_number  ROW_NUMBER() over (partition by name order by testid) (partition by 是可选的) 其他排名...
  • Hive窗口函数Over和排序函数Rank

    千次阅读 2018-08-13 20:33:39
    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类...
  • 本文详细介绍了hive窗口函数,即包括开窗函数和分析函数两部分。
  • MySQL操作实战(二):窗口函数

    万次阅读 多人点赞 2019-02-21 16:25:43
    窗口函数:在满足某些条件的记录集合上执行的特殊函数,对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同,窗口大小都是固定的,称为静态窗口;有的函数则相反,不同的记录对应着不同的窗口,称为滑动...
  • postgresql系列之窗口函数

    千次阅读 2018-11-11 21:39:42
    一、 窗口函数 1.1 基本概念 窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级操作;聚合函数将结果集进行计算并且通常返回一行。窗口函数也是基于结果集的运算。与聚合函数不同的是,窗口函数并...
  • hive 窗口函数,分析函数的一些理解

    千次阅读 2018-03-30 14:33:12
    先解释什么是窗口函数首先,我们要知道什么是窗口子句:需要指定一个窗口的边界,语法是这样的:ROWS betweenCURRENT ROW | UNBOUNDED PRECEDING | [num] PRECEDING AND UNBOUNDED FOLLOWING | [num] FOLLOWING| ...
  • oracle窗口函数详解

    千次阅读 2018-09-15 22:52:16
    在工作的过程中,我们有时候需要编写复杂的sql脚本.... 窗口函数的名字是over()函数,常用的有两个属性partition by和order by,partition by类似于group by,我们通常将group by叫做分组,而partition by称作分...
  • pyspark Window 窗口函数

    千次阅读 2020-07-07 15:39:03
    窗口函数 At its core, a window function calculates a return value for every input row of a table based on a group of rows, called the Frame. Every input row can have a unique frame associated with it....
  • 窗口函数和聚合函数嵌套的问题

    千次阅读 2017-03-02 16:25:42
    窗口函数是对显示出来的结果( 运算后显示在桌面)做函数加工的。不是对原来的表加工的。 如下的窗口函数,是对许多数据运营到算出count(1)后再做的窗口函数。而不是原来的原始数据

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 53,983
精华内容 21,593
关键字:

窗口函数