V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
ditie
V2EX  ›  MySQL

小白求教: mysql 怎么实现排序后新增一列并分区段赋值,比如分数排名前 20%的就是绩效 A,后 10%的就是绩效 C

  •  
  •   ditie · 2019-05-26 09:40:52 +08:00 · 7509 次点击
    这是一个创建于 2017 天前的主题,其中的信息可能已经有所发展或是发生改变。

    比如数据表 kaohe 的字段有 id,department,date,userid,score。

    需求有三个: 1、怎么根据 score 在一个语句里分月度、分部门进行组内排序,以及当月全体排序?

    2、怎么能多一列来显示自动分布的绩效?排名前 20%的就是绩效 A,后 10%的就是绩效 C

    3、怎么把这个排序和新增后的绩效结果全部插入到一个新表里?

    我是 MySQL 数据库,木有 row_number ()函数

    67 条回复    2019-05-28 13:02:38 +08:00
    tairan2006
        1
    tairan2006  
       2019-05-26 10:10:10 +08:00 via Android
    答案是用 Python
    lihongjie0209
        2
    lihongjie0209  
       2019-05-26 10:33:15 +08:00   ❤️ 1
    不懂 sql 的话直接在代码里实现.
    leriou
        3
    leriou  
       2019-05-26 10:36:51 +08:00
    case when
    DefoliationM
        4
    DefoliationM  
       2019-05-26 10:45:44 +08:00 via Android
    很简单 使用游标
    ditie
        5
    ditie  
    OP
       2019-05-26 10:57:48 +08:00
    @tairan2006 并不会呀。。。
    ditie
        6
    ditie  
    OP
       2019-05-26 10:58:01 +08:00
    @lihongjie0209 代码里更不会了。。。
    Cat73
        7
    Cat73  
       2019-05-26 10:58:12 +08:00
    MySQL 有 count,有 limit,麻烦点的思路可以先查记录的 id,然后 where id in ( ... )
    ditie
        8
    ditie  
    OP
       2019-05-26 10:58:54 +08:00
    @leriou case when 我会,但怎么表示前 20%这种分布?怎么进行总排序和组内排序?
    ditie
        9
    ditie  
    OP
       2019-05-26 10:59:05 +08:00
    @DefoliationM 游标。。。我百度了下都没明白。。。
    ditie
        10
    ditie  
    OP
       2019-05-26 10:59:44 +08:00
    @Cat73 想想就头大呀大佬
    Huelse
        11
    Huelse  
       2019-05-26 11:03:08 +08:00
    呃,我的理念是,任何多余的 where 条件都不写,全部交给语言去处理
    ditie
        12
    ditie  
    OP
       2019-05-26 11:06:39 +08:00
    @Huelse 我已经听不懂了啊大佬
    francis59
        13
    francis59  
       2019-05-26 11:40:26 +08:00
    太复杂的话要么写程序分析,要么复制到 excel 里用公式分析,excel 有不少统计分析的公式
    Alexhohom
        14
    Alexhohom  
       2019-05-26 11:41:09 +08:00
    前 20%可以在代码里实现
    thedog
        15
    thedog  
       2019-05-26 11:50:38 +08:00
    用 rank() over(partition by ....)
    您可以搜一下这个东西的用法
    thedog
        16
    thedog  
       2019-05-26 11:51:07 +08:00
    哦,没有 row_number,那当我没说。。。
    ditie
        17
    ditie  
    OP
       2019-05-26 12:24:07 +08:00
    @francis59 就是想后台 sql 直接出结果,前端网页就查看
    ditie
        18
    ditie  
    OP
       2019-05-26 12:24:19 +08:00
    @thedog 给热心的你点赞
    ditie
        19
    ditie  
    OP
       2019-05-26 12:24:43 +08:00
    @Alexhohom 是指什么代码呀,我前端似乎是 nodejs, 数据库就是 mysql 5.7
    lolizeppelin
        20
    lolizeppelin  
       2019-05-26 15:57:24 +08:00   ❤️ 1
    over 是窗口函数 mysql 8.0 mariadb 10.2 以后支持
    zeraba
        21
    zeraba  
       2019-05-26 16:01:57 +08:00 via Android
    搜索 mysql 添加行号,有了行号有序的列表实现这个需求应该很简单了
    Takamine
        22
    Takamine  
       2019-05-26 18:28:50 +08:00 via Android
    换 postgerSQL。:doge:
    Takamine
        23
    Takamine  
       2019-05-26 18:29:36 +08:00 via Android
    @Takamine postgreSQL。_(:з」∠)_
    sinchuan
        24
    sinchuan  
       2019-05-26 18:48:41 +08:00 via iPhone
    存储过程。尽量语句分开写,方便日后有变更。
    Umenezumi
        25
    Umenezumi  
       2019-05-26 19:57:23 +08:00
    求总 Count 然后根据需要 when case
    zxcslove
        26
    zxcslove  
       2019-05-26 19:59:25 +08:00 via Android
    插句话,假设有并列名次遇到区段边界如何处理?
    ditie
        27
    ditie  
    OP
       2019-05-26 20:04:06 +08:00
    @zxcslove 脑壳痛。。。问到了这么尖锐的问题。。
    ditie
        28
    ditie  
    OP
       2019-05-26 20:04:38 +08:00
    @lolizeppelin 5.7 版本呀,我倒想换呢,无奈我说了不算
    ditie
        29
    ditie  
    OP
       2019-05-26 20:05:35 +08:00
    @Takamine 抓住一个吉他手
    ditie
        30
    ditie  
    OP
       2019-05-26 20:05:54 +08:00
    @zeraba。。。请问有了行号之后该怎么做
    ditie
        31
    ditie  
    OP
       2019-05-26 20:06:15 +08:00
    @sinchuan mysql 不是存储过程就是个笑话么。。
    ditie
        32
    ditie  
    OP
       2019-05-26 20:06:32 +08:00
    @Umenezumi 麻烦给点具体语句示例
    acehow
        33
    acehow  
       2019-05-26 20:27:06 +08:00 via Android
    MySQL8 支持 over 开窗函数。
    ditie
        34
    ditie  
    OP
       2019-05-26 20:29:39 +08:00
    @acehow 无奈我的 是 5.7 版本呀
    webdisk
        35
    webdisk  
       2019-05-26 20:30:33 +08:00
    弄完之后发现自己的绩效是 C
    strcmp
        36
    strcmp  
       2019-05-26 20:37:31 +08:00
    redis zset
    zander1024
        37
    zander1024  
       2019-05-26 20:50:36 +08:00
    @ditie mysql 存储不是笑话... 代码明明很好实现你一定要数据库实现那就存储,游标吧
    ditie
        38
    ditie  
    OP
       2019-05-26 21:15:08 +08:00
    @zander1024 真心求教下,这个“代码”是指什么,怎么个写法呢?我是真的不会呀
    ditie
        39
    ditie  
    OP
       2019-05-26 21:15:20 +08:00
    @webdisk 整个人都不好了
    ditie
        40
    ditie  
    OP
       2019-05-26 21:15:44 +08:00
    @strcmp 这又是个什么路子,大佬再给点清晰的指导
    Alexhohom
        41
    Alexhohom  
       2019-05-26 21:16:03 +08:00
    @ditie #19 比如你根据一些需求排序后,你的 query 里会有 count 属性,然后代码里想要控制多少数目都行。
    Alexhohom
        42
    Alexhohom  
       2019-05-26 21:17:39 +08:00
    @ditie #40 想要分月度就 group by month 分部门就 group by department 同时 group by month,department
    ditie
        43
    ditie  
    OP
       2019-05-26 21:30:49 +08:00
    @Alexhohom group by 我会了。现在碰到俩问题:

    背景:数据表 kaohe 的字段有 id,department,date,userid,score。数据表 staff 有 userid,username,department

    1、我有 100 个员工,当月可能只有 30 条员工的考核记录,那么最后 group by 员工号统计时另外 70 人就没有月度(KH.date)的值,请问该怎么让这 70 个人的结果里也有一致的月度值。语句是这样:
    select KH.date,ST.userid,St.username,sum(KH.score)+100 as total
    from staff as ST
    left join on kaohe as KH on ST.userid=KH.userid
    group by KH.date,KH.userid


    2、count 属性该怎么体现或者加工出来,每个月给前 20%的人赋值为 A,后 10%的人为 C,其他人为 B
    Alexhohom
        44
    Alexhohom  
       2019-05-26 21:39:05 +08:00
    1. sql server 中可以这样(case sum(KH.score)+100 when 100 then -1 else sum(KH.score)+100 end) as total,其中-1 是你自己确定的。
    2. count 在代码中执行完 query 会有一个返回吧,就是你需要从数据库中取值的那个变量。count 可以帮助你遍历你的查询
    ditie
        45
    ditie  
    OP
       2019-05-26 21:58:24 +08:00
    @Alexhohom 完了,这两条我都没看明白 - -@ 。第一个点是用来实现什么效果的呢?第二个 count 我还是不懂是指什么,count(*) 这样的记录数?
    Alexhohom
        46
    Alexhohom  
       2019-05-26 22:04:45 +08:00
    @ditie #45 1. 就相当于 sql 里面的 if 语句, 如果考核表没有记录,那么 sum(kh.score)+100=100,发现这样的记录使其 total=-1。
    2. 第二个就是你执行 query 后,没有报错会返回一个 result (我刚刚查的,没用过 nodejs ),result 中应该有 count 相关属性吧。result.count 这类的。
    ditie
        47
    ditie  
    OP
       2019-05-26 22:08:58 +08:00
    @Alexhohom 嗯,第一点我知道 sql 这么写执行后的结果,但这个结果和我的需求没关系呀,还是不能分出百分比然后赋值;第二点这个返回的 result 不就是后台执行 sql 的结果么?你的意思是 sql 不动了、在前台用代码来实现分出百分比然后赋值的效果?然而这个前台的代码我也不会。。。
    Alexhohom
        48
    Alexhohom  
       2019-05-26 22:17:45 +08:00
    @ditie #47 对的,sql 语句排序不行就用后台代码实现,换一种思路。第一个不是解决让其他 70 个人的值一致嘛
    mmdsun
        49
    mmdsun  
       2019-05-26 22:52:57 +08:00 via Android
    最终显示效果是怎么样的?这样?
    (月 score 部门,月 score 全体,绩效)
    xuanbg
        50
    xuanbg  
       2019-05-27 07:19:40 +08:00
    分 3 次操作就简单了呀,先不要管什么 ABC,把数据存进去。然后再把排序在前 20%的更新成 A,最后把倒序前 10%更新成 C。
    楼上那些无脑查询让代码处理的,就没遇到过数据量太大导致磁盘 IO 和网络 IO 飞起么?
    DRcoding
        51
    DRcoding  
       2019-05-27 09:19:08 +08:00
    有了序号就简单了:
    SELECT (@i := @i + 1) as ord FROM XXXX ,(SELECT @i := 0) i ORDER BY .....
    ratel
        52
    ratel  
       2019-05-27 09:32:27 +08:00
    可以增加另外的统计表,不建议太复杂的 SQL
    ditie
        53
    ditie  
    OP
       2019-05-27 09:36:20 +08:00
    @Alexhohom 昨晚睡觉了。。。这么操作数据是一致成-1 了,但是并不是一致成月度日期呀
    ditie
        54
    ditie  
    OP
       2019-05-27 09:38:00 +08:00
    @mmdsun 是的,我想要的数据是结果是 月度,部门,员工 ID,score,部门内排名,绩效(根据部门内排名的百分比分布来给 ABC ),全公司排名
    ditie
        55
    ditie  
    OP
       2019-05-27 09:39:02 +08:00
    @DRcoding 嗯,我通过这个实现了部门内部的排名,还差两个:一个是分出百分比来给绩效、第二个是全公司排名
    ditie
        56
    ditie  
    OP
       2019-05-27 09:39:54 +08:00
    @xuanbg 分三次,是要写存储过程么,这个又超出了我的技术水平。。。
    ditie
        57
    ditie  
    OP
       2019-05-27 09:40:38 +08:00
    @ratel 那就是楼上说的分步骤操作?
    Beeethoven
        58
    Beeethoven  
       2019-05-27 09:56:59 +08:00
    为什么一定要一个 sql 查出来呢,后台代码实现要简单很多而且性能高很多,一次把需要的数据全部查出来,然后分类输出就好了
    Beeethoven
        59
    Beeethoven  
       2019-05-27 10:03:05 +08:00
    java 的话就新建一个 examine 实体,department,date,userid,score 四个属性,再加一个 rank 来分类,根据条件查出来然后循环赋值 rank 给前端显示需要的结果。

    插入到新表中就用代码 insert 回去就好了
    Alexhohom
        60
    Alexhohom  
       2019-05-27 10:38:56 +08:00
    @ditie #53 order by month,total 这样是先按月度排序,然后按 total 排序。
    xuanbg
        61
    xuanbg  
       2019-05-27 11:02:50 +08:00
    @ditie 其实是 4 次,1 次 insert,3 次 update,不需要存储过程,当然,你把这 3 个操作写到一个存储过程里面也是可以的。
    tudouxiong
        62
    tudouxiong  
       2019-05-27 11:05:51 +08:00 via Android
    窗口函数,需要 mysql 8 以后
    LeeSeoung
        63
    LeeSeoung  
       2019-05-27 14:00:05 +08:00
    先查一遍总数 按分数倒序排 ,然后 case when 判断 rownum<total*0.2 则 A ……这样可以不
    ditie
        64
    ditie  
    OP
       2019-05-28 13:01:25 +08:00
    @Beeethoven 是的,现在打算分步骤了,不打算一步到位。说实话,java 我一点都不会
    ditie
        65
    ditie  
    OP
       2019-05-28 13:01:47 +08:00
    @Alexhohom 好的,后面我试试
    ditie
        66
    ditie  
    OP
       2019-05-28 13:02:17 +08:00
    @xuanbg 那我试一下,确实还是分步骤好操作
    ditie
        67
    ditie  
    OP
       2019-05-28 13:02:38 +08:00
    @LeeSeoung rownum<total*0.2 这个方式好,厉害了,谢谢大佬
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5530 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 07:40 · PVG 15:40 · LAX 23:40 · JFK 02:40
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.