{DAY9} SQL查询语法1

前言

今天要来练习SQL的查询语法

使用的软件是SQLite Studio

我上的课程是郭耀仁老师在台大资讯系统训练班所开设的『SQL资料分析』

这篇文章会使用老师上课教到的观念

利用kaggle上的资料实际操作

会分成三个部分，分别是：

• 函数的介绍
• 通用函数
• 聚合函数

使用的资料是kaggle上一笔五百人的身高体重纪录

500 Person Gender-Height-Weight-Body Mass Index
先来看看前十笔资料长怎样

SELECT *
 FROM data
 LIMIT 10;

另外一笔资料是netflix上的纪录

https://www.kaggle.com/sarahjeeeze/imdbfile

也来看看资料长怎样

SELECT *
 FROM record
 LIMIT 10;

函数类型

函数可依照功能分为两类：

• 通用函数
  • 转换资料类型。
  • 计算数值。
  • 操作文字。
  • 操作日期时间。
• 聚合函数

通用函数与聚合函数的不同在於其所作用的维度

• 通用函数作用会显示在水平方向。

  像是每个观测值会对应到一个输出

  假如现在想让身高变成公尺的形式

SELECT *,
       Height*0.01 AS height_in_meters
 FROM data
 LIMIT 10;

可以看到最右边那栏的将左边的身高一一输出成公尺的形式

• 聚合函数整合在垂直方向

  就是整栏变数最後会对应到一个输出

  SELECT AVG(Height) AS avg_height
   FROM data;
  

可以通过聚合函数计算整体的平均身高

通用函数

举例来说，要计算每个人的BMI值

BMI = 体重(公斤) / 身高^2(公尺)^2

SELECT *,
      Weight/((Height*0.01)*(Height*0.01))AS BMI
 FROM data
 LIMIT 10;

可以看到出来的BMI小数点位数很多

若想要限制输出的小数点位数

ROUND 函数: 调整查询结果的小数点位数

ROUND(REAL, n_digits)

假如只想要取小数点後三位

SELECT *,
      ROUND(Weight/((Height*0.01)*(Height*0.01)),3)AS BMI
 FROM data
 LIMIT 10;

可以看到最後指出来小数点後三位

COALESCE ：将遗漏值转换为指定常数

COALESCE(NULL, replacement)

举例来说，在 Netflix 的 record 资料表中有许多director没有被记录到

再看一次这笔资料前十笔的纪录
可以看到director的栏位内有很多空格

利用COALESCE来替换指定的常数

LENGTH :计算文字中有几个字元，包含空格、标点符号

LENGTH(TEXT)

假设现在想知道每个title的文字长度

SELECT title,
       LENGTH(title) AS length_of_title
 FROM record
 LIMIT 10;

LOWER 与 UPPER : 调整英文的大小写

LOWER(TEXT)
UPPER(TEXT)

SELECT title,
       UPPER(title) AS upper_title,
       LOWER(title) AS lower_title
 FROM record
 LIMIT 10;

聚合函数

常见的聚合函数

• AVG(column_name)：变数平均数
• COUNT(column_name)：计算变数的「非」遗漏值数
• COUNT(*)：计算观测值数
• MAX(column_name)：最大值
• MIN(column_name)：最小值
• SUM(column_name)：计算变数的加总

计算平均身高和体重

SELECT AVG(Height) AS avg_height,
       AVG(Weight) AS avg_weight
 FROM data
 LIMIT 10;

找出身高最高和最矮的

SELECT MAX(Height) AS highest,
       MIN(Height) AS shortest
 FROM data
 LIMIT 10;