关于一条SQL语句的优化

2016-07-28 06:21:25 218 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

oracle11g 一条几百行行的sql语句优化需要适可而止才行 1，同事报告了一条非常复杂的sql，说很慢，叫我优化下。很慢的sql，也很长有几百行吧，^_^，如下： select bm2.bis_must_id, bm2.qz_year_month, bm2.money, bm2.adjust_money, bm2.bis_cont_id, 阅读详情

目前有这样一张表，查询要求：查询出当每个用户交易金额累计达到70时的交易时间。
源码：



DROP TABLE IF EXISTS t_g;

CREATE TABLE t_g

(t_id BIGINT AUTO_INCREMENT,

 t_userid VARCHAR(20) DEFAULT '' COMMENT '用户id',

 t_je VARCHAR(20) DEFAULT '' COMMENT '交易金额',

 t_date VARCHAR(20) DEFAULT '' COMMENT '交易时间',

 PRIMARY KEY(t_id)

);

-- 创建索引

CREATE INDEX i_a ON t_g (t_userid,t_date);



INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160701');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','10','20160702');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','15','20160703');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','15','20160704');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160705');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','50','20160706');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160707');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('A','20','20160708');



INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','40','20160701');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','20','20160702');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','15','20160703');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('B','15','20160704');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','20','20160701');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','50','20160702');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','20','20160703');

INSERT INTO t_g (t_userid,t_je,t_date) VALUES ('C','20','20160704');

我的查询语句是这样的写的：



select x.t_userid,MIN(x.t_date) from

(select a.t_userid,a.t_date,(select sum(t_je) from t_g where t_userid = a.t_userid and t_date <= a.t_date 

)total from t_g a)x 

where x.total/100 > 70  GROUP BY x.t_userid;

这样写可以是实现查询的功能，但是这张表有30万数据的时候，运行速度超级慢，大概需要2分钟。请问高手们，这句该怎么样优化，或者有其他更好的办法实现没，用存储过程也可以，只要能提升性能。

...全文

告别Quartus II的漫长等待：用VSCode+iverilog+GTKWave搭建你的轻量级Verilog仿真环境（2024保姆级教程）本文详细介绍了如何利用VSCode、iverilog和GTKWave构建高效的Verilog/SystemVerilog开发环境，替代传统的Quartus II和ModelSim工具。通过2024年最新的保姆级教程，帮助开发者实现秒级启动、低资源消耗的轻量级仿真环境，显著提升数字电路设计的开发效率和调试体验。阅读详情

nightgoblin

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

zjcxc 2016-07-29

打赏
举报

你的 t_userid 有多少？如果很多，那说明返回的数据多，查询慢也比较正常另外，如果你的每个 t_userid 的记录需要累计达到限额的记录也非常多的话，那么查询慢也正常如果设计上能够加累计金额，确实会方便很多，不用实时算，也能索引

soton_dolphin 2016-07-29

打赏
举报

不如再加一列叫累计消费金额，那样会比较方便

nightgoblin 2016-07-29

打赏
举报

引用 2 楼 ACMAIN_CHM 的回复:

[quote=引用 2 楼 ACMAIN_CHM 的回复:]

mysql> select * from t_g;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date   |
+------+----------+------+----------+
|    1 | A        | 20   | 20160701 |
|    2 | A        | 10   | 20160702 |
|    3 | A        | 15   | 20160703 |
|    4 | A        | 15   | 20160704 |
|    5 | A        | 20   | 20160705 |
|    6 | A        | 50   | 20160706 |
|    7 | A        | 20   | 20160707 |
|    8 | A        | 20   | 20160708 |
|    9 | B        | 40   | 20160701 |
|   10 | B        | 20   | 20160702 |
|   11 | B        | 15   | 20160703 |
|   12 | B        | 15   | 20160704 |
|   13 | C        | 20   | 20160701 |
|   14 | C        | 50   | 20160702 |
|   15 | C        | 20   | 20160703 |
|   16 | C        | 20   | 20160704 |
+------+----------+------+----------+
16 rows in set (0.00 sec)

mysql> select *
    -> from t_g t
    -> where (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)<70
    -> and (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)>=70-t.t_je;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date   |
+------+----------+------+----------+
|    5 | A        | 20   | 20160705 |
|   11 | B        | 15   | 20160703 |
|   14 | C        | 50   | 20160702 |
+------+----------+------+----------+
3 rows in set (0.00 sec)

mysql>

.[/quote] 你这个也很慢

nightgoblin 2016-07-29

打赏
举报

引用 1 楼 fatsimple 的回复:

select t.user_id,t.date_id from (select t_userid as user_id,t_date as date_id,sum(t_je) as threadhold from t_g group by t_userid,t_date) as t where t.threadhold>=70 PS:t_date => datetime类型 t_je=>decimal类型

明显是错误的结果

soton_dolphin 2016-07-29

打赏
举报

引用 5 楼 soton_dolphin 的回复:

不如再加一列叫累计消费金额，那样会比较方便

如果有这一列，就可以写 SELECT T_USERID, T_DATE, T_AGGREGATED_AMT FROM T_G WHERE MIN(T_AGGREGATED_AMT) >=70 GROUP BY T_USERID, T_DATE

ACMAIN_CHM 2016-07-28

打赏
举报

引用 2 楼 ACMAIN_CHM 的回复:

mysql> select * from t_g;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date   |
+------+----------+------+----------+
|    1 | A        | 20   | 20160701 |
|    2 | A        | 10   | 20160702 |
|    3 | A        | 15   | 20160703 |
|    4 | A        | 15   | 20160704 |
|    5 | A        | 20   | 20160705 |
|    6 | A        | 50   | 20160706 |
|    7 | A        | 20   | 20160707 |
|    8 | A        | 20   | 20160708 |
|    9 | B        | 40   | 20160701 |
|   10 | B        | 20   | 20160702 |
|   11 | B        | 15   | 20160703 |
|   12 | B        | 15   | 20160704 |
|   13 | C        | 20   | 20160701 |
|   14 | C        | 50   | 20160702 |
|   15 | C        | 20   | 20160703 |
|   16 | C        | 20   | 20160704 |
+------+----------+------+----------+
16 rows in set (0.00 sec)

mysql> select *
    -> from t_g t
    -> where (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)<70
    -> and (select sum(t_je) from t_g where t_userid=t.t_userid and t_date<t.t_date)>=70-t.t_je;
+------+----------+------+----------+
| t_id | t_userid | t_je | t_date   |
+------+----------+------+----------+
|    5 | A        | 20   | 20160705 |
|   11 | B        | 15   | 20160703 |
|   14 | C        | 50   | 20160702 |
+------+----------+------+----------+
3 rows in set (0.00 sec)

mysql>

小胖总 2016-07-28

打赏
举报

select t.user_id,t.date_id from (select t_userid as user_id,t_date as date_id,sum(t_je) as threadhold from t_g group by t_userid,t_date) as t where t.threadhold>=70 PS:t_date => datetime类型 t_je=>decimal类型

关于一条SQL语句的优化

相关推荐