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

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 星辰大海这是之前使用R语言完成的一道简单的数据统计题目链接:https://zhuanlan.zhihu.com/p/27092971完成之后心理还是有点小得意的。
+关注继续查看
img_e6b69ffd3581ca5da2c3e5fc96fdff06.jpe
星辰大海

这是之前使用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'
目录
相关文章
|
2月前
|
SQL 关系型数据库 数据库
关系数据库SQL语言简介
一、关系数据库SQL语言简介 SQL(Structured Query Language)是关系型数据库管理系统(RDBMS)中最常用的语言。它是一种标准化的语言,用于存储、操作和查询数据。SQL语言具有以下特点: 1. 数据操作语言(DML):用于对数据库中的数据进行增、删、改等操作,包括INSERT、UPDATE、DELETE等命令。 2. 数据定义语言(DDL):用于定义数据库的结构,包括表、字段、索引、视图等,包括CREATE、ALTER、DROP等命令。 3. 数据查询语言(DQL):用于查询数据库中的数据,包括SELECT、FROM、WHERE等命令。 4. 数据控制语言(DCL)
114 0
|
2月前
|
SQL 存储 数据库
SQL语言易错面试题含答案
SQL语言易错面试题含答案
15 2
|
2月前
|
SQL 数据管理 数据库
关系数据库SQL语言简介
关系数据库SQL语言简介 一、SQL语言概述 SQL(Structured Query Language)是一种用于管理和操作关系数据库的语言。它是数据库管理系统(DBMS)的核心组成部分,用于定义、操作和查询数据库中的数据。SQL语言是一种标准化的语言,由美国国家标准局(ANSI)和国际标准化组织(ISO)制定和维护。 SQL语言具有简洁、易学、易用的特点,被广泛应用于各个领域的数据管理和应用开发中。通过SQL语言,用户可以使用简单的语句来完成复杂的数据查询、更新、删除等操作,实现对数据库的全面管理和控制。 SQL语言主要包括以下几个方面的内容: 1. 数据定义语言(DDL):用于
18 0
|
2月前
|
SQL 安全 数据库
深入理解SQL的数据操作语言(DML)
SQL(Structured Query Language)是一种用于管理和操作关系数据库的强大语言。SQL语言被分为多个子语言,其中之一是DML(Data Manipulation Language),用于执行与数据的操作和管理相关的任务。在本文中,我们将深入探讨DML的各个方面,从基础操作到高级技巧,以帮助初学者更好地理解和使用SQL的DML。
30 1
|
3月前
|
SQL
sql语言之子查询语句
sql语言之子查询语句
|
4月前
|
SQL 关系型数据库 数据库连接
【Python入门系列】第七篇:Python数据库操作和SQL语言
本文介绍了使用Python进行数据库操作的基本知识,包括连接数据库、执行SQL查询和更新操作等。同时,还介绍了SQL语言的基本语法和常用的查询语句。通过学习本文,读者将能够使用Python与各种类型的数据库进行交互,并掌握基本的SQL语言知识。
65 0
|
5月前
|
SQL XML Java
Java语言执行SQL脚本文件
在程序执行过程中,难免会遇到一些建表等数据库操作 如果只有一个操作,可以执行在XML里编写。但是有多个时,就需要像Navicat这种开发工具直接执行SQL文件,在Java里同样也可以。
130 0
|
6月前
|
SQL 关系型数据库 MySQL
15天学习MySQL计划-SQL语言学习(基础篇)-第二天
15天学习MySQL计划-SQL语言学习(基础篇)-第二天
107 1
|
6月前
|
SQL Oracle 关系型数据库
SQL语言基础知识(二)
向员工表中插入一条记录,要求符合以下条件
|
6月前
|
SQL Oracle NoSQL
SQL语言基础知识(一)
这是从零开始系统学习SQL语言的课程,课程中的所有SQL语句同时兼容MySQL、Oracle、SQL Server和PostgreSQL等4种最流行的数据库。
推荐文章
更多