› 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
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
这段 SQL 大佬能给点优化建议吗？统计一个月每天的数据还有更加简洁的写法吗？目前运行速度太慢了
galaChe · 2019-09-25 10:35:52 +08:00 · 9260 次点击这是一个创建于 1879 天前的主题，其中的信息可能已经有所发展或是发生改变。
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 官网回答一个帖子，邮箱手机号都要求绑定了不绑定不让回帖这样好吗？
SQL https://s2.ax1x.com/2019/09/25/uVmWE4.png
第一次用 V2EX 发帖子，不会排版和发图片。带个大家困扰了不好意思。
为什么没有拆分表？ 这个功能使用频率很低用的人也很少 后期也会更改这个 SQL 就不会用了，新建表会带来代码和数据的冗余。
为什么有 on like 这个其实已经改了贴的是老 sql。不过用 = 会带来别的问题。这些数据可以理解为爬虫。源头不受控制写太死也不好，这里不表。
感谢嘲讽冷嘲热讽你们戾气太重了，如果嘲讽可以帮到你请继续。
感谢各位回答，我其实好奇这种月按天统计、或者天按时统计一定要增加表吗？
再次感谢耐心回答。另外吐槽一下 V2EX 官网回答一个帖子，邮箱手机号都要求绑定了不绑定不让回帖这样好吗？
 |
1
polymerdg 2019-09-25 10:38:13 +08:00
这一坨 看着好难受
|
 |
2
dovme 2019-09-25 10:40:52 +08:00
格式化一下，然后截图发图片，你这个看都不想看
|
 |
3
sethverlo 2019-09-25 10:41:27 +08:00
没排版，没 explain, 甚至都没个业务逻辑说明…这咋看…
|
 |
4
liprais 2019-09-25 10:44:57 +08:00
坐等楼下说把数据读出来在应用里手写逻辑处理
|
 |
5
arrow8899 2019-09-25 10:51:31 +08:00
把数据读出来在应用里手写逻辑处理
|
 |
6
leexy 2019-09-25 10:51:58 +08:00
把数据读出来在应用里手写逻辑处理
|
 |
7
woodensail 2019-09-25 10:52:12 +08:00
把数据读出来在应用里手写逻辑处理
|
 |
8
zzz686970 2019-09-25 10:52:17 +08:00
有一点是为什么没有每一天 date 的字段，在这个基础上进行 left join 和 group by 不是更简单快捷么
|
 |
9
a2532521 2019-09-25 11:04:28 +08:00
喷了..穷举
|
 |
10
junan008 2019-09-25 11:08:56 +08:00  1
新增统计表，每天统计啊。。
|
 |
11
CallMeReznov 2019-09-25 11:11:00 +08:00 1
要统计每天的数据,肯定是每天统计然后单独出表,你这个.......
|
 |
12
ZXCDFGTYU 2019-09-25 11:11:13 +08:00
这条 SQL 惊为天人，骨骼惊奇，秀的我头皮发麻
|
|
13
ZXCDFGTYU 2019-09-25 11:11:26 +08:00
程序员写个代码不容易，放过孩子吧
|
 |
14
claysec 2019-09-25 11:13:33 +08:00
我的天。。当初是怎么撸出来的
|
 |
16
fuchaofather 2019-09-25 11:22:22 +08:00
吐了, 这谁能看. 不变更现有逻辑, 改离线模式呢?
|
 |
17
wps353 2019-09-25 11:23:29 +08:00
AP 业务？
|
 |
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 很叼,叼爆了 |
 |
19
HansCathy 2019-09-25 11:27:15 +08:00 1
在内存里面处理啊
|
 |
20
phantomzz 2019-09-25 11:27:40 +08:00 1
能否每天做一次 snapshot ？
|
|
21
nnnToTnnn 2019-09-25 11:28:34 +08:00
其次,这种粘贴复制的,连代码都不格式化一下的,我觉得这对帮你解决问题的人,很不友好.
真的是日了狗了,我严重怀疑你写代码也是不是不注重格式 |
 |
22
mirrorpen 2019-09-25 11:30:51 +08:00
看到这一坨 SQL 语句，头皮发麻，手脚冰凉...
|
 |
