Trang này được dịch từ tiếng Anh với sự hỗ trợ của AI. Nếu hai bản có chỗ khác nhau, hãy lấy phiên bản tiếng Anh làm chuẩn.
WITH (CTE)
Định nghĩa một biểu thức bảng chung (CTE) để dùng trong câu lệnh SELECT, INSERT, UPDATE hoặc DELETE theo sau. CTE hoạt động như các tập kết quả tạm thời có tên, chỉ tồn tại trong suốt thời gian thực hiện truy vấn.
Xem thêm:
[SELECT] [INSERT] [UPDATE] [DELETE]
Cú pháp
withClause::=
cteDefinition::=
WITH [RECURSIVE] cte_name [(column_name, ...)] AS (
select_statement
)
[, cte_name [(column_name, ...)] AS (
select_statement
)]
SELECT ... FROM cte_name ...;
Đối số
RECURSIVE
Từ khóa không bắt buộc cho phép CTE tham chiếu đến chính nó, giúp thực hiện các truy vấn đệ quy như duyệt dữ liệu phân cấp.
cte_name
Tên được gán cho CTE. Có thể tham chiếu tên này trong truy vấn chính như thể đó là một bảng.
column_name
Không bắt buộc. Tên cột cho tập kết quả của CTE. Nếu không được chỉ định, tên cột được lấy từ câu lệnh SELECT.
select_statement
Truy vấn định nghĩa tập kết quả của CTE.
Giá trị trả về
CTE không trả về giá trị trực tiếp. Chúng định nghĩa các tập kết quả tạm thời có thể được tham chiếu trong truy vấn chính.
Ví dụ
CTE cơ bản để tổ chức truy vấn phức tạp
CTE có thể đơn giản hóa truy vấn phức tạp bằng cách chia chúng thành các thành phần logic có tên.
-- Calculate moving average of commit activity
WITH weekly_totals AS (
SELECT
week,
SUM(json_each.value) as commits_this_week
FROM github.repos.stats_commit_activity, JSON_EACH(days)
WHERE owner = 'stackql'
AND repo = 'stackql'
GROUP BY week
)
SELECT
week,
commits_this_week,
ROUND(AVG(commits_this_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg,
SUM(commits_this_week) OVER (ORDER BY week) as cumulative_commits
FROM weekly_totals
ORDER BY week;
Kết hợp dữ liệu từ nhiều nguồn với UNION ALL
CTE hữu ích để kết hợp dữ liệu từ nhiều nguồn trước khi thực hiện phân tích.
-- Rank total contributions across multiple repos
WITH all_contributors AS (
SELECT login, contributions, html_url
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql'
UNION ALL
SELECT login, contributions, html_url
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql-deploy'
UNION ALL
SELECT login, contributions, html_url
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
)
SELECT
DENSE_RANK() OVER (ORDER BY SUM(contributions) DESC) as rank,
login,
SUM(contributions) as total_contributions,
MAX(html_url) as html_url
FROM all_contributors
GROUP BY login
ORDER BY total_contributions DESC;
Sử dụng CTE với hàm cửa sổ để xếp hạng
-- Rank contributors within each repo using CTE and window functions
WITH repo_contributors AS (
SELECT
repo,
login,
contributions,
RANK() OVER (PARTITION BY repo ORDER BY contributions DESC) as rank_in_repo,
SUM(contributions) OVER (PARTITION BY repo) as repo_total_contributions
FROM (
SELECT 'stackql' as repo, login, contributions
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql'
UNION ALL
SELECT 'stackql-provider-registry' as repo, login, contributions
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
) t
)
SELECT
repo,
login,
contributions,
rank_in_repo
FROM repo_contributors
ORDER BY repo, rank_in_repo;
Nhiều CTE trong một truy vấn
Bạn có thể định nghĩa nhiều CTE, phân cách bằng dấu phẩy.
-- Analyze issues and releases together
WITH open_issues AS (
SELECT number, title, created_at
FROM github.issues.issues
WHERE owner = 'stackql'
AND repo = 'stackql'
AND state = 'open'
),
recent_releases AS (
SELECT tag_name, published_at
FROM github.repos.releases
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY published_at DESC
LIMIT 5
)
SELECT
'Open Issues' as category,
COUNT(*) as count
FROM open_issues
UNION ALL
SELECT
'Recent Releases' as category,
COUNT(*) as count
FROM recent_releases;
CTE đệ quy
CTE đệ quy cho phép một CTE tham chiếu đến chính nó, giúp thực hiện các truy vấn duyệt cấu trúc dữ liệu phân cấp hoặc dạng đồ thị.
WITH RECURSIVE cte_name (columns) AS (
-- Base case (anchor member)
SELECT ...
UNION ALL
-- Recursive case (references cte_name)
SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;
CTE đệ quy đặc biệt hữu ích cho dữ liệu phân cấp như sơ đồ tổ chức, hệ thống tệp hoặc cây phụ thuộc. Luôn đưa một điều kiện dừng vào trường hợp đệ quy để tránh vòng lặp vô hạn.
Phương pháp hay nhất
- Dùng CTE để dễ đọc hơn: Chia truy vấn phức tạp thành các thành phần logic có tên.
- Kết hợp với hàm cửa sổ: CTE hoạt động tốt với hàm cửa sổ cho các phân tích nâng cao.
- Dùng nhiều CTE nối tiếp nhau: Tham chiếu các CTE trước trong các CTE sau để từng bước tạo nên logic phức tạp.
- Cân nhắc hiệu năng: Mặc dù CTE giúp truy vấn dễ đọc hơn, các CTE rất lớn có thể ảnh hưởng đến hiệu năng. Hãy kiểm thử với khối lượng dữ liệu thực tế của bạn.
Để biết thêm thông tin, hãy xem https://sqlite.org/lang_with.html.