Truy vấn PostgreSQL nâng cao

-- USING — JOIN trên cột cùng tên
SELECT u.id, u.name, o.total
FROM users u JOIN orders o USING (id);    -- tự match user.id = orders.id
                                          -- và chỉ trả 1 cột id

-- NATURAL JOIN — tự JOIN trên tất cả cột cùng tên (tránh dùng — nguy hiểm)
SELECT * FROM a NATURAL JOIN b;

-- LATERAL cho phép subquery bên phải REFERENCE bảng bên trái
-- Ví dụ: top 3 đơn mới nhất của mỗi user
SELECT u.id, u.name, o.*
FROM users u
LEFT JOIN LATERAL (
    SELECT *
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o ON true;

WITH high_value AS (
    SELECT * FROM orders WHERE total > 10000000
)
SELECT u.name, COUNT(*)
FROM high_value h JOIN users u ON u.id = h.user_id
GROUP BY u.name;

-- MATERIALIZED — bắt CTE chạy 1 lần, cache (cũ default)
WITH cte AS MATERIALIZED (
    SELECT * FROM big_table WHERE x > 100
)
SELECT ...;

-- NOT MATERIALIZED — inline (default 12+, planner tối ưu tốt hơn)
WITH cte AS NOT MATERIALIZED (
    SELECT * FROM big_table WHERE x > 100
)
SELECT ...;

-- Build cây tổ chức từ root
WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS lvl, name::TEXT AS path
    FROM employees WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, o.lvl + 1, o.path || ' > ' || e.name
    FROM employees e
    JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY path;

-- IGNORE NULLS — bỏ qua giá trị NULL trong window frame
-- Áp dụng cho: lead(), lag(), first_value(), last_value(), nth_value()
SELECT sensor_id, ts, reading,
    last_value(reading) IGNORE NULLS OVER (
        PARTITION BY sensor_id ORDER BY ts
    ) AS last_known_reading
FROM sensor_data;

-- RESPECT NULLS là default — trả NULL nếu gặp NULL
-- So sánh: IGNORE NULLS vs RESPECT NULLS
SELECT sensor_id, ts, reading,
    last_value(reading) RESPECT NULLS OVER (
        PARTITION BY sensor_id ORDER BY ts
    ) AS resp_nulls,
    last_value(reading) IGNORE NULLS OVER (
        PARTITION BY sensor_id ORDER BY ts
    ) AS ignore_nulls
FROM sensor_data;

-- Top N per group
SELECT id, user_id, total
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) rn
    FROM orders
) t WHERE rn <= 3;

-- Running total
SELECT order_date, total,
    SUM(total) OVER (ORDER BY order_date) AS running_total,
    AVG(total) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM orders;

-- Compare with previous row
SELECT order_date, total,
    LAG(total) OVER (ORDER BY order_date) AS prev_total,
    total - LAG(total) OVER (ORDER BY order_date) AS delta
FROM orders;

-- Đơn hàng mới nhất của mỗi user — viết ngắn hơn ROW_NUMBER
SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at DESC;

-- GROUPING SETS, ROLLUP, CUBE — giống SQL Server
SELECT city, status, COUNT(*)
FROM orders o JOIN users u ON u.id = o.user_id
GROUP BY GROUPING SETS ((city, status), (city), ());

-- FILTER — aggregate có điều kiện
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid_count,
    SUM(total) FILTER (WHERE status = 'paid') AS paid_revenue
FROM orders;

-- Containment, fastest với GIN index
SELECT * FROM events WHERE payload @> '{"type": "click"}';

-- Path access
SELECT * FROM events WHERE payload->>'type' = 'click';

-- Array contains
SELECT * FROM posts WHERE tags @> ARRAY['vue'];

-- JSONB path query
SELECT * FROM events
WHERE payload @? '$.tags[*] ? (@ == "promo")';

-- Add tsvector column
ALTER TABLE articles
    ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;

-- GIN index
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Query
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & 18');

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

-- ROLLBACK nếu lỗi
BEGIN;
... 
ROLLBACK;

-- Savepoint
BEGIN;
INSERT ...;
SAVEPOINT sp1;
UPDATE ...;
ROLLBACK TO sp1;     -- chỉ rollback từ sp1
COMMIT;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
...
COMMIT;

-- Bật log
SET log_lock_waits = on;
SET deadlock_timeout = '1s';

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1;

-- Memoize là cache node trong Nested Loop: nhớ kết quả subquery theo key
-- Postgres 19 hiển thị thêm thông tin ước lượng:
EXPLAIN (ANALYZE)
SELECT * FROM orders o
JOIN LATERAL (
    SELECT * FROM users u WHERE u.id = o.user_id
) u ON true;

->  Memoize  (cost=1.25..123.50 rows=10 width=100)
      Cache Key: o.user_id
      Estimates: capacity=2 distinct keys=2 lookups=1000 hit percent=99.80%
      Hits: 998  Misses: 2  Evictions: 0

-- Manual
VACUUM ANALYZE orders;
VACUUM FULL orders;          -- rebuild bảng, lock dài — chỉ làm khi rất fragment

-- Statistics riêng
ANALYZE orders;

-- Auto-vacuum config (postgresql.conf)
autovacuum = on
autovacuum_naptime = 1min

-- 1. Định nghĩa graph từ bảng có sẵn
CREATE PROPERTY GRAPH social_graph
  VERTEX TABLES (users LABEL person PROPERTIES (id, name, city))
  EDGE TABLES (
    follows
      SOURCE KEY (follower_id) REFERENCES users (id)
      DESTINATION KEY (followed_id) REFERENCES users (id)
      LABEL follows
  );

-- 2. Query bằng GRAPH_TABLE
SELECT * FROM GRAPH_TABLE (social_graph
    MATCH (a IS person WHERE a.name = 'Alice')
          -[IS follows]->(b IS person)
          -[IS follows]->(c IS person)
    COLUMNS (b.name AS friend, c.name AS friend_of_friend)
);

-- GRAPH_TABLE compose với JOIN, aggregate, CTE như bảng thường
WITH fof AS (
    SELECT * FROM GRAPH_TABLE (social_graph
        MATCH (a IS person WHERE a.name = 'Alice')
              -[IS follows]->(b IS person)
              -[IS follows]->(c IS person)
        COLUMNS (c.id AS fof_id, c.name AS fof_name)
    )
)
SELECT fof_name, COUNT(*) AS order_count
FROM fof JOIN orders o ON o.user_id = fof_id
GROUP BY fof_name
ORDER BY order_count DESC;

-- Tạo graph từ JOIN
CREATE PROPERTY GRAPH user_order_graph
  VERTEX TABLES (
    users LABEL user PROPERTIES (id, name),
    orders LABEL "order" PROPERTIES (id, total, status)
  )
  EDGE TABLES (
    -- Edge implicit từ FK
    orders AS placed
      SOURCE KEY (user_id) REFERENCES users (id)
      LABEL placed_by
  );