当前位置：首页 > news >正文

高级的 SQL 查询技巧

news 2025/9/24 23:36:59

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