首页   

【干货】2小时用AI完成的SQL教程也太赞了吧，不推荐deepseek

CDA数据分析师  · 大数据  · 2 天前

正文

作者：数据星爷，CDA特约作者
上市公司资深分析师/微软认证Excel专家


SQL查询是数据分析工作的基础，也是CDA数据分析师一级的核心考点，人工智能时代，AI能为我们节省多少工作量？本来想用deepseek部署，被卡出三界外，不在五行中，后来选择了通义灵码。


环境

  • AI环境：

    • VScode+通义灵码

  • 练习网站：

    • https://sqlfiddle.com/postgresql/online-compiler

1. 基础概念

1.1  数据库和表的概念

数据库：数据库是一个有组织的数据集合，通常以电子表格的形式存储。它由多个表组成，每个表代表一种特定类型的数据集合。

表：表是数据库中数据的基本存储单位，类似于Excel中的工作表。表由行（记录）和列（字段）构成。每一行代表一条记录，每一列代表一个属性。

例如，在一个电商数据库中，可能会有以下几张表：

  • users 表：存储用户信息，如用户ID、用户名、邮箱等。

  • orders 表：存储订单信息，如订单ID、用户ID、订单日期等。

  • products 表：存储商品信息，如商品ID、名称、价格等


1.2 SQL语言的基本结构

SQL（Structured Query Language，结构化查询语言）是一种用于管理和操作关系型数据库的标准语言。它的基本结构包括以下几个部分：

  • SELECT：用于从数据库中检索数据。

  • FROM：指定要查询的表。

  • WHERE：用于过滤数据，只返回满足条件的记录。

  • GROUP BY：用于将结果集按一个或多个列进行分组。

  • HAVING：用于在分组后进一步过滤数据。

  • ORDER BY：用于对结果集进行排序。

  • LIMIT：用于限制返回的结果数量。


1.3 示例

    假设我们有一个名为 users 的表，包含以下字段：

    • id (用户ID)

    • name (用户名)

    • email (用户邮箱)

    我们可以编写一个简单的SQL查询来获取所有用户的姓名和邮箱：

    SELECT name, email FROM users;

    如果我们只想获取名字中包含 "张" 的用户，可以使用 WHERE 子句：

    SELECT name, emailFROM usersWHERE name LIKE '%张%';

    1.4 总结

    通过理解数据库和表的概念以及SQL语言的基本结构，你可以开始构建简单的查询语句来检索和操作数据。这是学习SQL的基础，后续我们将在此基础上深入学习更复杂的查询和操作。

    如果大家对SQL比较熟悉，学得比较好，想了解具体的代码的掌握情况，可以在CDA认证小程序上刷模拟题测试效果。

    图片


    2. 数据检索

    数据检索是SQL中最常用的操作之一，它允许你从数据库中提取所需的数据。我们将详细讲解如何使用SELECT语句及其相关子句来实现各种数据检索操作。

    2.1 使用 WITH 子句创建临时表

    首先，我们创建一个包含10条员工数据的临时表 employees。

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    




    
SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)

    2.2  使用 SELECT 语句从单个表中检索数据

    示例

    要获取所有员工的姓名和部门：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT name, departmentFROM employees;

    结果：

    name

    department

    张三

    销售部

    李四

    市场部

    王五

    销售部

    赵六

    技术部

    孙七

    市场部

    周八

    技术部

    吴九

    销售部

    郑十

    市场部

    钱十一

    技术部

    王十二

    销售部

    2.3 使用 WHERE 子句过滤数据

    示例

    要获取工资大于5000的员工信息：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary 




    
UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT name, salaryFROM employeesWHERE salary > 5000;

      结果

    name

    salary

    张三

    5000

    李四

    6000

    王五

    5500

    赵六

    7000

    孙七

    6500

    周八

    7500

    郑十

    5000

    钱十一

    8000

    2.4 使用 ORDER BY 排序结果集

    示例

    要按工资从高到低排序员工信息：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL




    
    SELECT 10, '王十二', '销售部', 4000)SELECT name, salaryFROM employeesORDER BY salary DESC;

      结果

    name

    salary

    钱十一

    8000

    周八

    7500

    赵六

    7000

    孙七

    6500

    李四

    6000

    王五

    5500

    张三

    5000

    郑十

    5000

    吴九

    4500

    王十二

    4000

    2.5 使用 LIMIT 限制返回行数

    示例

    要获取前5名最高工资的员工信息，并按工资降序排序：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT name, salaryFROM employeesORDER BY salary DESCLIMIT 5; 
    结果

    name

    salary

    钱十一

    8000

    周八

    7500

    赵六

    7000

    孙七

    6500

    李四

    6000

    2.6 组合使用多个子句

    示例

    要获取属于 "销售部" 的前3名最高工资的员工信息，并按工资降序排序：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT name, salaryFROM employeesWHERE department = '销售部'ORDER BY salary DESCLIMIT 3;

     结果

    name

    salary

    王五

    5500

    张三

    5000

    吴九

    4500

    2.7 总结

    通过上述示例，你可以看到如何使用 WITH 子句创建一个临时表，并在此基础上进行各种数据检索操作。这些示例涵盖了 SELECT、WHERE、ORDER BY 和 LIMIT 的基本用法，帮助你更好地理解和验证查询结果。

    • 使用SELECT语句从单个表中检索数据

    • 使用WHERE子句过滤数据

    • 使用ORDER BY排序结果集

    • 使用LIMIT限制返回行数

    3. 聚合与分组

    使用COUNT(), SUM(), AVG(), MAX(), MIN()等聚合函数

    使用GROUP BY对数据进行分组

    3.1 使用 WITH 子句创建临时表

    首先，我们创建一个包含10条员工数据的临时表 employees。

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION




    
 ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)

    3.2 聚合函数

    聚合函数用于对一组值执行计算并返回单个值。常用的聚合函数包括：

    • COUNT()：计算行数。

    • SUM()：计算总和。

    • AVG()：计算平均值。

    • MAX()：返回最大值。

    • MIN()：返回最小值。

    示例- count

    计算总员工数：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT COUNT(*) AS total_employeesFROM employees;
    结果：

    total_employees

    10

    示例-sum

    计算所有员工的总薪水：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    




    
SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT SUM(salary) AS total_salaryFROM employees;

    结果：

    total_salary

    59000

    示例-avg

    计算所有员工的平均薪水：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT AVG(salary) AS average_salaryFROM employees;

    结果：

    average_salary

    5900.00

    示例-max

    返回最高薪水：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 




    
5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT MAX(salary) AS max_salaryFROM employees;

    结果：

    max_salary

    8000

    示例-min

    返回最低薪水：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT MIN(salary) AS min_salaryFROM employees;

    结果：

    min_salary

    4000

    3.3  使用 GROUP BY 进行分组

    GROUP BY 子句用于将结果集按一个或多个列进行分组，通常与聚合函数一起使用。

    示例-count

    按部门分组，计算每个部门的员工数：

    WITH




    
 employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT department, COUNT(*) AS employee_countFROM employeesGROUP BY department;

    结果：

    department

    employee_count

    销售部

    4

    市场部

    3

    技术部

    3

    示例-sum

    • 按部门分组，计算每个部门的总薪水：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT




    
 10, '王十二', '销售部', 4000)SELECT department, SUM(salary) AS total_salaryFROM employeesGROUP BY department;

    结果：

    department

    total_salary

    销售部

    19000

    市场部

    17500

    技术部

    22500

    示例-avg

    • 按部门分组，计算每个部门的平均薪水：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT department, AVG(salary) AS average_salaryFROM employeesGROUP BY department;

    结果：

    department

    average_salary

    销售部

    4750

    市场部

    5833.3

    技术部

    7500

    示例-max-min

    • 按部门分组，返回每个部门的最高和最低薪水：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, '销售部' AS department, 5000 AS salary UNION ALL    SELECT 2, '李四', '市场部', 6000 UNION ALL    SELECT 3, '王五', '销售部', 5500 UNION ALL    SELECT 4, '赵六', '技术部', 7000 UNION




    
 ALL    SELECT 5, '孙七', '市场部', 6500 UNION ALL    SELECT 6, '周八', '技术部', 7500 UNION ALL    SELECT 7, '吴九', '销售部', 4500 UNION ALL    SELECT 8, '郑十', '市场部', 5000 UNION ALL    SELECT 9, '钱十一', '技术部', 8000 UNION ALL    SELECT 10, '王十二', '销售部', 4000)SELECT department, MAX(salary) AS max_salary, MIN(salary) AS min_salaryFROM employeesGROUP BY department;

    结果：

    department

    max_salary

    min_salary

    销售部

    5500

    4000

    市场部

    6500

    5000

    技术部

    8000

    7000

    3.4 总结

    通过上述示例，你可以看到如何使用聚合函数和 GROUP BY 子句来对数据进行分组和计算。这些示例涵盖了 COUNT()、SUM()、AVG()、MAX() 和 MIN() 的基本用法，并结合 GROUP BY 进行分组操作。

    如果大家对SQL比较熟悉，学得比较好，想了解具体的代码的掌握情况，可以在CDA认证小程序上刷模拟题测试效果。

    图片


    4. 多表操作

    我们将详细讲解SQL中的多表操作，包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN）。我们将使用 WITH 子句创建两个临时表 employees 和 departments，然后进行各种多表查询。

    4.1 使用 WITH 子句创建临时表

    首先，我们创建两个临时表 employees 和 departments。

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 




    
UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部')

    4.2  内连接（INNER JOIN）

    内连接返回两个表中满足连接条件的所有行。

    示例

    要获取每个员工及其所属部门的名称：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部')SELECT e.name, d.department_name, e.salaryFROM employees eINNER JOIN departments d ON e.department_id = d.id;

      结果

    name

    department_name

    salary

    张三

    销售部

    5000

    李四

    市场部

    6000

    王五

    销售部

    5500

    赵六

    技术部

    7000

    孙七

    市场部

    6500

    周八

    技术部

    7500

    吴九

    销售部

    4500

    郑十

    市场部

    5000

    钱十一

    技术部

    8000

    王十二

    销售部

    4000

    4.3 左连接（LEFT JOIN）

      左连接返回左表中的所有行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则结果为 NULL。

      示例

      要获取所有员工及其所属部门的名称，即使某些员工没有部门：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000 UNION ALL    SELECT 11, '无部门员工', NULL, 4000 -- 添加一个没有部门的员工),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部')SELECT e.name, d.department_name, e.salaryFROM employees eLEFT JOIN departments d ON e.department_id = d.id;

      结果

    name

    department_name

    salary

    张三

    销售部

    5000

    李四

    市场部

    6000

    王五

    销售部

    5500

    赵六

    技术部

    7000

    孙七

    市场部

    6500

    周八

    技术部

    7500

    吴九

    销售部

    4500

    郑十

    市场部

    5000

    钱十一

    技术部

    8000

    王十二

    销售部

    4000

    无部门员工

    NULL

    4000

    4.4 右连接（RIGHT JOIN）

    右连接返回右表中的所有行，以及左表中满足连接条件的行。如果左表中没有匹配的行，则结果为 NULL。

    示例

    要获取所有部门及其所属员工的名称，即使某些部门没有员工：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部' UNION ALL    SELECT 4, '人力资源部' -- 添加一个没有员工的部门)SELECT e.name, d.department_name, e.salaryFROM employees eRIGHT JOIN departments d ON e.department_id = d.id;

      结果

    name

    department_name

    salary

    张三

    销售部

    5000

    李四

    市场部

    6000

    王五

    销售部

    5500

    赵六

    技术部

    7000

    孙七

    市场部

    6500

    周八

    技术部

    7500

    吴九

    销售部

    4500

    郑十

    市场部

    5000

    钱十一

    技术部

    8000

    王十二

    销售部

    4000

    NULL

    人力资源部

    NULL

    4.5 全外连接（FULL OUTER JOIN）

    全外连接返回两个表中的所有行，如果某个表中没有匹配的行，则结果为 NULL。需要注意的是，并非所有数据库系统都支持 FULL OUTER JOIN，例如 MySQL 不支持，但可以通过 UNION 实现类似效果。

    示例

    要获取所有员工及其所属部门的名称，以及所有部门及其所属员工的名称，即使某些员工或部门没有匹配：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部' UNION 




    
