數據分析系列——SQL 必知必會（三）

此篇繼續學習SQL的進階操作

第七章 創建計算欄位

7.1 計算欄位

為什麼需要計算欄位：存儲在表中的數據不是應用程序所需要的，需要直接從資料庫中檢索出轉換、計算或格式化過的數據，而不是先檢索出數據，然後再在客戶端應用程序中重新格式化

7.2 拼接欄位

含 接到一起構成單個值

Access 和 SQL Sever版：

SELECT vend_name + ( + vend_country + )

FROM Vendors

ORDER BY vend_name;

DB2、Oracle、SQLite等：

SELECT vend_name || ( || vend_country || )

FROM Vendors

ORDER BY vend_name;

MySQL 和 MariaDB：

SELECT Concat(vend_name, ( , vend_country , ))

FROM Vendors

ORDER BY vend_name;

以上版本，在SELECT輸出時，結合成一個計算欄位的兩個列用空格填充；為正確返回格式化數據，需要去掉這些空格，對應的代碼為：

SELECT RTRIM(vend_name) + ( + RTRIM(vend_country) + )

FROM Vendors

ORDER BY vend_name;

SELECT RTRIM(vend_name) || ( || RTRIM(vend_country) || )

FROM Vendors

ORDER BY vend_name;

註：RTRIM()去掉字元串右邊的空格，LTRIM()去掉字元串左邊的空格，TRIM()去掉字元串左右兩邊的空格。

使用別名，使計算欄位在客戶端也可以使用

SELECT RTRIM(vend_name) + ( + RTRIM(vend_country) + ) AS vend_title

FROM Vendors

ORDER BY vend_name;

SELECT RTRIM(vend_name) || ( || RTRIM(vend_country) || ) AS vend_title

FROM Vendors

ORDER BY vend_name;

SELECT Concat(vend_name, ( , vend_country , )) AS vend_title

FROM Vendors

ORDER BY vend_name;

7.3 執行算數計算

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price

FROM OrderItems

WHERE order_number=2008;

註：SQL支持的基本算數操作符「+，-，*，/」

第八章 使用數據處理函數

8.1 函數

使用函數的困境：實現同一個功能，每一個DBMS都有自己特定的函數；只有少數幾個函數被所有主要的DBMS等同地支持，因此，SQL函數可移植性較低。

8.2 使用函數

文本處理函數：大多數SQL實現支持的函數

（1）UPPER（）：將文本轉化成大寫

SELECT vend_name , UPPER(vend_name) AS vend_name_upcase

FROM Vendors

ORDER BY vend_name;

（2）SOUNDEX（）：返回字元串的SOUNDEX值，將文本轉換為描述其語音表示的子母數字模式

SELECT cust_name, cust_contact

FROM Customers

WHERE cust_contact = Michael Green;

說明：此語句返回結果為空，因為Customers表中的聯繫名為Michelle Green，使用下面的代碼將成功查找內容

SELECT cust_name, cust_contact

FROM Customers

WHERE SOUNDEX(cust_contact) = SOUNDEX(Michael Green);

（3）其他文本處理函數

LEFT()：返回字元串左邊的字元

LENGTH()：返回字元串的長度

LOWER()：將字元串轉換為小寫

日期和時間處理函數：可移植性最差

目標：檢索2012年的所有訂單

SQL Server

SELECT order_num

FROM Orders

WHERE DATEPART(yy, order_date) = 2012;

ACCESS

SELECT order_num

FROM Orders

WHERE DATEPART(yyyy, order_date) = 2012;

PostgreSQL

SELECT order_num

FROM Orders

WHERE DATE_PART(year, order_date) = 2012;

Oracle

SELECT order_num

FROM Orders

WHERE to_number(to_char( order_date,yyyy)) = 2012;

另一種方法：

SELECT order_num

FROM Orders

WHERE order_date BETWEEN to_date(01-01-2012) AND to_date(31-12-2012)

MYSQL:

SELECT order_num

FROM Orders

WHERE YEAR(order_date) = 2012;

SQLite

SELECT order_num

FROM Orders

WHERE strftime(%Y, order_date) = 2012;

數值處理函數：一致性、統一性較高

ABS()：返回一個數的絕對值

COS()：餘弦

EXP()：指數

PI()：圓周率

SIN()：正弦

SQRT()：平方根

TSN()：正切

本章小結：函數的具體應用應結合DBMS，靈活使用

第九章 匯總數據

9.1 聚集函數

功能：確定表中行數；獲得表中某些行的和；找出表列的最大值、最小值、平均值

特點：聚集函數在各SQL實現中得到相當一致的支持

5個聚集函數：AVG()，COUNT()，MAX()，MIN()，SUM()

AVG()：對表中的行數計數並計算其列值之和，求平均值

SELECT AVG(prod_price) AS avg_price

FROM Products;

SELECT AVG(prod_price) AS avg_price

FROM Products

WHERE vend_id = DLL01;

注:AVG()函數忽略列值為NULL的行

COUNT()：確定表中行的數目或者符合特定條件的行的數目

（1）COUNT(*)：對中行的數目進行計數，不管表列中包含的是空值還是非空值

SELECT COUNT(*) AS num_cust

FROM Customers;

（2）指定列名的計數：忽略指定列的值為空的行

SELECT COUNT(cust_email) AS num_cust

FROM Customers;

MAX/MIN()：返回列中的最大（小）值

SELECT MAX(prod_price) AS max_price

FROM Products;

註：MAX、MIN函數忽略值為NULL的行；當用於文本數據時，MAX()返回按該列排序後的最後一行，MIN()返回最前面的行

SUM()：返回指定列值的和，忽略列值為NULL的行

SELECT SUM(quantity) AS items_ordered

FROM OrderItems

WHERE order_num =20005;

SELECT SUM(quantity*item_price) AS total_price

FROM OrderItems

WHERE order_num =20005;

9.2 聚集不同值

SELECT AVG(DISTINCT prod_price) AS avg_price

FROM Products

WHERE vend_id = DLL01;

註：DISTINCT不能用於COUNT(*)，必須使用列名，不能用於計算或表達式

9.3 組合聚集函數

包含多個聚集函數

SELECT COUNT(*) AS num_items,

MIN(prod_price) AS price_min,

Max(prod_price) AS price_max,

AVG(prod_price) AS price_avg,

FROM Products;

待續......