SQL函式應用於資料分析與處理

-- 基礎數學函式示範
-- 這些函式在不同的資料庫系統中語法大致相同

-- 絕對值函式
-- ABS() 傳回數值的絕對值，常用於計算差異值
SELECT ABS(-15.7) AS absolute_value;
-- 結果：15.7

-- 四捨五入函式
-- ROUND() 將數值四捨五入到指定的小數位數
-- 第二個參數指定保留的小數位數
SELECT ROUND(3.14159, 2) AS rounded_value;
-- 結果：3.14

-- 無條件捨去
-- FLOOR() 傳回小於或等於該數值的最大整數
SELECT FLOOR(7.89) AS floor_value;
-- 結果：7

-- 無條件進位
-- CEILING() 或 CEIL() 傳回大於或等於該數值的最小整數
SELECT CEILING(7.01) AS ceiling_value;
-- 結果：8

-- 取餘數
-- MOD() 計算除法的餘數，也可以使用 % 運算子
SELECT MOD(17, 5) AS remainder;
-- 結果：2

-- 冪次運算
-- POWER() 計算指定數值的指定次方
SELECT POWER(2, 10) AS power_result;
-- 結果：1024

-- 平方根
-- SQRT() 計算數值的平方根
SELECT SQRT(144) AS square_root;
-- 結果：12

-- 建立測試資料表
-- 這個資料表包含學生的考試成績
CREATE TABLE exam_scores (
    -- 學生編號，主鍵
    student_id INT PRIMARY KEY,
    -- 學生姓名
    student_name VARCHAR(50),
    -- 科目名稱
    subject VARCHAR(30),
    -- 考試成績
    score DECIMAL(5, 2),
    -- 考試日期
    exam_date DATE
);

-- 插入測試資料
INSERT INTO exam_scores VALUES
(1, 'Alice', 'Mathematics', 85.5, '2025-11-01'),
(2, 'Bob', 'Mathematics', 72.0, '2025-11-01'),
(3, 'Charlie', 'Mathematics', 91.5, '2025-11-01'),
(4, 'Diana', 'Mathematics', 68.0, '2025-11-01'),
(5, 'Eve', 'Mathematics', 88.5, '2025-11-01'),
(6, 'Frank', 'Mathematics', 79.0, '2025-11-01'),
(7, 'Grace', 'Mathematics', 95.0, '2025-11-01'),
(8, 'Henry', 'Mathematics', 82.5, '2025-11-01');

-- 基礎聚合函式
-- 計算成績的基本統計量
SELECT
    -- COUNT() 計算資料筆數
    COUNT(*) AS total_students,
    -- SUM() 計算總和
    SUM(score) AS total_score,
    -- AVG() 計算平均值
    AVG(score) AS average_score,
    -- MIN() 找出最小值
    MIN(score) AS lowest_score,
    -- MAX() 找出最大值
    MAX(score) AS highest_score
FROM exam_scores
WHERE subject = 'Mathematics';
-- 結果：total_students=8, total_score=662.0, average_score=82.75,
--       lowest_score=68.0, highest_score=95.0

-- 進階統計函式
-- 計算成績的離散程度
SELECT
    -- 人口標準差
    -- STDDEV_POP() 假設資料為母體全部資料
    -- 計算公式：sqrt(sum((x - mean)^2) / n)
    STDDEV_POP(score) AS population_stddev,

    -- 樣本標準差
    -- STDDEV_SAMP() 假設資料為樣本
    -- 計算公式：sqrt(sum((x - mean)^2) / (n-1))
    -- 樣本標準差會比人口標準差稍大
    STDDEV_SAMP(score) AS sample_stddev,

    -- 人口變異數
    -- VAR_POP() 計算人口變異數
    VAR_POP(score) AS population_variance,

    -- 樣本變異數
    -- VAR_SAMP() 計算樣本變異數
    VAR_SAMP(score) AS sample_variance
FROM exam_scores
WHERE subject = 'Mathematics';

-- 分組統計
-- 按照不同維度進行統計分析
SELECT
    subject AS 科目,
    COUNT(*) AS 人數,
    ROUND(AVG(score), 2) AS 平均分數,
    ROUND(STDDEV_SAMP(score), 2) AS 標準差,
    MIN(score) AS 最低分,
    MAX(score) AS 最高分
