~30 phútAdvanced query patterns… lượt xem

Mini-challenge M08 — Sprint burndown chart: precompute snapshot hằng ngày

TaskFlow cần burndown chart realtime cho sprint. Compute on-the-fly quá chậm — precompute snapshot table cập nhật incremental. Combine 4 pattern Module 8: recursive CTE date series, window function cumulative, UPSERT incremental refresh.

TaskFlow vừa ra tính năng agile sprint: mỗi sprint có start_date, end_date, và một tập task được assign vào. PM yêu cầu burndown chart hiển thị số task còn lại mỗi ngày — đường thực tế so với đường ideal từ tổng số task về 0 cuối sprint.

Query on-the-fly nghe có vẻ đơn giản: COUNT(*) WHERE sprint_id = X AND status != 'done'. Vấn đề là "as-of ngày D" — bạn cần biết trạng thái tại thời điểm đó, không phải hôm nay. Với 50 sprint active, 1000 task mỗi sprint, dashboard load mỗi 30 giây — compute on-the-fly chạy hàng trăm subquery per request, p99 vọt lên 3 giây.

Giải pháp: precompute snapshot table — mỗi dòng lưu (sprint_id, day, remaining_count, ideal_count). Refresh incremental hằng ngày, chỉ update ngày hiện tại. Dashboard query trở thành SELECT * FROM sprint_burndown_snapshot WHERE sprint_id = X — O(sprint_duration), cực nhanh.

Bài này tổng hợp 4 pattern từ module này: recursive CTE (bài 2 của module này) để generate date series, window function (bài 3 của module này) để tính cumulative completed, và UPSERT (bài 5 của module này) để incremental refresh idempotent. LATERAL (bài 1 của module này) được nhắc đến như alternative approach cho per-day subquery.

Setup — schema sprint + snapshot table

Extend TaskFlow canonical schema thêm sprints, sprint_id trên tasks, và bảng snapshot:

-- ==============================================
-- DDL: sprint tables
-- ==============================================

CREATE TABLE sprints (
  id         BIGSERIAL PRIMARY KEY,
  project_id BIGINT NOT NULL REFERENCES projects(id),
  name       TEXT NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL,
  CHECK (end_date >= start_date)
);

-- Extend tasks de assign vao sprint
ALTER TABLE tasks ADD COLUMN sprint_id BIGINT REFERENCES sprints(id);

-- Snapshot table: 1 row per (sprint, day)
CREATE TABLE sprint_burndown_snapshot (
  sprint_id      BIGINT NOT NULL REFERENCES sprints(id),
  day            DATE NOT NULL,
  remaining_count INT NOT NULL,
  ideal_count    INT NOT NULL,    -- linear trajectory: total -> 0 theo ngay
  computed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (sprint_id, day)
);

-- Index cho dashboard query
CREATE INDEX idx_burndown_sprint ON sprint_burndown_snapshot(sprint_id, day);

Seed data: 1 sprint 14 ngày, 30 task, một số đã hoàn thành rải đều trong sprint:

-- ==============================================
-- Seed: 1 sprint 2 tuan + 30 tasks
-- ==============================================

-- Sprint demo (project_id = 1 phai ton tai)
INSERT INTO sprints (id, project_id, name, start_date, end_date)
VALUES (1, 1, 'Sprint 1 — Auth & Dashboard', '2026-04-14', '2026-04-27')
ON CONFLICT (id) DO NOTHING;

-- 30 tasks assign vao sprint 1
-- Assignee, project la du lieu co san tu seed truoc
INSERT INTO tasks (project_id, assignee_id, title, status, sprint_id, created_at, updated_at)
SELECT
  1,                                             -- project_id
  (random() * 5 + 1)::int,                      -- assignee_id 1-6
  'Task ' || gs,
  CASE
    WHEN gs <= 8  THEN 'done'                    -- 8 task done tu ngay dau sprint
    WHEN gs <= 12 THEN 'doing'
    ELSE               'todo'
  END,
  1,                                             -- sprint_id
  '2026-04-14'::timestamptz,
  CASE
    WHEN gs <= 3  THEN '2026-04-15'::timestamptz -- done ngay 2
    WHEN gs <= 6  THEN '2026-04-18'::timestamptz -- done ngay 5
    WHEN gs <= 8  THEN '2026-04-21'::timestamptz -- done ngay 8
    ELSE               '2026-04-14'::timestamptz -- chua done
  END
