首页新闻找找看学习计划

mysql语句优化,统计一段时间玩家回流用户数

0
悬赏园豆:50 [已解决问题] 解决于 2019-03-16 09:52

因某需求统计回流用户,目前已写出sql语句,但是效率很低,目前数据200多万,
回流用户:如2019-01-05号登陆过的玩家,在6号到7号之间没有登陆过,但是他们在8号又登陆过的用户。

问题补充:

sql语句如下:

 1 SELECT count(*) FROM player_login WHERE openid IN (
 2   SELECT openid FROM player_login WHERE openid NOT IN (
 3     SELECT openid FROM player_login
 4     WHERE
 5       date_time BETWEEN '2019-01-06 00:00:00'
 6       AND '2019-01-07 23:59:59'
 7       AND EXISTS (
 8         SELECT openid FROM player_login
 9         WHERE
10           date_time BETWEEN '2019-01-05 00:00:00'
11           AND '2019-01-05 23:59:59'
12           GROUP BY openid
13       )
14     )
15     AND date_time BETWEEN '2019-01-05 00:00:00'
16     AND '2019-01-05 23:59:59'
17   )
18 AND date_time BETWEEN '2019-01-08 00:00:00'
19 AND '2019-01-08 23:59:59'

水平有限只能写成这样,请问各位大神还能怎么优化?

花儿为何那样红的主页 花儿为何那样红 | 初学一级 | 园豆:81
提问于:2019-02-25 18:45

in 用join 表连接试一下。会快吗?

Sopcce 1年前
< >
分享
最佳答案
0

--要查询8日登录的数量。筛选是否含有5日登录且67没有登录

需要创建临时表,去除in 使用exists,在数据多的时候,in 会严重影响索引,所以一个使用

#创建一个小数据的临时表。使用链接查询,选择交集。

这段sql 主要是查询在5日登陆并且67没有登录的用户。把所有星替换掉啊。

CREATE TEMPORARY TABLE table_temp_567
select openid from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-05 00:00:00'AND '2019-02-05 23:59:59'
GROUP BY openid ) as table_temp_5 where not exists(select * from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-06 00:00:00'AND '2019-02-07 23:59:59'
GROUP BY openid ) as table_temp_67 where table_temp_67.openid = table_temp_5.openid)
-----------------------------------
DROP TABLE table_temp_567;
select * from table_temp_567
---------------
可以不用临时表,但是查询太多也会印象查询速度的,一般最后拆分开。

select * from table_temp_567
left join (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid ) as table_temp_8 on table_temp_567.openid=table_temp_8.openid

这样子的速度,不清楚 哈哈

测试流程数据

CREATE TEMPORARY TABLE table_temp_567
select openid from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-05 00:00:00'AND '2019-02-05 23:59:59'
GROUP BY openid ) as table_temp_5 where not exists(select * from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-06 00:00:00'AND '2019-02-07 23:59:59'
GROUP BY openid ) as table_temp_67 where table_temp_67.openid = table_temp_5.openid)

select * from table_temp_567
left join (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid ) as table_temp_8 on table_temp_567.openid=table_temp_8.openid

select * from player_login

--查询67日登陆的 测试6条
select * from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-06 00:00:00'AND '2019-02-07 23:59:59'
GROUP BY openid ) as table_temp_67

--查询5日登陆 7条
SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-05 00:00:00'AND '2019-02-05 23:59:59'
GROUP BY openid

--查询5日登陆且67没有登录的 31.4
select openid from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-05 00:00:00'AND '2019-02-05 23:59:59'
GROUP BY openid ) as table_temp_5 where not exists(select * from (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-06 00:00:00'AND '2019-02-07 23:59:59'
GROUP BY openid ) as table_temp_67 where table_temp_67.openid = table_temp_5.openid)

--查询8日登录 8条
SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid

--创建临时表,需要对567、8日的,一567为主,毕竟567表示5日等且67日登录的。所以一567为主。关联查询。
select * from table_temp_567
left join (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid ) as table_temp_8 on table_temp_567.openid=table_temp_8.openid
--查询2条 31.4

测试数据,存在局限性,要不请提供sql 结构包括数据,数据请自动脱敏。

测试数据表结构和数据下面回复贴出来了

https://img2018.cnblogs.com/q/676728/201902/676728-20190228161325733-975401087.png

下载此文件,修改后缀改为SQL ,可以打开结构和数据脚本

收获园豆:30
Sopcce | 菜鸟二级 |园豆:475 | 2019-02-27 14:35

select * from table_temp_567
left join (SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid ) as table_temp_8 on table_temp_567.openid=table_temp_8.openid

这段sql查询的结果和table_temp_567表的结果一样不知道是怎么回事。