FROM exam_scores
GROUP BY subject
ORDER BY 平均分數 DESC;

-- 三角函式示範
-- 這些函式在地理資訊系統和科學計算中經常使用

-- 取得圓周率
SELECT PI() AS pi_value;
-- 結果：3.141592653589793

-- 正弦函式（輸入為弧度）
SELECT SIN(PI() / 2) AS sine_90_degrees;
-- 結果：1.0

-- 餘弦函式
SELECT COS(PI()) AS cosine_180_degrees;
-- 結果：-1.0

-- 正切函式
SELECT TAN(PI() / 4) AS tangent_45_degrees;
-- 結果：1.0（約等於）

-- 角度與弧度轉換
-- RADIANS() 將角度轉換為弧度
SELECT RADIANS(180) AS radians_value;
-- 結果：3.141592653589793

-- DEGREES() 將弧度轉換為角度
SELECT DEGREES(PI()) AS degrees_value;
-- 結果：180.0

-- 實際應用：計算兩點之間的距離
-- 使用 Haversine 公式計算地球表面兩點之間的距離
-- 這在地理資訊系統中非常常用

-- 假設我們有一個城市座標表
CREATE TABLE cities (
    city_name VARCHAR(50),
    latitude DECIMAL(10, 6),  -- 緯度
    longitude DECIMAL(10, 6)  -- 經度
);

INSERT INTO cities VALUES
('Taipei', 25.033964, 121.564468),
('Tokyo', 35.689487, 139.691711),
('Seoul', 37.566536, 126.977966);

-- 計算台北到東京的距離（公里）
-- 地球半徑約為 6371 公里
SELECT
    6371 * 2 * ASIN(
        SQRT(
            POWER(SIN(RADIANS(35.689487 - 25.033964) / 2), 2) +
            COS(RADIANS(25.033964)) * COS(RADIANS(35.689487)) *
            POWER(SIN(RADIANS(139.691711 - 121.564468) / 2), 2)
        )
    ) AS distance_km;
-- 結果：約 2100 公里

-- 單位轉換範例
-- 建立一個包含各種測量值的資料表

CREATE TABLE measurements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    measurement_name VARCHAR(50),
    value_imperial DECIMAL(10, 4),  -- 英制單位值
    unit_imperial VARCHAR(20)        -- 英制單位
);

INSERT INTO measurements (measurement_name, value_imperial, unit_imperial) VALUES
('Distance to Moon', 252088, 'miles'),
('Car Speed', 65, 'mph'),
('Room Temperature', 72, 'fahrenheit'),
('Body Weight', 150, 'pounds'),
('Fuel Capacity', 15, 'gallons');

-- 英制轉公制的轉換查詢
SELECT
    measurement_name AS 測量項目,
    value_imperial AS 英制數值,
    unit_imperial AS 英制單位,

    -- 根據不同單位進行轉換
    CASE
        -- 英里轉公里：1 英里 = 1.60934 公里
        WHEN unit_imperial = 'miles' THEN
            ROUND(value_imperial * 1.60934, 2)
        -- 英里/小時轉公里/小時
        WHEN unit_imperial = 'mph' THEN
            ROUND(value_imperial * 1.60934, 2)
        -- 華氏轉攝氏：(°F - 32) × 5/9
        WHEN unit_imperial = 'fahrenheit' THEN
            ROUND((value_imperial - 32) * 5 / 9, 2)
        -- 磅轉公斤：1 磅 = 0.453592 公斤
        WHEN unit_imperial = 'pounds' THEN
            ROUND(value_imperial * 0.453592, 2)
        -- 加侖轉公升：1 加侖 = 3.78541 公升
        WHEN unit_imperial = 'gallons' THEN
            ROUND(value_imperial * 3.78541, 2)
        ELSE value_imperial
    END AS 公制數值,

    CASE
        WHEN unit_imperial = 'miles' THEN 'kilometers'
        WHEN unit_imperial = 'mph' THEN 'km/h'
        WHEN unit_imperial = 'fahrenheit' THEN 'celsius'
        WHEN unit_imperial = 'pounds' THEN 'kilograms'
        WHEN unit_imperial = 'gallons' THEN 'liters'
        ELSE unit_imperial
    END AS 公制單位
FROM measurements;

-- 月球距離計算練習
-- 月球距離地球 252,088 英里
-- 轉換為公里並四捨五入到整數
SELECT
    ROUND(252088 * 1.60934, 0) AS moon_distance_km;
-- 結果：405,696 公里

-- CAST 函式基本語法
-- CAST(值 AS 目標型別)

-- 數值轉字串
-- 在需要將數值與字串連接時很有用
SELECT CAST(12345 AS CHAR) AS string_value;
-- 結果：'12345'

-- 字串轉數值
-- 在進行數值運算前需要先轉換
SELECT CAST('456.78' AS DECIMAL(10, 2)) AS numeric_value;
-- 結果：456.78

-- 日期時間轉換
-- 從 DATETIME 中取出日期部分
SELECT CAST('2025-11-27 14:30:00' AS DATE) AS date_only;
-- 結果：2025-11-27

-- 從 DATETIME 中取出時間部分
SELECT CAST('2025-11-27 14:30:00' AS TIME) AS time_only;
-- 結果：14:30:00

-- 小數轉整數
-- 會直接截斷小數部分（不是四捨五入）
SELECT CAST(123.789 AS SIGNED) AS integer_value;
-- 結果：123

-- 實際應用場景
-- 訂單資料表中的日期時間處理

CREATE TABLE online_orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_datetime DATETIME,
    total_amount DECIMAL(10, 2)
);

INSERT INTO online_orders VALUES
(1001, 'Alice Chen', '2025-11-27 09:15:30', 1250.00),
(1002, 'Bob Wang', '2025-11-27 14:22:45', 3680.50),
(1003, 'Charlie Lin', '2025-11-27 18:05:10', 890.00),
(1004, 'Diana Wu', '2025-11-28 10:30:00', 2150.75);

-- 按日期統計每日訂單數量和金額
SELECT
    -- 將 DATETIME 轉換為 DATE 進行分組
    CAST(order_datetime AS DATE) AS order_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS daily_total,
    AVG(total_amount) AS average_amount
FROM online_orders
GROUP BY CAST(order_datetime AS DATE)
ORDER BY order_date;

-- 格式化輸出
-- 組合字串和數值
SELECT
    CONCAT(
        customer_name,
        ' 的訂單金額為 NT$',
        CAST(total_amount AS CHAR)
    ) AS order_summary
FROM online_orders;

-- CONVERT 函式基本語法
-- CONVERT(值, 目標型別)
-- 或
-- CONVERT(值 USING 字元集)

-- 型別轉換
SELECT CONVERT('2025-11-27', DATE) AS converted_date;

-- 字元集轉換
-- 這在處理多語言資料時特別有用
SELECT CONVERT('中文字串' USING utf8mb4) AS utf8_string;

-- 二進位轉換
SELECT CONVERT('Hello' USING binary) AS binary_string;

-- COALESCE 基本用法
-- COALESCE(值1, 值2, ..., 值N)
-- 從左到右檢查，傳回第一個非 NULL 的值

SELECT COALESCE(NULL, NULL, 'Default') AS result;
-- 結果：'Default'

SELECT COALESCE(NULL, 'First', 'Second') AS result;
-- 結果：'First'

SELECT COALESCE('Actual', 'Backup', 'Default') AS result;
-- 結果：'Actual'

-- 實際應用：求職者資料表
CREATE TABLE job_candidates (
    candidate_id INT PRIMARY KEY,
    full_name VARCHAR(100),
    current_employer VARCHAR(100),  -- 可能為 NULL
    preferred_salary DECIMAL(10, 2),  -- 可能為 NULL
    years_experience INT
);

INSERT INTO job_candidates VALUES
(1, 'Alice Chen', 'TechCorp', 85000, 5),
(2, 'Bob Wang', NULL, 70000, 3),
(3, 'Charlie Lin', 'DataInc', NULL, 7),
(4, 'Diana Wu', NULL, NULL, 2);

