Presto 0.100 Documentation

7.10. 查询

7.10. 查询

概述

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expr [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition]
[ UNION [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT count ]

from_item 为以下之一

table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
from_item join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

说明

从0或多个表获取数据行

GROUP BY子句

GROUP BY 子句对 SELECT 语句的输出进行分组, 分组中是匹配值的数据行。 Group BY 子句支持任意表达式, 包括指定列名或列序号(从1开始)。

以下查询是等价的。 他们都对 nationkey 列进行分组, 第一个查询使用列序号, 第二个查询使用列名:

SELECT count(*), nationkey FROM customer GROUP BY 2;

SELECT count(*), nationkey FROM customer GROUP BY nationkey;

在查询语句中没有指定列名的情况下, GROUP BY 子句也可以将输出进行分组。 例如,以下查询使用列 mktsegment 进行分组, 统计出 customer 表的行数:

SELECT count(*) FROM customer GROUP BY mktsegment;
 _col0
-------
 29968
 30142
 30189
 29949
 29752
(5 rows)

SELECT 语句中使用 GROUP BY 子句时, 进行分组的列要么是聚会函数, 要么是 GROUP BY 子句中的列。

HAVING子句

HAVING 子句与聚合函数以及 GROUP BY 子句共同使用, 用来控制选择分组。 HAVING 子句去掉不满足条件的分组。 在分组和聚合计算完成后,HAVING 对分组进行过滤。

以下示例查询 customer 表,并进行分组, 查出账户余额大于指定值的记录:

SELECT count(*), mktsegment, nationkey,
       CAST(sum(acctbal) AS bigint) AS totalbal
FROM customer
GROUP BY mktsegment, nationkey
HAVING sum(acctbal) > 5700000
ORDER BY totalbal DESC;
 _col0 | mktsegment | nationkey | totalbal
-------+------------+-----------+----------
  1272 | AUTOMOBILE |        19 |  5856939
  1253 | FURNITURE  |        14 |  5794887
  1248 | FURNITURE  |         9 |  5784628
  1243 | FURNITURE  |        12 |  5757371
  1231 | HOUSEHOLD  |         3 |  5753216
  1251 | MACHINERY  |         2 |  5719140
  1247 | FURNITURE  |         8 |  5701952
(7 rows)

UNION子句

UNION 子句用于将多个查询语句的结果合并为一个结果集:

query UNION [ALL | DISTINCT] query

参数 ALLDISTINCT 控制最终结果集包含哪些行。 如果指定参数 ALL ,则包含全部行,即使行完全相同。 如果指定参数 DISTINCT , 则合并结果集,结果集只有唯一不重复的行。 如果不指定参数,执行时默认使用 DISTINCT

以下示例可能是最简单的 UNION 子句之一。 以下查询将返回值 13 与第二个查询的返回值 42 进行结果集连接:

SELECT 13
UNION
SELECT 42;
 _col0
-------
    13
    42
(2 rows)

多个union从左向右执行, 除非用括号明确指定顺序。

ORDER BY子句

ORDER BY 子句按照一个或多个输出表达式对结果集排序:

ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]

每个表达式由列名或列序号(从1开始)组成。 ORDER BY 子句作为查询的最后一步, 在 GROUP BYHAVING 子句之后。

LIMIT子句

LIMIT 子句限制结果集的行数。 以下示例为查询一个大表, limit子句限制它只输出5行(因为查询没有 ORDER BY , 所以随意返回几行):

SELECT orderdate FROM orders LIMIT 5;
 o_orderdate
-------------
 1996-04-14
 1992-01-15
 1995-02-01
 1995-11-12
 1992-04-26
(5 rows)

TABLESAMPLE

有多种抽样方法:

BERNOULLI

查出的每行记录都源于表样本,使用样本百分比概率。 当使用Bernoulli方法对表进行抽样时, 会扫描表的所有物理块, 并跳过某些行。 (基于样本百分比与运行时随机计算之间的比较)

结果中每行记录的概率都是独立的。 这不会减少从磁盘读取抽样表所需要的时间。 如果对抽样输出做处理, 它可能对整体查询时间有影响。

SYSTEM

这种抽样方法将表划分为逻辑数据段, 并按此粒度进行抽样。 这种抽样方法要么从特定数据段查询全部行, 要么跳过它。 (基于样本百分比与运行时随机计算之间的比较)

系统抽样选取哪些行,取决于使用哪种连接器。 例如,使用Hive, 它取决于HDFS上的数据是怎样存储的。 这种方法无法保证独立抽样概率。

Note

这两种方法都不能确定返回行数的范围。

示例:

SELECT *
FROM users TABLESAMPLE BERNOULLI (50);

SELECT *
FROM users TABLESAMPLE SYSTEM (75);

通过join进行抽样:

SELECT o.*, i.*
FROM orders o TABLESAMPLE SYSTEM (10)
JOIN lineitem i TABLESAMPLE BERNOULLI (40)
  ON o.orderkey = i.orderkey;

UNNEST

UNNEST 用于展开 数组类型map类型 的子查询。 数组展开为单列,map展开为双列(键,值)。 UNNEST 可以使用多个参数,它们展开为多列, 行数与最大的基础参数一样(其他列填空)。 UNNEST 通常与 JOIN 一起使用 也可以引用join左侧的关系列。

使用单列:

SELECT student, score
FROM tests
CROSS JOIN UNNEST(scores) AS t (score);

使用多列:

SELECT numbers, animals, n, a
FROM (
  VALUES
    (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']),
    (ARRAY[7, 8, 9], ARRAY['cow', 'pig'])
) AS x (numbers, animals)
CROSS JOIN UNNEST(numbers, animals) AS t (n, a);
  numbers  |     animals      |  n   |  a
-----------+------------------+------+------
 [2, 5]    | [dog, cat, bird] |    2 | dog
 [2, 5]    | [dog, cat, bird] |    5 | cat
 [2, 5]    | [dog, cat, bird] | NULL | bird
 [7, 8, 9] | [cow, pig]       |    7 | cow
 [7, 8, 9] | [cow, pig]       |    8 | pig
 [7, 8, 9] | [cow, pig]       |    9 | NULL
(6 rows)

WITH ORDINALITY clause:

SELECT numbers, n, a
FROM (
  VALUES
    (ARRAY[2, 5]),
    (ARRAY[7, 8, 9])
) AS x (numbers)
CROSS JOIN UNNEST(numbers) WITH ORDINALITY AS t (n, a);
  numbers  | n | a
-----------+---+---
 [2, 5]    | 2 | 1
 [2, 5]    | 5 | 2
 [7, 8, 9] | 7 | 1
 [7, 8, 9] | 8 | 2
 [7, 8, 9] | 9 | 3
(5 rows)