R语言实战第一,二章SQL版环境准备导入数据查询注册90天内购买的用户数量查询90天内未购用户和收到短信的比例

星辰大海

这是之前使用R语言完成的一道简单的数据统计题目链接: https://zhuanlan.zhihu.com/p/27092971

完成之后心理还是有点小得意的。但和答案一对比就发现问题了,自己的计算数据和正确结果差距太大了。看来我用语言暂时还是很难保证数据计算的准确性, 所以有了这篇,毕竟SQL语句更熟悉一些。

环境准备

要使用SQL查询自然要先有数据库了,有了docker技术后,我就不太倾向于直接在电脑上安装软件了,所以这次要先将MySQL在docker中启动起来。我使用的是Mac,docker的安装就不赘述了,直接总官网下载就可以了,目前Mac已经不在使用boot2docker了,号称是原生docker,但经过这次实践发现,其实谈不上原生,依旧是虚拟机方式实现的,只不过不再使用VirtualBox了,关于这点会在后面进行解释。接下来开始操作。

先下载mysql的docker image

docker pull mysql:5.6

启动mysql

docker run --name mysql -e MYSQL_ROOT_PASSWORD=mysql -d mysql:5.6 -p 3306:3306 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

这时问题就出现了, 以守护进程形式启动mysql时, 总是自动退出, 而且按照docker提供的日志存储目录(/var/lib/docker)根本就找不到, 系统上就没有这个目录. 没有日志又没法定位问题, 真是没想到第一步就卡住了.

只好到网上搜索为什么Mac系统上没有docker的日志目录,找到了一些线索:Mac依旧使用虚拟机实现的docker,所有的文件都保存在一个虚拟机的镜像文件里,"/var/lib/docker"其实是虚拟机中的目录,所以在Mac上当然找不到。但是也有办法进入虚拟机内部查看目录结构:

screen ~/Library/Containers/com.docker.docker/Data/com.docker.driver.amd64-linux/tty

接下来就比较分析问题了,mysql没能启动的主要原因还是docker run命令的参数顺序问题, 调整一下就好了。

docker run -d --name mysql -p 3306:3306 -v /Users/blackpiglet/Documents/big_data:/mnt/big_data -e MYSQL_ROOT_PASSWORD=mysql -e MYSQL_DATABASE=big_data mysql:5.6

导入数据

MySQL终于启动成功了,接下来就要倒入csv文件,在倒入之前要先把表建好:

create table `users` (`user.id` varchar(100), `signup.date` DATE);
create table `purchases` (`user.id` varchar(100), `purchase.date` DATE, `purchase.count` smallint);
create table `messages` (`user.id` varchar(100), `message.date` DATE, `message.count` smallint);

倒入csv文件的语句:

LOAD DATA LOCAL INFILE '/mnt/big_data/users.csv'
  INTO TABLE `users`
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (`user.id`, `signup.date`);

LOAD DATA LOCAL INFILE '/mnt/big_data/purchases.csv'
  INTO TABLE `purchases`
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (`user.id`, `purchase.date`, `purchase.count`);

LOAD DATA LOCAL INFILE '/mnt/big_data/messages.csv'
  INTO TABLE `messages`
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (`user.id`, `message.date`, `message.count`);

查询注册90天内购买的用户数量

查询注册用户数量, 并删除注册日期为'0000-00-00'的项.

select count(*) from users where `signup.date` != '0000-00-00';
23841

SET SQL_SAFE_UPDATES = 0;
delete from `users` where `signup.date` = '0000-00-00';

查询注册90天内购买的用户数量。 这里需要注意一点MySQL的日期计算最好不要直接使用算数运算,在这个语句前使用的是 and (p.purchase.date - u.signup.date) <= 90 结果计算的数量就比实际的数量少了很多,目前还不确定造成这个现象的原因,总之尽量是用date的计算函数。

select count( distinct (u.`user.id`)), u.`signup.date`, p.`purchase.date`, p.`purchase.count` from users u
  join purchases p on p.`user.id` = u.`user.id`
  where (p.`purchase.date` - u.`signup.date`) >= 1
  and (p.`purchase.date` <= date_add(u.`signup.date`, INTERVAL 90 DAY));

# count( distinct (u.`user.id`)), signup.date, purchase.date, purchase.count
'6369', '2013-06-17', '2013-06-19', '1'

在进行用户表,购买表和短信消息表的联合查询时,查询时长超过了30s,MySQL报错:

Error Code: 2013. Lost connection to MySQL server during query

我使用的是MySQL WorkBench,发现可以通过设置修改查询的超时时长,按照下面这个答案修改超时时长为3000s: https://stackoverflow.com/questions/2698401/how-to-store-mysql-query-results-in-another-table

修改后依旧查询超慢,可能是因为查询缺少优化,而且同时查询三张表,导致速度缓慢,优化的方法,可以将上一步用户表和购买表的联合查询结果先保存到一个中间表,然后将查询条件建好索引,之后再尝试。但是这次使用让我感觉是R确实在速度上比MySQL要快一些。

以下是使用三表联合查询的语句,真是慢的要死,几十分钟都没有响应。后来实在是没有办法,只能查询正在进行的query,然后kill了。

select count( distinct (u.`user.id`)), u.`signup.date`, p.`purchase.date`, p.`purchase.count` from users u
  join purchases p on p.`user.id` = u.`user.id`
  join messages m on m.`user.id` = u.`user.id`
  where (p.`purchase.date` - u.`signup.date`) >= 1
  and (p.`purchase.date` <= date_add(u.`signup.date`, INTERVAL 90 DAY))
  and (m.`message.date` >= date_add(u.`signup.date`, INTERVAL 1 DAY))
  and (m.`message.date` < p.`purchase.date`);