FROM generate_series(1, 30) gs;

-- Kiem tra
SELECT status, count(*) FROM tasks WHERE sprint_id = 1 GROUP BY status;
-- Expected: done=8, doing=4, todo=18

Step 1 — Recursive CTE date series

Cần generate mọi ngày từ start_date đến end_date của sprint. PostgreSQL có generate_series đơn giản hơn cho production — nhưng bài này dùng recursive CTE để demo pattern từ bài 2 của module này:

-- Alternative don gian hon (production-friendly):
-- SELECT day::date
-- FROM generate_series(
--   (SELECT start_date FROM sprints WHERE id = 1),
--   (SELECT end_date   FROM sprints WHERE id = 1),
--   '1 day'
-- ) AS day;

-- Recursive CTE (demo pattern bai 2 module nay -- ket qua tuong duong):
WITH RECURSIVE sprint_days AS (
  -- Anchor: ngay dau sprint
  SELECT start_date AS day, end_date
  FROM sprints
  WHERE id = 1

  UNION ALL

  -- Recursive: them 1 ngay, dung khi dat end_date
  SELECT day + 1, end_date
  FROM sprint_days
  WHERE day < end_date
)
SELECT day FROM sprint_days ORDER BY day;
-- 14 rows: 2026-04-14 ... 2026-04-27

Test nhanh: query trên phải trả về đúng 14 row cho sprint 14 ngày. Recursive CTE dừng tự nhiên khi day = end_date — không cần LIMIT hay điều kiện thoát tường minh ngoài WHERE day < end_date.

Tại sao recursive CTE thay vì generate_series? Trong production, generate_series ngắn gọn và đủ. Recursive CTE có giá trị khi logic phức tạp hơn — ví dụ skip ngày cuối tuần, hoặc generate path hierarchy (bài 2 của module này). Biết cả hai pattern là quan trọng.

Step 2 — Compute remaining count cumulative với window

Per ngày, cần biết có bao nhiêu task đã được done tính đến ngày đó (cumulative), rồi lấy total trừ đi. Window function SUM() OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) làm đúng việc đó — bài 3 của module này.

-- Tach rieng query nay de hieu tung buoc

WITH sprint_days AS (
  -- Dung generate_series ngan gon hon trong intermediate step
  SELECT day::date
  FROM generate_series(
    (SELECT start_date FROM sprints WHERE id = 1),
    (SELECT end_date   FROM sprints WHERE id = 1),
    '1 day'
  ) AS day
),

-- So task hoan thanh moi ngay (dua vao updated_at)
done_per_day AS (
  SELECT
    updated_at::date AS day,
    count(*)         AS done_today
  FROM tasks
  WHERE sprint_id = 1
    AND status = 'done'
  GROUP BY updated_at::date
),

-- Tong so task trong sprint (bat bien)
total_tasks AS (
  SELECT count(*) AS total FROM tasks WHERE sprint_id = 1
)

SELECT
  sd.day,
  tt.total,
  -- Cumulative done tinh den ngay nay (window: sum tu dau den hien tai)
  COALESCE(
    SUM(dpd.done_today) OVER (ORDER BY sd.day ROWS UNBOUNDED PRECEDING),
    0
  ) AS done_cumulative,
  -- Remaining = total - da hoan thanh
  tt.total - COALESCE(
    SUM(dpd.done_today) OVER (ORDER BY sd.day ROWS UNBOUNDED PRECEDING),
    0
  ) AS remaining_count
FROM sprint_days sd
LEFT JOIN done_per_day dpd ON dpd.day = sd.day
CROSS JOIN total_tasks tt
ORDER BY sd.day;

