SQL | Notion

SQL常用关键字

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
AS // 命名新变量

// SELECT后的关键字
DISTINCT // 去重
ALL      // 不去重（默认）

// FROM alias
FROM orders o // o as the alias
JOIN customers c // c as the aliass

// WHERE后的关键字
AND
OR
NOT
IN (a, b, c)
BETWEEN ... AND ...
IS NULL

// LIKE & REGEXP
LIKE 'startwiththis%'
-- % any number of chars
-- _ single char

REGEXP 'this' // like normal正则表达式

// 排序
ORDER BY A, B // 根据多个条件SORT
ORDER BY A DESC, B // 先A逆序，后B顺序

// 限制个数
LIMIT 3 // 从0开始截3个 -> 1,2,3
LIMIT 6, 3 // 从6开始截3个 -> 7,8,9

// Join
JOIN ... ON ... // JOIN XX ON condition
// by default it's inner join
// OUTER JOINS -> LEFT, RIGHT

SQL运算符

>
>=
<
<=
=
!=
<>

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CROSS JOIN

UNION
UNION ALL
INTERSECT
EXCEPT

Subquery

(SELECT 

) AS ...

// use CASE to categorize data
CASE WHEN ... THEN ...
WHEN ... THEN ...
ELSE ... END AS thenameyouwant

// use CASE to filter data
WHERE 
	CASE WHEN ... THEN ...  --- case 1
	WHEN ... THEN ...       --- case 2
	END 
IS NOT NULL
// 如果不符合1或者2的就会变成null，于是就可以用where ... is not null来筛选

// use CASE to aggregate

COUNT(CASE WHEN ... THEN ...
					 WHEN ... THEN ...
) -- 符合条件的会被计数
SUM(CASE WHEN ... THEN 1 ELSE 0 END) -- 获得计数
AVG(SUM(CASE WHEN ... THEN 1 ELSE 0 END))  -- 获得百分比

// Subqueries
// Subquery in WHERE
SELECT ... FROM ...
WHERE ... NOT IN
(
	SELECT ... FROM ...
	.........
)

// Subquery in FROM
FROM
	(
		SELECT ... FROM ...
		......
	) AS subquery

// Subquery in SELECT
SELECT ... AS ...
SELECT
	... AS ...,
	(SELECT AVG(...+...) - ... ) AS ...
//可以多重SELECT，但只能return一个值

// CTE: Common Table Expressions
WITH nameyouwant AS (
	SELECT ...

)
secondoneyouwant AS (
	SELECT ...
)
// CTE stored in memory and thus has better performance

// Windows function
// OVER()
SELECT
	(SELECT AVG(home_goal + away_goal
		FROM match
		WHERE season = '2011/2012') AS overall_avg
FROM match
WHERE season = '2011/2012'
// 可以改写成
SELECT
	AVG(home_goal + away_goal) OVER() AS overall_avg
FROM match
WHERE season = '2011/2012'

//RANK()
SELECT
	RANK() OVER(ORDER BY home_goal + away_goal) AS goals_rank
FROM match
WHERE season = '2011/2012'

// PARTITION BY
SELECT
	AVG(home_goal + away_goal) OVER(PARTITION BY season) AS season_avg
FROM match
// 会返回相应season的avg(home_goal + away_goal)
// 可以PARTITION BY ..., ...多个属性

// Sliding window
ROWS BETWEEN <Start> AND <Finish>
PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW

// 从第一条到当前ROW
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
// 从当前到最后一条
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

// Window functions

// 给每一行一个编号
ROW_NUMBER() OVER() AS row_n
ROW_NUMBER() OVER(ORDER BY year DESC, event ASC) AS row_n -- 根据倒序year, 正序event给编号

LAG(column, n) OVER(...) -- 返回当前行之前的n行数据, by default n=1
LEAD(column, n) OVER(...) -- 返回当前行之后的n行数据, by default n=1
OVER(PARTITION BY ...) 

FIRST_VALUE(column) OVER(ORDER BY year ASC) AS ...
LAST_VALUE(column) OVER(ORDER BY year ASC 
RANGE BETWEEN
	UNBOUNDED PRECEDING AND  -- LAST_VALUE要添加一个范围
	UNBOUNDED FOLLOWING      -- 因为Window默认是从第一行到当前行，加上这个RANGE之后就是全表
) AS ...

// RANK
ROW_NUMBER()   -- 从上到下123456789
RANK()         -- 值一样的话，跳过排序 113456
DENSE_RANK()   -- 值一样的话，不跳过次序排序 112345

// 要注意的一点就是rank with partition和rank without partition
// rank是和全表内容rank的，即使根据另一个属性分类了，也是在全表对比，而不是在那个相同的属性里面对比

执行顺序：

FROM
WHERE
GROUP BY
SELECT
HAVING
ORDER BY