SQL Nâng Cao: Window Functions, CTE Và Kỹ Thuật Tối Ưu Query

-- Cú pháp tổng quát
function_name(expression) OVER (
    [PARTITION BY column1, column2, ...]  -- Chia nhóm (tùy chọn)
    [ORDER BY column3 ASC|DESC]           -- Sắp xếp trong nhóm
    [ROWS/RANGE frame_clause]             -- Khung cửa sổ (tùy chọn)
)

-- STT trong mỗi phòng ban, sắp theo lương giảm dần
SELECT
    HoTen,
    PhongBan,
    Luong,
    ROW_NUMBER() OVER (PARTITION BY PhongBan ORDER BY Luong DESC) AS STT
FROM NhanVien;

-- Kết quả:
-- Nguyễn Văn An  | Kinh Doanh |  25,000,000 | 1
-- Trần Thị Bình  | Kinh Doanh |  22,000,000 | 2
-- Lê Văn Cường   | Kỹ Thuật   |  30,000,000 | 1
-- Phạm Hồng Đức  | Kỹ Thuật   |  28,000,000 | 2

-- So sánh 3 loại ranking
SELECT
    SanPham,
    DoanhThu,
    ROW_NUMBER() OVER (ORDER BY DoanhThu DESC) AS row_num,   -- 1,2,3,4
    RANK()       OVER (ORDER BY DoanhThu DESC) AS rank_val,  -- 1,2,2,4 (skip)
    DENSE_RANK() OVER (ORDER BY DoanhThu DESC) AS dense_val  -- 1,2,2,3 (không skip)
FROM BanHang;

-- ROW_NUMBER: luôn duy nhất (1,2,3,4...)
-- RANK: trùng thì cùng hạng, skip số tiếp theo (1,2,2,4)
-- DENSE_RANK: trùng thì cùng hạng, KHÔNG skip (1,2,2,3)

-- Doanh thu tháng trước + Tăng trưởng %
SELECT
    Thang,
    DoanhThu,
    LAG(DoanhThu, 1) OVER (ORDER BY Thang) AS DoanhThu_ThangTruoc,
    DoanhThu - LAG(DoanhThu, 1) OVER (ORDER BY Thang) AS ChenhLech,
    ROUND(
        (DoanhThu - LAG(DoanhThu, 1) OVER (ORDER BY Thang))
        / LAG(DoanhThu, 1) OVER (ORDER BY Thang) * 100, 1
    ) AS TangTruong_Pct
FROM DoanhThu_TheoThang;

-- LAG(col, 1): lấy giá trị dòng TRƯỚC 1 vị trí
-- LAG(col, 2): lấy giá trị dòng TRƯỚC 2 vị trí
-- LEAD(col, 1): lấy giá trị dòng SAU 1 vị trí

-- Doanh thu lũy kế theo tháng
SELECT
    Thang,
    DoanhThu,
    SUM(DoanhThu) OVER (ORDER BY Thang ROWS UNBOUNDED PRECEDING) AS LuyKe
FROM DoanhThu_TheoThang;

-- Lũy kế trong từng năm (reset đầu năm)
SELECT
    Nam, Thang, DoanhThu,
    SUM(DoanhThu) OVER (
        PARTITION BY Nam
        ORDER BY Thang
        ROWS UNBOUNDED PRECEDING
    ) AS LuyKe_Theo_Nam
FROM DoanhThu_TheoThang;

