SQL: Advanced Topics

Advanced Topics

ဒီ module မှာ သာမန် data ဖတ်ရှုခြင်းနှင့် ပြင်ဆင်ခြင်းအဆင့်ကိုကျော်လွန်ပြီး data တွေကို analyze လုပ်ခြင်း၊ data protection နှင့် performance တိုးမြှင့်ခြင်း စတဲ့အပိုင်းများကို လေ့လာကြပါမယ်။ ဒီ concepts တွေကို ကျွမ်းကျင်ပိုင်နိုင်ထားခြင်းက SQL developer နှင့် data analyst ကောင်းတစ်ယောက် ဖြစ်လာဖို့အတွက် မရှိမဖြစ်လိုအပ်ပါတယ်။

နမူနာ Employees Table ကိုအသုံးပြုသွားပါမယ်။

EmployeeID	FirstName	Department	Salary
101	Alice	Sales	$70,000
102	Bob	Sales	$80,000
106	Grace	Sales	$80,000
103	Charlie	IT	$95,000
107	Frank	IT	$105,000
104	Diana	HR	$65,000

Unleashing Analytical Power - Window Functions

သာမန် aggregate function တွေက (SUM(), AVG() စသည်) အသုံးဝင်ပေမယ့် သက်ဆိုင်ရာ row တွေကို တစ်ကြောင်းတည်းအဖြစ် ပေါင်းချုပ်ဖော်ပြလေ့ရှိပါတယ်။ Window function တွေကတော့ row အများအပြားကို ခြုံငုံတွက်ချက်ပေးနိုင်ပြီး မူလ row တွေရဲ့ အသေးစိတ်အချက်အလက်တွေကို မပျောက်ပျက်စေဘဲ row တစ်ကြောင်းချင်းစီအတွက် တန်ဖိုးတစ်ခုစီကို ပြန်လည်ထုတ်ပေးပါတယ်။

Window function ရဲ့အဓိကအချက်ကတော့ တွက်ချက်မယ့် row အပိုင်းအခြား “window” ကို သတ်မှတ်ပေးတဲ့ OVER() clause ဖြစ်ပါတယ်။

Ranking Functions

PARTITION BY ကိုသုံး၍ ဌာနတစ်ခုချင်းစီအတွက် window တစ်ခုစီခွဲပြီး ORDER BY ဖြင့် လစာအလိုက် အဆင့်သတ်မှတ်ပါမယ်။

• ROW_NUMBER(): unique ဖြစ်တဲ့ နံပါတ်တွေကို အစဥ်လိုက် တစ်ခုချင်းစီ ထုတ်ပေးပါတယ်။ (ဥပမာ- 1, 2, 3, 4)
• RANK(): အဆင့် သတ်မှတ်ရာမှာ တန်ဖိုးတူညီပါက အဆင့်တူပေးပြီး နောက်အဆင့်ကိုတော့ နံပါတ်ကျော်၍ သတ်မှတ်ပေးပါတယ်။ (ဥပမာ- 1, 2, 2, 4)
• DENSE_RANK(): တန်ဖိုးတူရင်တောင် နံပါတ်မကျော်ဘဲ ဆက်တိုက်သတ်မှတ်ပေးပါတယ်။ (ဥပမာ- 1, 2, 2, 3)

ဥပမာ - ဝန်ထမ်းတွေကို ဌာနအလိုက် လစာအပေါ်မူတည်ပြီး အဆင့်သတ်မှတ်ပါ။

SELECT
    FirstName,
    Department,
    Salary,
    ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RowNum,
    RANK()       OVER(PARTITION BY Department ORDER BY Salary DESC) AS RankNum,
    DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;

ရလဒ် -

FirstName	Department	Salary	RowNum	RankNum	DenseRankNum
Bob	Sales	$80,000	1	1	1
Grace	Sales	$80,000	2	1	1
Alice	Sales	$70,000	3	3	2