23
neoblackcap 2019-09-25 11:31:57 +08:00 1
粗略地看了一下，你这段 SQL 是分日期统计吧。那么就分表计算啊，每天跑定时任务，按小时，天，月分别建汇总表。你分析起来就简单了啦
|
 |
24
ccgoing10 2019-09-25 11:32:59 +08:00 1
oracle 有个分析函数可以一次性把每天的数据分别汇总，你这目测不是 oracle 数据库
|
 |
25
yusen01 2019-09-25 11:35:20 +08:00
看傻了
|
 |
26
Yourshell 2019-09-25 11:35:24 +08:00
我就想问一下你们数据库大牛都这样处理数据的吗？
|
|
27
nnnToTnnn 2019-09-25 11:37:01 +08:00 1
|
 |
28
lblblong 2019-09-25 11:39:26 +08:00
从你这段 sql 我只能看出我以前写的那都不叫 sql
|
 |
29
littleshy 2019-09-25 11:42:20 +08:00
作为一个后端，我只想说：隔行如隔山。
 |
 |
30
nicevar 2019-09-25 12:03:15 +08:00 1
以前我也是一股脑的在 SQL 上使劲耗，后来把数据放到 MongoDb 终于消停了，有条件的话把数据同步到 MongoDb，做数据分析会减轻很大的工作量
|
 |
32
lolizeppelin 2019-09-25 12:28:42 +08:00 via Android
不换数据库没有 多核都用不了的渣渣
|
 |
33
chengyiqun 2019-09-25 12:34:51 +08:00
这种就应该用 job 扫描数据, 然后单独按天汇总表.
|
 |
34
dswyzx 2019-09-25 12:41:49 +08:00 1
|
 |
35
fishCatcher 2019-09-25 12:44:20 +08:00 via iPhone
@nnnToTnnn 你竟然看完了 屌爆了
|
 |
36
zichen 2019-09-25 13:57:04 +08:00
前公司一堆这样的 sql 语句的作业在跑，面相数据库编程……几百行的存储过程我都改过。
|
 |
37
Macolor21 2019-09-25 14:05:07 +08:00 2
老哥，佩服你写 SQL 的毅力。这个要是我自己写，写完就看不懂了。
程序员最好不要写这么长的 SQL 或者代码。主要是后续维护特别麻烦，大家也没有嘲讽的意思，你冷静一点。 常用做法是开 job 跑每天任务，这样以后维护排查会方便一些，统计在一个 job 服务，查看再另一个报表，算某个方面的解耦，希望对你有帮助。 |
 |
38
Michaelssss 2019-09-25 14:12:04 +08:00 1
如果觉得慢。。。还是把这种 cpu 操作移到应用服务器吧，或者用 PL/SQL 的游标可能能简化点写法。。。
|
 |
39
magicsilence 2019-09-25 14:25:23 +08:00 1
sql 也是语言啊。
劝别人不要写这么长的 sql, 不如自己不要写这么长代码。 作为统计 sql 来说，这个 sql 根本不算长，真是隔行如何隔山... 我提供个思路，如果实在优化不动，可以试试 presto。 |
 |
40
galaChe OP @Macolor21 一些特殊对内的功能还是有很多长 SQL （形成原因也五花八门比如技术债务之类），当然我这个 SQL 确实写得不怎么样。您说的方法如果是个比较稳定的功能我也会这么写。至于没有用 MongoDB 涉及到一些多表联查不方便有些数据又写太死也不好。
至于嘲讽是开始下面有些为评论而评论，当时看了心里有些不忿。其实这个是我的原因没有发过帖子，导致 SQL 格式混乱。针对这个问题下的所有和问题相关的回答我也都感谢送铜币。 再次感谢回答，如果功能稳定我会进行数据拆分整合的。不在入口优化就在出口优化或者减少计算量还是知道的。提这个问题主要还是自己 SQL 确实写得比较少。想看看大家有没有不一样的 SQL 思路。 谢谢回答！ |
 |
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 |
 |
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、我严重怀疑表结构的设计有问题，建议发表结构出来，你这个逻辑其实很简单，就四张表联查，不要听上面说得那么恐怖。看具体数据量，一个月分区数据不超过百万条，三秒钟以内 |
 |
44
jaylee4869 2019-09-25 14:42:24 +08:00
硬件优化，逃（
|
 |