花儿为何那样红 | 园豆:81 (初学一级) | 2019-02-27 16:41

@花儿为何那样红: 567 是排除67七日登录用户的数据,5日等的用户,
select * from table_temp_567 这是查询567日数据的。

SELECT * FROM player_login WHERE
date_time BETWEEN '2019-02-08 00:00:00'AND '2019-02-08 23:59:59'
GROUP BY openid
这是查询8日数据的。

我的测试结果是一样的。

一下附上我的测试数据

/*
Navicat Premium Data Transfer

Source Server : localhost-root-123456
Source Server Type : MySQL
Source Server Version : 80013
Source Host : localhost:3306
Source Schema : mywebsite

Target Server Type : MySQL
Target Server Version : 80013
File Encoding : 65001

Date: 28/02/2019 16:00:33
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- Table structure for player_login
-- ----------------------------
DROP TABLE IF EXISTS player_login;
CREATE TABLE player_login (
id int(11) NOT NULL AUTO_INCREMENT,
openid int(11) NOT NULL,
name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
date_time datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
age int(11) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 40 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


-- Records of player_login
-- ----------------------------
INSERT INTO player_login VALUES (1, 1, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (2, 1, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (3, 1, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (4, 1, '1', '2019-02-07 13:40:43', 11);
INSERT INTO player_login VALUES (5, 1, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (6, 1, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (7, 2, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (8, 2, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (9, 2, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (10, 2, '1', '2019-02-07 13:40:43', 11);
INSERT INTO player_login VALUES (11, 2, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (12, 2, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (13, 3, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (14, 3, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (15, 3, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (16, 3, '1', '2019-02-07 13:40:43', 11);
INSERT INTO player_login VALUES (17, 3, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (18, 4, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (19, 31, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (20, 31, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (21, 31, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (22, 31, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (23, 4, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (24, 4, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (25, 4, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (26, 4, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (27, 5, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (28, 5, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (29, 5, '1', '2019-02-07 13:40:43', 11);
INSERT INTO player_login VALUES (30, 5, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (31, 5, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (32, 6, '1', '2019-02-04 12:40:43', 11);
INSERT INTO player_login VALUES (33, 6, '1', '2019-02-05 13:40:43', 11);
INSERT INTO player_login VALUES (34, 6, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (35, 6, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (36, 6, '1', '2019-02-09 13:40:43', 11);
INSERT INTO player_login VALUES (37, 7, '1', '2019-02-06 13:40:43', 11);
INSERT INTO player_login VALUES (38, 7, '1', '2019-02-08 13:40:43', 11);
INSERT INTO player_login VALUES (39, 7, '1', '2019-02-09 13:40:43', 11);

SET FOREIGN_KEY_CHECKS = 1;

Sopcce | 园豆:475 (菜鸟二级) | 2019-02-28 16:02

@Sopcce: 方便加个qq交流吧,我给你发表结构和数据。我的qq:919303698

花儿为何那样红 | 园豆:81 (初学一级) | 2019-02-28 16:19
其他回答(1)
0

SELECT count(*) FROM player_login WHERE openid IN (

count(*),建议用count(1)替代;
WHERE openid IN 建议用exists替代in

另外,用explain语句,执行下该sql,根据执行计划,进行修复

收获园豆:20
coco_xu | 园豆:505 (小虾三级) | 2019-02-26 13:15

我尝试过用exists,但是查询出来的数据和in查询出来的数据相差很大,具体原因未知

支持(0) 反对(0) 花儿为何那样红 | 园豆:81 (初学一级) | 2019-02-26 16:35

@花儿为何那样红: 建议发下表结构再发几条数据过来,只看sql没法看啊……

支持(0) 反对(0) coco_xu | 园豆:505 (小虾三级) | 2019-02-26 17:17

方便的话,给个邮箱,我发给你,谢谢!

支持(0) 反对(0) 花儿为何那样红 | 园豆:81 (初学一级) | 2019-02-26 17:27

@花儿为何那样红: 1009866898@qq.com

支持(0) 反对(0) coco_xu | 园豆:505 (小虾三级) | 2019-02-26 17:37

@coco_xu: 已发你邮箱

支持(0) 反对(0) 花儿为何那样红 | 园豆:81 (初学一级) | 2019-02-26 17:47

@花儿为何那样红: 看不出可还要怎么优化了,不好意思哈

支持(0) 反对(0) coco_xu | 园豆:505 (小虾三级) | 2019-02-27 11:04

@coco_xu: 好吧!那也多谢了

支持(0) 反对(0) 花儿为何那样红 | 园豆:81 (初学一级) | 2019-02-27 11:08
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册