Aggregate Window Functions

Standard aggregate function တွေကို Window function ဖြင့် row တွေကို ပေါင်းချုပ်မဖော်ပြဘဲ အသုံးပြုနိုင်ပါတယ်။

ဥပမာ - ဝန်ထမ်းတစ်ဦးချင်းစီရဲ့ လစာဘေးမှာ သူတို့ဌာနရဲ့ ပျမ်းမျှလစာကို ယှဉ်တွဲဖော်ပြပါ။

SELECT
    FirstName,
    Department,
    Salary,
    AVG(Salary) OVER(PARTITION BY Department) AS DeptAverageSalary
FROM Employees;

ရလဒ် -

FirstName	Department	Salary	DeptAverageSalary
Diana	HR	$65,000	$65,000.00
Charlie	IT	$95,000	$100,000.00
Frank	IT	$105,000	$100,000.00
Alice	Sales	$70,000	$76,666.67
Bob	Sales	$80,000	$76,666.67
Grace	Sales	$80,000	$76,666.67

ရလဒ်မှာ ဝန်ထမ်းတစ်ဦးချင်းစီရဲ့ row တိုင်း၌ DeptAverageSalary column အသစ်တစ်ခု ပါဝင်လာပြီး ၎င်းတို့ရဲ့ ဌာနအလိုက် ပျမ်းမျှလစာကို ဖော်ပြပေးထားတာကို တွေ့မြင်နိုင်ပါတယ်။ ဥပမာ - Sales ဌာနမှ ဝန်ထမ်းအားလုံးရဲ့ဘေးမှာ ထိုဌာနရဲ့ ပျမ်းမျှလစာ $76,666.67 ကို ပြသထားပါတယ်။ ထိုသို့ဖြင့် row တစ်ကြောင်းချင်းစီရဲ့ detail ကို aggregate calculation နှင့်အလွယ်တကူ နှိုင်းယှဉ်နိုင်ပါတယ်။

Conditional Logic - CASE Statements

CASE statement က SQL မှာ query အတွင်းရှိ if-then-else logic တွေကို တိုက်ရိုက်လုပ်ဆောင်နိုင်တဲ့ နည်းလမ်းတစ်ခုဖြစ်ပါတယ်။ Data တွေကို အမျိုးအစားတွေခွဲခြားခြင်း သို့မဟုတ် မိမိစိတ်ကြိုက် label များ သတ်မှတ်ခြင်းတို့အတွက် အထူးသင့်လျော်ပါတယ်။

Syntax:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE else_result
END

ဥပမာ - ဝန်ထမ်းတွေကို လစာအဆင့် (salary bands) တွေ ခွဲကြည့်ရအောင်။

SELECT
    FirstName,
    Salary,
    CASE
        WHEN Salary >= 100000 THEN 'Top Tier'
        WHEN Salary >= 75000 AND Salary < 100000 THEN 'Mid Tier'
        ELSE 'Standard Tier'
    END AS SalaryBand
FROM Employees;

ရလဒ် -

FirstName	Salary	SalaryBand
Alice	$70,000	Standard Tier
Bob	$80,000	Mid Tier
Grace	$80,000	Mid Tier
Charlie	$95,000	Mid Tier
Frank	$105,000	Top Tier
Diana	$65,000	Standard Tier

Ensuring Data Integrity - Transactions

