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