以下是创建新表,和将数据倒入新表,并创建索引的过程。

create table `user_purchase` (`user.id` varchar(100), `signup.dae` DATE, `purchase.date` DATE, `purchase.count` smallint);

insert into user_purchase select distinct(u.`user.id`), u.`signup.date`, p.`purchase.date`, p.`purchase.count` from users u
  join purchases p on p.`user.id` = u.`user.id`
  where (p.`purchase.date` - u.`signup.date`) >= 1
  and (p.`purchase.date` <= date_add(u.`signup.date`, INTERVAL 90 DAY));

alter table user_purchase add index `index_user_id` (`user.id`);
alter table user_purchase add index `index_signup_date` (`signup.date`);
alter table user_purchase add index `index_purchase_date` (`purchase.date`);

# 给messages表也要创建好索引:
alter table messages add index `index_user_id` (`user.id`);
alter table messages add index `index_message_date` (`message.date`);

查询90天内未购用户和收到短信的比例

创建一张新表,用于保存注册90天内未购买的用户信息。将users表中有,而user_purchase(保存注册90天内购买的用户信息)中没有的行插入user_not_buy表。

create table user_not_buy (`user.id` varchar(100), `signup.date` DATE);

insert into user_not_buy select * from users where users.`user.id` not in (select `user.id` from user_purchase );

给新表加上索引

select count(*) from user_not_buy;
alter table user_not_buy add index `index_user_id` (`user.id`);
alter table user_not_buy add index `index_signup_date` (`signup.date`);

查询收到的短信日期大于注册日期,并且小于注册日期90天的记录。

select count( distinct(u_n_b.`user.id`) ) from user_not_buy as u_n_b
    join messages m on u_n_b.`user.id` = m.`user.id`
    and (m.`message.date` >= date_add(u_n_b.`signup.date`, INTERVAL 1 DAY))
    and (m.`message.date` <= date_add(u_n_b.`signup.date`, INTERVAL 90 DAY));


# count( distinct(u_n_b.`user.id`) )
'16363'

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏hadoop学习笔记

Spring Boot中对自然语言处理工具包hanlp的调用详解

HanLP 是基于 Java开发的 NLP工具包,由一系列模型与算法组成,目标是普及自然语言处理在生产环境中的应用。而且 HanLP具备功能完善、性能高效、架构...

7000
来自专栏杨建荣的学习笔记

数据库收缩数据文件的尝试(三)(r11笔记第22天)

不知道大家在数据库运维中是否会有这样的困扰,一个数据文件里没有多少数据,但是数据文件的大小却调不下来,尝试使用resize来调整屡屡失败。如果一个数据文件里...

351120
来自专栏杨建荣的学习笔记

数据库负载急剧提高的应急处理(二) (r9笔记第55天)

对于之前碰到的一个数据库负载急剧提升的问题,做了应急处理之后,我们需要再冷静下来,来看看是哪些地方出现了问题,还需要哪些改进。 首先第一个问题就是为什么会突然负...

34140
来自专栏数据和云

SQL查询提速秘诀,避免锁死数据库的数据库代码

由于数据库领域仍相对不成熟,每个平台上的 SQL 开发人员都在苦苦挣扎,一次又一次犯同样的错误。当然,数据库厂商在取得一些进展,并继续在竭力处理较重大的问题。

17630
来自专栏杨建荣的学习笔记

复杂SQL性能优化的剖析(二)(r11笔记第37天)

昨天的一篇文章复杂SQL性能优化的剖析(一)(r11笔记第36天) 分析了一个SQL语句导致的性能问题,问题也算暂时告一段落,因为这个语句的执行频率是1...

37190
来自专栏数据和云

深入解析:DB2 V10.5新特性列式存储表的优点与缺点

李培杨 云和恩墨西区交付技术顾问,有多年数据库运维经验,长期服务移动运营商行业客户,熟悉 DB2 数据库故障诊断,数据库迁移升级。

14740
来自专栏杨建荣的学习笔记

结合EM快速解决复杂的配置问题(r4笔记第91天)

图形工具在学习中一般是不作为推荐工具使用的,很多时候可能工作环境都是字符界面,远程连接,基本没有可能接触到图形工具,图形工具的好处真是一把双刃剑,功能丰富全面而...

30360
来自专栏杨建荣的学习笔记

物化视图中的统计信息导致的查询问题分析和修复 (r7笔记第47天)

今天开发的同事下午反馈给我一个问题,说有操作直接卡住了,听这个描述,感觉很可能是查询慢了。 于是连接到环境中,查看了一下正在执行的sql语句情况,发现下面的语句...

35750
来自专栏杨建荣的学习笔记

shell脚本自动化采集性能sql(r2笔记39天)

通过v$sql_monitor能够实时采集可能存在的sql性能问题,但是每次问题发生的时候采取采取措施就有点“晚”了,我们需要防患于未然,把一些潜在问题提前发现...

29040
来自专栏数据和云

一波三折:一次CPU使用率过高故障分析SQL优化解决过程

作者 | 罗贵林: 云和恩墨技术工程师,具有8年以上的 Oracle 数据库工作经验,曾任职于大型的国家电信、省级财政、省级公安的维护,性能调优等。精通 Ora...

34430

扫码关注云+社区

领取腾讯云代金券