Tại sao LEFT JOIN với done_per_day? Ngày không có task nào done sẽ không có row trong done_per_day. LEFT JOIN giữ lại ngày đó với done_today = NULL — COALESCE(..., 0) chuyển thành 0. Không dùng LEFT JOIN → mất ngày, burndown chart có lỗ hổng.

Tại sao CROSS JOIN total_tasks? total_tasks trả 1 row duy nhất (scalar). CROSS JOIN với 1 row = thêm column total vào mọi row của sprint_days mà không cần subquery lặp lại.

Step 3 — Combine với CTE chain + ideal trajectory

Thêm ideal_count — đường linear từ total về 0 qua sprint_duration ngày. Công thức:

ideal_count = ROUND(total * (1 - day_index::float / sprint_duration))

Trong đó day_index = số thứ tự ngày trong sprint (0 = ngày đầu, duration = ngày cuối).

-- Full backfill query: compute snapshot cho moi ngay trong sprint
WITH RECURSIVE sprint_days AS (
  SELECT start_date AS day, end_date, start_date
  FROM sprints WHERE id = 1
  UNION ALL
  SELECT day + 1, end_date, start_date
  FROM sprint_days WHERE day < end_date
),

done_per_day AS (
  SELECT
    updated_at::date AS day,
    count(*)         AS done_today
  FROM tasks
  WHERE sprint_id = 1 AND status = 'done'
  GROUP BY updated_at::date
),

total_tasks AS (
  SELECT count(*) AS total FROM tasks WHERE sprint_id = 1
),

-- Tinh sprint duration mot lan
sprint_meta AS (
  SELECT
    (end_date - start_date) AS duration,   -- so ngay (int)
    start_date
  FROM sprints WHERE id = 1
),

cumulative AS (
  SELECT
    sd.day,
    sd.start_date,
    tt.total,
    sm.duration,
    (sd.day - sd.start_date) AS day_index,  -- 0-based
    COALESCE(
      SUM(dpd.done_today) OVER (ORDER BY sd.day ROWS UNBOUNDED PRECEDING),
      0
    ) AS done_cumulative
  FROM sprint_days sd
  LEFT JOIN done_per_day dpd ON dpd.day = sd.day
  CROSS JOIN total_tasks tt
  CROSS JOIN sprint_meta sm
)

SELECT
  1                              AS sprint_id,
  day,
  (total - done_cumulative)::int AS remaining_count,
  -- Ideal: tuyen tinh tu total -> 0 (round de tranh float)
  ROUND(total * (1.0 - day_index::float / NULLIF(duration, 0)))::int
                                 AS ideal_count
FROM cumulative
ORDER BY day;

NULLIF(duration, 0) — phòng sprint 0 ngày (CHECK constraint ngăn điều này, nhưng defensive coding không thừa). NULLIF trả NULL thay vì divide-by-zero → ROUND(... / NULL) = NULL → bắt được ở tầng app.

Step 4 — UPSERT incremental daily refresh

Production pattern: chạy mỗi đêm (ví dụ 23:55), chỉ refresh ngày hiện tại (và ngày hôm qua để phòng late update — task được mark done sau nửa đêm):

-- Chay hang dem: incremental refresh
-- Idempotent: chay lai nhieu lan an toan (ON CONFLICT DO UPDATE)

WITH RECURSIVE sprint_days AS (
  SELECT start_date AS day, end_date, start_date
  FROM sprints WHERE id = $1
  UNION ALL
  SELECT day + 1, end_date, start_date
  FROM sprint_days WHERE day < end_date
),

done_per_day AS (
  SELECT
    updated_at::date AS day,
    count(*)         AS done_today
  FROM tasks
  WHERE sprint_id = $1 AND status = 'done'
  GROUP BY updated_at::date
),

total_tasks AS (
  SELECT count(*) AS total FROM tasks WHERE sprint_id = $1
),

sprint_meta AS (
  SELECT (end_date - start_date) AS duration, start_date
  FROM sprints WHERE id = $1
),

