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
galaChe
V2EX  ›  MySQL

这段 SQL 大佬能给点优化建议吗?统计一个月每天的数据还有更加简洁的写法吗?目前运行速度太慢了

  •  
  •   galaChe · 2019-09-25 10:35:52 +08:00 · 8997 次点击
    这是一个创建于 1647 天前的主题,其中的信息可能已经有所发展或是发生改变。

    SELECT su.real_name AS realName, sd.name AS deptName, su.user_name AS userName, ahh.* FROM ( SELECT ROUND( SUM( IF ( DAY ( ah.audit_time ) = '1', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '1', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '2', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '2', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '3', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '3', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '4', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '4', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '5', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '5', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '6', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '6', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '7', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '7', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '8', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '8', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '9', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '9', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '10', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '10', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '11', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '11', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '12', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '12', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '13', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '13', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '14', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '14', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '15', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '15', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '16', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '16', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '17', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '17', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '18', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '18', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '19', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '19', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '20', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '20', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '21', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '21', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '22', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '22', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '23', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '23', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '24', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '24', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '25', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '25', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '26', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '26', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '27', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '27', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '28', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '28', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '29', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '29', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '30', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '30', ROUND( SUM( IF ( DAY ( ah.audit_time ) = '31', ah.audit_num * ifnull( kpi.kpi_value, 1 ), NULL ) ), 2 ) AS '31', SUM( ( ROUND( ( ah.audit_num * ifnull( kpi.kpi_value, 1 ) ), 2 ) ) ) AS auditCount, ah.account_number AS empNumber, ah.emp_name AS employeeName, SUM( ROUND( ( ah.pass_num * ifnull( kpi.kpi_value, 1 ) ), 2 ) ) AS passCount, SUM( ROUND( ( ah.delete_Num * ifnull( kpi.kpi_value, 1 ) ), 2 ) ) AS delCount FROM audit_days AS ah LEFT JOIN skill_group_kpi AS kpi ON ( ah.group_name = kpi.group_name ) AND kpi.is_deleted = 0 WHERE 1 = 1 GROUP BY ah.account_number ORDER BY NULL ) ahh LEFT JOIN sys_user su ON su.qq LIKE concat( '%', ahh.empNumber, '%' ) AND ahh.empNumber != 0 LEFT JOIN sys_department sd ON sd.id = su.department_id

    第 1 条附言  ·  2019-09-25 11:50:58 +08:00
    执行过程 https://s2.ax1x.com/2019/09/25/uVmI81.png
    SQL https://s2.ax1x.com/2019/09/25/uVmWE4.png

    第一次用 V2EX 发帖子,不会排版和发图片。带个大家困扰了不好意思。

    为什么没有拆分表? 这个功能使用频率很低用的人也很少 后期也会更改这个 SQL 就不会用了,新建表会带来代码和数据的冗余。

    为什么有 on like 这个其实已经改了贴的是老 sql。不过用 = 会带来别的问题。这些数据可以理解为爬虫。源头不受控制写太死也不好,这里不表。

    感谢嘲讽冷嘲热讽你们戾气太重了,如果嘲讽可以帮到你请继续。

    感谢各位回答,我其实好奇这种月按天统计、或者天按时统计一定要增加表吗?

    再次感谢耐心回答。另外吐槽一下 V2EX 官网回答一个帖子,邮箱手机号都要求绑定了不绑定不让回帖这样好吗?
    74 条回复    2019-09-26 09:30:40 +08:00
    polymerdg
        1
    polymerdg  
       2019-09-25 10:38:13 +08:00
    这一坨 看着好难受
    dovme
        2
    dovme  
       2019-09-25 10:40:52 +08:00
    格式化一下,然后截图发图片,你这个看都不想看
    sethverlo
        3
    sethverlo  
       2019-09-25 10:41:27 +08:00
    没排版,没 explain, 甚至都没个业务逻辑说明…这咋看…
    liprais
        4
    liprais  
       2019-09-25 10:44:57 +08:00
    坐等楼下说把数据读出来在应用里手写逻辑处理
    arrow8899
        5
    arrow8899  
       2019-09-25 10:51:31 +08:00
    把数据读出来在应用里手写逻辑处理
    leexy
        6
    leexy  
       2019-09-25 10:51:58 +08:00
    把数据读出来在应用里手写逻辑处理
    woodensail
        7
    woodensail  
       2019-09-25 10:52:12 +08:00
    把数据读出来在应用里手写逻辑处理
    zzz686970
        8
    zzz686970  
       2019-09-25 10:52:17 +08:00
    有一点是为什么没有每一天 date 的字段,在这个基础上进行 left join 和 group by 不是更简单快捷么
    a2532521
        9
    a2532521  
       2019-09-25 11:04:28 +08:00
    喷了..穷举
    junan008
        10
    junan008  
       2019-09-25 11:08:56 +08:00   ❤️ 1
    新增统计表,每天统计啊。。
    CallMeReznov
        11
    CallMeReznov  
       2019-09-25 11:11:00 +08:00   ❤️ 1
    要统计每天的数据,肯定是每天统计然后单独出表,你这个.......
    ZXCDFGTYU
        12
    ZXCDFGTYU  
       2019-09-25 11:11:13 +08:00
    这条 SQL 惊为天人,骨骼惊奇,秀的我头皮发麻
    ZXCDFGTYU
        13
    ZXCDFGTYU  
       2019-09-25 11:11:26 +08:00
    程序员写个代码不容易,放过孩子吧
    claysec
        14
    claysec  
       2019-09-25 11:13:33 +08:00
    我的天。。当初是怎么撸出来的
    sunziren
        15
    sunziren  
       2019-09-25 11:17:12 +08:00
    @a2532521 :doge
    fuchaofather
        16
    fuchaofather  
       2019-09-25 11:22:22 +08:00
    吐了, 这谁能看. 不变更现有逻辑, 改离线模式呢?
    wps353
        17
    wps353  
       2019-09-25 11:23:29 +08:00
    AP 业务?
    nnnToTnnn
        18
    nnnToTnnn  
       2019-09-25 11:26:22 +08:00   ❤️ 2
    ```
    SELECT su.real_name AS realName, sd.name AS deptName, su.user_name AS userName, ahh.*
    FROM (
    SELECT
    ROUND(SUM(IF(DAY(ah.audit_time) = '1', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '1'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '2', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '2'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '3', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '3'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '4', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '4'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '5', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '5'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '6', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '6'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '7', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '7'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '8', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '8'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '9', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '9'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '10', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '10'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '11', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '11'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '12', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '12'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '13', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '13'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '14', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '14'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '15', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '15'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '16', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '16'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '17', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '17'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '18', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '18'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '19', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '19'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '20', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '20'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '21', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '21'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '22', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '22'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '23', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '23'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '24', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '24'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '25', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '25'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '26', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '26'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '27', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '27'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '28', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '28'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '29', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '29'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '30', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '30'
    , ROUND(SUM(IF(DAY(ah.audit_time) = '31', ah.audit_num * ifnull(kpi.kpi_value, 1), NULL)), 2) AS '31'
    , SUM(ROUND(ah.audit_num * ifnull(kpi.kpi_value, 1), 2)) AS auditCount
    , ah.account_number AS empNumber, ah.emp_name AS employeeName
    , SUM(ROUND(ah.pass_num * ifnull(kpi.kpi_value, 1), 2)) AS passCount
    , SUM(ROUND(ah.delete_Num * ifnull(kpi.kpi_value, 1), 2)) AS delCount
    FROM audit_days ah
    LEFT JOIN skill_group_kpi kpi
    ON ah.group_name = kpi.group_name
    AND kpi.is_deleted = 0
    WHERE 1 = 1
    GROUP BY ah.account_number
    ORDER BY NULL
    ) ahh
    LEFT JOIN sys_user su
    ON su.qq LIKE concat('%', ahh.empNumber, '%')
    AND ahh.empNumber != 0
    LEFT JOIN sys_department sd ON sd.id = su.department_id

    ```

    我看了你的 SQL,特么有个问题想问下你


    ```
    ON su.qq LIKE concat('%', ahh.empNumber, '%')
    ```

    关联表的时候不应该强关联么? 怎么还有一个 on like? 哪个大神能解释一下?


    其次

    自己跑下解析计划,就不就知道哪里慢了? 我这里有没有数据,光看 SQL 估计有点难,目前就看出来了一个 on like 很叼,叼爆了
    HansCathy
        19
    HansCathy  
       2019-09-25 11:27:15 +08:00   ❤️ 1
    在内存里面处理啊
    phantomzz
        20
    phantomzz  
       2019-09-25 11:27:40 +08:00   ❤️ 1
    能否每天做一次 snapshot ?
    nnnToTnnn
        21
    nnnToTnnn  
       2019-09-25 11:28:34 +08:00
    其次,这种粘贴复制的,连代码都不格式化一下的,我觉得这对帮你解决问题的人,很不友好.


    真的是日了狗了,我严重怀疑你写代码也是不是不注重格式
    mirrorpen
        22
    mirrorpen  
       2019-09-25 11:30:51 +08:00
    看到这一坨 SQL 语句,头皮发麻,手脚冰凉...
    neoblackcap
        23
    neoblackcap  
       2019-09-25 11:31:57 +08:00   ❤️ 1
    粗略地看了一下,你这段 SQL 是分日期统计吧。那么就分表计算啊,每天跑定时任务,按小时,天,月分别建汇总表。你分析起来就简单了啦
    ccgoing10
        24
    ccgoing10  
       2019-09-25 11:32:59 +08:00   ❤️ 1
    oracle 有个分析函数可以一次性把每天的数据分别汇总,你这目测不是 oracle 数据库
    yusen01
        25
    yusen01  
       2019-09-25 11:35:20 +08:00
    看傻了
    Yourshell
        26
    Yourshell  
       2019-09-25 11:35:24 +08:00
    我就想问一下你们数据库大牛都这样处理数据的吗?
    nnnToTnnn
        27
    nnnToTnnn  
       2019-09-25 11:37:01 +08:00   ❤️ 1
    @nnnToTnnn 那个 sql 大神能否讲解一下

    on 和 where 的区别


    我一直以为

    on 是指两个表关联的条件
    where 指的是过滤条件

    on 使用过滤条件相比 where 查询会快吗?
    lblblong
        28
    lblblong  
       2019-09-25 11:39:26 +08:00
    从你这段 sql 我只能看出我以前写的那都不叫 sql
    littleshy
        29
    littleshy  
       2019-09-25 11:42:20 +08:00
    作为一个后端,我只想说:隔行如隔山。
    nicevar
        30
    nicevar  
       2019-09-25 12:03:15 +08:00   ❤️ 1
    以前我也是一股脑的在 SQL 上使劲耗,后来把数据放到 MongoDb 终于消停了,有条件的话把数据同步到 MongoDb,做数据分析会减轻很大的工作量
    aguesuka
        31
    aguesuka  
       2019-09-25 12:24:33 +08:00   ❤️ 1
    @nnnToTnnn 以前我也纠结过这个问题,后来看学会执行计划后发现都一样
    lolizeppelin
        32
    lolizeppelin  
       2019-09-25 12:28:42 +08:00 via Android
    不换数据库没有 多核都用不了的渣渣
    chengyiqun
        33
    chengyiqun  
       2019-09-25 12:34:51 +08:00
    这种就应该用 job 扫描数据, 然后单独按天汇总表.
    dswyzx
        34
    dswyzx  
       2019-09-25 12:41:49 +08:00   ❤️ 1
    fishCatcher
        35
    fishCatcher  
       2019-09-25 12:44:20 +08:00 via iPhone
    @nnnToTnnn 你竟然看完了 屌爆了
    zichen
        36
    zichen  
       2019-09-25 13:57:04 +08:00
    前公司一堆这样的 sql 语句的作业在跑,面相数据库编程……几百行的存储过程我都改过。
    Macolor21
        37
    Macolor21  
       2019-09-25 14:05:07 +08:00   ❤️ 2
    老哥,佩服你写 SQL 的毅力。这个要是我自己写,写完就看不懂了。

    程序员最好不要写这么长的 SQL 或者代码。主要是后续维护特别麻烦,大家也没有嘲讽的意思,你冷静一点。

    常用做法是开 job 跑每天任务,这样以后维护排查会方便一些,统计在一个 job 服务,查看再另一个报表,算某个方面的解耦,希望对你有帮助。
    Michaelssss
        38
    Michaelssss  
       2019-09-25 14:12:04 +08:00   ❤️ 1
    如果觉得慢。。。还是把这种 cpu 操作移到应用服务器吧,或者用 PL/SQL 的游标可能能简化点写法。。。
    magicsilence
        39
    magicsilence  
       2019-09-25 14:25:23 +08:00   ❤️ 1
    sql 也是语言啊。
    劝别人不要写这么长的 sql, 不如自己不要写这么长代码。
    作为统计 sql 来说,这个 sql 根本不算长,真是隔行如何隔山...

    我提供个思路,如果实在优化不动,可以试试 presto。
    galaChe
        40
    galaChe  
    OP
       2019-09-25 14:29:20 +08:00
    @Macolor21 一些特殊对内的功能还是有很多长 SQL (形成原因也五花八门比如技术债务之类),当然我这个 SQL 确实写得不怎么样。您说的方法如果是个比较稳定的功能我也会这么写。至于没有用 MongoDB 涉及到一些多表联查不方便有些数据又写太死也不好。

    至于嘲讽是开始下面有些为评论而评论,当时看了心里有些不忿。其实这个是我的原因没有发过帖子,导致 SQL 格式混乱。针对这个问题下的所有和问题相关的回答我也都感谢送铜币。

    再次感谢回答,如果功能稳定我会进行数据拆分整合的。不在入口优化就在出口优化或者减少计算量还是知道的。提这个问题主要还是自己 SQL 确实写得比较少。想看看大家有没有不一样的 SQL 思路。

    谢谢回答!
    changdy
        41
    changdy  
       2019-09-25 14:34:11 +08:00   ❤️ 1
    1 对 audit_time 进行增加 Date() 虚拟列,并创建索引 (当然也可以创建分区), 不要使用 if 判断.分成 31 条 sql 分别执行
    2 看看能不能推迟 left join 在 group 之后
    ------
    楼上说 sql 复杂的...这其实已经算是比较简单的 sql 了...23333
    Marstin
        42
    Marstin  
       2019-09-25 14:35:47 +08:00   ❤️ 1
    1、on 一般用于强关联的,你这样用 like 真的极少见,同时,ahh.empNumber != 0 建议加在查询出 ahh 结果的 where 条件中,会提升性能,增加可读性。
    2、建议把你从 1 号到 31 号这个日期穷举判断的逻辑放到业务代码中,不要分这么多列,在代码中完成这一部分的任务会简单很多,不需要这样穷举,也会规避一些错误,效率也会有所提升。
    3、分区,走时间索引,我在你的 sql 里没有看到时间查询条件
    5、join 的使用,最左边应该是 user 表,然后按照有效 user 添加信息,你是直接取有效 kpi,再关联人员数据,对应人员的 kpi 数据缺失时,容错性不足
    4、我严重怀疑表结构的设计有问题,建议发表结构出来,你这个逻辑其实很简单,就四张表联查,不要听上面说得那么恐怖。看具体数据量,一个月分区数据不超过百万条,三秒钟以内
    Marstin
        43
    Marstin  
       2019-09-25 14:37:26 +08:00   ❤️ 1
    @changdy 是的,就四张表,无语。果然 v2 是前端的天下
    jaylee4869
        44
    jaylee4869  
       2019-09-25 14:42:24 +08:00
    硬件优化,逃(
    CamWang
        45
    CamWang  
       2019-09-25 14:43:56 +08:00 via Android   ❤️ 1
    替 v2 说句话,上面要求所有论坛发帖均实名制。
    aguesuka
        46
    aguesuka  
       2019-09-25 14:45:57 +08:00   ❤️ 1
    首先,like 可以改成 instr,最好改成 =;然后给 join 加上索引.然后把日期的查询和总数的查询拆成两个查询. 如果两个查询只有一个慢,重点改慢的查询,如果都慢,改外面的 join. 我猜是时间的问题 ,建议一个账号的数据不要放在一样而是拆成 31 行,然后在 sql 里合并表头或者放程序里处理
    ```

    GROUP BY ah.account_number ,DAY(ah.audit_time)

    ```
    这是非常简单的四表联查,几万条数据而已就算没有索引也不应该超过 1 秒
    admintest001
        47
    admintest001  
       2019-09-25 15:09:45 +08:00
    每天跑个定时任务统计前一天的数据,跑完之后数据存起来,此外,跑之前去读一下当月前几天的数据存不存在,若不存在,则再往前多查一天,依此类推
    npe
        48
    npe  
       2019-09-25 15:21:51 +08:00   ❤️ 1
    说点别的:
    1.常用的报表统计,如果有条件可以上大数据分析。
    2.不常用的报表,可以做成一个任务中心,采用离线、异步、阻塞的方式供用户使用。
    lygmqkl
        49
    lygmqkl  
       2019-09-25 15:24:14 +08:00   ❤️ 1
    每每看到这种 SQL 我都想说,这压根不是 SQL 的锅,项目初期如果能设计的恰当,数据库自然能兼容到,压根不应该出现这样的场景。

    其次 即使不得已遇到这样的情况,也应该做内存级计算,而不是 SQL 级计算,虽然 sql 有 group, like, on blabla, 但是这真不是这样用的。真的哪怕不用 go, python, 你用 php 的 cli 读出来处理都会快几十倍 甚至更多。

    最后,这段 SQL 真的很 low,上面的各位已经很留情了。
    janus77
        50
    janus77  
       2019-09-25 15:31:54 +08:00
    咋了,我感觉楼上嘲讽的不多啊,好像不超过 5 条回复?这就忍不住了吗?
    大部分都是说让你改方案,这难道算嘲讽吗?
    <del>还是说都被我 b 了所以看不到……</del>
    l00t
        51
    l00t  
       2019-09-25 16:15:53 +08:00
    @Marstin #42 我觉得你没完全看懂。它是 left join,ahh 里的数据是不能丢的,每条都要,不可以加 where 过滤的。同理,交换 left join 两边的表也是不可行的,直接把业务逻辑都改了。
    Marstin
        52
    Marstin  
       2019-09-25 16:38:43 +08:00
    @l00t 我是出于业务角度去考虑修改的,不好意思,可能确实有失妥当。
    目前的查询逻辑是查所有 kpi 信息,那么就可能查出来没有对应人员的 kpi 数据,且不能查出哪些人员的 kpi 数据缺失,
    我认为应该是针对所有有效人员去查 kpi。(该业务逻辑确实是我的问题,我还是坚持这种方式更好点,哈哈)

    基于以上考虑
    account_number AS empNumber 且 GROUP BY ah.account_number 那么 account_number 为空,应该是脏数据了,在 where 中去掉应该没问题,where 中可以加上 ahh.empNumber != 0

    最费解的是他这里 account_number 应该就是账户信息了,怎么用 qq 去匹配,如果 qq 对应的就是 account_number 字段,那就直接=,如果有生成逻辑,那就要冗余一个字段吧,用 qq 的 like 匹配肯定会有问题啊。
    123456 就会匹配到 1234567 12345678 的数据
    l00t
        53
    l00t  
       2019-09-25 16:55:59 +08:00   ❤️ 1
    @Marstin #52 我也觉得你对他的业务逻辑的质疑是很有道理的哈。qq 这里,我推测它是存在一个人有多个 qq 的情况,然后存一个字段里了,甚至这个字段可能还夹了别的不相干的文本内容,而 qq 的不同位数的问题它又没考虑进去。
    akmissxt
        54
    akmissxt  
       2019-09-25 17:02:15 +08:00   ❤️ 1
    lz 下次可以试试这个 [Carbon]( https://carbon.now.sh/)
    Marstin
        55
    Marstin  
       2019-09-25 17:04:51 +08:00   ❤️ 1
    @l00t 问题很多,估计这老哥也是接盘,以后有得玩了
    smallpython
        56
    smallpython  
       2019-09-25 17:08:32 +08:00
    楼主对于自己不喜欢的回复可以选择隐藏处理
    pieors
        57
    pieors  
       2019-09-25 17:11:34 +08:00 via Android
    @ccgoing10 MySQL 数据库
    CruelMoon
        58
    CruelMoon  
       2019-09-25 17:16:31 +08:00
    看执行计划
    realpg
        59
    realpg  
       2019-09-25 17:26:11 +08:00 via Android
    写商业软件出身的吧

    上次面了一个就是这套路 人家毫秒级的非得给你写个秒级的单 SQ 解决问题
    galaChe
        60
    galaChe  
    OP
       2019-09-25 17:27:31 +08:00
    @lygmqkl 其实是知道 low 的。上面大佬说的方案我是知道的。考虑到这个功能的不确定,就不想在其他方面进行优化了(怕麻烦之后懒得清理这些冗余代码)。钻了牛角尖死磕 SQL 所以提了这个问题,希望看看大家是如何处理这类问题的,也算是学习一下见贤思齐。
    stevenkang
        61
    stevenkang  
       2019-09-25 17:31:01 +08:00
    ```sql
    SELECT DATE_FORMAT( audit_time, "%Y-%m-%d" ) date, audit_num * ifnull(kpi_value, 1) from (
    select ah.audit_time, ah.audit_num, kpi.kpi_value from audit_days ah left join skill_group_kpi kpi on ah.group_name = kpi.group_name
    ) t where audit_time BETWEEN "2019-08-01" and "2019-09-01" group by date
    ```
    leapV3
        62
    leapV3  
       2019-09-25 17:32:05 +08:00
    每天凌晨一个定时器,跑昨天的统计任务,然后记录
    galaChe
        63
    galaChe  
    OP
       2019-09-25 17:33:38 +08:00
    @realpg 是懒,一考虑只是个过渡功能。就想一个 SQL 走下去后面方便删除代码和数据不会有遗留冗余。确实不应该有点敷衍。
    realpg
        64
    realpg  
       2019-09-25 17:35:18 +08:00   ❤️ 1
    @galaChe #63
    这跟懒一毛钱关系都没有
    一个搞互联网开发的工程师,无论懒不懒都写不出这种 SQL 来
    而且对于普遍的了解性能的工程师,他们写你这种更费劲
    c6h6benzene
        65
    c6h6benzene  
       2019-09-25 17:37:08 +08:00
    我没看明白那堆 AS 1-31 部分的作用是什么,如果只是按天统计的话 GROUP BY 到天不就好了吗...
    galaChe
        66
    galaChe  
    OP
       2019-09-25 17:41:29 +08:00
    @realpg 请问写一个临时低频功能会建表做汇总吗?功能抛弃之后代码和冗余表处理流程能够分享学习一下吗?
    VEEX6
        67
    VEEX6  
       2019-09-25 17:49:24 +08:00
    艺术家,跪拜
    halk
        68
    halk  
       2019-09-25 18:00:13 +08:00
    写过比这长得多的 hql,真是痛苦
    lygmqkl
        69
    lygmqkl  
       2019-09-25 19:27:00 +08:00
    @galaChe 其实也没啥 做技术的 都是一个坑一个坑 填过来的,国内公司的一些习惯真的 只能摇摇头。。 表示理解吧。
    noah9999
        70
    noah9999  
       2019-09-25 21:21:43 +08:00
    v2 应该有付费机制了
    mingl0280
        71
    mingl0280  
       2019-09-26 06:25:03 +08:00
    SELECT ... from select 是真的骚操作……
    首先你是取的一个月的数据,先把数据按月 select 出来,group by 天数,然后前端再处理下是不是更好?
    luoqeng
        72
    luoqeng  
       2019-09-26 08:42:20 +08:00
    统计用时序数据库
    ebony0319
        73
    ebony0319  
       2019-09-26 09:27:10 +08:00
    如果是 PostgreSQL 我会这样写:
    ```Sql
    select i::date from generate_series('2019-06-01', '2019-06-30', '1 day'::interval) i
    ```
    如果是其他数据库我会这样写:

    ```Sql
    with t as
    (
    select cast('2019-06-01' as date) as time
    union all
    select dateadd(day ,1,t.time) from t where t.time<getdate()
    )
    select * from t
    ```

    大意就是先自己构建一个日期迭代器,然后通过日期迭代器左关联查询。这样也可以解决 某些日期没有,但是需要显示为 0 的日期。
    ebony0319
        74
    ebony0319  
       2019-09-26 09:30:40 +08:00
    ![]( )
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5397 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 06:51 · PVG 14:51 · LAX 23:51 · JFK 02:51
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.