Day46 - 2018-05-22
2018-05-22 本文已影响0人
谁共我醉明月
WITH 语句经常称为公用表表达式（简称 CTE）使用这个方法来简化子查询，将公用子查询的部分使用WITH包裹命名别名，它会建立一个临时表，方便后面复用
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;
可改写为
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;