cumulative AS (
  SELECT
    sd.day,
    tt.total,
    sm.duration,
    (sd.day - sd.start_date) AS day_index,
    COALESCE(
      SUM(dpd.done_today) OVER (ORDER BY sd.day ROWS UNBOUNDED PRECEDING),
      0
    ) AS done_cumulative
  FROM sprint_days sd
  LEFT JOIN done_per_day dpd ON dpd.day = sd.day
  CROSS JOIN total_tasks tt
  CROSS JOIN sprint_meta sm
),

final_rows AS (
  SELECT
    $1::bigint                             AS sprint_id,
    day,
    (total - done_cumulative)::int         AS remaining_count,
    ROUND(total * (1.0 - day_index::float / NULLIF(duration, 0)))::int
                                           AS ideal_count
  FROM cumulative
  -- Chi compute 2 ngay gan nhat (hom nay + hom qua phong late update)
  WHERE day >= CURRENT_DATE - INTERVAL '1 day'
    AND day <= CURRENT_DATE
)

INSERT INTO sprint_burndown_snapshot (sprint_id, day, remaining_count, ideal_count)
SELECT sprint_id, day, remaining_count, ideal_count FROM final_rows
ON CONFLICT (sprint_id, day) DO UPDATE
  SET remaining_count = EXCLUDED.remaining_count,
      ideal_count     = EXCLUDED.ideal_count,
      computed_at     = now();
-- Khong co WHERE tren DO UPDATE: update ca hai ngay (hom qua + hom nay)

Tại sao idempotent quan trọng? Cron job có thể chạy lại do retry. Job chạy 2 lần lúc 23:55 và 23:56 phải cho cùng kết quả — ON CONFLICT DO UPDATE đảm bảo điều đó. Không có DO NOTHING vì bạn muốn kết quả mới nhất ghi đè nếu có thêm task done trong ngày.

Phong trào late update (ngày hôm qua): Task đôi khi được mark done vào 00:01 sáng — về mặt updated_at thuộc hôm nay nhưng về mặt business thuộc ngày hôm qua của sprint. Refresh cả hai ngày đảm bảo chart không bị lag 1 ngày.

Deliverable — 1 SQL file complete

Copy-paste file sprint_burndown.sql với 3 section:

-- ====================================================
-- sprint_burndown.sql
-- Mini-challenge M08: Sprint Burndown Chart Snapshot
-- 3 section: DDL, backfill, daily incremental
-- ====================================================


-- ====================================================
-- SECTION A: DDL + seed (chay mot lan khi setup)
-- ====================================================

CREATE TABLE sprints (
  id         BIGSERIAL PRIMARY KEY,
  project_id BIGINT NOT NULL REFERENCES projects(id),
  name       TEXT NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL,
  CHECK (end_date >= start_date)
);

ALTER TABLE tasks ADD COLUMN IF NOT EXISTS sprint_id BIGINT REFERENCES sprints(id);

CREATE TABLE sprint_burndown_snapshot (
  sprint_id      BIGINT NOT NULL REFERENCES sprints(id),
  day            DATE NOT NULL,
  remaining_count INT NOT NULL,
  ideal_count    INT NOT NULL,
  computed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (sprint_id, day)
);

CREATE INDEX idx_burndown_sprint ON sprint_burndown_snapshot(sprint_id, day);

-- Seed: 1 sprint 14 ngay
INSERT INTO sprints (id, project_id, name, start_date, end_date)
VALUES (1, 1, 'Sprint 1 — Auth & Dashboard', '2026-04-14', '2026-04-27')
ON CONFLICT (id) DO NOTHING;

-- 30 tasks: 8 done rai trong sprint, 22 con lai
INSERT INTO tasks (project_id, assignee_id, title, status, sprint_id, created_at, updated_at)
SELECT
  1,
  (random() * 5 + 1)::int,
  'Task ' || gs,
  CASE WHEN gs <= 8 THEN 'done' WHEN gs <= 12 THEN 'doing' ELSE 'todo' END,
  1,
  '2026-04-14'::timestamptz,
  CASE
    WHEN gs <= 3 THEN '2026-04-15'::timestamptz
    WHEN gs <= 6 THEN '2026-04-18'::timestamptz
    WHEN gs <= 8 THEN '2026-04-21'::timestamptz
    ELSE              '2026-04-14'::timestamptz
  END
