Presto实战

144

｜

第

8

章

FROM tpch.tiny.customer

ORDER BY id, total_acctbal;

mktsegment | total_acctbal | id

------------+---------------+----

FURNITURE | 1265282.8 | 0

HOUSEHOLD | 1279340.66 | 0

MACHINERY | 1296958.61 | 0

AUTOMOBILE | 1395695.72 | 0

BUILDING | 1444587.8 | 0

NULL | 6681865.59 | 1

(6 rows)

8.17

　

WITH

子句

WITH

子句用于在单个查询中定义一个内联视图。这通常可以使查询更可读，因为查询可能

需要多次包含相同的嵌套查询。

在下面这个查询中，我们查找有哪些细分市场的账户总余额大于细分市场的平均数：

SELECT mktsegment,

total_per_mktsegment,

average

FROM

(

SELECT mktsegment,

round(sum(acctbal)) AS total_per_mktsegment

FROM tpch.tiny.customer

GROUP BY 1

),

(

SELECT round(avg(total_per_mktsegment)) AS average

FROM

(

SELECT mktsegment, ...