提升SQL技能，掌握数据分析

提升SQL技能，掌握数据分析

在今天这个数据驱动的世界，掌握SQL（结构化查询语言）不仅是一项技术能力，更是一种职业竞争力。想象一下，如果你能像魔法师一样轻松从庞大且复杂的数据集中提取出有用的信息，直观呈现给团队和管理层，那你就不仅是在做数据分析，更是在为决策提供支持。那么，如何能掌握更高级的SQL技巧呢？接下来，让我们一同探索一些提升数据分析能力的关键技巧。

常见表表达式（CTEs）

CTE的定义与用途

常见表表达式（CTEs）是给复杂查询提供结构的一种方式。你可以把CTE想成是在撰写一篇研究论文时使用的小章节，它们帮助你清楚地组织思路和逻辑。通过CTE，你可以创建临时表，使得查询的逻辑结构更为清晰。例如：

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 DISTINCT name FROM toronto_ppl) 
AND salary >= (SELECT avgSalary FROM avg_female_salary);

在这个例子中，CTE不仅让查询更简约易懂，还显著提高了数据提取的效率。这是一项基础技能，但却是扶持高级SQL能力的关键基础。你也许会好奇，为什么我们不直接写长查询呢？答案简单且直接：长查询往往导致可读性差、难以维护！来源

示例：使用CTE简化复杂查询

使用CTE的另一个明显好处是，它能将复杂的查询简化为更易管理的模块。想象一下，在满是错综复杂的子查询时，你是否有过审视挫败感？例如，下面的查询使用多个子查询：

SELECT Sales_Manager, Product_Category, UnitPrice
FROM Dummy_Sales_Data_v1
WHERE Sales_Manager IN (SELECT DISTINCT Sales_Manager
                        FROM Dummy_Sales_Data_v1
                        WHERE Shipping_Address = 'Germany' AND UnitPrice > 150)
AND Product_Category IN (SELECT DISTINCT Product_Category
                         FROM Dummy_Sales_Data_v1
                         WHERE Product_Category = 'Healthcare' AND UnitPrice > 150)
ORDER BY UnitPrice DESC;

你是否能够立刻找出这样的查询中的所有关键要素？使用CTE，我们可以将此查询改写为：

WITH SM AS
(
  SELECT DISTINCT Sales_Manager
  FROM Dummy_Sales_Data_v1
  WHERE Shipping_Address = 'Germany' AND UnitPrice > 150
),
PC AS
(
  SELECT DISTINCT Product_Category
  FROM Dummy_Sales_Data_v1
  WHERE Product_Category = 'Healthcare' AND UnitPrice > 150
)
SELECT Sales_Manager, Product_Category, UnitPrice
FROM Dummy_Sales_Data_v1
WHERE Product_Category IN (SELECT Product_Category FROM PC)
AND Sales_Manager IN (SELECT Sales_Manager FROM SM)
ORDER BY UnitPrice DESC;

这样重构的查询不仅能提高你在查询时的清晰度，还便于维护与更新。来源

递归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 
   JOIN org_structure os ON os.id = sm.manager_id 
) 
SELECT * FROM org_structure;

这个查询自动将每个员工的层级关系建立起来。正因为有了递归的能力，CTE在复杂数据结构提供了巨大的灵活性和可读性。来源

窗口函数与排名

ROW_NUMBER()、RANK()与DENSE_RANK()

你千万不要小看这些排名工具，它们能够令你的数据分析过程如虎添翼！当需要为行赋予排名时，我们会用到 ROW_NUMBER()、RANK() 和 DENSE_RANK()。你是否曾经因为重复值而陷入困境？这三者的区别恰好可以在这时为你提供解决思路。

SELECT Name, GPA, 
       ROW_NUMBER() OVER (ORDER BY GPA DESC) AS RowNum, 
       RANK() OVER (ORDER BY GPA DESC) AS RankNum, 
       DENSE_RANK() OVER (ORDER BY GPA DESC) AS DenseRankNum 
FROM student_grades;

在上述代码中，ROW_NUMBER() 为每一行赋予唯一的编号，而RANK() 和 DENSE_RANK() 则处理重复值的方式不同，你会发现它们在多种情况下能各司其职。假如你在创建竞争分析时，选择使用错误的排名工具，可能会错估潜在客户的价值。来源

计算Delta值与运行总和

利用 LAG() 和 LEAD() 函数，我们可以对不同时期的字段值进行比较。例如：

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

这样我们便能轻松比较本月和上月的销售数据。运行总和的计算更是可以通过结合窗口函数和 SUM() 来达成，例如：

SELECT Month, Revenue, SUM(Revenue) OVER (ORDER BY Month) AS Cumulative 
FROM monthly_revenue;

想象一下，你能快速获取到每个月的累计收入，这在制定策略时可提供强大的支持！来源

其他窗口函数的实用示例

窗口函数不仅能提高你的数据分析能力，它们还有助于识别销售趋势。例如，通过分析每个产品的销售数据，你可以轻松了解哪些产品在特定季节的表现更好，从而在库存管理和推广策略上做出更为明智的决策。这种强大的工具使得你的数据不仅仅是数字，更是问题解决的钥匙。来源

数据操作技巧

使用CASE WHEN语句

想要实现复杂的条件逻辑，CASE WHEN 语句就是你的最佳朋友。它不仅能用来实现复杂的数据分类，还能在数据透视时提供灵活的聚合选择。例如：

SELECT Sales_Manager, 
       COUNT(CASE WHEN Shipping_Address = 'Singapore' THEN OrderID END) AS Singapore_Orders, 
       COUNT(CASE WHEN Shipping_Address = 'UK' THEN OrderID END) AS UK_Orders 
FROM Dummy_Sales_Data_v1 
GROUP BY Sales_Manager;

想象一下，当你需要对不同市场的销售表现作出直观对比时，有了这种灵活的语句，数据不再局限于冷冰冰的数字，而是变得生动鲜明。来源

EXCEPT与NOT IN的区别

在比较两个数据集时，EXCEPT 和 NOT IN 都是不二之选，但它们的应用场景却各有千秋。EXCEPT 去掉重复值并返回不同的行，而NOT IN 则会检查某个值是否存在于另一个查询集中。这两者的灵活运用能够让你的查询更加精准和高效。来源

自联结的实现

自联结则能够帮助你在同一表中进行比较，比如查找那些工资高于直接管理者的员工。想象一下，这不仅是对数据库操作技巧的考量，更是在挑战你的逻辑思维：

SELECT a.Name AS Employee 
FROM Employee a 
JOIN Employee b ON a.ManagerId = b.Id 
WHERE a.Salary > b.Salary;

通过这种直观的自联结运算，便能快速识别潜在的值得关注的职员，帮助管理层更好地决策。来源

结论

掌握高级 SQL 技巧不仅能帮助你提升数据分析能力，更能在日后工作中迅速、准确地提取出有价值的信息。正如我们所探索的，从 CTE 到窗口函数，你的每一个选择都可能影响最终的数据结论。你有没有想过：在你当前的工作环境中，如何应用这些技巧来创造更多的价值？又或者有没有你希望尝试的新功能呢？记住，科技在不断进步，掌握新技能永远是保持竞争力的最佳方式。探索、学习并应用这些技巧，你将会在数据分析的旅程中走得更远。