FROM generate_series(1, 30) gs;


-- ====================================================
-- SECTION B: Initial backfill (chay mot lan sau Section A)
-- Compute snapshot cho moi ngay trong toan bo sprint range
-- ====================================================

WITH RECURSIVE sprint_days AS (
  SELECT start_date AS day, end_date, start_date
  FROM sprints WHERE id = 1
  UNION ALL
  SELECT day + 1, end_date, start_date
  FROM sprint_days WHERE day < end_date
),
done_per_day AS (
  SELECT updated_at::date AS day, count(*) AS done_today
  FROM tasks
  WHERE sprint_id = 1 AND status = 'done'
  GROUP BY updated_at::date
),
total_tasks AS (
  SELECT count(*) AS total FROM tasks WHERE sprint_id = 1
),
sprint_meta AS (
  SELECT (end_date - start_date) AS duration, start_date
  FROM sprints WHERE id = 1
),
cumulative AS (
  SELECT
    sd.day,
    tt.total,
    sm.duration,
    (sd.day - sd.start_date) AS day_index,
    COALESCE(
      SUM(dpd.done_today) OVER (ORDER BY sd.day ROWS UNBOUNDED PRECEDING),
      0
    ) AS done_cumulative
  FROM sprint_days sd
  LEFT JOIN done_per_day dpd ON dpd.day = sd.day
  CROSS JOIN total_tasks tt
  CROSS JOIN sprint_meta sm
)
INSERT INTO sprint_burndown_snapshot (sprint_id, day, remaining_count, ideal_count)
SELECT
  1,
  day,
  (total - done_cumulative)::int,
  ROUND(total * (1.0 - day_index::float / NULLIF(duration, 0)))::int
FROM cumulative
ORDER BY day
ON CONFLICT (sprint_id, day) DO UPDATE
  SET remaining_count = EXCLUDED.remaining_count,
      ideal_count     = EXCLUDED.ideal_count,
      computed_at     = now();

-- Kiem tra ket qua backfill
SELECT day, remaining_count, ideal_count
FROM sprint_burndown_snapshot
WHERE sprint_id = 1
ORDER BY day;
-- 14 rows, remaining_count giam dan, ideal_count giam tuyen tinh


-- ====================================================
-- SECTION C: Daily incremental UPSERT
-- Chay moi dem 23:55 (hoac qua pg_cron / bat ky scheduler)
-- Idempotent: chay lai nhieu lan van cho ket qua dung
-- $1 = sprint_id can refresh
-- ====================================================

WITH RECURSIVE sprint_days AS (
  SELECT start_date AS day, end_date, start_date
  FROM sprints WHERE id = $1
  UNION ALL
  SELECT day + 1, end_date, start_date
  FROM sprint_days WHERE day < end_date
),
done_per_day AS (
  SELECT updated_at::date AS day, count(*) AS done_today
  FROM tasks
  WHERE sprint_id = $1 AND status = 'done'
  GROUP BY updated_at::date
),
total_tasks AS (
  SELECT count(*) AS total FROM tasks WHERE sprint_id = $1
),
sprint_meta AS (
  SELECT (end_date - start_date) AS duration, start_date
  FROM sprints WHERE id = $1
),
cumulative AS (
  SELECT
    sd.day,
    tt.total,
    sm.duration,
    (sd.day - sd.start_date) AS day_index,
    COALESCE(
      SUM(dpd.done_today) OVER (ORDER BY sd.day ROWS UNBOUNDED PRECEDING),
      0
    ) AS done_cumulative
  FROM sprint_days sd
  LEFT JOIN done_per_day dpd ON dpd.day = sd.day
  CROSS JOIN total_tasks tt
  CROSS JOIN sprint_meta sm
  WHERE sd.day >= CURRENT_DATE - INTERVAL '1 day'   -- chi 2 ngay gan nhat
    AND sd.day <= CURRENT_DATE
),
final_rows AS (
  SELECT
    $1::bigint                               AS sprint_id,
    day,
    (total - done_cumulative)::int           AS remaining_count,
    ROUND(total * (1.0 - day_index::float / NULLIF(duration, 0)))::int
                                             AS ideal_count
  FROM cumulative
)
INSERT INTO sprint_burndown_snapshot (sprint_id, day, remaining_count, ideal_count)
SELECT sprint_id, day, remaining_count, ideal_count FROM final_rows
ON CONFLICT (sprint_id, day) DO UPDATE
  SET remaining_count = EXCLUDED.remaining_count,
      ideal_count     = EXCLUDED.ideal_count,
      computed_at     = now();