-- 使用 COALESCE 提供預設值
SELECT
    full_name AS 姓名,
    -- 如果目前雇主為 NULL，顯示「求職中」
    COALESCE(current_employer, '求職中') AS 目前雇主,
    -- 如果期望薪資為 NULL，顯示「面議」
    COALESCE(
        CAST(preferred_salary AS CHAR),
        '面議'
    ) AS 期望薪資,
    years_experience AS 年資
FROM job_candidates;

-- 結果：
-- 姓名        | 目前雇主 | 期望薪資 | 年資
-- Alice Chen  | TechCorp | 85000    | 5
-- Bob Wang    | 求職中   | 70000    | 3
-- Charlie Lin | DataInc  | 面議     | 7
-- Diana Wu    | 求職中   | 面議     | 2

-- 多層級預設值
-- 當有多個備選欄位時，COALESCE 特別有用
CREATE TABLE contact_info (
    user_id INT,
    mobile_phone VARCHAR(20),
    home_phone VARCHAR(20),
    work_phone VARCHAR(20),
    email VARCHAR(100)
);

INSERT INTO contact_info VALUES
(1, '0912-345-678', '02-1234-5678', '02-8765-4321', '[email protected]'),
(2, NULL, '03-1234-5678', NULL, '[email protected]'),
(3, NULL, NULL, '04-1234-5678', '[email protected]'),
(4, NULL, NULL, NULL, '[email protected]');

-- 選擇優先的聯絡電話
SELECT
    user_id,
    -- 優先順序：手機 > 住家 > 公司
    COALESCE(
        mobile_phone,
        home_phone,
        work_phone,
        '請使用 Email 聯絡'
    ) AS primary_phone,
    email
FROM contact_info;

-- IFNULL 函式（MySQL 特有）
-- IFNULL(值, 預設值)
-- 如果值為 NULL，傳回預設值
SELECT IFNULL(NULL, 'Default') AS result;
-- 結果：'Default'

-- IFNULL 與 COALESCE 的差異
-- IFNULL 只接受兩個參數，COALESCE 可以接受多個
SELECT IFNULL(current_employer, '求職中') AS employer
FROM job_candidates;

-- NULLIF 函式
-- NULLIF(值1, 值2)
-- 如果值1等於值2，傳回 NULL；否則傳回值1
-- 常用於避免除以零的錯誤

CREATE TABLE sales_data (
    product_id INT,
    revenue DECIMAL(10, 2),
    units_sold INT
);

INSERT INTO sales_data VALUES
(1, 10000, 100),
(2, 5000, 50),
(3, 0, 0);  -- 沒有銷售

-- 計算平均單價
-- 如果 units_sold 為 0，NULLIF 會傳回 NULL，避免除以零
SELECT
    product_id,
    revenue,
    units_sold,
    revenue / NULLIF(units_sold, 0) AS average_price
FROM sales_data;
-- product_id 3 的 average_price 會是 NULL 而不是錯誤

-- 實用範例：處理分母為零
-- 計算成長率時避免除以零
CREATE TABLE monthly_sales (
    year_month VARCHAR(7),
    sales_amount DECIMAL(12, 2)
);

INSERT INTO monthly_sales VALUES
('2025-09', 0),
('2025-10', 50000),
('2025-11', 75000);

-- 計算月成長率
SELECT
    year_month,
    sales_amount,
    LAG(sales_amount) OVER (ORDER BY year_month) AS prev_month,
    -- 使用 NULLIF 避免除以零
    ROUND(
        (sales_amount - LAG(sales_amount) OVER (ORDER BY year_month)) * 100 /
        NULLIF(LAG(sales_amount) OVER (ORDER BY year_month), 0),
        2
    ) AS growth_rate_percent
FROM monthly_sales;

-- IF 函式基本語法
-- IF(條件, 真值結果, 假值結果)

-- 基本範例
SELECT IF(10 > 5, 'Greater', 'Less or Equal') AS comparison;
-- 結果：'Greater'

-- 判斷及格與否
SELECT IF(85 >= 60, 'Pass', 'Fail') AS result;
-- 結果：'Pass'

