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

    2020-07-31 16:10:11
    Mysql 开窗函数Mysql8.0+ 中可以得以使用,实在且好用。 row number() over rank() over dense rank() ntile() 我们先上测试数据,是不同姓名,不同课程的分数表; /*测试数据*/ CREATE TABLE `school_...

    Mysql 开窗函数在Mysql8.0+ 中可以得以使用,实在且好用。

    • row number() over
    • rank() over
    • dense rank()
    • ntile() 

    我们先上测试数据,是不同姓名,不同课程的分数表;

    复制代码

    /*测试数据*/
    CREATE TABLE `school_score` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` char(1) DEFAULT NULL,
        `course` char(10) DEFAULT NULL,
      `score`  int (2) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ;
    
    INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (1, 'A','Chinese',80);
    INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (2, 'B','Chinese',90);
    INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (3, 'C','Chinese',70);
    INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (4, 'A','Math',70);
    INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (5, 'B','Math',100);
    INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (6, 'C','Math',80);
    INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (7, 'A','English',90);
    INSERT INTO `test`.`school_score`(`id`, `name`,`course`,`score`) VALUES (8, 'B','English',85);
    INSERT INTO `test`.`school_score` (`id`, `name`,`course`,`score`) VALUES (9, 'C','English',99);

    复制代码

     

    1. row number() over

    复制代码

    /*开窗函数和排名类函数结合,看每个课程的排名*/
    
    SELECT
        `name`,
        `course`,
        `score`,
        row_number ( ) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank 
    FROM
        `test`.`school_score`;

    复制代码

    结果👇:

     

    复制代码

        /*使用开窗函数计算每个课程分数最高的一个*/
        
    SELECT
        * 
    FROM
        ( SELECT `name`, `course`, `score`, row_number ( ) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank FROM `test`.`school_score` ) AS a 
    WHERE
        a.score_rank = 1;

    复制代码

    结果👇:

     

     

     

     

     

    复制代码

        /*第二部分:开窗函数和SUM() ,AVG() 等聚合函数结合*/
        
    SELECT
        `name`,
        `course`,
        `score`,
        SUM( score ) over ( PARTITION BY `course` ) AS course_score_total ,
        round(AVG(score) over (PARTITION BY `course`),2)  as  course_score_avg
    FROM
        `test`.`school_score`;

    复制代码

    结果👇:

    复制代码

    /* SUM(score) over (PARTITION BY `course` ORDER BY score ASC)   如果执行这个语句,就是在每个
    课程对分数进行累加*/
    
    SELECT
        `name`,
        `course`,
        `score`,
        SUM(score) over (PARTITION BY `course` ORDER BY score ASC ) as course_score_total
    FROM
    `test`.`school_score`;

    复制代码

     

     

     

    思考🤔: 有order by ,按照排序连续累加;无order by ,计算partition by 后的和;over() 中没有partition by ,计算所有数据总和

    同时,order by 的asc 和 desc 的排序不同,有order by 的结果也不一样。

     

    2. row number() over , rank() over ,dense rank() 三者对比。

    复制代码

    create table students_score(
        id int(4)  auto_increment primary key,
        name varchar(50) not null, 
        score int(4) not null
        );
        
        insert into students_score(name,score) values
        ('A', 300),
        ('B', 240),
        ('C', 250), 
        ('D', 280), 
        ('E', 240), 
        ('F', 200);
        

    复制代码

     

    执行👇语句:

    复制代码

    SELECT
        `id`,
        `name`,
        rank ( ) over ( ORDER BY score DESC ) AS r,
        DENSE_RANK ( ) OVER ( ORDER BY score DESC ) AS dense_r,
        row_number ( ) OVER ( ORDER BY score DESC ) AS row_r 
    FROM
        students_score;

    复制代码

     

     👆 看图🤔区别,就可以知道三者的排名的区别了,如果我是校长,我希望可以按照 DENSE_RANK() 的排序,公平且可以激励着一代代莘莘学子。

     

    3.ntile()分组

    复制代码

    SELECT
        `id`,
        `name`,
     score,
     ntile(3) over (order by score desc)  as n
    FROM
        students_score;

    复制代码

    展开全文
  • mysql 开窗函数

    2021-01-07 14:23:19
    场景1: 取max 表结构 create table tb_test_info( student_id char(4) comment '学号', grade_id int comment '课程号', score int comment '分数', primary key (student_id,grade_id) );...
  • MySql开窗函数

    2020-06-22 10:57:47
    Mysql中支持的开窗函数有很多,这里重点给大家介绍三个:row_number(),rank() ,dense_rank() row_number:不管排名是否有相同的,都按照顺序1,2,3……n rank:排名相同的名次一样,同一排名有几个,后面排名就会跳过...

    #Key word :Partiton by & order by
    <开窗函数> over ([PARTITION by <列清单>]
    Order by <排序用列清单>)

    Mysql中支持的开窗函数有很多,这里重点给大家介绍三个:row_number(),rank()
    ,dense_rank()
    row_number:不管排名是否有相同的,都按照顺序1,2,3……n
    rank:排名相同的名次一样,同一排名有几个,后面排名就会跳过几次
    dense_rank:排名相同的名次一样,且后面名次不跳跃在这里插入图片描述

    展开全文
  • mysql开窗函数

    2016-04-11 17:01:58
    开窗函数又叫OLAP函数。 mysql没有开窗算法,等价写法: select modify_name,shotel_id,modify_time,op_type from ( select if(@part...
    开窗函数又叫OLAP函数。
    mysql没有开窗算法,等价写法:

    select modify_name,shotel_id,modify_time,op_type 
    from 
    (

    select if(@part1=ff.modify_name&&@part2=ff.shotel_id,@rank:=@rank+1,@rank:=1) rank,
     @part1:=ff.modify_name modify_name,
     @part2:=ff.shotel_id shotel_id,
     ff.modify_time,ff.op_type
     from 
     (
        select  a.modify_name,a.shotel_id ,a.modify_time, a.op_type
         from temp_table a
         where 
         op_type = 3     
         and
         modify_time BETWEEN DATE_FORMAT('2016-04-10 00:00:00', '%Y-%m-%e %H:%i:%s') 
        AND DATE_FORMAT('2016-04-10 23:59:59', '%Y-%m-%e %H:%i:%s')
        order by  a.modify_name asc ,a.shotel_id asc,a.modify_time desc -- 排序
    ) ff,
    (select @rank:=0,@part1='',@part2='') tt

    ) AA
    where rank=1 ;

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29665621/viewspace-2079149/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/29665621/viewspace-2079149/

    展开全文
  • 本文以门店销售收入为例。 使用场景 表结构与数据如下: 创建表结果 建表语句如下: -- 创建门店收入表 create table store_income( id INT UNSIGNED KEY AUTO_INCREMENT, storeName varchar(50), -- 店名 ...

    本文以门店销售收入为例。

    使用场景

    表结构与数据如下:
    在这里插入图片描述
    统计每个月总收入top2的门店:
    在这里插入图片描述

    创建表结果

    建表语句如下:

    -- 创建门店收入表
    create table store_income(
       id INT UNSIGNED KEY AUTO_INCREMENT,   
       storeName varchar(50),     -- 店名
       incomeDate datetime,       -- 收入时间
       income decimal(15,2),      -- 收入金额
       updateName varchar(30)     -- 记录人
    
    );
    

    插入数据

    插入数据语句如下:

    insert into store_income(storeName,incomeDate,income,updateName) values
    ('天通苑家乐福店','2020-01-01 09:23:34',3001.89,'赵四'),
    ('天通苑家乐福店','2020-01-01 09:21:34',101.89,'赵四'),
    ('天通苑家乐福店','2020-01-02 10:23:34',501.89,'赵四'),
    ('天通苑家乐福店','2020-01-02 11:01:34',36.78,'赵四'),
    ('天通苑家乐福店','2020-02-01 09:23:34',231.00,'赵四'),
    ('天通苑家乐福店','2020-02-01 09:21:34',345.54,'赵四'),
    ('天通苑家乐福店','2020-01-03 13:23:34',3001.89,'赵四'),
    ('天通苑家乐福店','2020-01-03 14:21:34',567.12,'赵四'),
    ('天通苑家乐福店','2020-01-02 15:23:34',129,'赵四'),
    ('天通苑家乐福店','2020-01-02 18:01:34',267,'赵四'),
    ('天通苑家乐福店','2020-02-10 09:23:34',10009,'赵四'),
    ('天通苑家乐福店','2020-02-21 09:21:34',3890,'赵四'),
    
    ('霍营家乐福店','2020-01-01 09:23:34',5433.43,'王五'),
    ('霍营家乐福店','2020-01-01 15:23:34',789.53,'王五'),
    ('霍营家乐福店','2020-01-12 09:23:34',10.22,'王五'),
    ('霍营家乐福店','2020-01-14 09:23:34',200,'王五'),
    ('霍营家乐福店','2020-01-20 09:23:34',12332,'王五'),
    ('霍营家乐福店','2020-01-22 09:23:34',3098,'王五'),
    ('霍营家乐福店','2020-02-01 09:23:34',432,'王五'),
    ('霍营家乐福店','2020-02-01 15:23:34',789.11,'王五'),
    ('霍营家乐福店','2020-02-12 09:23:34',34.1,'王五'),
    ('霍营家乐福店','2020-02-14 09:23:34',109.22,'王五'),
    ('霍营家乐福店','2020-02-20 09:23:34',212.33,'王五'),
    ('霍营家乐福店','2020-02-22 09:23:34',1293,'王五');
    

    插入数据之后如下图:
    在这里插入图片描述

    解题思路

    1.计算每个门店每个月的收入总额
    2.对所有门店每个月的总收入排行
    3.统计每个月总收入top2的门店
    sql如下:

    select storeName,dateMonth,sumincome
    from (
    select storeName,dateMonth,sumincome,row_number() over(partition by dateMonth order by sumincome desc) paixu
    from (
    	select storeName,date_format(incomeDate,'%Y-%m') dateMonth ,sum(income) sumincome
    	from store_income
    	group by storeName,date_format(incomeDate,'%Y-%m')
    ) as a 
    ) as b 
    where paixu <= 2
    

    最后结果:
    在这里插入图片描述

    展开全文
  • Hive&Mysql开窗函数

    2019-12-16 18:01:40
    Mysql实现开窗 [不好用建议迁移oracle或者impala] SUM AVG MIN MAX SELECT id , date_time , pv , -- 从第一行到当前行进行sum SUM ( pv ) OVER ( PARTITION BY id ORDER BY date_time ) AS ...
  • 语法 MySQL 8.0之后,开始支持开窗函数; 语法格式:row_number() over(partition by 分组列 order by 排序列 desc) row_number() over()分组排序功能: 在使用 row_number() over()函数时候,over()里头的分组以及...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 411
精华内容 164
关键字:

mysql开窗函数

mysql 订阅