-- Sau khi chay: kiem tra ngay hom nay da duoc cap nhat
SELECT sprint_id, day, remaining_count, ideal_count, computed_at
FROM sprint_burndown_snapshot
WHERE sprint_id = $1
  AND day >= CURRENT_DATE - INTERVAL '1 day'
ORDER BY day;

Dashboard query sau khi có snapshot — đơn giản và nhanh:

-- Dashboard query: O(sprint_duration) thay vi O(tasks * days)
SELECT day, remaining_count, ideal_count
FROM sprint_burndown_snapshot
WHERE sprint_id = $1
ORDER BY day;

Forward — M11 của khoá này: Materialized View + pg_cron

Pattern bài này là manual snapshot: bạn tự viết UPSERT, tự schedule, tự quản lý idempotency. Hoạt động tốt cho 1–5 sprint. Khi scale lên 100 sprint active, bạn cần automation.

Bước tiếp theo trong production — 2 công cụ PostgreSQL native:

MATERIALIZED VIEW REFRESH CONCURRENTLY — định nghĩa view một lần, PostgreSQL quản lý storage và refresh. CONCURRENTLY cho phép refresh không lock read:

-- Thay the snapshot table bang materialized view
CREATE MATERIALIZED VIEW sprint_burndown_mv AS
  -- (query tu Section B, khong co UPSERT)
  ...;

-- Refresh khong lock: doc van chay duoc trong luc refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY sprint_burndown_mv;

pg_cron — scheduler chạy trong PostgreSQL process, không cần cron bên ngoài:

-- Cai pg_cron extension (AWS RDS, Supabase ho tro san)
-- Schedule refresh luc 23:55 moi dem
SELECT cron.schedule(
  'burndown-refresh',
  '55 23 * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY sprint_burndown_mv'
);

Khi nào nên dùng manual snapshot (bài này) vs MV + pg_cron (M11)?

	Manual snapshot (bài này)	MV + pg_cron (M11)
Setup	DDL + script UPSERT	DDL + cron.schedule()
Partial refresh	Dễ — chỉ UPSERT ngày cần	Khó — MV refresh toàn bộ
Debug	SQL thông thường	Cần hiểu MV internals
Dependency	Không	Cần pg_cron extension
Scale	Tốt cho vài sprint	Tốt cho nhiều view đồng thời

Manual snapshot phù hợp khi bạn cần kiểm soát chi tiết (chỉ refresh ngày mới, phòng late update). MV + pg_cron phù hợp khi bạn muốn declarative — khai báo "view này trông như thế nào", PostgreSQL lo phần còn lại.

M11 của khoá này sẽ deep dive MATERIALIZED VIEW REFRESH CONCURRENTLY (cơ chế lock, unique index requirement), pg_cron (cấu hình, monitoring job log), và chiến lược kết hợp partial refresh với MV cho dataset lớn.

Tự kiểm tra

Recursive CTE trong bài này dùng pattern anchor + recursive part. Điều kiện termination ở đâu và vì sao không cần viết tường minh?

▸

Điều kiện termination nằm ở mệnh đề WHERE day < end_date trong recursive part. Khi day đạt end_date, điều kiện day < end_date trả false → recursive part trả 0 row → PostgreSQL dừng vòng lặp tự nhiên.

