1.常见表表达式（CTEs）
with toronto_ppl as (
SELECT DISTINCT name
FROM population
WHERE country = "Canada"
AND city = "Toronto"
)
, avg_female_salary as (
SELECTAVG(salary) as avgSalary
FROM salaries
WHERE gender = "Female"
)
SELECT name
, salary
FROM People
WHERE name in (SELECT DISTINCT FROM toronto_ppl)
AND salary >= (SELECT avgSalary FROM avg_female_salary)
2.递归CTEs.
递归CTE是引用自己的CTE，就像Python中的递归函数一样。递归CTE尤其有用，它涉及查询组织结构图，文件系统，网页之间的链接图等的分层数据，尤其有用。
递归CTE有3个部分：
- 锚构件：返回CTE的基本结果的初始查询
- 递归成员：引用CTE的递归查询。这是所有与锚构件的联盟
- 停止递归构件的终止条件
with org_structure as (
SELECT id
, manager_id
FROM staff_members
WHERE manager_id IS NULL
UNION ALL
SELECT sm.id
, sm.manager_id
FROM staff_members sm
INNER JOIN org_structure os
ON os.id = sm.manager_id
3.临时函数
- 它允许您将代码的块分解为较小的代码块
- 它适用于写入清洁代码
- 它可以防止重复，并允许您重用类似于使用Python中的函数的代码。
SELECT name
, CASEWHEN tenure < 1THEN"analyst"
WHEN tenure BETWEEN1and3THEN"associate"
WHEN tenure BETWEEN3and5THEN"senior"
WHEN tenure > 5THEN"vp"
ELSE"n/a"
ENDAS seniority
FROM employees
=====改写为使用临时函数=====》
CREATE TEMPORARYFUNCTION get_seniority(tenure INT64) AS (
CASEWHEN tenure < 1THEN"analyst"
WHEN tenure BETWEEN1and3THEN"associate"
WHEN tenure BETWEEN3and5THEN"senior"
WHEN tenure > 5THEN"vp"
ELSE"n/a"
END
);
SELECTname
, get_seniority(tenure) as seniority
FROM employees
Rank vs Dense Rank vs Row Number
SELECT Name
, GPA
, ROW_NUMBER() OVER (ORDER BY GPA desc)
, RANK() OVER (ORDER BY GPA desc)
, DENSE_RANK() OVER (ORDER BY GPA desc)
FROM student_grades
ROW_NUMBER（）返回每行开始的唯一编号。当存在关系时（例如，BOB vs Carrie），ROW_NUMBER（）如果未定义第二条标准，则任意分配数字。
Rank（）返回从1开始的每行的唯一编号，除了有关系时，等级（）将分配相同的数字。同样，差距将遵循重复的等级。
dense_rank（）类似于等级（），除了重复等级后没有间隙。请注意，使用dense_rank（），Daniel排名第3，而不是第4位（）。
计算Delta值
SELECT month
, sales
, sales - LAG(sales, 1) OVER (ORDER BY month)
FROM monthly_sales
# Comparing each month's sales to the same month last year
SELECT month
, sales
, sales - LAG(sales, 12) OVER (ORDER BY month)
FROM monthly_sales
计算运行总数
SELECT Month
, Revenue
, SUM(Revenue) OVER (ORDER BY Month) AS Cumulative
FROM monthly_revenue
