美文网首页小黑猪的大数据学习专栏
R语言实战第一,二章SQL版

R语言实战第一,二章SQL版

作者: blackpiglet | 来源:发表于2017-05-31 22:40 被阅读0次
    星辰大海

    这是之前使用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'
    

    相关文章

      网友评论

        本文标题:R语言实战第一,二章SQL版

        本文链接:https://www.haomeiwen.com/subject/pffjfxtx.html