-- 實際應用：考試成績判定
CREATE TABLE test_results (
    student_id INT,
    student_name VARCHAR(50),
    subject VARCHAR(30),
    score INT
);

INSERT INTO test_results VALUES
(1, 'Lisa', 'Math', 85),
(2, 'Bart', 'Math', 42),
(3, 'Nelson', 'Math', 28),
(4, 'Milhouse', 'Math', 61),
(5, 'Ralph', 'Math', 55);

-- 判定考試是否及格
SELECT
    student_name AS 學生,
    score AS 分數,
    IF(score >= 60, '及格', '不及格') AS 判定結果
FROM test_results;

-- 結果：
-- 學生     | 分數 | 判定結果
-- Lisa     | 85   | 及格
-- Bart     | 42   | 不及格
-- Nelson   | 28   | 不及格
-- Milhouse | 61   | 及格
-- Ralph    | 55   | 不及格

-- 巢狀 IF
-- 雖然可行，但不建議使用過多層巢狀，改用 CASE 更清晰
SELECT
    student_name,
    score,
    IF(score >= 90, '優秀',
        IF(score >= 60, '及格', '不及格')
    ) AS grade
FROM test_results;

-- CASE 表達式有兩種形式
-- 1. 簡單 CASE：比較單一表達式與多個值
-- 2. 搜尋 CASE：評估多個布林表達式

-- 簡單 CASE 語法
SELECT
    student_name,
    score,
    CASE score
        WHEN 100 THEN '滿分'
        WHEN 90 THEN '優秀'
        WHEN 60 THEN '及格邊緣'
        ELSE '其他'
    END AS grade_note
FROM test_results;

-- 搜尋 CASE 語法（更常用）
SELECT
    student_name AS 學生,
    score AS 分數,
    CASE
        WHEN score >= 90 THEN '優秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 70 THEN '中等'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS 等第
FROM test_results
ORDER BY score DESC;

-- 結果：
-- 學生     | 分數 | 等第
-- Lisa     | 85   | 良好
-- Milhouse | 61   | 及格
-- Ralph    | 55   | 不及格
-- Bart     | 42   | 不及格
-- Nelson   | 28   | 不及格

-- 實際應用：電工頭銜分配
CREATE TABLE electricians (
    emp_id INT,
    name VARCHAR(50),
    years_experience INT
);

INSERT INTO electricians VALUES
(1, 'John', 3),
(2, 'Mary', 7),
(3, 'David', 12),
(4, 'Sarah', 5),
(5, 'Tom', 1);

-- 根據經驗年數分配頭銜
SELECT
    name AS 姓名,
    years_experience AS 年資,
    CASE
        -- 經驗少於 5 年：學徒
        WHEN years_experience < 5 THEN 'Apprentice'
        -- 經驗 5-10 年：技師
        WHEN years_experience <= 10 THEN 'Journeyman'
        -- 經驗超過 10 年：師傅
        ELSE 'Master Electrician'
    END AS 頭銜
FROM electricians
ORDER BY years_experience DESC;

-- CASE 在聚合函式中的應用
-- 這是一個非常強大的技巧，可以進行條件聚合

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    status VARCHAR(20)
);

INSERT INTO orders VALUES
(1, '2025-11-01', 1500, 'completed'),
(2, '2025-11-05', 2300, 'completed'),
(3, '2025-11-10', 800, 'cancelled'),
(4, '2025-11-15', 3200, 'completed'),
(5, '2025-11-20', 1100, 'pending'),
(6, '2025-11-25', 2800, 'completed');

-- 使用 CASE 進行條件聚合
SELECT
    -- 計算各狀態的訂單數
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count,
    -- 計算各狀態的金額總計
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_amount,
    SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_amount,
    SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled_amount
FROM orders;

-- 建立客戶資料表
CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(100),
    country VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO customers VALUES
(1, 'Alice', 'Taiwan', 'Taipei'),
(2, 'Bob', 'Taiwan', 'Kaohsiung'),
(3, 'Charlie', 'Japan', 'Tokyo'),
(4, 'Diana', 'Taiwan', 'Taipei'),
(5, 'Eve', 'Japan', 'Osaka'),
(6, 'Frank', 'Korea', 'Seoul');

