Menu

generate_series trong PostgreSQL: tạo dãy số và dãy ngày

generate_series trả về một tập hàng từ giá trị bắt đầu tới giá trị kết thúc: số, ngày và timestamp. Dùng nó để lấp khoảng trống trong báo cáo, sinh dữ liệu thử và dựng lịch.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

generate_series(start, stop, step) trả về một hàng cho mỗi giá trị từ start tới stop, tính cả stop, mỗi lần tăng thêm step (là 1 nếu bạn bỏ qua). Nó chạy với số nguyên, numeric, và timestamp với bước là một interval. Dùng nó trong FROM như một bảng.

Kết quả là 1, 4, 7 và 10 cùng bình phương của chúng. Bước âm sẽ đếm lùi: generate_series(5, 1, -2) cho ra 5, 3, 1. Một dãy không thể tới điểm kết thúc, như generate_series(5, 1), không trả về hàng nào. generate_series(0, 1, 0.25) đi qua các giá trị numeric.

Tạo dãy ngày

Truyền timestamp hoặc ngày với bước là một interval. Không có phiên bản cho date, nên các giá trị trả về là timestamptz; hãy ép chúng bằng ::date:

Kết quả có năm ngày, từ 26 tháng 2 đến 2 tháng 3, vượt qua cuối tháng. Bất kỳ interval nào cũng dùng được làm bước: interval '1 hour', interval '15 minutes', interval '1 month', interval '1 week'.

Với giá trị timestamptz, bước interval '1 day' đi theo lịch của TimeZone trong phiên. Khi qua thời điểm đổi giờ mùa hè, một bước có thể là 23 hoặc 25 giờ. Từ PostgreSQL 16, đối số thứ tư cho biết múi giờ dùng để bước, bất kể phiên đang dùng múi giờ nào:

SELECT generate_series(
    timestamptz '2026-03-28 00:00 Europe/Berlin',
    timestamptz '2026-03-31 00:00 Europe/Berlin',
    interval '1 day',
    'Europe/Berlin');

Điền những ngày bị thiếu trong báo cáo

Một GROUP BY trên dữ liệu của bạn chỉ trả về những khoảng thời gian có hàng. Để hiện mọi ngày, với số 0 cho những ngày không có đơn hàng, hãy sinh ra các ngày rồi LEFT JOIN dữ liệu vào chúng:

Ngày 1 tháng 11 có 2 đơn hàng, ngày 4 có 1, còn ngày 2, 3 và 5 hiện 0. Hãy đếm một cột của bảng được join (count(o.id)), không phải count(*), nếu không mỗi ngày trống sẽ được tính là 1.

So sánh created_at với một khoảng, thay vì created_at::date = d, giúp dùng được index trên created_at. Cùng cách đó với interval '1 month' và date_trunc sẽ điền những tháng trống (xem hàm ngày tháng).

Sinh dữ liệu thử

INSERT ... SELECT từ một dãy tạo ra bao nhiêu hàng tùy bạn chỉ trong một câu lệnh:

Bảng có 1.000 người dùng trải đều trên 30 ngày đăng ký. Các giá trị được tính từ i, nên lần chạy nào cũng cho cùng kết quả; dùng random() khi bạn muốn chúng thay đổi. Sinh một triệu hàng theo cùng cách để thử một index và so sánh các kế hoạch với EXPLAIN ANALYZE.

Đánh số hàng và dùng dãy trong join

WITH ORDINALITY thêm một cột vị trí cho bất kỳ hàm nào trả về tập hàng:

Kết quả liệt kê năm khung giờ 15 phút từ 09:00 đến 10:00, đánh số từ 1 đến 5. Một dãy cũng dùng được trong CROSS JOIN, ví dụ để ghép mỗi người dùng với mỗi ngày trong tháng.

generate_series trong danh sách SELECT cũng trả về nhiều hàng, nhưng hai lời gọi như vậy có độ dài khác nhau sẽ đệm lời gọi ngắn hơn bằng NULL. Hãy để các hàm trả về tập hàng trong FROM. Với những dãy mà một hàm đơn lẻ không diễn đạt được, như duyệt một cây, hãy dùng CTE đệ quy.

Câu hỏi thường gặp

generate_series có bao gồm giá trị kết thúc không?

Có, khi các bước rơi đúng vào nó: generate_series(1, 5) trả về 1 đến 5. Khi không rơi đúng, dãy dừng ở giá trị cuối cùng chưa vượt quá điểm kết thúc, nên generate_series(1, 10, 4) trả về 1, 5 và 9.

MySQL có generate_series không?

Không. Trong MySQL bạn dựng một dãy bằng CTE đệ quy (WITH RECURSIVE), cách này cũng chạy được trong PostgreSQL nhưng viết dài hơn.

Vì sao generate_series với ngày lại trả về timestamp?

Hàm này không có phiên bản cho date, nên PostgreSQL dùng phiên bản timestamptz. Hãy ép từng giá trị trở lại bằng ::date.

Làm sao sinh một số ngẫu nhiên cho mỗi hàng?

Gọi random() trong danh sách cột: SELECT i, random() FROM generate_series(1, 5) AS i cho mỗi hàng một giá trị từ 0 đến 1, còn floor(random() * 100)::int cho một số nguyên từ 0 đến 99. Từ PostgreSQL 17, random(1, 100) trả về trực tiếp một số nguyên ngẫu nhiên trong khoảng đó.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu