本篇主要讲述SQL语句中查询技能。（实用的具体数据库PostgreSQL）
主要内容
- 基础查询
- JOIN操作和UNION操作
- 聚合操作
- 子查询&临时表格
- 清理操作
基础查询
SELECT column1, column2
FROM table1
WHERE column3 > 2 AND column4 LIKE '%tm%'
ORDER BY column5
LIMIT 5;
其他常用关键词：
| 关键词 | 含义 | 用法 |
|---|---|---|
| IN | 枚举范围 | WHERE column3 > IN ('NY','CA') |
| AND | 和 | column3 > 2 AND column4 LIKE '%tm%' |
| NOT | 非 | NOT column3 == 1 |
| OR | 或 | column3 > 2 OR column4 LIKE '%tm%' |
| BETWEEN…AND… | 在…和…之间 | column1 BETWEEN 2 AND 3 |
| IS NULL | 是空值 | column1 IS NULL |
JOIN操作
- JOIN
SELECT r.name region, s.name rep, a.name account
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
WHERE r.name = 'Midwest'
ORDER BY a.name;
（r.name region代表将r.name 取别名region，中间省略了AS，
即：r.name AS region）
- LEFT JOIN & RIGHT JOIN
SELECT DISTINCT a.id, w.channel
FROM accounts a
RIGHT JOIN web_events w
ON a.id = w.account_id
WHERE a.id = '1001';
(DISTINCT 取唯一值)
- FULL OUTER JOIN
SELECT column1
FROM table1
FULL OUTER JOIN table2 ON table1.column1 = table2.column2;
- Self JOIN !!!
SELECT column1
FROM table1 T1, table1 T2
WHERE condition;
- UNION
SELECT column1 FROM table1
UNION
SELECT column2 FROM table2;
聚合操作
- GROUP BY
分组，常与聚合函数搭配
SELECT a.name, SUM(total_amt_usd) total_sales
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY a.name;
- HAVING
配合GROUP BY ，后接聚合函数
SELECT column1, column2
FROM table1, table2
WHERE column1==1
GROUP BY column1, column2
HAVING SUM(column1)>2
ORDER BY column1, column2
- 聚合函数
SQL Aggregate 函数：
| 关键词 | 含义 | 用法 |
|---|---|---|
| MAX | 最大值 | MAX(column1) |
| MIN | 最小值 | MIN(column1) |
| COUNT | 计数（不计NULL） | COUNT(column1) |
| AVG | 平均值 | AVG(column1) |
| SUM | 总值 | SUM(column1) |
| FIRST | 第一个值 | FIRST(column1) |
| LAST | 最后一个值 | LAST(column1) |
SQL Scalar 函数:
| 关键词 | 含义 | 用法 |
|---|---|---|
| UCASE() / UPPER() | 将某个字段转换为大写 | UPPER(column1) |
| LCASE() / LOWER() | 将某个字段转换为小写 | LOWER(column1) |
| MID() | 从某个文本字段提取字符（length可选） | MID(column1,start,length) |
| LEN() | 返回某个文本字段的长度 | LEN(column1) |
| ROUND() | 对某个数值字段进行指定小数位数的四舍五入 | ROUND(column1,2) |
| NOW() | 返回当前的系统日期和时间 | NOW() |
| FORMAT() | 格式化某个字段的显示方式 | FORMAT(Now(),'YYYY-MM-DD') |
- DATE 函数
DATE_TRUNC
将日期截取到日期时间列的特定部分，常见的截取依据包括日期、月份 和 年份。
DATE_TRUNC('month', column1)
DATE_PART
获取日期的特定部分，但是注意获取 month 或 dow 意味着无法让年份按顺序排列。而是按照特定的部分分组，无论它们属于哪个年份。
DATE_PART('year', column1)
事实上，日期函数是一块非常大的内容，详细情况请看这里：其他日期函数的用法
- CASE
将数据按照不同的标准分成不同的类别
CASE WHEN SUM(column1) > 1000 THEN 'top'
WHEN SUM(column1) > 666 THEN 'middle'
ELSE 'low' END AS customer_level
子查询&临时表格
- 子查询
主要将一个完整的查询语句作为表格或者条件值使用
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
WHERE column1 OPERATOR
(SELECT column1 [,2 ]
FROM table1 [, table2 ]
[WHERE])
SELECT t1.col1, t1.col2, t1
FROM(SELECT语句) t1
- 临时表格
WITH 语句
WITH t1 AS(SELECT语句),
t2 AS(SELECT语句)
SELECT ……
数据清理
- LEFT&RIGHT
LEFT(col1,2)
RIGHT(col1,3)
- 位置函数(POSITION 和 STRPOS 都区分大小写)
POSITION(',' IN city_state)
STRPOS(city_state, ‘,’)
- 连接(CONCAT)
CONCAT(first_name, ' ', last_name)
=
first_name || ' ' || last_name
- REPLACE
REPLACE(original_string，search_string，replace_string)
- TRIM
移除头尾空格 - CAST （类型转换）
CAST(date_column AS DATE)
=
date_column::DATE
注意
- SQL 不区分大小写
- 但关键词的顺序非常重要！
- 一个好的习惯：关键词大写
虽然SQL可以进行简单的数据清理工作，但python中pandas工具也是一个非常棒的数据工具包：
5天10分钟，学会数据清洗！