Transaction ဆိုတာက လုပ်ငန်းဆောင်တာအစုအဝေးတစ်ခုကို တစ်ခုတည်းအဖြစ် သတ်မှတ်ပြီး လုပ်ဆောင်ခြင်းဖြစ်ပါတယ်။ Transaction အတွင်းရှိ လုပ်ငန်းဆောင်တာအားလုံး အောင်မြင်စွာပြီးဆုံးပါက data အပြောင်းအလဲတွေကို database ထဲမှာ အပြီးတိုင်သိမ်းဆည်းပါတယ်။ ဒါမှမဟုတ် Transaction အတွင်းက လုပ်ငန်းဆောင်တာတစ်ခုခု မှားယွင်းခဲ့ရင်တော့ လုပ်ဆောင်ခဲ့သမျှအားလုံးကို မူလအခြေအနေအတိုင်း ပြန်ရောက်အောင်လုပ်ဆောင်ပေးပါတယ်။ ဒီလိုလုပ်ခြင်းအားဖြင့် data တွေရဲ့ မှန်ကန်တိကျမှု (integrity) ကို အာမခံပေးပါတယ်။

အဓိက Command များ -

• BEGIN TRANSACTION; - Transaction block ကို စတင်ပါတယ်။
• COMMIT; - data အပြောင်းအလဲ (data changes) တွေကို အတည်ပြု သိမ်းဆည်းပါတယ်။
• ROLLBACK; - Transaction စတင်ချိန်မှစ၍ လုပ်ဆောင်ခဲ့တဲ့ data အပြောင်းအလဲအားလုံးကို ပြန်ဖျက်ပြီး မူလအခြေအနေကို ပြန်ရောက်စေပါတယ်။

ဥပမာ - ဘဏ်အကောင့်နှစ်ခုကြား ငွေလွှဲခြင်း။

data integrity ရှိဖို့အတွက် ဒီလုပ်ငန်းစဉ်က တစ်ပြိုင်နက်တည်း အကုန်ဖြစ်မြောက်ရပါမယ် ဒါမှမဟုတ် လုံးဝမဖြစ်မြောက်ရပါဘူး။

BEGIN TRANSACTION;

-- အဆင့် ၁: Account A ကနေ $500 နှုတ်ပါ။
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 'A';

-- အဆင့် ၂: Account B ကို $500 ပေါင်းပါ။
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 'B';

-- အဆင့်နှစ်ခုလုံး အောင်မြင်မှသာ data changes တွေကို အတည်ပြုသိမ်းဆည်းပါမယ်။
-- အကယ်၍ လုပ်ငန်းစဉ်တစ်ခုခု (ဥပမာ- Account B မရှိခြင်း) မှားယွင်းပါက
-- database system က transaction တစ်ခုလုံးကို အလိုအလျောက် ROLLBACK ပြုလုပ်ပြီး
-- Account A မှ နှုတ်ယူခဲ့တဲ့ငွေကို ပြန်ထည့်ပေးပါမယ်။
COMMIT;

Improving Performance - Indexes

Index ဆိုတာက database search engine က data တွေကို လျင်မြန်စွာရှာဖွေနိုင်ရန် အထူးပြုလုပ်ထားတဲ့ lookup table တစ်ခုဖြစ်ပါတယ်။ စာအုပ်တစ်အုပ်ရဲ့ မာတိကာ (index) လိုပါပဲ။ အကြောင်းအရာတစ်ခုကို ရှာချင်ရင် စာအုပ်တစ်အုပ်လုံးကို တစ်မျက်နှာချင်း လိုက်လှန်ဖတ်မယ့်အစား မာတိကာတွင် အကြောင်းအရာကို အက္ခရာစဉ်အတိုင်းရှာပြီး သက်ဆိုင်ရာ စာမျက်နှာနံပါတ်ကို တိုက်ရိုက်သွားရောက်နိုင်၍ ပိုမြန်ဆန်အောင် ကူညီပေးပါတယ်။

ဘယ်လိုအလုပ်လုပ်လဲ