Không cần viết STOP hay BREAK vì cơ chế recursive CTE hoạt động theo nguyên tắc: tiếp tục khi recursive part có row mới, dừng khi không còn row mới nào. làm cho vòng tiếp theo không có row → tự dừng. Đây là cùng pattern với cây comment bài 2 của module này: dừng khi không còn node con.

Window function `SUM(done_today) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING)` tính cumulative. Tại sao cần `LEFT JOIN` với `done_per_day` thay vì `INNER JOIN`? Kết quả sẽ khác nhau thế nào?

▸

LEFT JOIN giữ lại mọi ngày trong sprint_days — kể cả ngày không có task nào được done (done_today = NULL). COALESCE(..., 0) chuyển NULL thành 0 cho những ngày đó.

Nếu dùng INNER JOIN: ngày không có done task sẽ bị loại khỏi kết quả. Sprint 14 ngày mà chỉ 3 ngày có task done → snapshot chỉ có 3 row thay vì 14. Burndown chart sẽ bị thiếu điểm dữ liệu — chart frontend không thể vẽ đường liên tục. LEFT JOIN đảm bảo mọi ngày đều có row trong snapshot, kể cả ngày "không có gì thay đổi".

UPSERT Section C dùng `WHERE sd.day >= CURRENT_DATE - INTERVAL '1 day' AND sd.day <= CURRENT_DATE` để filter chỉ 2 ngày. Tại sao cần refresh cả ngày hôm qua thay vì chỉ hôm nay?

▸

Task có thể được mark done vào đầu giờ sáng ngày hôm nay (ví dụ 00:05 AM) — nhưng về mặt updated_at::date thuộc ngày hôm nay. Nếu cron job đã chạy vào 23:55 hôm qua trước khi task đó được mark done, snapshot của hôm qua bị thiếu task đó.

Refresh lại ngày hôm qua (CURRENT_DATE - INTERVAL '1 day') đảm bảo bắt được các late update này. ON CONFLICT DO UPDATE ghi đè giá trị cũ — idempotent hoàn toàn. Chi phí thêm: tính lại 1 ngày thêm, không đáng kể. Lợi ích: chart không hiển thị số sai một ngày sau khi ai đó mark done lúc nửa đêm.

Bài này dùng `CROSS JOIN total_tasks` để thêm scalar vào mọi row. Tại sao không dùng subquery `(SELECT count(*) FROM tasks WHERE sprint_id = 1)` trực tiếp trong SELECT list? Có sự khác biệt về hiệu năng không?

▸

Về kết quả: hai cách cho cùng output. Về hiệu năng và readability có khác biệt quan trọng.

Subquery trong SELECT list là correlated subquery — PostgreSQL có thể evaluate lại mỗi row (dù optimizer thường nhận ra là uncorrelated và hoist ra ngoài). Code khó đọc hơn khi subquery lặp lại trong nhiều column (ví dụ dùng total ở cả remaining_count lẫn ideal_count).

CROSS JOIN total_tasks tường minh hơn: PostgreSQL evaluate total_tasks một lần, materialize kết quả (1 row), join với mọi row. Không có ambiguity về "evaluate lại hay không". Khi dùng cùng scalar trong nhiều biểu thức, CTE + CROSS JOIN tránh lặp code và dễ thay đổi sau này (chỉ sửa CTE một chỗ).

Trong thực tế với query phức tạp nhiều CTE, CROSS JOIN rõ ràng hơn về intent và dễ debug hơn khi cần kiểm tra giá trị intermediate.

⁂

Module tiếp theo: Module 9 — JSONB, full-text search & pgvector

Bài này có giúp bạn hiểu bản chất không?

← Bài trước

MERGE Statement — ETL multi-rule sync trong một câu lệnh

Bài tiếp JSONB, full-text & pgvector

JSONB vs JSON vs TEXT — lưu trữ dữ liệu bán cấu trúc trong PostgreSQL