WITH 語(yǔ)句經(jīng)常稱為公用表表達(dá)式（簡(jiǎn)稱 CTE）使用這個(gè)方法來(lái)簡(jiǎn)化子查詢，將公用子查詢的部分使用WITH包裹命名別名，它會(huì)建立一個(gè)臨時(shí)表，方便后面復(fù)用

SELECT channel, AVG(events) AS average_events
FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2) sub
GROUP BY channel
ORDER BY 2 DESC;

可改寫(xiě)為

WITH events AS (
SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2)

SELECT channel, AVG(events) AS average_events
FROM events
GROUP BY channel
ORDER BY 2 DESC;