ALL    SELECT 4, '人力资源部' -- 添加一个没有员工的部门)SELECT e.name, d.department_name, e.salaryFROM employees eFULL OUTER JOIN departments d ON e.department_id = d.id;

    结果

    name

    department_name

    salary

    张三

    销售部

    5000

    李四

    市场部

    6000

    王五

    销售部

    5500

    赵六

    技术部

    7000

    孙七

    市场部

    6500

    周八

    技术部

    7500

    吴九

    销售部

    4500

    郑十

    市场部

    5000

    钱十一

    技术部

    8000

    王十二

    销售部

    4000

    NULL

    人力资源部

    NULL

    4.6 总结

    通过上述示例，你可以看到如何使用内连接、左连接、右连接和全外连接来操作多个表。这些示例涵盖了 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN 的基本用法，并结合 WITH 子句创建临时表进行验证。

    5. 高级特性

    我们将详细讲解SQL中的高级特性，包括公用表表达式（CTE）、子查询和窗口函数。我们将使用 WITH 子句创建临时表 employees 和 departments，然后进行各种高级查询。

    5.1 使用 WITH 子句创建临时表

    首先，我们创建两个临时表 employees 和 departments。

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION




    
 ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部')

    5.2 公用表表达式（CTE）

    公用表表达式（CTE）是一个临时结果集，可以在查询中多次引用。CTE 使用 WITH 子句定义。

    示例

    要计算每个部门的平均薪水，并使用 CTE 来简化查询：

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部'),department_avg_salary AS (--每个部门平均薪水    SELECT department_id, AVG(salary) AS avg_salary    FROM employees    GROUP BY department_id)SELECT d.department_name, das.avg_salaryFROM departments dJOIN department_avg_salary das ON d.id = das.department_id;

    结果

    department_name

    avg_salary

    销售部

    4750.0

    市场部

    5833.3

    技术部

    7500.0

    5.3 子查询

    子查询是嵌套在另一个查询中的查询。子查询可以出现在 SELECT、FROM、WHERE 子句中。

    示例

    要获取薪水高于平均薪水的员工：

    WITH




    
 employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT name, salaryFROM employeesWHERE salary > (SELECT AVG(salary) FROM employees);

    结果：

    name

    salary

    李四

    6000

    赵六

    7000

    孙七

    6500

    周八

    7500

    钱十一

    8000

    5.4 常用的窗口函数

    窗口函数对一组行进行计算，并返回每个行的计算结果。窗口函数通常与 OVER 子句一起使用。

    序号

    函数

    含义

    语法

    1

    ROW_NUMBER()

    为每一行分配一个唯一的序号。

    ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

    2

    RANK()

    为每一行分配一个排名，相同值会分配相同的排名，但会跳过后续的排名。

    RANK() OVER (PARTITION BY ... ORDER BY ...)

    3

    DENSE_RANK()

    为每一行分配一个排名，相同值会分配相同的排名，但不会跳过后续的排名

    DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)

    4

    NTILE(n)

    将结果集分成 n 个桶，并为每一行分配一个桶号

    NTILE(n) OVER (PARTITION BY ... ORDER BY ...)

    5

    LAG() 

    LEAD()

    访问前一行或后一行的数据。

    LAG(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)

    LEAD(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)

    6

    FIRST_VALUE()  LAST_VALUE()

    返回窗口中的第一个值和最后一个值。

    FIRST_VALUE(column) OVER (PARTITION BY ... ORDER BY ...)

    LAST_VALUE(column) OVER (PARTITION BY ... ORDER BY ...)

    7

    聚合函数

    聚合函数也可以作为窗口函数使用，如 SUM(), AVG(), MAX(),  COUNT() ...

    SUM(column) OVER (PARTITION BY ... ORDER BY ...)

    ROW_NUMBER()，RANK() 和 DENSE_RANK()

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT    name, department_id, salary,   ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_number,   RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_salary,   DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_salaryFROM employees

    结果：

    name

    department_id

    salary

    ROW_NUMBER

    rank_salary

    dense_rank_salary

    张三

    1

    5000

    1

    1

    1

    王五

    1

    4500

    2

    2

    2

    吴九

    1

    4500

    3

    2

    2

    王十二

    1

    4000

    4

    4

    3

    孙七

    2

    6500

    1

    1

    1

    李四

    2

    6000

    2

    2

    2

    郑十

    2

    5000

    3

    3

    3

    钱十一

    3

    8000

    1

    1

    1

    周八

    3

    7500

    2

    2

    2

    赵六

    3

    7000

    3

    3

    3

    NTILE(2)

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT




    
 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, department_id, salary,    NTILE(2) OVER (PARTITION BY department_id ORDER BY salary DESC) AS ntile_2FROM     employees;

    结果：

    name
    department_id
    salary
    ntile
    张三
    1
    5000
    1
    王五
    1
    4500
    1
    吴九
    1
    4500
    2
    王十二
    1
    4000
    2
    钱十一
    3
    8000
    1
    周八
    3
    7500
    1
    赵六
    3
    7000
    2
    孙七
    2
    6500
    1
    李四
    2
    6000
    1
    郑十
    2
    5000
    2


    LAG() 和 LEAD()

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, department_id, salary,    LAG(salary, 1, 




    
0) OVER (PARTITION BY department_id ORDER BY salary) AS lag_salary,    LEAD(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary) AS lead_salaryFROM employees;

    结果：

    name
    department_id
    salary
    lag
    lead
    王十二
    1
    4000
    0
    4500
    王五
    1
    4500
    4000
    4500
    吴九
    1
    4500
    4500
    5000
    张三
    1
    5000
    4500
    0
    赵六
    3
    7000
    0
    7500
    周八
    3
    7500
    7000
    8000
    钱十一
    3
    8000
    7500
    0
    郑十
    2
    5000
    0
    6000
    李四
    2
    6000
    5000
    6500
    孙七
    2
    6500
    6000
    0


    FIRST_VALUE() 和 LAST_VALUE()

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9




    
, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, department_id, salary,    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS first_value_salary,    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS last_value_salaryFROM employees;

    结果

    name
    department_id
    salary
    first_value_salary
    last_value_salary
    郑十
    2
    5000
    5000
    5000
    李四
    2
    6000
    5000
    6000
    孙七
    2
    6500
    5000
    6500
    王十二
    1
    4000
    4000
    4000
    王五
    1
    4500
    4000
    4500
    吴九
    1
    4500
    4000
    4500
    张三
    1
    5000
    4000
    5000
    赵六
    3
    7000
    7000
    7000
    周八
    3
    7500
    7000
    7500
    钱十一
    3
    8000
    7000
    8000


    💡注意： LAST_VALUE() 默认情况下会在窗口内逐行计算，如果需要在整个分区计算，可以使用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 4500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION 




    
ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, department_id, salary,    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS first_value_salary,    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value_salaryFROM employees;

    结果：

    name
    department_id
    salary
    first_value_salary
    last_value_salary
    郑十
    2
    5000
    5000
    6500
    李四
    2
    6000
    5000
    6500
    孙七
    2
    6500
    5000
    6500
    赵六
    3
    7000
    7000
    8000
    周八
    3
    7500
    7000
    8000
    钱十一
    3
    8000
    7000
    8000
    王十二
    1
    4000
    4000
    5000
    王五
    1
    4500
    4000
    5000
    吴九
    1
    4500
    4000
    5000
    张三
    1
    5000
    4000
    5000


    常用聚合函数在窗口中的用法

    聚合函数在窗口中的用法允许你在窗口内进行聚合计算，例如计算窗口内的总和、平均值等。这通常使用 OVER 子句来定义窗口。

    序号

    函数

    含义

    1

    SUM()

    • 计算窗口内行的累积和。

    2

    AVG()

    • 计算窗口内行的平均值。

    3

    COUNT()

    • 计算窗口内行的数量。

    4

    MAX()  和 MIN()

    • 计算窗口内行的最大值和最小值。

    1. SUM() - 累积和

    计算每个员工的累积薪水（按薪水降序排列）。

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT     name, salary,    SUM(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salaryFROM     employees;

    结果：

    name
    salary
    cumulative_salary
    钱十一
    8000
    8000
    周八
    7500
    15500
    赵六
    7000
    22500
    孙七
    6500
    29000
    李四
    6000
    35000
    王五
    5500
    40500
    张三
    5000
    45500
    郑十
    5000
    50500
    吴九
    4500
    55000
    王十二
    4000
    59000


    💡在这个示例中，SUM(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 计算从第一个行到当前行的累积薪水。

    2. AVG() - 移动平均

    计算每个员工的移动平均薪水（按薪水降序排列，窗口大小为3）。

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 




    
AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT name, salary,       AVG(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_salaryFROM employees;

    结果：

    name
    salary
    moving_avg_salary
    钱十一
    8000
    8,000.00
    周八
    7500
    7,750.00
    赵六
    7000
    7,500.00
    孙七
    6500
    7,000.00
    李四
    6000
    6,500.00
    王五
    5500
    6,000.00
    张三
    5000
    5,500.00
    郑十
    5000
    5,166.67
    吴九
    4500
    4,833.33
    王十二
    4000
    4,500.00


    💡在这个示例中，AVG(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 计算当前行及其前两行的平均薪水。

    3. COUNT() - 窗口内行数

    计算每个员工的窗口内行数（按薪水降序排列，窗口大小为3）。

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION 




    
ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT name, salary,       COUNT(*) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS window_countFROM employees;

    结果：

    name
    salary
    window_count
    钱十一
    8000
    1
    周八
    7500
    2
    赵六
    7000
    3
    孙七
    6500
    3
    李四
    6000
    3
    王五
    5500
    3
    郑十
    5000
    3
    张三
    5000
    3
    吴九
    4500
    3
    王十二
    4000
    3


    💡在这个示例中，COUNT(*) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 计算当前行及其前两行的行数。

    4. MAX() 和 MIN() - 窗口内最大值和最小值

    计算每个员工的窗口内最大值和最小值（按薪水降序排列，窗口大小为3）。

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT




    
 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000)SELECT name, salary,       MAX(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS max_salary,       MIN(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS min_salaryFROM employees;

    结果：

    name
    salary
    max_salary
    min_salary
    钱十一
    8000
    8000
    8000
    周八
    7500
    8000
    7500
    赵六
    7000
    8000
    7000
    孙七
    6500
    7500
    6500
    李四
    6000
    7000
    6000
    王五
    5500
    6500
    5500
    张三
    5000
    6000
    5000
    郑十
    5000
    5500
    5000
    吴九
    4500
    5000
    4500
    王十二
    4000
    5000
    4000





    💡在这个示例中，MAX(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 和 MIN(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 分别计算当前行及其前两行的最大值和最小值。

    5.5 总结

    通过上述示例，你可以看到如何使用 FIRST_VALUE() 和 LAST_VALUE() 窗口函数来获取窗口内的第一个和最后一个值，以及如何在窗口中使用聚合函数（如 SUM()、AVG()、COUNT()、MAX() 和 MIN()）进行复杂的计算。

    如果大家对SQL比较熟悉，学得比较好，想了解具体的代码的掌握情况，可以在CDA认证小程序上刷模拟题测试效果。

    图片


    6. 案例

    下是三个综合的SQL案例，涵盖了我们之前学习的各种概念，包括数据检索、聚合与分组、多表操作以及高级特性（如窗口函数和公用表表达式）。这些案例将帮助你更好地理解和应用这些知识。

    6.1 案例：员工薪资分析

    6.1.1 目标

    分析每个部门的员工薪资情况，包括平均薪资、最高薪资、最低薪资以及每个员工的薪资排名。

    6.1.2 数据准备

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 




    
6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部')

    6.1.3 查询

    WITH employees AS (    SELECT 1 AS id, '张三' AS name, 1 AS department_id, 5000 AS salary UNION ALL    SELECT 2, '李四', 2, 6000 UNION ALL    SELECT 3, '王五', 1, 5500 UNION ALL    SELECT 4, '赵六', 3, 7000 UNION ALL    SELECT 5, '孙七', 2, 6500 UNION ALL    SELECT 6, '周八', 3, 7500 UNION ALL    SELECT 7, '吴九', 1, 4500 UNION ALL    SELECT 8, '郑十', 2, 5000 UNION ALL    SELECT 9, '钱十一', 3, 8000 UNION ALL    SELECT 10, '王十二', 1, 4000),departments AS (    SELECT 1 AS id, '销售部' AS department_name UNION ALL    SELECT 2, '市场部' UNION ALL    SELECT 3, '技术部'),department_stats AS (    SELECT department_id,           




    
AVG(salary) AS avg_salary,           MAX(salary) AS max_salary,           MIN(salary) AS min_salary    FROM employees    GROUP BY department_id)SELECT e.name,       e.salary,       d.department_name,       ds.avg_salary,       ds.max_salary,       ds.min_salary,       RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rankFROM employees eJOIN departments d ON e.department_id = d.idJOIN department_stats ds ON e.department_id = ds.department_id;

    6.1.4 结果

    name
    salary
    department_name
    avg_salary
    max_salary
    min_salary
    salary_rank
    钱十一
    8000
    技术部
    7,500.00
    8000
    7000
    1
    周八
    7500
    技术部
    7,500.00
    8000
    7000
    2
    赵六
    7000
    技术部
    7,500.00
    8000
    7000
    3
    王五
    5500
    销售部
    4,750.00
    5500
    4000
    1
    张三
    5000
    销售部
    4,750.00
    5500
    4000
    2
    吴九
    4500
    销售部
    4,750.00
    5500
    4000
    3
    王十二
    4000
    销售部
    4,750.00
    5500
    4000
    4
    孙七
    6500
    市场部
    5,833.33
    6500
    5000
    1
    李四
    6000
    市场部
    5,833.33
    6500
    5000
    2
    郑十
    5000
    市场部
    5,833.33
    6500
    5000
    3


    6.2 案例：销售数据分析

    6.2.1 目标

    分析每个销售员的销售业绩，包括总销售额、平均销售额以及每个销售员的排名。

    6.2.2 数据准备

    WITH sales AS (    




    
SELECT 1 AS id, '张三' AS salesperson, 1000 AS amount UNION ALL    SELECT 2, '李四', 1500 UNION ALL    SELECT 3, '张三', 2000 UNION ALL    SELECT 4, '李四', 2500 UNION ALL    SELECT 5, '王五', 3000 UNION ALL    SELECT 6, '王五', 3500 UNION ALL    SELECT 7, '张三', 4000 UNION ALL    SELECT 8, '李四', 4500 UNION ALL    SELECT 9, '王五', 5000 UNION ALL    SELECT 10, '张三', 5500)

    6.2.3 查询

    WITH sales AS (    SELECT 1 AS id, '张三' AS salesperson, 1000 AS amount UNION ALL    SELECT 2, '李四', 1500 UNION ALL    SELECT 3, '张三', 2000 UNION ALL    SELECT 4, '李四', 2500 UNION ALL    SELECT 5, '王五', 3000 UNION ALL    SELECT 6, '王五', 3500 UNION ALL    SELECT 7, '张三', 4000 UNION ALL    SELECT 8, '李四', 4500 UNION ALL    SELECT 9, '王五', 5000 UNION ALL    SELECT 10, '张三', 5500),sales_summary AS (    SELECT salesperson,           SUM(amount) AS total_sales,           AVG(amount) AS avg_sales    FROM sales    GROUP BY salesperson)SELECT salesperson,       total_sales,       avg_sales,       RANK() OVER (ORDER BY total_sales DESC) AS sales_rankFROM sales_summary;

    6.2.4 结果

    salesperson
    total_sales
    avg_sales
    sales_rank
    张三
    12500
    3125
    1
    王五
    11500
    3833.3
    2
    李四
    8500
    2833.3
    3


    6.3 案例：股票价格分析

    6.3.1 目标

    分析股票价格的变化趋势，包括最高价、最低价以及每日价格变化。

    6.3.2 数据准备

    WITH stock_prices AS (    SELECT '2023-01-01' AS date, 100 AS open_price, 105 AS close_price UNION ALL    SELECT '2023-01-02', 105, 110 UNION ALL    SELECT '2023-01-03', 110, 108 UNION ALL    SELECT '2023-01-04', 108, 112 UNION ALL    SELECT '2023-01-05', 112, 115 UNION ALL    SELECT '2023-01-06', 115, 113 UNION ALL    SELECT '2023-01-07', 113, 118 UNION ALL    SELECT '2023-01-08', 118, 120 UNION ALL    SELECT '2023-01-09', 120, 119 UNION ALL    SELECT '2023-01-10', 119, 122)

    6.3.3 查询

    WITH stock_prices AS (    SELECT '2023-01-01' AS date, 100 AS open_price, 105 AS close_price UNION ALL    SELECT '2023-01-02', 105, 110 UNION ALL    SELECT '2023-01-03', 110, 108 UNION ALL    SELECT '2023-01-04', 108, 112 UNION ALL    SELECT '2023-01-05', 112, 115 UNION ALL    SELECT '2023-01-06', 115, 113 UNION ALL    SELECT '2023-01-07', 113, 118 UNION ALL    SELECT '2023-01-08', 118, 120 UNION ALL    SELECT '2023-01-09', 120, 119 UNION ALL    SELECT '2023-01-10', 119, 122),daily_stats AS (    SELECT date,           close_price,           LAG(close_price) OVER (ORDER BY date) AS prev_close_price,           MAX(close_price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND




    
 CURRENT ROW) AS max_close_price,           MIN(close_price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS min_close_price    FROM stock_prices)SELECT date,       close_price,       prev_close_price,       max_close_price,       min_close_price,       close_price - prev_close_price AS price_changeFROM daily_stats;

    6.3.4 结果

    date
    close_price
    prev_close_price
    max_close_price
    min_close_price
    price_change
    2023/1/1
    105
    null
    105
    105
    null
    2023/1/2
    110
    105
    110
    105
    5
    2023/1/3
    108
    110
    110
    105
    -2
    2023/1/4
    112
    108
    112
    105
    4
    2023/1/5
    115
    112
    115
    105
    3
    2023/1/6
    113
    115
    115
    105
    -2
    2023/1/7
    118
    113
    118
    105
    5
    2023/1/8
    120
    118
    120
    105
    2
    2023/1/9
    119
    120
    120
    105
    -1
    2023/1/10
    122
    119
    122
    105
    3


    6.4 总结

    通过这三个综合案例，你可以看到如何将数据检索、聚合与分组、多表操作以及高级特性（如窗口函数和公用表表达式）结合起来，解决实际问题。这些案例涵盖了从简单到复杂的查询，帮助你更好地理解和应用SQL知识。

    如果大家对SQL比较熟悉，学得比较好，想了解具体的代码的掌握情况，可以在CDA认证小程序上刷模拟题测试效果。

    图片


    2025年，是数据时代的新起点，也是职业探索的新征程。如果渴望在这个充满机遇和挑战的领域中有所作为，那么不妨加入CDA数据分析脱产就业班，与志同道合的伙伴一起，开启一段全新的职业之旅。


    | 薪资介绍


    数据分析师行业的人才需求将大幅增加，行业也将迎来新的发展机遇。




    图片


    | 行业介绍

    各行各业都需要的数据分析,那么是具体哪些行业需求最大呢？

    数据分析在当今社会已经渗透到了各行各业，成为了许多企业和组织不可或缺的一部分。无论是金融、医疗、教育、制造还是零售等行业，都需要数据分析来帮助企业更好地理解市场需求、优化运营流程、提高决策效率等。根据相关招聘数据，以下11个行业值得关注：图片

    | 岗位介绍

    数据分析已不再“IT”，早已成各个岗位的必备技能

    数据分析技能具有很强的通用性和可迁移性。无论是从事哪个行业或领域，掌握数据分析技能都可以帮助个人更好地理解和分析数据，发现问题和机会，提高工作效率和质量。图片

    | 数据人才成长体系

    科学完善的课程体系分级，学习更有效

    作为专注于数据科学领域课程研发17年的培训机构, CDA数据分析师总结并实践出了一套行业、企业、市场认可的“全栈数据人才成长体系”，从数据分析、数据挖掘、人工智能等方向为学员提供更科学、系统的学习线路和课程，助力学员实现持续的职场岗位晋升和薪酬增长。



    图片



    | 课程分级

    一个科学完善的课程体系分级可以帮助学习者更加有效地掌握数据分析的知识和技能，提高学习效果和实际应用能力。


    图片


    CDA数据分析师就业班2025.02.22日 第一次课，欢迎大家扫码咨询。
    若不方便扫码，搜微信号：CDAshujufenxi


    图片

    扫码回复"就业班"，咨询课程优惠

    | 讲师团队

    与智者同行，与高人为伍，让大师成为你的私人智库

    在这个快速变化的世界中，与智者同行、与高人为伍，成为了我们追求成长和智慧的捷径。智者以他们的深厚学识和独特见解，为我们指明前行的方向；高人则以其卓越的能力和非凡的成就，激励我们不断超越自我。



    图片

    | 课程案例

    高标准师资团队，课程与时俱进，不断融入热门技术

    选择智慧启航，就是选择了一个高标准师资团队、前沿技术和优质课程的结合。在这里，你将获得最专业、最全面、最前沿的学习体验，为你的未来奠定坚实的基础。

    图片
    图片

    | 学习平台

    四大智能学习系统，高效辅助全程

    一线行业大咖，实战业务经验分享，优质学长实用求职方法传授。每月3-4次


    图片


    | 权威教材

    行业权威教材及知识体系

    CDA数据分析师就业培训班所使用的教材及相关讲义（电子版），均由CDA数据科学研究院独家支持研发！


    图片



    10大行业 300+ 套完整行业案例


    图片


    CDA数据科学研究院是国内率先成立的专注于数据科学领域的专业研究团队，团队具有专业的学术素养、精湛的研究水平，扎实的企业实战经验，丰富的行业资源，通过对各类企业、社会组织等进行全面、系统、深入的调查和访问，从而获得紧跟技术发展的经验与数据，并结合数据行业的未来发展方向进行系统的研究，不断研发新的知识体系和技术应用。

    | 职业路径

    全流程“沉浸式”精准个性化职业解决方案，坚决抵制过度承诺！

    精准个性化指导，为你量身定制职业发展路径。我们的专业团队将根据你的兴趣、能力和职业目标，为你提供个性化的建议与方案，帮助你在职业道路上少走弯路，更快地实现自己的梦想。

    坚决抵制过度承诺。我们深知，每个人的职业发展都是一个长期而持续的过程，需要不断的努力与积累。因此，我们承诺为你提供真实、可靠的职业指导与支持，帮助你实现自己的职业目标，而不是仅仅为你描绘一个美好的蓝图。

    选择全流程“沉浸式”精准个性化职业解决方案，就是选择了一个真实、可靠的职业发展伙伴。让我们携手共进，共同开创属于你的美好未来！


    图片

    | 学员案例

    图片


    | 培训成果

    专注数据科学前沿技术、人才培养17年、往期学员超百人成为数据科学家，培训学员10万+吸引世界名校学员总数超5000 (哥伦比亚大学、清华大学、北京大学、人民大学等)全国30万+的数据分析从业者，有10万在CDA,行业大咖师资203位、名企内训合作118家、公益直播730场沙龙会议318期。


    图片

    | 课程大纲

    完善系统的教学体系，紧跟时代需求

    在瞬息万变的时代里，教育不再是一成不变的灌输，而是需要与时俱进，紧密贴合时代的脉搏。完善系统的教学体系，是我们对教育的坚持和追求，确保每一位学习者都能获得全面、深入、实用的知识和技能。


    基于CDA的新大纲的改版，2025年1月对课程大纲做了较大更新，就业班和脱产班增加了以下内容：
    脱产班：


    • 新增企业需要的数据能力、数据分析思维、指标体系管理内容
    • 新增标签体系与用户画像内容，及其相应案例
    • 新增归因分析内容
    • 新增进阶数据分析思维、量化策略分析框架与流程内容
    • 新增数据管理与数据安全内容
    • 新增时间序列建模内容
    • 新增人工智能商业应用内容，录播
    • 数据架构与ETL改为SQL授课
    • 案例优化为沙盘模拟的形式授课
    • 删除Hive SQL内容
    • 删除精准营销与金融风控案例内容删除深度学习内容



    周末班：


    • 新增企业需要的数据能力、数据分析思维、指标体系管理内容
    • 新增标签体系与用户画像内容，及其相应案例
    • 新增归因分析内容
    • 新增人工智能商业应用内容数据架构与ETL改为SQL授课
    • 案例优化为沙盘模拟的形式授课
    • 删除Hive SQL内容



    （以往课表）
    图片
    图片
    图片
    图片
    图片
    图片
    图片
    图片
    图片
    CDA数据分析就业班02月22日第一次课， 欢迎咨询
    若不方便扫码，加客服微信：CDAshujufenxi
    图片



    扫码回复"就业班"，咨询课程优惠



    往期干货文章：

    Deepseek来袭，数据分析师会失业吗？

    用Deepseek处理复杂数据效果好吗？小白搞得定吗？

    【干货】指标波动归因分析：数据背后的故事

    【干货】Deepseek教我数据可视化看板实时更新

    【干货】2步学会构成分析，找到业务增长关键

    【干货】比‘红蓝对决’还精彩！哑变量如何在分类变量中扮演关键角色

    【干货】画用户画像与找相亲对象一样简单

    【干货】5分钟学会数据分析方法之【对比分析法】

    【干货】数据可视化很难？不存在的！一文弄懂PyEcharts可视化技术

    【干货】用半监督学习方法处理标签（上）Label Propagation

    【干货】半监督学习（下）Label Spreading

    【干货】掌握这50个常用Excel函数，你的Excel就无敌了

     20000字！一文学会Python数据分析

    【测一测】解锁数据分析潜力！量身定制的测试挑战等你来战！

    【干货】7类常见的统计分析错误

    【干货】7款超强大的AI数据分析工具，再也不用痛苦背函数了，建议收藏

    【干货】12种实用数据分析模型，强烈建议收藏！

    【干货】字节大神：讲透数据指标体系搭建的3个关键步骤

    【面试】秋招年薪50万，央企数据分析岗成功上岸！

    【干货】百试不爽，提高思维能力的5个数据分析模型

    【干货】大厂数据分析师面试，最常犯的2个技术错误

    【干货】2年银行数据分析师：大数据部门工作重点及技能要求

    推荐文章
    © 2024 精读
    删除内容请联系邮箱 2879853325@qq.com