高级的 SQL 查询技巧

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

RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，这里为什么和ROW_NUMBER()不一样那，  

ROW_NUMBER()是排序，当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()则不一样出现相同的，他们的排名是一样的

DENSE_RANK()密集的排名他和RANK()区别在于，排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃的排名

NTILE()函数是将有序分区中的行分发到指定数目的组中，各个组有编号，编号从1开始，就像我们说的’分区’一样 ，分为几个区，一个区会有多少个。

 

 

ROW_NUMBER() OVER (

[PARTITION BY 分组字段]

ORDER BY 排序字段 [ASC|DESC]

)

 

 

 NTILE()函数是将有序分区中的行分发到指定数目的组中，各个组有编号，编号从1开始，就像我们说的’分区’一样 ，分为几个区，一个区会有多少个。

 

 

 

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