-- Trung bình trượt 3 tháng
SELECT
    Thang,
    DoanhThu,
    AVG(DoanhThu) OVER (
        ORDER BY Thang
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MA_3Thang
FROM DoanhThu_TheoThang;

-- Top 3 nhân viên doanh thu cao nhất mỗi phòng ban
WITH RankedSales AS (
    SELECT
        NV.HoTen,
        NV.PhongBan,
        SUM(DH.ThanhTien) AS TongDoanhThu,
        RANK() OVER (PARTITION BY NV.PhongBan ORDER BY SUM(DH.ThanhTien) DESC) AS Hang
    FROM NhanVien NV
    JOIN DonHang DH ON NV.MaNV = DH.MaNV
    GROUP BY NV.HoTen, NV.PhongBan
)
SELECT * FROM RankedSales WHERE Hang <= 3;

-- Tổng hợp: Doanh thu vs Chi phí vs Lợi nhuận theo tháng
WITH DoanhThu AS (
    SELECT
        FORMAT(NgayBan, 'yyyy-MM') AS Thang,
        SUM(ThanhTien) AS TongDoanhThu
    FROM DonHang
    GROUP BY FORMAT(NgayBan, 'yyyy-MM')
),
ChiPhi AS (
    SELECT
        FORMAT(NgayChi, 'yyyy-MM') AS Thang,
        SUM(SoTien) AS TongChiPhi
    FROM ChiPhi
    GROUP BY FORMAT(NgayChi, 'yyyy-MM')
)
SELECT
    DT.Thang,
    DT.TongDoanhThu,
    CP.TongChiPhi,
    DT.TongDoanhThu - ISNULL(CP.TongChiPhi, 0) AS LoiNhuan
FROM DoanhThu DT
LEFT JOIN ChiPhi CP ON DT.Thang = CP.Thang
ORDER BY DT.Thang;

-- Hiển thị cây phân cấp nhân viên
WITH OrgChart AS (
    -- Anchor: Giám đốc (không có quản lý)
    SELECT MaNV, HoTen, MaQuanLy, 0 AS Cap, CAST(HoTen AS NVARCHAR(500)) AS DuongDan
    FROM NhanVien
    WHERE MaQuanLy IS NULL

    UNION ALL

    -- Recursive: nhân viên dưới quyền
    SELECT NV.MaNV, NV.HoTen, NV.MaQuanLy, OC.Cap + 1,
           CAST(OC.DuongDan + ' → ' + NV.HoTen AS NVARCHAR(500))
    FROM NhanVien NV
    JOIN OrgChart OC ON NV.MaQuanLy = OC.MaNV
)
SELECT
    REPLICATE('  ', Cap) + HoTen AS SoDoToChuc,
    Cap AS CapBac,
    DuongDan
FROM OrgChart
ORDER BY DuongDan;

-- Doanh thu theo Sản Phẩm (dòng) → Quý (cột)
SELECT * FROM (
    SELECT SanPham, Quy, DoanhThu
    FROM BanHang_TheoQuy
) AS src
PIVOT (
    SUM(DoanhThu)
    FOR Quy IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt;

-- Kết quả:
-- SanPham    | Q1          | Q2          | Q3          | Q4
-- Laptop     | 500,000,000 | 600,000,000 | 450,000,000 | 800,000,000
-- Điện thoại | 300,000,000 | 350,000,000 | 400,000,000 | 500,000,000

-- ❌ Bad: SELECT * (lấy hết cột)
SELECT * FROM DonHang WHERE MaKH = 1001;

-- ✅ Good: chỉ lấy cột cần
SELECT MaDH, NgayBan, ThanhTien FROM DonHang WHERE MaKH = 1001;

-- ❌ Bad: Subquery trong SELECT (chạy N lần)
SELECT HoTen, (SELECT COUNT(*) FROM DonHang WHERE MaNV = NV.MaNV) AS SoDon
FROM NhanVien NV;

-- ✅ Good: JOIN + GROUP BY (chạy 1 lần)
SELECT NV.HoTen, COUNT(DH.MaDH) AS SoDon
FROM NhanVien NV
LEFT JOIN DonHang DH ON NV.MaNV = DH.MaNV
GROUP BY NV.HoTen;

-- ❌ Bad: LIKE '%keyword%' (full table scan)
-- ✅ Good: LIKE 'keyword%' (sử dụng index) hoặc Full-Text Search