本文共 2769 字，大约阅读时间需要 9 分钟。

作为一名技术人员，我认为掌握以下10个SQL高级技巧对您在数据科学面试中至关重要。这些概念不仅能帮助您更高效地解决问题，还能让您在技术讨论中脱颖而出。

1. 常见表表达式（CTEs）

CTEs（Common Table Expressions）在SQL中是不可或缺的工具，尤其是在处理复杂的子查询时。通过将表数据抽象为临时表，您可以在查询中模块化代码，同样您可以为每个CTE分配变量名，使代码更加易于维护。例如，在下面的查询中，CTE toronto_ppl 把Toronto的用户数据抽象出来，而 avg_female_salary 则计算女性平均工资。

WITH toronto_ppl AS (
    SELECT DISTINCT name FROM population WHERE country = 'Canada' AND city = 'Toronto'
), avg_female_salary AS (
    SELECT AVG(salary) as avgSalary FROM salaries WHERE gender = 'Female'
)
SELECT name, salary FROM People WHERE name IN (SELECT name FROM toronto_ppl) AND salary > (
    SELECT avgSalary FROM avg_female_salary
);

2. 递归CTEs

递归CTE具有自我引用特性，适用于处理层级数据，比如组织架构图中的上级至员工关系。递归CTE的应用场景包括查询文件系统、网页链接图等分层数据。以下是一个获取每个员工ID管理者ID的递归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. 临时函数

在需要重复使用复杂逻辑时，临时函数能派上用场。例如，下面的资历函数可以通过CASE WHEN分解，为每个员工分配不同的级别：

CREATE TEMPORARY FUNCTION get_seniority(tenure INT64) AS (
    CASE WHEN tenure < 1 THEN 'analyst'
    WHEN tenure BETWEEN 1 AND 3 THEN 'associate'
    WHEN tenure BETWEEN 3 AND 5 THEN 'senior'
    WHEN tenure > 5 THEN 'vp'
    ELSE 'n/a'
    END
);
SELECT name, get_seniority(tenure) AS seniority FROM employees;

4. 使用CASE WHEN枢转数据

CASE WHEN不仅适合编写条件语句，还能用来枢转数据。例如，可以通过CASE WHEN将多个月的收入数据合并为单一列：

SELECT 
   CASE 
        WHEN month = 'Jan' THEN revenue
        ELSE NULL
    END AS Jan_Revenue
FROM monthly_data;

5. EXCEPT vs NOT IN

EXCEPT和NOT IN虽然都用于比较两张表，但细微差别在于它们过滤数据的方式。EXCEPT会删除重复行并保留唯一值，而NOT IN会比较两个表的行是否完全一致。

6. 自联结

尽管自联结看似复杂，但在一些场景下却非常实用。例如，下面的自联结查询可以找出比管理人员工资更高的员工：

SELECT a.Name as Employee FROM Employee a
JOIN Employee b ON a.ManagerID = b.Id
WHERE a.Salary > b.Salary;

7. RANK vs DENSE_RANK vs ROW_NUMBER

在SQL中，您可以使用RANK、DENSE_RANK和ROW_NUMBER等函数来为数据排序。RANK函数会跳过重复值，而DENSE_RANK则不会留下间隔。以下是其中一个示例：

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;

8. 计算Delta值

当您需要比较不同时间段的数据时，LAG和LEAD函数特别有用。例如，可以计算本月与上个月销售额的差异：

SELECT 
    month,
    sales,
    sales - LAG(sales, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_sales;

9. 计算运行总数

如果您需要生成累计数据，使用SUM函数搭配窗口函数就能轻松实现。以下是一个累计营收额的示例：

SELECT 
    Month,
    Revenue,
    SUM(Revenue) OVER (/order by Month) AS Cumulative
FROM monthly_revenue;

10. 日期时间操纵

在处理日期时间数据时，掌握一些关键函数至关重要。比如，可以用DATE_DIFF计算两个日期之间的差异：

SELECT 
    a.Id,
    a.RecordDate,
    a.Temperature,
    b.RecordDate,
    DATEDIFF(a.RecordDate, b.RecordDate) as diff_date
FROM Weather a
JOIN Weather b ON a.Temperature > b.Temperature;

总结

掌握以上10个SQL高级技巧不仅有助于您在面试中脱颖而出，还能让您在日常工作中更加高效。希望这篇文章对您有所帮助，祝您学习愉快！