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

    2020-11-30 21:12:21
    Mysql窗口函数 跟拉钩数据分析训练营一起学习窗口函数 在输出结果方面:窗口函数本身也有分组与group by分组最显著的不同是窗口函数输入计算的有多少行数据,输出的也有多少行,不会像group by那样进行聚合。 窗口...

    Mysql窗口函数

    跟拉钩数据分析训练营一起学习窗口函数
    在输出结果方面:窗口函数本身也有分组与group by分组最显著的不同是窗口函数输入计算的有多少行数据,输出的也有多少行,不会像group by那样进行聚合。


    窗口函数结构:

    ***函数名([expr]) over(partition by <要分列的组> order by <要排序的列> rows between <数据范围>)***

    其中,over是关键字,用来指定函数执行的窗口范围,包含三个分析子句:分组(partition by)子句,排序(order by)子句,窗口(rows)子句,如果后面括号中什么都不写,则意味着窗口包含满 足where条件的所有行,窗口函数基于所有行进行计算。


    窗口函数内部解读:

    窗口函数(专有窗口函数+聚合类窗口函数)和普通场景下的聚合函数也很容易混淆,二者区别 如下:

    普通场景下的聚合函数是将多条记录聚合为一条(多到一);窗口函数是每条记录都会执行,有几条记录执行完还是几条(多到多)。 分组(partition by):记录按照字段进行分组,窗口函数在不同的分组上分别执行。
    分组(partition by):记录按照字段进行分组,窗口函数在不同的分组上分别执行。
    排序(order by):按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号,可 以和partition子句配合使用,也可以单独使用。如果没有partition子句,数据范围则是整 个表的数据行。
    窗口(rows):就是进行函数分析时要处理的数据范围,属于当前分区的一个子集,通常用来 作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单 的移动平均支付金额,则可以设置rows子句来创建滑动窗口(rows)。

    几个常用的窗口函数:

    1. 排序

    row_number其实算不上排序,只是给了一个行号。

    row_number() over() --顾名思义 行号

    rank() over() --排序,那种1111,4那种

    dense_rank() over() --排序,112233334这种 就是排序名没有间隔

    **substr()、substring()**函数,都是截取函数。

    2. 分类、切成几片

    ntile(n) over(partition by …A… order by …B… )

    n:切分的片数

    A:分组的字段名称 B:排序的字段名称 ntile(n),用于将分组数据按照顺序切分成n片,返回当前切片值 NTILE不支持ROWS BETWEEN。

    3.偏移函数

    Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead) 作为 独立的列。 在实际应用当中,若要用到取今天和昨天的某字段差值时,Lag和Lead函数的应用就显得尤为重要。
    lag(exp_str,offset,defval) over(partion by …order by …) lead(exp_str,offset,defval) over(partion by …order by …)
    exp_str是字段名称。 offset是偏移量,即是上1个或上N个的值,假设当前行在表中排在第5 行,则offset 为3,则表示我 们所要找的数据行就是表中的第2行(即5-3=2)。offset默认值为1。 defval默认值,当两个函数取上N/下N个值,当在表中从当前行位置向前数N行已经超出了表的 范 围时,lag()函数将defval这个参数值作为函数的返回值,若没有指定默认值,则返回NULL, 那么 在数学运算中,总要给一个默认值才不会出错。

    例子

    全年累计销售额就是窗口函数输出的结果,有多少行就会,算多少结果,一行一行的,一个窗口一个窗口的算。
    第三列全年累计销售额就是窗口函数输出的结果,初始数据有多少行就会算出多少行的结果,一行一行的,一个窗口一个窗口的算。并不会像聚合函数一样聚合。

    展开全文
  • Mysql窗口函数

    2020-10-14 16:17:45
    窗口函数的基本语法如下: <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>) <窗口函数>的位置,可以放以下两种函数: 专用窗口函数 序号函数:row_number...

    窗口函数的基本语法如下:

    <窗口函数> over (partition by <用于分组的列名>
                    order by <用于排序的列名>)
    

    <窗口函数>的位置,可以放以下两种函数:

    1. 专用窗口函数
      • 序号函数:row_number() / rank() / dense_rank()
      • 分布函数:percent_rank() / cume_dist()
      • 前后函数:lag() / lead()
      • 头尾函数:first_val() / last_val()
      • 其他函数:nth_value() (用途:返回窗口中第N个expr的值,expr可以是表达式,也可以是列名)/ntile()(用途:将分区中的有序数据分为n个桶,记录桶号
    2. 聚合函数,如sum(), avg(), count(), max(), min()等

    注意事项

    • 窗口函数对where和group by子句处理后的结果进行操作,所以原则上窗口函数只能写在select子句中
    • 聚合函数是将多条记录聚合为一条;而窗口函数是每条记录都会执行,查询结果并不会改变记录条数,有几条记录执行完还是几条
    • 普通聚合函数也可以用于窗口函数中,赋予它窗口函数的功能。
    • 窗口函数的执行顺序(逻辑上的)是在FROM,JOIN,WHERE,GROUP BY,HAVING之后,在ORDER BY,LIMIT,SELECT,DISTINCT之前。它执行时GROUP BY的聚合过程已经完成了,所以不会再产生数据聚合。

    建表

    
    create table student (sid char(2), sname char(5), sclass char(2));
    create table course (cid char(2), cname char(10));
    create table score (sid char(2), cid char(2), score int);
    
    insert into student values('01', '崔健', '01');
    insert into student values('02', '李健', '01');
    insert into student values('03', '高虎', '01');
    insert into student values('04', '子健', '01');
    insert into student values('05', '石璐', '01');
    insert into student values('06', '亚千', '01');
    insert into student values('07', '史立', '01');
    insert into student values('08', '窦唯', '01');
    insert into student values('09', '华东', '01');
    
    insert into course values('01', '金属');
    insert into course values('02', '迷幻');
    insert into course values('03', '朋克');
    insert into course values('04', '后摇');
    
    insert into score values('01', '01', 60);
    insert into score values('02', '01', 85);
    insert into score values('03', '01', 57);
    insert into score values('04', '01', 34);
    insert into score values('05', '01', 78);
    insert into score values('06', '01', 90);
    insert into score values('07', '01', 76);
    insert into score values('08', '01', 90);
    insert into score values('09', '01', 85);
    insert into score values('01', '02', 78);
    insert into score values('02', '02', 59);
    insert into score values('03', '02', 59);
    insert into score values('04', '02', 79);
    insert into score values('05', '02', 88);
    insert into score values('01', '03', 65);
    insert into score values('03', '03', 89);
    insert into score values('05', '03', 46);
    insert into score values('06', '03', 85);
    insert into score values('07', '03', 89);
    insert into score values('08', '03', 79);
    insert into score values('03', '04', 99);
    insert into score values('04', '04', 95);
    insert into score values('07', '04', 68);
    insert into score values('08', '04', 59);
    insert into score values('09', '04', 80);
    

    1.专用窗口函数

    1.1序号函数

    • row_number() 、 rank() 、 dense_rank()都是序号函数,一个例子说明三者的区别,对每门课程的成绩排序:
    SELECT s.sname, c.cname, sc.score,
    	ROW_NUMBER() OVER (PARTITION BY c.cname
    			   ORDER BY sc.score DESC) AS row_num, 
            RANK() OVER (PARTITION BY c.cname
    		     ORDER BY sc.score DESC) AS ranking,
            DENSE_RANK() OVER(PARTITION BY c.cname
    		          ORDER BY sc.score DESC) AS dense_ranking
    FROM student s INNER JOIN score sc ON s.sid = sc.sid
    	       INNER JOIN course c ON sc.cid = c.cid	
    

    在这里插入图片描述

    • row_number 相同成绩不会并列,按出现顺序排名
    • rank 相同成绩会并列,且下一名为并列排名+并列人数
    • dense_rank 相同成绩会并列,并且下一名为并列排名+1

    1.2分布函数

    • percent_rank()
      用途:和之前的RANK()函数相关,每行按照如下公式进行计算:

    (rank - 1) / (rows - 1)

    其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数。

    SELECT s.sname, c.cname, sc.score, RANK() OVER(PARTITION BY c.cname ORDER BY sc.score DESC) as ranking,
             PERCENT_RANK() OVER (PARTITION BY c.cname
    			      ORDER BY sc.score DESC) as percent																									
    FROM student s INNER JOIN score sc ON s.sid = sc.sid
    	       INNER JOIN course c ON sc.cid = c.cid
    

    在这里插入图片描述

    • cume_dist()
      用途:分组内大于等于当前rank值的行数/分组内总行数,这个函数比percen_rank使用场景更多。
      应用场景:某门课程各同学排名在前百分之几
    SELECT s.sname, c.cname, sc.score, RANK() OVER(PARTITION BY c.cname ORDER BY sc.score DESC) as ranking,
           CUME_DIST() OVER (PARTITION BY c.cname
                             ORDER BY sc.score DESC) as cumdist																									
    FROM student s INNER JOIN score sc ON s.sid = sc.sid
    	       INNER JOIN course c ON sc.cid = c.cid;
    

    在这里插入图片描述
    亚千和窦唯的金属成绩并列第一,在班级排名前22.22%

    1.3前后函数

    lag和lead函数可以在同一次查询中取出同一字段的前N行数据(lag)和后N行数据(lead)
    语法:

    LAG(EXP_STR,OFFSET,DEFVAL)OVER()
    
    LEAD(EXP_STR,OFFSET,DEFVAL)OVER()
    

    EXP_STR:要取的列
    OFFSET: 取偏移后的第几行数据
    DEFVAL:无偏移值的取值,默认为NULL

    应用场景:求每个用户相邻两次浏览的时间差;求每个同学相邻两门考试的成绩差

    SELECT s.sname, c.cname, sc.score,
           lead(sc.score,1) OVER (PARTITION BY s.sname
                                  ORDER BY sc.score DESC) as leadVal,
           lag(sc.score,1) OVER (PARTITION BY s.sname
                                 ORDER BY sc.score DESC) as lagVal,
           score - leadVal as diff1,
           score - lagVal as diff2
    FROM student s INNER JOIN score sc ON s.sid = sc.sid
    	       INNER JOIN course c ON sc.cid = c.cid;
    

    在这里插入图片描述

    1.4头尾函数

    • first_val()/last_val()
      用途:得到分区中的第一个/最后一个指定参数的值。
    SELECT s.sname, c.cname, sc.score,
           FIRST_VALUE(sc.score) OVER (PARTITION BY s.sname
                                       ORDER BY sc.score DESC) as firstVal,
           LAST_VALUE(sc.score) OVER (PARTITION BY s.sname
                                      ORDER BY sc.score DESC) as lastVal
    FROM student s INNER JOIN score sc ON s.sid = sc.sid
    	       INNER JOIN course c ON sc.cid = c.cid
    

    在这里插入图片描述

    1.5其他函数

    • nth_value()
    SELECT s.sname, s.sclass, c.cname, sc.score,
           nth_value(sc.score,1) OVER (PARTITION BY s.sname
                                       ORDER BY sc.score DESC) as 1th,
           nth_value(sc.score,2) OVER (PARTITION BY s.sname
                                       ORDER BY sc.score DESC) as 2th
    FROM student s INNER JOIN score sc ON s.sid = sc.sid
                   INNER JOIN course c ON sc.cid = c.cid
    

    在这里插入图片描述

    • ntile()
      • 用途:将分区中的有序数据分为n个桶,记录桶号。
      • 此函数在数据分析中应用较多,比如由于数据量大,需要将数据平均分配到N个并行的进程分别计算,此时就可以用NFILE(N)对数据进行分组,由于记录数不一定被N整除,所以数据不一定完全平均,多出来的部分则依次加给第一组、第二组···直到分配完。
    SELECT
    	sid, cid, score, ntile(4) over(partition by sid order by score desc) as tile 
    DROM
    	score;
    

    在这里插入图片描述

    2.聚合函数

    聚合函数用作窗口函数分为两种情况

    2.1 不指定分组

    SELECT
       sid, cid, score,
       sum(score) over (order by sid) as current_sum,
       avg(score) over (order by sid) as current_avg,
       count(score) over (order by sid) as current_count,
       max(score) over (order by sid) as current_max,
       min(score) over (order by sid) as current_min
    FROM 
       score
    

    在这里插入图片描述

    • 结果解释
      聚合函数sum在窗口函数中,是对自身记录、及位于自身记录以上的数据进行求和的结果。当排序字段有相同值时,会对这些相同值执行一次聚合函数。
      如按照sid排序,sid为01的记录有三条,则对这三条记录执行聚合函数,并在此基础上再进行接下来的累积(相当于把这三条重复记录执行一次聚合函数,然后看成一行)

    排序字段没有重复的情况:

    select sid, cid, score,
       sum(score) over (order by sid,cid) as current_sum,
       avg(score) over (order by sid,cid) as current_avg,
       count(score) over (order by sid,cid) as current_count,
       max(score) over (order by sid,cid) as current_max,
       min(score) over (order by sid,cid) as current_min
    from score
    

    在这里插入图片描述

    • 进行的是累积操作,在每一个新的行都对包括此行在内的之前所有行执行一次聚合函数。

    2.2 指定分组

    select cid, sid, score,
       sum(score) over (partition by cid order by sid) as current_sum,
       avg(score) over (partition by cid order by sid) as current_avg,
       count(score) over (partition by cid order by sid) as current_count,
       max(score) over (partition by cid order by sid) as current_max,
       min(score) over (partition by cid order by sid) as current_min
    from score
    

    在这里插入图片描述
    体现了窗口
    根据cid分窗,在每个cid中执行2.1中不分窗的操作

    2.3 滑动窗口

    1、unbounded preceding:从当前分区的第一行开始,到当前行结束。
    2、current row:从当前行开始,也结束于当前行。
    3、[numeric expression] preceding:对于rows来说从当前行之前的第[numeric expression]行开始(共[numeric expression]+1行),到当前行结束。对range来说从小于数值表达式的值开始,到当前行结束。
    4、[numeric expression] following:与[numeric expression] preceding相反。

    展开全文
  • mysql窗口函数(mysql版本8 ):1.涉及到排名问题,可以使用窗口函数2.专用窗口函数rank, dense_rank, row_number有什么区别呢?它们的区别我举个例子,你们一下就能看懂:select *,rank() over (order by 成绩 desc) ...

    mysql窗口函数(mysql版本8 ):

    1.涉及到排名问题,可以使用窗口函数

    2.专用窗口函数rank, dense_rank, row_number有什么区别呢?

    它们的区别我举个例子,你们一下就能看懂:

    select *,

    rank() over (order by 成绩 desc) as ranking,

    dense_rank() over (order by 成绩 desc) as dese_rank,

    row_number() over (order by 成绩 desc) as row_num

    from 班级

    结果:

    1)rank函数:如果有并列名次的行,会占用下一名次的位置。

    2)dense_rank函数:如果有并列名次的行,不占用下一名次的位置。

    3)row_number函数:不考虑并列名次的情况。

    其他:

    对于“累计”问题,要想到用聚合函数作为窗口函数。比如累计求和,用sum。

    sum(列名) over (partition byorder by)

    累计求平均值,用avg。

    avg(列名) over (partition byorder by)

    可用聚合函数最为窗口函数:

    select 雇员编号,薪水,sum(薪水) over(order by 雇员编号) as累计薪水

    from薪水表

    总结:

    select 列1,列2,sum(列2) over (order by 列1) as 累计值

    展开全文
  • mysql 窗口函数

    2020-03-24 21:38:31
    从version 8.0开始,MySQL支持在查询中使用窗口函数。 CREATE TABLE sales( sales_employee VARCHAR(50) NOT NULL, fiscal_year INT NOT NULL, sale DECIMAL(14,2) NOT NULL, PRIMARY KEY(sales_employee,...

    从version 8.0开始,MySQL支持在查询中使用窗口函数。

    CREATE TABLE sales(
        sales_employee VARCHAR(50) NOT NULL,
        fiscal_year INT NOT NULL,
        sale DECIMAL(14,2) NOT NULL,
        PRIMARY KEY(sales_employee,fiscal_year)
    );
     
    INSERT INTO sales(sales_employee,fiscal_year,sale)
    VALUES('Bob',2016,100),
          ('Bob',2017,150),
          ('Bob',2018,200),
          ('Alice',2016,150),
          ('Alice',2017,100),
          ('Alice',2018,200),
           ('John',2016,200),
          ('John',2017,150),
          ('John',2018,250);

    使用sum聚合查询
    SELECT 
        fiscal_year, 
        sales_employee,
        sale,
        SUM(sale) OVER (PARTITION BY fiscal_year) total_sales
    FROM
        sales;
    +-------------+----------------+--------+-------------+
    | fiscal_year | sales_employee | sale   | total_sales |
    +-------------+----------------+--------+-------------+
    |        2016 | Alice          | 150.00 |      450.00 |
    |        2016 | Bob            | 100.00 |      450.00 |
    |        2016 | John           | 200.00 |      450.00 |
    |        2017 | Alice          | 100.00 |      400.00 |
    |        2017 | Bob            | 150.00 |      400.00 |
    |        2017 | John           | 150.00 |      400.00 |
    |        2018 | Alice          | 200.00 |      650.00 |
    |        2018 | Bob            | 200.00 |      650.00 |
    |        2018 | John           | 250.00 |      650.00 |
    +-------------+----------------+--------+-------------+
    9 rows in set (0.00 sec)

    这里,sum()函数充当了窗口函数,得到了根据fiscal_year计算出的sale的总和total_sales列,但是又不像它作为聚合函数使用时一样,这里的结果保留了每一行的信息。
    原因就在于窗口函数的执行顺序(逻辑上的)是在FROM,JOIN,WHERE,GROUP BY,HAVING之后,在ORDER BY,LIMIT,SELECT DISTINCT之前。它执行时GROUP BY的聚合过程已经完成了,所以不会再产生数据聚合。

    窗口函数的语法
    窗口函数的语法是

    window_function_name(expression) 
        OVER (
            [partition_defintion]
            [order_definition]
            [frame_definition]
        )
     

    先指定作为窗口函数的函数名,然后是OVER(…),就算OVER里面没有内容,括号也需要保留。
    窗口函数的一个概念是当前行,当前行属于某个窗口,窗口由“[partition_defintion]”,“[order_definition]”,“[frame_definition]“确定。

    partition_defintion
    翻译过来应该是分区,语法是"PARTITION BY < expression>[{,< expression>…}]",它会根据单个或者多个表达式的计算结果来分区(列名也是一种表达式,它的结果就是列名本身)。在前面的例子中,结果中的每一行都有自己的分区,total_sales列的值就是它所属的分区里面的sum(sale)的结果。
    frame_definition
    这里先讲frame_definition,可能应该是叫帧吧。它的作用是在分区里面再进一步细分窗口。语法是"frame_unit {< frame_start>|< frame_between>}",frame_unit有两种,分别是ROWS和RANGE,由ROWS定义的frame是由开始和结束位置的行确定的,由RANGE定义的frame由在某个值区间的行确定。
    如果只指定了frame的开始位置,那么结束位置就默认为当前行。frame_start有三种:
    UNBOUNDED PRECEDING: 区间的第一行
    N PRECEDING: 当前行之前的N行,N可以是数字,也可以是一个能计算出数字的表达式
    CURRENT ROW: 当前行
    frame_between的可以取的值如下:

    frame_start:如前面所列
    UNBOUNDED FOLLOWING:区间的最后一行
    N FOLLOWING:当前行之后的N行,N可以是数字,也可以是一个能计算出数字的表达式
    如果没显式指定frame的话,MySQL会认为frame是“ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”
     

    例子:

    SELECT 
       fiscal_year, 
       sales_employee,
       sale,
       SUM(sale) OVER (PARTITION BY sales_employee 
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total_sales
    FROM
       sales;
    +-------------+----------------+--------+-------------+
    | fiscal_year | sales_employee | sale   | total_sales |
    +-------------+----------------+--------+-------------+
    |        2016 | Alice          | 150.00 |      150.00 |
    |        2017 | Alice          | 100.00 |      250.00 |
    |        2018 | Alice          | 200.00 |      450.00 |
    |        2016 | Bob            | 100.00 |      100.00 |
    |        2017 | Bob            | 150.00 |      250.00 |
    |        2018 | Bob            | 200.00 |      450.00 |
    |        2016 | John           | 200.00 |      200.00 |
    |        2017 | John           | 150.00 |      350.00 |
    |        2018 | John           | 250.00 |      600.00 |
    +-------------+----------------+--------+-------------+
    9 rows in set (0.00 sec)

    第一行所在的区间是sales_employee为Alice的区间,所在的帧是区间第一行到当前行,只有一行,total_sales为150;第二行所在的区间是sales_employee为Alice的区间,所在的帧是区间第一行到当前行,有两行,total_sales为150+100=250;第一行所在的区间是sales_employee为Alice的区间,所在的帧是区间第一行到当前行,有三行,total_sales为150+100+200=450。

    order_definition
    定义了分区内的行的排列顺序,语法是“ORDER BY < expression> [ASC|DESC], [{,< expression>…}]”。
     

    https://blog.csdn.net/m0_38063172/article/details/83789830

    展开全文
  • Mysql 窗口函数

    2020-11-28 17:05:04
    专用窗口函数rank, dense_rank, row_number 排序问题: 例: 题目:下图是"班级"表中的内容,记录了每个学生所在班级,和对应的成绩。 select *, rank() over (order by 成绩 desc) as ranking, dense_rank() over...
  • mysql窗口函数

    2020-08-14 15:35:33
    专用的窗口函数 CUME_DIST() CUME_DIST() OVER ( PARTITION BY expr, … ORDER BY expr [ASC | DESC], … ) 计算公式是: ROW_NUMBER() / total_rows 示例: 在这里插入代码片 ROW_NUMBER() / total_rows 3. ...

空空如也

空空如也

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

mysql窗口函数

mysql 订阅