Trong hướng dẫn này, chúng ta sẽ tìm hiểu về hoạt động của mệnh đề PARTITION BY trong SQL và khám phá cách chúng ta có thể sử dụng nó để phân vùng dữ liệu cho một tập hợp con chi tiết hơn.
Cú pháp:
Chúng ta hãy bắt đầu với cú pháp của mệnh đề PARTITION BY. Cú pháp có thể phụ thuộc vào ngữ cảnh bạn sử dụng nhưng đây là cú pháp chung:
CHỌN cột1, cột2, ...
OVER (PHẦN THAM GIA THEO phân vùng_column1, phân vùng_column2, ...)
TỪ tên_bảng
Cú pháp đã cho đại diện cho các phần tử sau:
- cột1, cột2 – Điều này đề cập đến các cột mà chúng tôi muốn đưa vào tập kết quả.
- PHẦN THAM GIA THEO cột - Mệnh đề này xác định cách chúng tôi muốn phân vùng hoặc nhóm dữ liệu.
Dữ liệu mẫu
Chúng ta hãy tạo một bảng cơ bản với dữ liệu mẫu để minh họa cách sử dụng mệnh đề PARTITION BY. Trong ví dụ này, hãy tạo một bảng cơ bản để lưu trữ thông tin sản phẩm.
TẠO sản phẩm BẢNG (
sản phẩm_id INT KHÓA CHÍNH AUTO_INCREMENT,
tên_sản phẩmVARCHAR( 255 ),
danh mục VARCHAR( 255 ),
giá thập phân( 10 , 2 ),
số lượng INT,
ngày hết hạn DATE,
mã vạch BIGINT
);
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Mũ đầu bếp 25cm' ,
'cửa hàng bánh mì' ,
24,67 ,
57 ,
'2023-09-09' ,
2854509564204 );
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Trứng Cút - Đóng Hộp' ,
'nhà bếp' ,
17,99 ,
67 ,
'2023-09-29' ,
1708039594250 );
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Cà phê - Egg Nog Capuccino' ,
'cửa hàng bánh mì' ,
92,53 ,
10 ,
'2023-09-22' ,
8704051853058 );
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Quả Lê - Gai' ,
'cửa hàng bánh mì' ,
65,29 ,
48 ,
'2023-08-23' ,
5174927442238 );
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Mì ống - Tóc thiên thần' ,
'nhà bếp' ,
48,38 ,
59 ,
'2023-08-05' ,
8008123704782 );
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Rượu - Prosecco Valdobiaddene' ,
'sản xuất' ,
44,18 ,
3 ,
'2023-03-13' ,
6470981735653 );
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Bánh Ngọt - Pháp Mini Các Loại' ,
'nhà bếp' ,
36,73 ,
52 ,
'2023-05-29' ,
5963886298051 );
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Cam - Đóng hộp, Quan thoại' ,
'sản xuất' ,
65,0 ,
1 ,
'2023-04-20' ,
6131761721332 );
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Thịt lợn vai' ,
'sản xuất' ,
55,55 ,
73 ,
'2023-05-01' ,
9343592107125 );
chèn
vào trong
sản phẩm (tên_sản phẩm,
loại,
giá,
Số lượng,
ngày hết hạn,
mã vạch)
giá trị ( 'Dc Hikiage Hira Huba' ,
'sản xuất' ,
56,29 ,
53 ,
'2023-04-14' ,
3354910667072 );
Sau khi thiết lập xong dữ liệu mẫu, chúng ta có thể tiếp tục và sử dụng mệnh đề PARTITION BY.
Cách sử dụng cơ bản
Giả sử chúng ta muốn tính tổng số mặt hàng cho từng loại sản phẩm trong bảng trước. Chúng ta có thể sử dụng PARTITION BY để chia các mục thành các danh mục duy nhất và sau đó xác định tổng số lượng trong mỗi danh mục.
Một ví dụ như sau:
LỰA CHỌN
tên sản phẩm,
loại,
Số lượng,
TỔNG(số lượng) TRÊN (PHẦN THAM GIA THEO danh mục) NHƯ tổng_mục
TỪ
các sản phẩm;
Lưu ý rằng trong ví dụ đã cho, chúng tôi phân vùng dữ liệu bằng cột “danh mục”. Sau đó, chúng tôi sử dụng hàm tổng hợp SUM() để xác định tổng số mục trong từng danh mục riêng biệt. Kết quả hiển thị tổng số mục trong mỗi danh mục.
Sử dụng mệnh đề PARTITION BY
Tóm lại, trường hợp sử dụng phổ biến nhất của mệnh đề PARTITION BY là kết hợp với các hàm cửa sổ. Chức năng cửa sổ được áp dụng cho từng phân vùng riêng biệt.
Một số chức năng cửa sổ phổ biến được sử dụng với PARTITION BY bao gồm:
- SUM() – Tính tổng của một cột trong mỗi phân vùng.
- AVG() – Tính giá trị trung bình của một cột trong mỗi phân vùng.
- COUNT() – Đếm số hàng trong mỗi phân vùng.
- ROW_NUMBER() – Gán một số hàng duy nhất cho mỗi hàng trong mỗi phân vùng.
- RANK() – Gán thứ hạng cho mỗi hàng trong mỗi phân vùng.
- DENSE_RANK() – Gán thứ hạng dày đặc cho mỗi hàng trong mỗi phân vùng.
- NTILE() – Chia dữ liệu thành các lượng tử trong mỗi phân vùng.
Đó là nó!
Phần kết luận
Trong hướng dẫn này, chúng ta đã học cách làm việc với mệnh đề PARTITION BY trong SQL để phân vùng dữ liệu thành nhiều phân đoạn khác nhau, sau đó áp dụng một thao tác cụ thể cho từng phân vùng kết quả một cách riêng biệt.