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