-- 取得不重複的國家列表
SELECT DISTINCT country AS 國家
FROM customers
ORDER BY country;

-- 結果：
-- 國家
-- Japan
-- Korea
-- Taiwan

-- 計算不重複的國家數量
-- COUNT(DISTINCT column) 是非常常用的組合
SELECT COUNT(DISTINCT country) AS 國家數量
FROM customers;
-- 結果：3

-- 多欄位 DISTINCT
-- 傳回國家和城市的唯一組合
SELECT DISTINCT country, city
FROM customers
ORDER BY country, city;

-- DISTINCT 與聚合函式的進階應用
SELECT
    country AS 國家,
    COUNT(*) AS 客戶總數,
    COUNT(DISTINCT city) AS 城市數量
FROM customers
GROUP BY country
ORDER BY 客戶總數 DESC;

-- 結果：
-- 國家   | 客戶總數 | 城市數量
-- Taiwan | 3        | 2
-- Japan  | 2        | 2
-- Korea  | 1        | 1

-- DATABASE() 函式
-- 傳回目前使用的資料庫名稱
USE company_db;
SELECT DATABASE() AS current_database;
-- 結果：company_db

-- VERSION() 函式
-- 傳回資料庫伺服器的版本
SELECT VERSION() AS mysql_version;
-- 結果：8.0.32（依實際版本而異）

-- USER() 和 CURRENT_USER() 函式
-- 傳回目前連線的使用者資訊
SELECT
    USER() AS connected_user,
    CURRENT_USER() AS authenticated_user;

-- CONNECTION_ID() 函式
-- 傳回目前連線的唯一識別碼
SELECT CONNECTION_ID() AS connection_id;

-- LAST_INSERT_ID() 函式
-- 傳回最近一次 AUTO_INCREMENT 產生的值
-- 這在插入資料後需要取得新記錄 ID 時很有用

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100)
);

INSERT INTO products (product_name) VALUES ('Laptop');
SELECT LAST_INSERT_ID() AS new_product_id;

-- 查詢系統資訊的綜合範例
SELECT
    DATABASE() AS 資料庫,
    VERSION() AS 版本,
    USER() AS 使用者,
    CONNECTION_ID() AS 連線ID,
    NOW() AS 目前時間;

-- 綜合案例：銷售資料分析報表
-- 建立銷售資料表

CREATE TABLE sales_records (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    sale_date DATE,
    product_name VARCHAR(100),
    category VARCHAR(50),
    quantity INT,
    unit_price DECIMAL(10, 2),
    discount_rate DECIMAL(3, 2),
    salesperson VARCHAR(50),
    region VARCHAR(50)
);

-- 插入測試資料
INSERT INTO sales_records
(sale_date, product_name, category, quantity, unit_price, discount_rate, salesperson, region)
VALUES
('2025-11-01', 'Laptop Pro', 'Electronics', 5, 35000, 0.10, 'Alice', 'North'),
('2025-11-03', 'Office Chair', 'Furniture', 10, 8000, 0.15, 'Bob', 'South'),
('2025-11-05', 'Laptop Pro', 'Electronics', 3, 35000, 0.10, 'Charlie', 'North'),
('2025-11-07', 'Desk Lamp', 'Electronics', 20, 1200, 0.05, 'Alice', 'East'),
('2025-11-10', 'Office Desk', 'Furniture', 8, 15000, 0.20, 'Diana', 'South'),
('2025-11-12', 'Monitor', 'Electronics', 15, 12000, 0.08, 'Bob', 'West'),
('2025-11-15', 'Keyboard', 'Electronics', 30, 2500, 0.00, 'Charlie', 'North'),
('2025-11-18', 'Mouse', 'Electronics', 50, 800, 0.00, 'Alice', 'East'),
('2025-11-20', 'Bookshelf', 'Furniture', 6, 5000, 0.10, 'Diana', 'South'),
('2025-11-22', 'Laptop Basic', 'Electronics', 8, 25000, 0.12, 'Bob', 'West');

