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