SQL nâng cao cho BI: View, Stored Procedure, Window Function và lịch chạy tự động

Tóm tắt nhanh

• View: truy vấn được lưu lại như một bảng ảo — tạo lớp dữ liệu sạch cho Power BI/Excel.
• Stored Procedure: khối lệnh có tham số, chạy nhiều bước, ghi kết quả vào bảng tổng hợp.
• Window Function: tính lũy kế, xếp hạng, trung bình trượt mà không làm mất chi tiết dòng.
• Lập lịch (SQL Server Agent, cron): chạy procedure hằng đêm để dashboard sáng nào cũng có số mới.

Bài này tiếp nối SQL cơ bản. Cú pháp minh hoạ theo SQL Server; MySQL/PostgreSQL có lệnh tương đương.

Kiến trúc dữ liệu 3 lớp cho báo cáo

Lớp	Nội dung	Ví dụ
Nguồn (raw)	Bảng gốc từ ERP/MES, không sửa	erp.PhieuKiem, mes.DungMay
Sạch (clean)	View chuẩn hoá tên, kiểu, loại bỏ dòng hỏng	bi.v_KiemTra
Tổng hợp (mart)	Bảng tổng hợp theo ngày/ca/máy, phục vụ dashboard	bi.KPI_ChatLuong_Ngay

Power BI chỉ đọc lớp sạch hoặc tổng hợp → nhanh, ổn định, logic tính toán nằm một chỗ.

View – lớp dữ liệu sạch

CREATE OR ALTER VIEW bi.v_KiemTra AS
SELECT
    CAST(p.NgayKiem AS date)          AS Ngay,
    UPPER(LTRIM(RTRIM(p.DayChuyen)))  AS DayChuyen,
    p.MaSP,
    COALESCE(p.MaLoi, 'OK')           AS MaLoi,
    p.SoKiem,
    COALESCE(p.SoLoi, 0)              AS SoLoi
FROM erp.PhieuKiem p
WHERE p.TrangThai <> 'HUY'
  AND p.SoKiem > 0;

Stored Procedure – tổng hợp theo ngày

CREATE OR ALTER PROCEDURE bi.sp_TongHopChatLuong
    @TuNgay date, @DenNgay date
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM bi.KPI_ChatLuong_Ngay
    WHERE Ngay BETWEEN @TuNgay AND @DenNgay;

    INSERT INTO bi.KPI_ChatLuong_Ngay (Ngay, DayChuyen, SoKiem, SoLoi, PPM)
    SELECT Ngay, DayChuyen, SUM(SoKiem), SUM(SoLoi),
           SUM(SoLoi) * 1000000.0 / NULLIF(SUM(SoKiem), 0)
    FROM bi.v_KiemTra
    WHERE Ngay BETWEEN @TuNgay AND @DenNgay
    GROUP BY Ngay, DayChuyen;
END;

-- Chạy thử 7 ngày gần nhất
EXEC bi.sp_TongHopChatLuong
     @TuNgay = '2026-10-25', @DenNgay = '2026-10-31';

Cách “xoá rồi ghi lại theo khoảng ngày” giúp chạy lại an toàn nhiều lần (không bị nhân đôi số liệu).

Window Function – phân tích không mất chi tiết

SELECT Ngay, DayChuyen, SoLoi,
    -- Lũy kế trong tháng theo dây chuyền
    SUM(SoLoi) OVER (PARTITION BY DayChuyen, YEAR(Ngay), MONTH(Ngay)
                     ORDER BY Ngay) AS LuyKeThang,
    -- Trung bình trượt 7 ngày
    AVG(SoLoi * 1.0) OVER (PARTITION BY DayChuyen ORDER BY Ngay
                     ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS TB7Ngay,
    -- So với ngày trước
    SoLoi - LAG(SoLoi) OVER (PARTITION BY DayChuyen ORDER BY Ngay) AS ChenhLech,
    -- Xếp hạng dây chuyền theo số lỗi trong ngày
    DENSE_RANK() OVER (PARTITION BY Ngay ORDER BY SoLoi DESC) AS HangTrongNgay
FROM bi.KPI_ChatLuong_Ngay;

Hàm	Công dụng
SUM() OVER (... ORDER BY)	Lũy kế
AVG() OVER (ROWS BETWEEN ...)	Trung bình trượt
LAG(), LEAD()	Giá trị kỳ trước/kỳ sau
ROW_NUMBER(), RANK(), DENSE_RANK()	Đánh số, xếp hạng
NTILE(4)	Chia nhóm tứ phân vị

Lập lịch chạy tự động

SQL Server Agent: tạo Job → Step chạy EXEC bi.sp_TongHopChatLuong @TuNgay = ..., @DenNgay = ... (thường là 3 ngày gần nhất để bắt dữ liệu nhập muộn) → Schedule hằng ngày 5:00. Sau đó Power BI Service làm mới lúc 6:00 — xem Power BI Service.

Không có SQL Server Agent (bản Express, MySQL)? Dùng Windows Task Scheduler gọi sqlcmd, hoặc event scheduler của MySQL.

Kiểm soát chất lượng dữ liệu

• Ghi nhật ký: mỗi lần chạy lưu thời điểm, số dòng, trạng thái vào bảng bi.NhatKyChay.
• Cảnh báo khi số dòng hôm nay bằng 0 hoặc lệch bất thường so với trung bình.
• Đối chiếu tổng tháng giữa lớp tổng hợp và nguồn.

Lỗi hay gặp

• Viết logic tính KPI ở cả SQL lẫn DAX → hai nơi cho hai kết quả.
• Procedure chỉ “INSERT” mà không xoá trước → chạy lại bị nhân đôi.
• Cấp quyền ghi cho tài khoản báo cáo trên cơ sở dữ liệu sản xuất.

Cần người làm giúp?

Industrial Knowledge thiết kế lớp dữ liệu BI, procedure tổng hợp và lịch làm mới cho dashboard nhà máy. Xem dịch vụ.