-- 銷售分析報表
SELECT
    -- 基本資訊
    sale_id AS 銷售編號,
    sale_date AS 銷售日期,
    product_name AS 產品名稱,

    -- 使用 CASE 分類產品價位
    CASE
        WHEN unit_price >= 20000 THEN '高價位'
        WHEN unit_price >= 5000 THEN '中價位'
        ELSE '低價位'
    END AS 價位分類,

    -- 計算原始金額
    quantity * unit_price AS 原始金額,

    -- 計算折扣金額
    ROUND(quantity * unit_price * discount_rate, 2) AS 折扣金額,

    -- 計算實際金額
    ROUND(quantity * unit_price * (1 - discount_rate), 2) AS 實際金額,

    -- 使用 COALESCE 處理可能的 NULL
    COALESCE(salesperson, '未指定') AS 銷售人員,

    -- 使用 IF 判斷是否達到業績目標
    IF(quantity * unit_price * (1 - discount_rate) >= 100000,
       '達標', '未達標') AS 業績判定

FROM sales_records
ORDER BY sale_date;

-- 按區域統計分析
SELECT
    region AS 區域,
    COUNT(*) AS 銷售筆數,
    COUNT(DISTINCT salesperson) AS 銷售人員數,
    COUNT(DISTINCT category) AS 產品類別數,

    -- 金額統計
    SUM(quantity * unit_price * (1 - discount_rate)) AS 總銷售額,
    ROUND(AVG(quantity * unit_price * (1 - discount_rate)), 2) AS 平均銷售額,

    -- 使用 CASE 計算各類別銷售額
    SUM(CASE WHEN category = 'Electronics'
        THEN quantity * unit_price * (1 - discount_rate)
        ELSE 0 END) AS 電子產品銷售額,
    SUM(CASE WHEN category = 'Furniture'
        THEN quantity * unit_price * (1 - discount_rate)
        ELSE 0 END) AS 傢俱銷售額,

    -- 計算電子產品佔比
    ROUND(
        SUM(CASE WHEN category = 'Electronics'
            THEN quantity * unit_price * (1 - discount_rate)
            ELSE 0 END) * 100 /
        NULLIF(SUM(quantity * unit_price * (1 - discount_rate)), 0),
        2
    ) AS 電子產品佔比

FROM sales_records
GROUP BY region
ORDER BY 總銷售額 DESC;

-- 銷售人員績效排名
SELECT
    salesperson AS 銷售人員,
    COUNT(*) AS 銷售筆數,
    SUM(quantity) AS 總銷售數量,
    SUM(quantity * unit_price * (1 - discount_rate)) AS 總銷售額,
    ROUND(AVG(discount_rate) * 100, 2) AS 平均折扣率,

    -- 績效等級
    CASE
        WHEN SUM(quantity * unit_price * (1 - discount_rate)) >= 200000 THEN 'A'
        WHEN SUM(quantity * unit_price * (1 - discount_rate)) >= 100000 THEN 'B'
        ELSE 'C'
    END AS 績效等級

FROM sales_records
GROUP BY salesperson
ORDER BY 總銷售額 DESC;

-- 效能考量範例

-- 不佳的做法：在 WHERE 子句中對欄位使用函式
-- 這會導致無法使用索引
SELECT * FROM orders
WHERE YEAR(order_date) = 2025;  -- 無法使用 order_date 的索引

-- 較佳的做法：使用範圍條件
SELECT * FROM orders
WHERE order_date >= '2025-01-01'
  AND order_date < '2026-01-01';  -- 可以使用索引

-- 不佳的做法：重複計算
SELECT
    product_name,
    quantity * unit_price AS subtotal,
    quantity * unit_price * 0.05 AS tax,
    quantity * unit_price * 1.05 AS total
FROM sales_records;

-- 較佳的做法：使用子查詢或 CTE 避免重複計算
WITH calculated AS (
    SELECT
        product_name,
        quantity * unit_price AS subtotal
    FROM sales_records
)
SELECT
    product_name,
    subtotal,
    subtotal * 0.05 AS tax,
    subtotal * 1.05 AS total
FROM calculated;

-- 使用適當的資料型別
-- CAST 操作會消耗資源，應該在資料庫設計時就使用正確的型別
-- 避免頻繁的型別轉換