函式、排序與分組

Snowflake SQL 入門

George Boorman

Senior Curriculum Manager, DataCamp

字串函式 - INITCAP

語法：INITCAP( <expr> )

將字串中每個單字首字母大寫

SELECT INITCAP(category) AS capitalized_category 
FROM pizza_type

Capitalized pizza names result

字串函式 - CONCAT

合併多個表達式

語法：

CONCAT( <expr1> [ , <exprN> ... ] )

合併前：

Before concatenation category column

將 category 與 ' - Pizza' 結合

SELECT CONCAT(category, ' - Pizza') 
  AS pizza_category 
FROM pizza_type

合併後：

Before concatenation category column

DATE 與 TIME 函式

CURRENT_DATE() 或 CURRENT_DATE
CURRENT_TIME() 或 CURRENT_TIME

SELECT CURRENT_DATE

SELECT CURRENT_TIME

Current date result

Current time result

EXTRACT

語法

EXTRACT( <date_or_time_part> FROM <date_or_time_expr> )
- <date_or_time_part>：year、month、day、weekday 等

SELECT EXTRACT(MONTH FROM order_date) AS order_month,
    COUNT(*) AS num_orders
FROM orders
GROUP BY order_month

Number of orders per month

排序與分組

排序：ORDER BY
分組：GROUP BY
- Snowflake：GROUP BY ALL

GROUP BY ALL

GROUP BY column1, column2

SELECT 
    pizza_type_id,
    size,
    AVG(price) AS average_price
FROM 
    pizzas
GROUP BY
    pizza_type_id, -- explicit columns
    size
ORDER BY 
    pizza_type_id, average_price DESC

GROUP BY ALL

SELECT 
    pizza_type_id,
    size,
    AVG(price) AS average_price
FROM 
    pizzas
GROUP BY ALL -- 不需逐一指定欄位 
ORDER BY 
    pizza_type_id, average_price DESC

重點整理

函式/關鍵字	用途
`INITCAP()`	將字串中每個單字首字母大寫
`CONCAT()`	合併多個字串
`CURRENT_DATE`	取得目前日期
`CURRENT_TIME`	取得目前時間
`EXTRACT`	擷取日期/時間元素，例如從日期取出 `month`
`ORDER BY`	排序查詢結果
`GROUP BY ALL`	依所有（非聚合）欄位分組

一起來練習吧！

Snowflake SQL 入門