
以下文章來源于數(shù)有道 ,作者數(shù)據(jù)星爺
SQL查詢是數(shù)據(jù)分析工作的基礎,也是CDA數(shù)據(jù)分析師一級的核心考點,人工智能時代,AI能為我們節(jié)省多少工作量?本來想用deepseek部署,被卡出三界外,不在五行中,后來選擇了通義靈碼。
環(huán)境
AI環(huán)境:VScode+通義靈碼
練習網(wǎng)站:https://sqlfiddle.com/postgresql/online-compiler
數(shù)據(jù)庫:數(shù)據(jù)庫是一個有組織的數(shù)據(jù)集合,通常以電子表格的形式存儲。它由多個表組成,每個表代表一種特定類型的數(shù)據(jù)集合。
表:表是數(shù)據(jù)庫中數(shù)據(jù)的基本存儲單位,類似于Excel中的工作表。表由行(記錄)和列(字段)構(gòu)成。每一行代表一條記錄,每一列代表一個屬性。
例如,在一個電商數(shù)據(jù)庫中,可能會有以下幾張表:
SQL(Structured Query Language,結(jié)構(gòu)化查詢語言)是一種用于管理和操作關(guān)系型數(shù)據(jù)庫的標準語言。它的基本結(jié)構(gòu)包括以下幾個部分:
假設我們有一個名為 users 的表,包含以下字段:
我們可以編寫一個簡單的SQL查詢來獲取所有用戶的姓名和郵箱:
SELECT name, email FROM users;
如果我們只想獲取名字中包含 "張" 的用戶,可以使用 WHERE 子句:
SELECT name, email
FROM users
WHERE name LIKE '%張%';
通過理解數(shù)據(jù)庫和表的概念以及SQL語言的基本結(jié)構(gòu),你可以開始構(gòu)建簡單的查詢語句來檢索和操作數(shù)據(jù)。這是學習SQL的基礎,后續(xù)我們將在此基礎上深入學習更復雜的查詢和操作。
數(shù)據(jù)檢索是SQL中最常用的操作之一,它允許你從數(shù)據(jù)庫中提取所需的數(shù)據(jù)。我們將詳細講解如何使用SELECT語句及其相關(guān)子句來實現(xiàn)各種數(shù)據(jù)檢索操作。
首先,我們創(chuàng)建一個包含10條員工數(shù)據(jù)的臨時表 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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
示例 要獲取所有員工的姓名和部門:
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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT name, department
FROM employees;
結(jié)果:
示例 要獲取工資大于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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT name, salary
FROM employees
WHERE salary > 5000;
結(jié)果
示例 要按工資從高到低排序員工信息:
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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT name, salary
FROM employees
ORDER BY salary DESC;
結(jié)果
示例 要獲取前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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
結(jié)果
示例 要獲取屬于 "銷售部" 的前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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT name, salary
FROM employees
WHERE department = '銷售部'
ORDER BY salary DESC
LIMIT 3;
結(jié)果
通過上述示例,你可以看到如何使用 WITH 子句創(chuàng)建一個臨時表,并在此基礎上進行各種數(shù)據(jù)檢索操作。這些示例涵蓋了 SELECT、WHERE、ORDER BY 和 LIMIT 的基本用法,幫助你更好地理解和驗證查詢結(jié)果。
使用COUNT(), SUM(), AVG(), MAX(), MIN()等聚合函數(shù)
使用GROUP BY對數(shù)據(jù)進行分組
首先,我們創(chuàng)建一個包含10條員工數(shù)據(jù)的臨時表 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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
聚合函數(shù)用于對一組值執(zhí)行計算并返回單個值。常用的聚合函數(shù)包括:
示例- count 計算總員工數(shù):
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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT COUNT(*) AS total_employees
FROM employees;
結(jié)果:
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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT SUM(salary) AS total_salary
FROM employees;
結(jié)果:
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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT AVG(salary) AS average_salary
FROM employees;
結(jié)果:
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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT MAX(salary) AS max_salary
FROM employees;
結(jié)果:
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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT MIN(salary) AS min_salary
FROM employees;
結(jié)果:
min_salary
4000
GROUP BY 子句用于將結(jié)果集按一個或多個列進行分組,通常與聚合函數(shù)一起使用。
示例-count 按部門分組,計算每個部門的員工數(shù):
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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
結(jié)果:
示例-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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
結(jié)果:
示例-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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;
結(jié)果:
示例-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, '趙六', '技術(shù)部', 7000 UNION ALL
SELECT 5, '孫七', '市場部', 6500 UNION ALL
SELECT 6, '周八', '技術(shù)部', 7500 UNION ALL
SELECT 7, '吳九', '銷售部', 4500 UNION ALL
SELECT 8, '鄭十', '市場部', 5000 UNION ALL
SELECT 9, '錢十一', '技術(shù)部', 8000 UNION ALL
SELECT 10, '王十二', '銷售部', 4000
)
SELECT department, MAX(salary) AS max_salary, MIN(salary) AS min_salary
FROM employees
GROUP BY department;
結(jié)果:
通過上述示例,你可以看到如何使用聚合函數(shù)和 GROUP BY 子句來對數(shù)據(jù)進行分組和計算。這些示例涵蓋了 COUNT()、SUM()、AVG()、MAX() 和 MIN() 的基本用法,并結(jié)合 GROUP BY 進行分組操作。
我們將詳細講解SQL中的多表操作,包括內(nèi)連接(INNER JOIN)、左連接(LEFT JOIN)、右連接(RIGHT JOIN)和全外連接(FULL OUTER JOIN)。我們將使用 WITH 子句創(chuàng)建兩個臨時表 employees 和 departments,然后進行各種多表查詢。
首先,我們創(chuàng)建兩個臨時表 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, '技術(shù)部'
)
內(nèi)連接返回兩個表中滿足連接條件的所有行。
示例 要獲取每個員工及其所屬部門的名稱:
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, '技術(shù)部'
)
SELECT e.name, d.department_name, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
結(jié)果
左連接返回左表中的所有行,以及右表中滿足連接條件的行。如果右表中沒有匹配的行,則結(jié)果為 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, '技術(shù)部'
)
SELECT e.name, d.department_name, e.salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
結(jié)果
右連接返回右表中的所有行,以及左表中滿足連接條件的行。如果左表中沒有匹配的行,則結(jié)果為 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, '技術(shù)部' UNION ALL
SELECT 4, '人力資源部' -- 添加一個沒有員工的部門
)
SELECT e.name, d.department_name, e.salary
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
結(jié)果
全外連接返回兩個表中的所有行,如果某個表中沒有匹配的行,則結(jié)果為 NULL。需要注意的是,并非所有數(shù)據(jù)庫系統(tǒng)都支持 FULL OUTER JOIN,例如 MySQL 不支持,但可以通過 UNION 實現(xiàn)類似效果。
示例 要獲取所有員工及其所屬部門的名稱,以及所有部門及其所屬員工的名稱,即使某些員工或部門沒有匹配:
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, '技術(shù)部' UNION ALL
SELECT 4, '人力資源部' -- 添加一個沒有員工的部門
)
SELECT e.name, d.department_name, e.salary
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
結(jié)果
通過上述示例,你可以看到如何使用內(nèi)連接、左連接、右連接和全外連接來操作多個表。這些示例涵蓋了 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN 的基本用法,并結(jié)合 WITH 子句創(chuàng)建臨時表進行驗證。
我們將詳細講解SQL中的高級特性,包括公用表表達式(CTE)、子查詢和窗口函數(shù)。我們將使用 WITH 子句創(chuàng)建臨時表 employees 和 departments,然后進行各種高級查詢。
首先,我們創(chuàng)建兩個臨時表 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, '技術(shù)部'
)
公用表表達式(CTE)是一個臨時結(jié)果集,可以在查詢中多次引用。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, '技術(shù)部'
),
department_avg_salary AS (--每個部門平均薪水
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT d.department_name, das.avg_salary
FROM departments d
JOIN department_avg_salary das ON d.id = das.department_id;
結(jié)果
子查詢是嵌套在另一個查詢中的查詢。子查詢可以出現(xiàn)在 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, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
結(jié)果:
窗口函數(shù)對一組行進行計算,并返回每個行的計算結(jié)果。窗口函數(shù)通常與 OVER 子句一起使用。
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_salary
FROM employees
結(jié)果:
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_2
FROM
employees;
結(jié)果:
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_salary
FROM employees;
結(jié)果:
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_salary
FROM employees;
結(jié)果
????注意: LAST_VALUE() 默認情況下會在窗口內(nèi)逐行計算,如果需要在整個分區(qū)計算,可以使用 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_salary
FROM employees;
結(jié)果:
聚合函數(shù)在窗口中的用法允許你在窗口內(nèi)進行聚合計算,例如計算窗口內(nèi)的總和、平均值等。這通常使用 OVER 子句來定義窗口。
計算每個員工的累積薪水(按薪水降序排列)。
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_salary
FROM
employees;
結(jié)果:
????在這個示例中,SUM(salary) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 計算從第一個行到當前行的累積薪水。
計算每個員工的移動平均薪水(按薪水降序排列,窗口大小為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_salary
FROM employees;
結(jié)果:
????在這個示例中,AVG(salary) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 計算當前行及其前兩行的平均薪水。
計算每個員工的窗口內(nèi)行數(shù)(按薪水降序排列,窗口大小為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_count
FROM employees;
結(jié)果:
????在這個示例中,COUNT(*) OVER (ORDER BY salary DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 計算當前行及其前兩行的行數(shù)。
計算每個員工的窗口內(nèi)最大值和最小值(按薪水降序排列,窗口大小為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_salary
FROM employees;
結(jié)果:
????在這個示例中,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) 分別計算當前行及其前兩行的最大值和最小值。
通過上述示例,你可以看到如何使用 FIRST_VALUE() 和 LAST_VALUE() 窗口函數(shù)來獲取窗口內(nèi)的第一個和最后一個值,以及如何在窗口中使用聚合函數(shù)(如 SUM()、AVG()、COUNT()、MAX() 和 MIN())進行復雜的計算。
下是三個綜合的SQL案例,涵蓋了我們之前學習的各種概念,包括數(shù)據(jù)檢索、聚合與分組、多表操作以及高級特性(如窗口函數(shù)和公用表表達式)。這些案例將幫助你更好地理解和應用這些知識。
分析每個部門的員工薪資情況,包括平均薪資、最高薪資、最低薪資以及每個員工的薪資排名。
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, '技術(shù)部'
)
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, '技術(shù)部'
),
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_rank
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN department_stats ds ON e.department_id = ds.department_id;
分析每個銷售員的銷售業(yè)績,包括總銷售額、平均銷售額以及每個銷售員的排名。
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
)
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_rank
FROM sales_summary;
分析股票價格的變化趨勢,包括最高價、最低價以及每日價格變化。
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
)
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_change
FROM daily_stats;
6.3.4 結(jié)果
通過這三個綜合案例,你可以看到如何將數(shù)據(jù)檢索、聚合與分組、多表操作以及高級特性(如窗口函數(shù)和公用表表達式)結(jié)合起來,解決實際問題。這些案例涵蓋了從簡單到復雜的查詢,幫助你更好地理解和應用SQL知識。
隨著各行各業(yè)進行數(shù)字化轉(zhuǎn)型,數(shù)據(jù)分析能力已經(jīng)成了職場的剛需能力,這也是這兩年CDA數(shù)據(jù)分析師大火的原因。和領導提建議再說“我感覺”“我覺得”,自己都覺得心虛,如果說“數(shù)據(jù)分析發(fā)現(xiàn)……”,肯定更有說服力。想在職場精進一步還是要學習數(shù)據(jù)分析的,統(tǒng)計學、概率論、商業(yè)模型、SQL,Python還是要會一些,能讓你工作效率提升不少。備考CDA數(shù)據(jù)分析師的過程就是個自我提升的過程。
CDA 考試官方報名入口:https://www.cdaglobal.com/pinggu.html
數(shù)據(jù)分析咨詢請掃描二維碼
若不方便掃碼,搜微信號:CDAshujufenxi
LSTM 模型輸入長度選擇技巧:提升序列建模效能的關(guān)鍵? 在循環(huán)神經(jīng)網(wǎng)絡(RNN)家族中,長短期記憶網(wǎng)絡(LSTM)憑借其解決長序列 ...
2025-07-11CDA 數(shù)據(jù)分析師報考條件詳解與準備指南? ? 在數(shù)據(jù)驅(qū)動決策的時代浪潮下,CDA 數(shù)據(jù)分析師認證愈發(fā)受到矚目,成為眾多有志投身數(shù) ...
2025-07-11數(shù)據(jù)透視表中兩列相乘合計的實用指南? 在數(shù)據(jù)分析的日常工作中,數(shù)據(jù)透視表憑借其強大的數(shù)據(jù)匯總和分析功能,成為了 Excel 用戶 ...
2025-07-11尊敬的考生: 您好! 我們誠摯通知您,CDA Level I和 Level II考試大綱將于 2025年7月25日 實施重大更新。 此次更新旨在確保認 ...
2025-07-10BI 大數(shù)據(jù)分析師:連接數(shù)據(jù)與業(yè)務的價值轉(zhuǎn)化者? ? 在大數(shù)據(jù)與商業(yè)智能(Business Intelligence,簡稱 BI)深度融合的時代,BI ...
2025-07-10SQL 在預測分析中的應用:從數(shù)據(jù)查詢到趨勢預判? ? 在數(shù)據(jù)驅(qū)動決策的時代,預測分析作為挖掘數(shù)據(jù)潛在價值的核心手段,正被廣泛 ...
2025-07-10數(shù)據(jù)查詢結(jié)束后:分析師的收尾工作與價值深化? ? 在數(shù)據(jù)分析的全流程中,“query end”(查詢結(jié)束)并非工作的終點,而是將數(shù) ...
2025-07-10CDA 數(shù)據(jù)分析師考試:從報考到取證的全攻略? 在數(shù)字經(jīng)濟蓬勃發(fā)展的今天,數(shù)據(jù)分析師已成為各行業(yè)爭搶的核心人才,而 CDA(Certi ...
2025-07-09【CDA干貨】單樣本趨勢性檢驗:捕捉數(shù)據(jù)背后的時間軌跡? 在數(shù)據(jù)分析的版圖中,單樣本趨勢性檢驗如同一位耐心的偵探,專注于從單 ...
2025-07-09year_month數(shù)據(jù)類型:時間維度的精準切片? ? 在數(shù)據(jù)的世界里,時間是最不可或缺的維度之一,而year_month數(shù)據(jù)類型就像一把精準 ...
2025-07-09CDA 備考干貨:Python 在數(shù)據(jù)分析中的核心應用與實戰(zhàn)技巧? ? 在 CDA 數(shù)據(jù)分析師認證考試中,Python 作為數(shù)據(jù)處理與分析的核心 ...
2025-07-08SPSS 中的 Mann-Kendall 檢驗:數(shù)據(jù)趨勢與突變分析的有力工具? ? ? 在數(shù)據(jù)分析的廣袤領域中,準確捕捉數(shù)據(jù)的趨勢變化以及識別 ...
2025-07-08備戰(zhàn) CDA 數(shù)據(jù)分析師考試:需要多久?如何規(guī)劃? CDA(Certified Data Analyst)數(shù)據(jù)分析師認證作為國內(nèi)權(quán)威的數(shù)據(jù)分析能力認證 ...
2025-07-08LSTM 輸出不確定的成因、影響與應對策略? 長短期記憶網(wǎng)絡(LSTM)作為循環(huán)神經(jīng)網(wǎng)絡(RNN)的一種變體,憑借獨特的門控機制,在 ...
2025-07-07統(tǒng)計學方法在市場調(diào)研數(shù)據(jù)中的深度應用? 市場調(diào)研是企業(yè)洞察市場動態(tài)、了解消費者需求的重要途徑,而統(tǒng)計學方法則是市場調(diào)研數(shù) ...
2025-07-07CDA數(shù)據(jù)分析師證書考試全攻略? 在數(shù)字化浪潮席卷全球的當下,數(shù)據(jù)已成為企業(yè)決策、行業(yè)發(fā)展的核心驅(qū)動力,數(shù)據(jù)分析師也因此成為 ...
2025-07-07剖析 CDA 數(shù)據(jù)分析師考試題型:解鎖高效備考與答題策略? CDA(Certified Data Analyst)數(shù)據(jù)分析師考試作為衡量數(shù)據(jù)專業(yè)能力的 ...
2025-07-04SQL Server 字符串截取轉(zhuǎn)日期:解鎖數(shù)據(jù)處理的關(guān)鍵技能? 在數(shù)據(jù)處理與分析工作中,數(shù)據(jù)格式的規(guī)范性是保證后續(xù)分析準確性的基礎 ...
2025-07-04CDA 數(shù)據(jù)分析師視角:從數(shù)據(jù)迷霧中探尋商業(yè)真相? 在數(shù)字化浪潮席卷全球的今天,數(shù)據(jù)已成為企業(yè)決策的核心驅(qū)動力,CDA(Certifie ...
2025-07-04CDA 數(shù)據(jù)分析師:開啟數(shù)據(jù)職業(yè)發(fā)展新征程? ? 在數(shù)據(jù)成為核心生產(chǎn)要素的今天,數(shù)據(jù)分析師的職業(yè)價值愈發(fā)凸顯。CDA(Certified D ...
2025-07-03