Day 14 為什麼用VIEW

2022 iThome 鐵人賽

DAY 14

自我挑戰組

資料庫新手入門--以PostgreSQL為例系列第 14 篇

14th鐵人賽

啾啾丸

2022-09-14 20:21:30

914 瀏覽

分享至

對於學校環境而言，學生的成績被視為重要的指標，
假設有一位在升學班教書的物理老師，他喜歡跟隔壁班的物理老師比較，
他希望知道班上物理成績最爛的的是哪四位同學?為了面子必須揪出來。
這位老師考量以下的條件因素，經過計算之後可以找出這四位同學!

小考成績總和 * 20%
期中考成績 * 40%
期末考成績 * 40%

建立一個例子看看會長怎樣

CREATE TABLE student_scores (
    id SERIAL PRIMARY KEY,
		student_id INT, -- 學號 (正常來說是學號，但這個例子只用一碼數字當學號)
    name VARCHAR(100) NOT NULL,
    subject VARCHAR(100) NOT NULL, -- 科目
    test VARCHAR(100) NOT NULL, -- 考試類別
    score INT
);

這個例子要從五個人當中挑出四大天王，莫名的很動漫情節 (笑

INSERT INTO student_scores(student_id, name,subject, test, score)
VALUES (1,'張阿生', 'physics','quiz', 30),
(1,'張阿生', 'physics','quiz', 35),
(1,'張阿生', 'physics','midterm', 50),
(1,'張阿生', 'physics','final', 50),
(2,'沈青剛', 'physics','quiz', 60),
(2,'沈青剛', 'physics','quiz', 70),
(2,'沈青剛', 'physics','midterm', 70),
(2,'沈青剛', 'physics','final', 60),
(3,'郝大通', 'physics','quiz', 10),
(3,'郝大通', 'physics','quiz', 15),
(3,'郝大通', 'physics','midterm', 0),
(3,'郝大通', 'physics','final', 5),
(4,'穆念慈', 'physics','quiz', 100),
(4,'穆念慈', 'physics','quiz', 100),
(4,'穆念慈', 'physics','midterm', 100),
(4,'穆念慈', 'physics','final', 100),
(5,'段天德', 'physics','quiz', 35),
(5,'段天德', 'physics','quiz', 45),
(5,'段天德', 'physics','midterm', 30),
(5,'段天德', 'physics','final', 60);

執行以下指令可以算出老師的要求，不過目前尚未講到SUM跟CASE WHEN，
如果看不懂也沒關係，之後還會介紹。

SELECT 
    student_id, 
    name,
    AVG(CASE
        WHEN test = 'quiz' THEN score * 0.2
    END) + 
    SUM(CASE 
    	WHEN test <> 'quiz' THEN score * 0.4
    END)    
FROM student_scores
GROUP BY student_id, name;

老師告訴班長要依這個條件去找出成績最差的四位同學，上課必須罰站，
但是每次都重覆跟班長說一樣的話有點麻煩，所以老師把這個條件取了名子，
稱為物理四大天王，以後只要跟班長說找出物理四大天王，班長就懂意思了!

// 幫這個條件用VIEW建立一個名稱
CREATE VIEW physics_4kings
AS SELECT 
    student_id, 
    name,
    AVG(CASE
        WHEN test = 'quiz' THEN score * 0.2
    END) + 
    SUM(CASE 
    	WHEN test <> 'quiz' THEN score * 0.4
    END)    
FROM student_scores
GROUP BY student_id, name;

日後隨時可以叫出物理四天王，是不是很酷?

SELECT * FROM physics_4kings;

VIEW介紹

VIEW稱為檢視表，或者意義上更適合被稱為虛擬表，我們已經看到物理老師把複雜性條件給簡化了，也就是說 VIEW 具有隱藏複雜性的好處，不過老師生性多疑，半夜夢到班長協助成績差的同學竄改成績，於是老師想到了Day 13 權限的概念，老師只給班長 VIEW physics_4kings 的權限，VIEW只是唯讀的虛擬結構並不能修改資料，而且班長也看不到原始資料，一切就妥果的了，因此VIEW的另一個好處是提供安全性。