数据分析——SQL初学者入门手册2

 

数据分析——SQL初学者入门手册2

Tomi Mester

这是关于数据分析中SQL的入门手册系列的第二部分。今天主要介绍一下关于WHERE子句的细节。我利用一整个篇幅来介绍WHERE并不奇怪,因为它真的很重要,在SQL中你需要利用它来筛选你想要的数据。

前半部分我将给你展示不同的操作。在后半部分,我们最终会导入我们关心的7,000,000+行的数据集(关于飞机延误),并且还会给你一些小的作业来测试你的SQL知识,让你多加练习!

如果你是初学者,你可以先浏览下面的文章:

  1. 如何安装Python, SQL, R和Bash
  2. 数据分析——SQL初学者入门手册1
  3. 如何给postgreSQL安装SQL Workbench

确定你的SQL数据环境能够正常运作

如果你已经按照上述的文章中的做法完成了基本设定,你应该已经准备好了。但是为了以防万一,你最好再次确认3件事:

  1. 你的可运作的数据服务器(文章
  2. SQL工作台可靠地连接到你的数据服务器(文章
  3. zoo数据集(文章

确认之后,你就可以开始学习新的东西了!

SQL WHERE 子句

在SQL数据分析第一部,我们是在终端上进行的,但是这次我们重新在SQL工作台上运行语句:

SELECT * FROM zoo WHERE animal = 'elephant';

1

这是使用SQL最基本的例子,你可以更加熟练地应用…

SQL WHERE 与比较符号

首先-你不仅可以考虑“什么等于什么”。它也可以“大于”,“小于”,“不等于”,等等。

这里是一个比较符号的列表:

比较符号 含义
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于

看几个具体的例子:

你怎么选择所有不是斑马的动物呢?

SELECT * FROM zoo WHERE animal <> 'zebra';

2

你怎么选择需水量小于300的动物呢?

SELECT * FROM zoo WHERE water_need < 300;

SQL WHERE语句与LIKE语句

有时候你想要选择某种模式的,看一下下面的数据集:

user_name user_id
frank 1001
franky 1002
frankeinsterin 1003
francis 1004
frappucino 1005

现在,假如你想选择那些’user_name’是’frank’开始的行。是时候使用你的LIKE语句:

SELECT * FROM franks WHERE user_name LIKE 'frank%';

LIKE逻辑语句告诉SQL,你想要基于某种模式来进行过滤。你确定了你的模式是'frank%'。巧妙的是%—这意味着在frank之后,任何字符都无所谓。所以你的结果是:

回到我们的动物园数据,试着回答这些问题:

你怎么选择名字里包含一个e字母的动物?

SELECT * FROM zoo WHERE animal LIKE '%e%';

在e的前后都要%,因为字母e可以在词中的任意位置,而不是在最前或者最后。

你怎么选择名字是以’roo’结尾的动物呢?

SELECT * FROM zoo WHERE animal LIKE '%roo';

这里介绍另一个神奇的字符:_(它的标准的名字叫做通配符。)

%意味着任何任意多的字符。但是_意味着,任意字符但是只有一个。单词tiger可以被表示成't%',也可以用't____'

接下来是另一个问题:

你怎么选择物种名称为5个字母长的动物呢?

SELECT * FROM zoo WHERE animal LIKE '_____';

如何SQL WHERE语句中的不同条件

你也可以把多个条件组合起来!有两个重要的关键词:ANDOR(’且‘,’或‘)。

我们如果想要选择一种动物,它的名字长度为5,但不是’tiger’:

SELECT
  *
FROM
  zoo
WHERE
  animal LIKE '_____'
  AND animal <> 'tiger';

现在把情况变得更加复杂一点:

  • 名字字符长度为5
  • 不是老虎
  • 需水量大于200
SELECT
  *
FROM
  zoo
WHERE
  animal LIKE '_____'
  AND animal <> 'tiger'
  AND water_need > 200;

是的,你也可以用多个AND。最后我们看一个关于OR的例子!

SELECT
  *
FROM
  zoo
WHERE
  water_need < 300
  OR animal = 'lion';

出现在你屏幕上的所有动物,是狮子以及那些需水量小于300的动物。

SQL IN 语句

想象一种情况,你想要选择所有动物,其id是‘1001,1008,1012,1015,1018’中的任意一个。

你可以用之前的方法:

SELECT
  *
FROM
  zoo
WHERE
  uniq_id = 1001
  OR uniq_id = 1008
  OR uniq_id = 1012
  OR uniq_id = 1015
  OR uniq_id = 1018;

虽然这个答案是对的,但真不是一种简洁优雅的方法。如果你想要减少OR的数量,那么你可选择IN

SELECT
  *
FROM
  zoo
WHERE
  uniq_id IN (1001,1008,1012,1015,1018);

得到了同样的结果。

SQL NOT 语句

还有一个逻辑语句,非常简单。NOT可以调整你的逻辑语句来实现它的对立面。我们回到前面,名字长度为5的动物例子中:

SELECT * FROM zoo WHERE animal LIKE '_____';

怎么来选择不是5个长度的呢?

SELECT * FROM zoo WHERE animal NOT LIKE '_____';

导入postgreSQL一个更大的数据集

你是不是觉得22行的动物园数据很无聊?让我们导入一个7,000,000+飞机延误数据。按照下面几步来完成导入:

我觉得具体步骤比较繁琐,所以我把所有的操作都放入一个短视频中。

这个工作是在bash中完成的,如果你没有看过我的bash手册系列,我强烈建议你去阅读至少第一部分的内容,你不读的话,按照我下面的步骤也没有关系:

  1. 打开终端,登录(ssh)你的数据服务器

  2. 下载’flight delays’数据wget http://stat-computing.org/dataexpo/2009/2007.csv.bz2。如果你已经有了数据,跳到第5步。

  3. 安装dtrx!这是一个用于解压的工具!(可能已经安装好了,跳过此步。)

    sudo apt-get install dtrx

  4. 解压csv文件!dtrx 2007.csv.bz2

    注意它大概要60秒左右来实现解压,所以并不是你的终端出问题了。

  5. 把你的数据格式化!cat 2007.csv |cut -d',' -f1,2,3,4,5,7,10,11,14,15,16,17,18,19 | grep -v ',NA' > sql_ready.csv

  6. 现在我们必须给我们的postgreSQL用户权限,来创造并且导入数据。这个将需要几步。下面的gif给了简单的示范:

    首先sudo用户”postgres”:

    sudo -u postgres -i然后开始postgreSQL:

    psql命令更改为:postgres=#!输入:alter user [your_user_name] superuser;这会把你原来的用户变为超级用户!

    退出postgreSQL:\q

    然后从“postgre”中退出用户:

    exit最后测试你原始用户的postgreSQL数据库,使用下面的命令行:

    psql -d postgres

  7. 接下来你要做的就是简单地把这些命令复制到你的终端中来创建表格:

    CREATE TABLE flight_delays (
      year INTEGER,
      month INTEGER,
      dayofmonth INTEGER,
      dayofweek INTEGER,
      deptime INTEGER,
      arrtime INTEGER,
      flightnum INTEGER,
      tailnum VARCHAR,
      airtime INTEGER,
      arrdelay INTEGER,
      depdelay INTEGER,
      origin VARCHAR,
      dest VARCHAR,
      distance INTEGER);
    
  8. 并且最终,把你下载的数据从csv文件中复制出来:

    COPY flight_delays FROM '/home/tomi/practice/sql_ready.csv' DELIMITER ',' CSV HEADER;

  9. 回到sql工作台,尝试简单的操作,当然也保证你用了LIMIT语句。现在你有超过7,000,000行的数据,即使postgreSQL能够很简单地处理这些数据,你的电脑如果在尝试把所有数据显示出来的话,可能会死机。所以首先尝试:

    SELECT * FROM flight_delays LIMIT 10;

练习题#1

在2007-4-23的时候,有多少个尾号为’N253WN’的航班?

(提示:我们没有学COUNT函数,但是你也可以显示出来之后,自己数出多少行。)

答案:

SELECT
  *
FROM
  flight_delays
WHERE month = 4
  AND year = 2007
  AND dayofmonth = 23
  AND tailnum = 'N253WN';

注意年月日分别为不同的列,所以你要在WHERE中使用并行的多个条件。

练习题2

选出满足下列条件的行:

  • 航班在2007, 4月
  • 是偶数工作日(每周的周2、4、6)
  • 飞行距离小于50英里
  • 离开或者到达延误时间小于0(意味着飞机提前起飞或者落地)

答案:

SELECT
  *
FROM
  flight_delays
WHERE month = 4
  AND dayofweek IN (2,4,6)
  AND distance < 50
  AND (arrdelay < 0
  OR depdelay < 0);

注意到,AND语句要优先于OR,所以注意一定要有括号,否则结果就和下面代码的结果一样,是错误的:

WHERE (month = 4
  AND dayofweek IN (2,4,6)
  AND distance < 50
  AND arrdelay < 0)
  OR (depdelay < 0);

总结

理解并且学会使用SQL WHERE语句,并且组合正确的子语句关键词是很重要的!这是这篇文章的主要内容!现在我们可以继续下面的学习了!我的下一篇文章会介绍基础的SQL函数(MAX,MIN,SUM,COUNT),并且我会给介绍你更多的语句(ORDER BY,GROUP BY, DISTINCT)。你可以继续学习,点击这里

如果你想浏览更多我的新文章、视频、博客以及其他很酷的数据工作者,订阅我每周更新的Newsletter

 

由 Editor 于 2018 年 02 月 01 日 发布在 数据科学 栏目