45
CamWang 2019-09-25 14:43:56 +08:00 via Android 1
替 v2 说句话，上面要求所有论坛发帖均实名制。
|
 |
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 秒 |
 |
47
admintest001 2019-09-25 15:09:45 +08:00
每天跑个定时任务统计前一天的数据，跑完之后数据存起来，此外，跑之前去读一下当月前几天的数据存不存在，若不存在，则再往前多查一天，依此类推
|
 |
48
npe 2019-09-25 15:21:51 +08:00 1
说点别的：
1.常用的报表统计，如果有条件可以上大数据分析。 2.不常用的报表，可以做成一个任务中心，采用离线、异步、阻塞的方式供用户使用。 |
 |
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，上面的各位已经很留情了。 |
 |
50
janus77 2019-09-25 15:31:54 +08:00
咋了，我感觉楼上嘲讽的不多啊，好像不超过 5 条回复？这就忍不住了吗？
大部分都是说让你改方案，这难道算嘲讽吗？ <del>还是说都被我 b 了所以看不到……</del> |
 |
51
l00t 2019-09-25 16:15:53 +08:00
@Marstin #42 我觉得你没完全看懂。它是 left join，ahh 里的数据是不能丢的，每条都要，不可以加 where 过滤的。同理，交换 left join 两边的表也是不可行的，直接把业务逻辑都改了。
|
|
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 的数据 |
|
53
l00t 2019-09-25 16:55:59 +08:00 1
@Marstin #52 我也觉得你对他的业务逻辑的质疑是很有道理的哈。qq 这里，我推测它是存在一个人有多个 qq 的情况，然后存一个字段里了，甚至这个字段可能还夹了别的不相干的文本内容，而 qq 的不同位数的问题它又没考虑进去。
|
 |
54
akmissxt 2019-09-25 17:02:15 +08:00 1
lz 下次可以试试这个 [Carbon]( https://carbon.now.sh/)
|
 |
56
smallpython 2019-09-25 17:08:32 +08:00
楼主对于自己不喜欢的回复可以选择隐藏处理
|
 |
58
CruelMoon 2019-09-25 17:16:31 +08:00
看执行计划
|
 |
59
realpg 2019-09-25 17:26:11 +08:00 via Android
写商业软件出身的吧
上次面了一个就是这套路 人家毫秒级的非得给你写个秒级的单 SQ 解决问题 |
|
60
galaChe OP @lygmqkl 其实是知道 low 的。上面大佬说的方案我是知道的。考虑到这个功能的不确定，就不想在其他方面进行优化了（怕麻烦之后懒得清理这些冗余代码）。钻了牛角尖死磕 SQL 所以提了这个问题，希望看看大家是如何处理这类问题的，也算是学习一下见贤思齐。
|
 |
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 ``` |
 |
62
leapV3 2019-09-25 17:32:05 +08:00
每天凌晨一个定时器，跑昨天的统计任务，然后记录
|
|
63
galaChe OP @realpg 是懒，一考虑只是个过渡功能。就想一个 SQL 走下去后面方便删除代码和数据不会有遗留冗余。确实不应该有点敷衍。
|
|
64
realpg 2019-09-25 17:35:18 +08:00 1
|
 |
65
c6h6benzene 2019-09-25 17:37:08 +08:00
我没看明白那堆 AS 1-31 部分的作用是什么，如果只是按天统计的话 GROUP BY 到天不就好了吗...
|
 |
67
VEEX6 2019-09-25 17:49:24 +08:00
艺术家，跪拜
|
 |
68
halk 2019-09-25 18:00:13 +08:00
写过比这长得多的 hql，真是痛苦
|
 |
70
noah9999 2019-09-25 21:21:43 +08:00
v2 应该有付费机制了
|
 |
71
mingl0280 2019-09-26 06:25:03 +08:00
SELECT ... from select 是真的骚操作……
首先你是取的一个月的数据，先把数据按月 select 出来，group by 天数，然后前端再处理下是不是更好？ |
 |
72
luoqeng 2019-09-26 08:42:20 +08:00
统计用时序数据库
|
 |
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 的日期。 |
|
74
ebony0319 2019-09-26 09:30:40 +08:00
 ) |
Select Language