• Index တည်ဆောက်ခြင်း - သင် index ပြုလုပ်လိုက်တဲ့ column (ဥပမာ- Department) ရဲ့တန်ဖိုးတွေကို ကူးယူပြီး စီစဉ်တကျ (sorted) စာရင်းတစ်ခု ပြုလုပ်ပါတယ်။
• Pointer များ သိမ်းဆည်းခြင်း - ထိုတန်ဖိုးတစ်ခုချင်းစီရဲ့ဘေးမှာ ၎င်းပါဝင်သော row ရဲ့ တည်နေရာကို ညွှန်ပြတဲ့ လမ်းညွှန် (pointer) တစ်ခုကို သိမ်းဆည်းထားပါတယ်။
• Query လုပ်ဆောင်ခြင်း - သင် WHERE Department = ‘IT’ လို့ရှာတဲ့အခါ database က table တစ်ခုလုံးကို မစစ်ဆေးတော့ဘဲ စီစဉ်ထားပြီးဖြစ်တဲ့ index ထဲမှာ ‘IT’ ကို လျင်မြန်စွာရှာဖွေပြီး သက်ဆိုင်ရာ pointer များမှတစ်ဆင့် ကိုက်ညီတဲ့ row တွေကို တိုက်ရိုက် ဆွဲထုတ်နိုင်ပါတယ်။

Trade-off

Index တွေက SELECT query တွေကို မြန်ဆန်စေသော်လည်း INSERT, UPDATE, DELETE လုပ်ငန်းစဉ်တွေကိုတော့ နှေးကွေးစေနိုင်ပါတယ်။ ဘာလို့လဲဆိုတော့ data ပြောင်းလဲမှုတိုင်းမှာ index ကိုယ်တိုင်ကိုလည်း update ပြုလုပ်ရန် လိုအပ်တာကြောင့်ဖြစ်ပါတယ်။

ဘယ်အချိန်မှာသုံးမလဲ

WHERE clause တွေထဲမှာ မကြာခဏအသုံးပြုတဲ့ column တွေမှာ သုံးသင့်ပါတယ်။ Primary key တွေကိုတော့ database က အလိုအလျောက် index ပြုလုပ်ပြီးသားဖြစ်ပါတယ်။

Syntax:

CREATE INDEX idx_employees_department
ON Employees (Department);

ဒီ command က table တစ်ခုမှာ SELECT * FROM Employees WHERE Department = 'IT'; လို query မျိုးကို သိသိသာသာ ပိုမြန်စေပါတယ်။

Simplifying Complex Queries - Views

View ဆိုတာက သိမ်းဆည်းထားသော SELECT query တစ်ခုဖြစ်ပြီး virtual table (အမှန်တကယ် မရှိသော table) တစ်ခုအနေဖြင့် သဘောထားနိုင်ပါတယ်။ View ကိုယ်တိုင်က data မသိမ်းထားပေမယ့် မူရင်း data တွေကို ရိုးရှင်းပြီး လုံခြုံတဲ့ပုံစံဖြင့် ကြည့်ရှုနိုင်ရန် ပြုလုပ်ပေးပါတယ်။

အကျိုးကျေးဇူးများ -

• ရိုးရှင်းမှု (Simplicity) - ရှုပ်ထွေးတဲ့ JOIN တွေနှင့် တွက်ချက်မှုတွေကို end-users တွေမမြင်ရအောင် ပြုလုပ်ပေးနိုင်ပါတယ်။
• လုံခြုံမှု (Security) - sensitive column တွေ (လစာလိုမျိုး) ဒါမှမဟုတ် သတ်မှတ်ထားတဲ့ row တွေကိုပဲ ကြည့်လို့ရအောင် user တွေကို ကန့်သတ်ထားနိုင်ပါတယ်။

CREATE VIEW v_EmployeeHighSalaries AS
SELECT
    FirstName,
    Department,
    Salary
FROM Employees
WHERE Salary >= 80000;

အခုဆိုရင် ဒီ view ကို table အစစ်တစ်ခုလိုမျိုး query လုပ်နိုင်ပြီး သူ့ရဲ့ logic ကလည်း အမြဲတမ်း တသမတ်တည်းဖြစ်နေမှာဖြစ်ပါတယ်။

SELECT * FROM v_EmployeeHighSalaries WHERE Department = 'Sales';