从补丁 198 开始，Amazon Redshift 将不再支持创建新的 Python UDF。现有的 Python UDF 将继续正常运行至 2026 年 6 月 30 日。有关更多信息，请参阅[博客文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。 # SELECT 返回表、视图和用户定义的函数中的行。 **注意** 单个 SQL 语句的最大大小为 16MB。 ## 语法 ``` [ WITH with_subquery [, ...] ] SELECT [ TOP number | [ ALL | DISTINCT ] * | expression [ AS output_name ] [, ...] ] [ EXCLUDE column_list ] [ FROM table_reference [, ...] ] [ WHERE condition ] [ [ START WITH expression ] CONNECT BY expression ] [ GROUP BY ALL | expression [, ...] ] [ HAVING condition ] [ QUALIFY condition ] [ { UNION | ALL | INTERSECT | EXCEPT | MINUS } query ] [ ORDER BY expression [ ASC | DESC ] ] [ LIMIT { number | ALL } ] [ OFFSET start ] ``` **Topics** + [语法](#r_SELECT_synopsis-synopsis) + [WITH 子句](r_WITH_clause.md) + [SELECT 列表](r_SELECT_list.md) + [EXCLUDE column\$1list](r_EXCLUDE_list.md) + [FROM 子句](r_FROM_clause30.md) + [WHERE 子句](r_WHERE_clause.md) + [GROUP BY 子句](r_GROUP_BY_clause.md) + [HAVING 子句](r_HAVING_clause.md) + [QUALIFY 子句](r_QUALIFY_clause.md) + [UNION、INTERSECT 和 EXCEPT](r_UNION.md) + [ORDER BY 子句](r_ORDER_BY_clause.md) + [CONNECT BY 子句](r_CONNECT_BY_clause.md) + [子查询示例](r_Subquery_examples.md) + [关联的子查询](r_correlated_subqueries.md) # WITH 子句 WITH 子句是一个可选子句，该子句在查询中位于 SELECT 列表之前。WITH 子句定义一个或多个 *common\$1table\$1expressions*。每个通用表表达式 (CTE) 均定义一个临时表，它与视图定义类似。您可以在 FROM 子句中引用这些临时表。它们仅在它们所属的查询运行时使用。WITH 子句中的每个子 CTE 均指定一个表名、一个可选的列名称列表以及一个计算结果为表的查询表达式（SELECT 语句）。当您在定义临时表的同一查询表达式的 FROM 子句中引用临时表名时，CTE 是递归的。 WITH 子句子查询是定义可在单个查询的执行过程中使用的表的有效方式。在所有情况下，在 SELECT 语句的主体中使用子查询可获得相同的结果，不过 WITH 子句子查询可能在编写和阅读方面更加简单。如果可能，会将已引用多次的 WITH 子句子查询优化为常用子表达式；即，可以计算 WITH 子查询一次并重用其结果。（请注意，常用子表达式不只是限于 WITH 子句中定义的子表达式。） ## 语法 ``` [ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ] ``` 其中 *common\$1table\$1expression* 可以是非递归的，也可以是递归的。以下是非递归形式： ``` CTE_table_name [ ( column_name [, ...] ) ] AS ( query ) ``` 以下是 *common\$1table\$1expression* 的递归形式： ``` CTE_table_name (column_name [, ...] ) AS ( recursive_query ) ``` ## 参数 RECURSIVE 将查询标识为递归 CTE 的关键词。如果 WITH 子句中定义的任何 *common\$1table\$1expression* 是递归的，则需要此关键词。即使 WITH 子句包含多个递归 CTE，您也只能紧跟 WITH 关键词之后指定一次 RECURSIVE 关键词。通常，递归 CTE 是一个包含两个部分的 UNION ALL 子查询。 *common\$1table\$1expression* 定义一个您可以在 [FROM 子句](r_FROM_clause30.md) 中引用并且仅在执行其所属的查询期间使用的临时表。 *CTE\$1table\$1name* 临时表的唯一名称，该临时表用于定义 WITH 子句子查询的结果。不能在单个 WITH 子句中使用重复名称。必须为每个子查询提供一个可在 [FROM 子句](r_FROM_clause30.md)中引用的表名。 *column\$1name* WITH 子句子查询的输出列名称的列表（用逗号分隔）。指定的列名数目必须等于或小于子查询定义的列数。对于非递归的 CTE，*column\$1name* 子句是可选的。对于递归的 CTE，*column\$1name* 列表是必需的。 *query* Amazon Redshift 支持的任何 SELECT 查询。请参阅 [SELECT](r_SELECT_synopsis.md)。 *recursive\$1query* 由两个 SELECT 子查询组成的 UNION ALL 查询： + 第一个 SELECT 子查询没有对同一个 *CTE\$1table\$1name* 进行递归引用。它返回一个结果集，该结果集是递归的初始种子。此部分称为初始成员或种子成员。 + 第二个 SELECT 子查询在其 FROM 子句中引用同一个 *CTE\$1table\$1name*。它被称为递归成员。*recursive\$1query* 包含一个 WHERE 条件来结束 *recursive\$1query*。 ## 使用说明 可在以下 SQL 语句中使用 WITH 子句： + SELECT + SELECT INTO + CREATE TABLE AS + CREATE VIEW + DECLARE + EXPLAIN + INSERT INTO...SELECT + PREPARE + UPDATE（在 WHERE 子句子查询中。您无法在子查询中定义递归 CTE。递归 CTE 必须位于 UPDATE 子句之前。） + DELETE 如果包含 WITH 子句的查询的 FROM 子句未引用 WITH 子句所定义的任何表，则将忽略 WITH 子句，并且查询将正常执行。 WITH 子句子查询所定义的表只能在 WITH 子句开始的 SELECT 查询范围内引用。例如，可以在 SELECT 列表的子查询的 FROM 子句、WHERE 子句或 HAVING 子句中引用这样的表。不能在子查询中使用 WITH 子句，也不能在主查询或其他子查询的 FROM 子句中引用其表。此查询模式会为 WITH 子句表生成 `relation table_name doesn't exist` 形式的错误消息。 不能在 WITH 子句子查询中指定另一个 WITH 子句。 不能对 WITH 子句子查询定义的表进行前向引用。例如，以下查询返回一个错误，因为在表 W1 的定义中对表 W2 进行了前向引用： ``` with w1 as (select * from w2), w2 as (select * from w1) select * from sales; ERROR: relation "w2" does not exist ``` WITH 子句子查询不能包含 SELECT INTO 语句；不过，您可以在 SELECT INTO 语句中使用 WITH 子句。 ## 递归公用表表达式 递归*公用表表达式 (CTE)* 是一个引用自身的 CTE。递归 CTE 在查询分层数据（如显示员工和经理之间的报告关系的组织结构图）时非常有用。请参阅 [示例：递归 CTE](#r_WITH_clause-recursive-cte-example)。 另一个常见用途是多级物料清单，当产品由多个组件组成并且每个组件本身也包含其它组件或子装配件时。 通过在递归查询的第二个 SELECT 子查询中包含 WHERE 子句来确保限制递归的深度。有关示例，请参阅 [示例：递归 CTE](#r_WITH_clause-recursive-cte-example)。否则，可能会出现类似于以下内容的错误： + `Recursive CTE out of working buffers.` + `Exceeded recursive CTE max rows limit, please add correct CTE termination predicates or change the max_recursion_rows parameter.` **注意** `max_recursion_rows` 参数用于设置递归 CTE 可以返回的最大行数，以防止无限递归循环。我们建议更改该值时不要超过默认值。这样可以防止查询中的无限递归问题占用集群的过多空间。 您可以指定递归 CTE 结果的排序顺序和限制。您可以在递归 CTE 的最终结果中包含分组依据和不同选项。 不能在子查询中指定 WITH RECURSIVE 子句。*recursive\$1query* 成员不能包含排序依据或限制子句。 ## 示例 以下示例说明了包含 WITH 语句的查询的最简单示例。在名为 VENUECOPY 的 WITH 查询中，选择 VENUE 表中的所有行。主查询又选择 VENUECOPY 中的所有行。VENUECOPY 表仅在此查询的持续时间内存在。 ``` with venuecopy as (select * from venue) select * from venuecopy order by 1 limit 10; ``` ``` venueid | venuename | venuecity | venuestate | venueseats ---------+----------------------------+-----------------+------------+------------ 1 | Toyota Park | Bridgeview | IL | 0 2 | Columbus Crew Stadium | Columbus | OH | 0 3 | RFK Stadium | Washington | DC | 0 4 | CommunityAmerica Ballpark | Kansas City | KS | 0 5 | Gillette Stadium | Foxborough | MA | 68756 6 | New York Giants Stadium | East Rutherford | NJ | 80242 7 | BMO Field | Toronto | ON | 0 8 | The Home Depot Center | Carson | CA | 0 9 | Dick's Sporting Goods Park | Commerce City | CO | 0 v 10 | Pizza Hut Park | Frisco | TX | 0 (10 rows) ``` 以下示例显示一个 WITH 子句，该子句生成两个分别名为 VENUE\$1SALES 和 TOP\$1VENUES 的表。第二个 WITH 查询表从第一个表中进行选择。而主查询块的 WHERE 子句又包含一个约束 TOP\$1VENUES 表的子查询。 ``` with venue_sales as (select venuename, venuecity, sum(pricepaid) as venuename_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid group by venuename, venuecity), top_venues as (select venuename from venue_sales where venuename_sales > 800000) select venuename, venuecity, venuestate, sum(qtysold) as venue_qty, sum(pricepaid) as venue_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid and venuename in(select venuename from top_venues) group by venuename, venuecity, venuestate order by venuename; ``` ``` venuename | venuecity | venuestate | venue_qty | venue_sales ------------------------+---------------+------------+-----------+------------- August Wilson Theatre | New York City | NY | 3187 | 1032156.00 Biltmore Theatre | New York City | NY | 2629 | 828981.00 Charles Playhouse | Boston | MA | 2502 | 857031.00 Ethel Barrymore Theatre | New York City | NY | 2828 | 891172.00 Eugene O'Neill Theatre | New York City | NY | 2488 | 828950.00 Greek Theatre | Los Angeles | CA | 2445 | 838918.00 Helen Hayes Theatre | New York City | NY | 2948 | 978765.00 Hilton Theatre | New York City | NY | 2999 | 885686.00 Imperial Theatre | New York City | NY | 2702 | 877993.00 Lunt-Fontanne Theatre | New York City | NY | 3326 | 1115182.00 Majestic Theatre | New York City | NY | 2549 | 894275.00 Nederlander Theatre | New York City | NY | 2934 | 936312.00 Pasadena Playhouse | Pasadena | CA | 2739 | 820435.00 Winter Garden Theatre | New York City | NY | 2838 | 939257.00 (14 rows) ``` 以下两个示例演示基于 WITH 子句子查询的表引用范围的规则。第一个查询运行，但第二个查询失败，并出现意料中的错误。在第一个查询中，主查询的 SELECT 列表中包含 WITH 子句子查询。SELECT 列表中的子查询的 FROM 子句中将引用 WITH 子句定义的表 (HOLIDAYS)： ``` select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join date on sales.dateid=date.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; caldate | daysales | dec25sales -----------+----------+------------ 2008-12-25 | 70402.00 | 70402.00 2008-12-31 | 12678.00 | 70402.00 (2 rows) ``` 第二个查询失败，因为它尝试在主查询和 SELECT 列表子查询中引用 HOLIDAYS 表。主查询引用超出范围。 ``` select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join holidays on sales.dateid=holidays.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; ERROR: relation "holidays" does not exist ``` ## 示例：递归 CTE 以下是递归 CTE 的示例，该示例返回直接或间接向 John 报告的员工。递归查询包含一个 WHERE 子句，用于将递归深度限制为小于 4 个级别。 ``` --create and populate the sample table create table employee ( id int, name varchar (20), manager_id int ); insert into employee(id, name, manager_id) values (100, 'Carlos', null), (101, 'John', 100), (102, 'Jorge', 101), (103, 'Kwaku', 101), (110, 'Liu', 101), (106, 'Mateo', 102), (110, 'Nikki', 103), (104, 'Paulo', 103), (105, 'Richard', 103), (120, 'Saanvi', 104), (200, 'Shirley', 104), (201, 'Sofía', 102), (205, 'Zhang', 104); --run the recursive query with recursive john_org(id, name, manager_id, level) as ( select id, name, manager_id, 1 as level from employee where name = 'John' union all select e.id, e.name, e.manager_id, level + 1 as next_level from employee e, john_org j where e.manager_id = j.id and level < 4 ) select distinct id, name, manager_id from john_org order by manager_id; ``` 以下是查询的结果。 ``` id name manager_id ------+-----------+-------------- 101 John 100 102 Jorge 101 103 Kwaku 101 110 Liu 101 201 Sofía 102 106 Mateo 102 110 Nikki 103 104 Paulo 103 105 Richard 103 120 Saanvi 104 200 Shirley 104 205 Zhang 104 ``` 以下是 John 所在部门的组织结构图。 ![\[John 所在部门的组织结构图。\]](http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/images/org-chart.png) # SELECT 列表 **Topics** + [语法](#r_SELECT_list-synopsis) + [参数](#r_SELECT_list-parameters) + [使用说明](#r_SELECT_list_usage_notes) + [示例](#r_SELECT_list-examples) SELECT 列表指定希望查询返回的列、函数和表达式。列表表示查询的输出。 有关 SQL 函数的更多信息，请参阅 [SQL 函数参考](c_SQL_functions.md)。有关表达式的更多信息，请参阅[条件表达式](c_conditional_expressions.md)。 ## 语法 ``` SELECT [ TOP number ] [ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...] ``` ## 参数 TOP *number* TOP 将正整数用作其参数，用于定义返回到客户端的行数。使用 TOP 子句的行为与使用 LIMIT 子句的行为相同。返回的行数是固定的，但行集不固定。要返回一致的行集，请将 TOP 或 LIMIT 与 ORDER BY 子句结合使用。 ALL 一个冗余关键字，定义未指定 DISTINCT 的情况下的默认行为。`SELECT ALL *` 与 `SELECT *` 的含义相同（选择所有列的所有行并保留重复条目）。 DISTINCT 一个选项，用于根据一个或多个列中的匹配值消除结果集中的重复行。 如果您的应用程序允许无效的外键或主键，则会导致查询返回错误的结果。例如，如果主键列不包含所有唯一值，则 SELECT DISTINCT 查询可能会返回重复的行。有关更多信息，请参阅[定义表约束](https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html)。 \$1（星号） 返回表的完整内容（所有列和所有行）。 *expression* 由查询引用的表中存在的一个或多个列构成的表达式。表达式可包含 SQL 函数。例如： ``` avg(datediff(day, listtime, saletime)) ``` AS *column\$1alias* 在最终结果集中使用的列的临时名称。AS 关键字是可选的。例如： ``` avg(datediff(day, listtime, saletime)) as avgwait ``` 如果您没有为不是简单列名的表达式指定别名，则结果集将对该列应用默认名称。 在目标列表中定义别名后，它将立即被识别。您可以在其他表达式中使用在同一目标列表中晚于该别名定义的某个别名。以下示例对此进行了说明。 ``` select clicks / impressions as probability, round(100 * probability, 1) as percentage from raw_data; ``` 横向别名引用的好处是，当在同一目标列表中构建更复杂的表达式时，不需要重复已指定别名的表达式。当 Amazon Redshift 分析这种类型的引用时，它只会内联之前定义的别名。如果在 `FROM` 子句中定义了一个与之前指定了别名的表达式同名的列，`FROM` 子句中定义的列将优先。例如，在上述查询中，如果表 raw\$1data 中有一个名为“probability”的列，那么目标列表中的第二个表达式中的“probability”引用该列而不是别名“probability”。 ## 使用说明 TOP 是一个 SQL 扩展；它提供 LIMIT 行为的替代。不能在同一个查询中使用 TOP 和 LIMIT。 ## 示例 以下示例从 SALES 表中返回 10 行。尽管查询使用 TOP 子句，但它仍然返回一组不可预测的行，因为没有指定 ORDERBY 子句， ``` select top 10 * from sales; ``` 以下查询具有相同的功能，但使用的是 LIMIT 子句而非 TOP 子句： ``` select * from sales limit 10; ``` 以下示例使用 TOP 子句返回 SALES 表的前 10 行，按 QTYSOLD 列降序排序。 ``` select top 10 qtysold, sellerid from sales order by qtysold desc, sellerid; qtysold | sellerid --------+---------- 8 | 518 8 | 520 8 | 574 8 | 718 8 | 868 8 | 2663 8 | 3396 8 | 3726 8 | 5250 8 | 6216 (10 rows) ``` 以下示例返回 SALES 表中的前两个 QTYSOLD 和 SELLERID 值（按 QTYSOLD 列排序）： ``` select top 2 qtysold, sellerid from sales order by qtysold desc, sellerid; qtysold | sellerid --------+---------- 8 | 518 8 | 520 (2 rows) ``` 以下示例显示 CATEGORY 表中不同类别组的列表： ``` select distinct catgroup from category order by 1; catgroup ---------- Concerts Shows Sports (3 rows) --the same query, run without distinct select catgroup from category order by 1; catgroup ---------- Concerts Concerts Concerts Shows Shows Shows Sports Sports Sports Sports Sports (11 rows) ``` 以下示例返回 2008 年 12 月的一组不同的周编号。如果没有 DISTINCT 子句，该语句将返回 31 行，或对于月份的每一天返回 1 行。 ``` select distinct week, month, year from date where month='DEC' and year=2008 order by 1, 2, 3; week | month | year -----+-------+------ 49 | DEC | 2008 50 | DEC | 2008 51 | DEC | 2008 52 | DEC | 2008 53 | DEC | 2008 (5 rows) ``` # EXCLUDE column\$1list EXCLUDE column\$1list 对从查询结果中排除的列进行命名。当只需从*宽* 表（包含许多列的表）中排除一部分列时，使用 EXCLUDE 选项会很有用。 **Topics** + [语法](#r_EXCLUDE_list-synopsis) + [参数](#r_EXCLUDE_list-parameters) + [示例](#r_EXCLUDE_list-examples) ## 语法 ``` EXCLUDE column_list ``` ## 参数 *column\$1list* 查询引用的表中存在的一个或多个列名称的逗号分隔列表。*column\$1list* 可以选择用括号括起来。列名称的排除列表中仅支持列名称，而不支持表达式（例如 `upper(col1)`）或星号（\$1）。 ``` column-name, ... | ( column-name, ... ) ``` 例如： ``` SELECT * EXCLUDE col1, col2 FROM tablea; ``` ``` SELECT * EXCLUDE (col1, col2) FROM tablea; ``` ## 示例 下面的示例使用包含以下各列的 SALES 表：salesid、listid、sellerid、buyerid、eventid、dateid、qtysold、pricepaid、commission 和 saletime。有关 SALES 表的更多信息，请参阅[示例数据库](c_sampledb.md)。 以下示例返回 SALES 表中的行，但不包括 SALETIME 列。 ``` SELECT * EXCLUDE saletime FROM sales; salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission --------+---------+----------+---------+---------+---------+----------+------------+----------- 150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2 8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75 46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3 ... ``` 以下示例返回 SALES 表中的行，但不包括 QTYSOLD 和 SALETIME 列。 ``` SELECT * EXCLUDE (qtysold, saletime) FROM sales; salesid | listid | sellerid | buyerid | eventid | dateid | pricepaid | commission --------+---------+----------+---------+---------+---------+------------+----------- 150314 | 173969 | 48680 | 816 | 8762 | 1827 | 688 | 103.2 8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 525 | 78.75 46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 482 | 72.3 ... ``` 以下示例创建了一个视图，该视图返回 SALES 表中的行，但不包括 SALETIME 列。 ``` CREATE VIEW sales_view AS SELECT * EXCLUDE saletime FROM sales; SELECT * FROM sales_view; salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission --------+---------+----------+---------+---------+---------+----------+------------+----------- 150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2 8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75 46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3 ... ``` 以下示例仅选择未排除到临时表中的列。 ``` SELECT * EXCLUDE saletime INTO TEMP temp_sales FROM sales; SELECT * FROM temp_sales; salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission --------+---------+----------+---------+---------+---------+----------+------------+----------- 150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2 8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75 46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3 ... ``` # FROM 子句 查询中的 FROM 子句列出从中选择数据的表引用（表、视图和子查询）。如果列出多个表引用，则必须在 FROM 子句或 WHERE 子句中使用适当的语法来联接表。如果未指定联接条件，则系统将查询作为交叉联接（笛卡尔乘积）进行处理。 **Topics** + [语法](#r_FROM_clause30-synopsis) + [参数](#r_FROM_clause30-parameters) + [使用说明](#r_FROM_clause_usage_notes) + [PIVOT 和 UNPIVOT 示例](r_FROM_clause-pivot-unpivot-examples.md) + [JOIN 示例](r_Join_examples.md) + [UNNEST 示例](r_FROM_clause-unnest-examples.md) ## 语法 ``` FROM table_reference [, ...] ``` 其中，*table\$1reference* 是下列项之一： ``` with_subquery_table_name [ table_alias ] table_name [ * ] [ table_alias ] ( subquery ) [ table_alias ] table_reference [ NATURAL ] join_type table_reference [ ON join_condition | USING ( join_column [, ...] ) ] table_reference join_type super_expression [ ON join_condition ] table_reference PIVOT ( aggregate(expr) [ [ AS ] aggregate_alias ] FOR column_name IN ( expression [ AS ] in_alias [, ...] ) ) [ table_alias ] table_reference UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ] ( value_column_name FOR name_column_name IN ( column_reference [ [ AS ] in_alias ] [, ...] ) ) [ table_alias ] UNPIVOT expression AS value_alias [ AT attribute_alias ] ( super_expression.attribute_name ) AS value_alias [ AT index_alias ] UNNEST ( column_reference ) [AS] table_alias ( unnested_column_name ) UNNEST ( column_reference ) WITH OFFSET [AS] table_alias ( unnested_column_name, [offset_column_name] ) ``` 可选的 *table\$1alias* 可用于为表和复杂表引用指定临时名称，如果需要，也可以为其列指定临时名称，如下所示： ``` [ AS ] alias [ ( column_alias [, ...] ) ] ``` ## 参数 *with\$1subquery\$1table\$1name* [WITH 子句](r_WITH_clause.md)中的子查询定义的表。 *table\$1name* 表或视图的名称。 *alias* 表或视图的临时备用名称。必须为派生自子查询的表提供别名。在其他表引用中，别名是可选的。AS 关键字始终是可选的。表别名提供了用于标识查询的其他部分（例如 WHERE 子句）中的表的快捷方法。例如： ``` select * from sales s, listing l where s.listid=l.listid ``` *column\$1alias* 表或视图中的列的临时备用名称。 *subquery* 一个计算结果为表的查询表达式。表仅在查询的持续时间内存在，并且通常会向表提供一个名称或*别名*。但别名不是必需的。您也可以为派生自子查询的表定义列名称。如果您希望将子查询的结果联接到其他表并且希望在查询中的其他位置选择或约束这些列，则指定列的别名是非常重要的。 子查询可以包含 ORDER BY 子句，但在未指定 LIMIT 或 OFFSET 子句的情况下，该子句可能没有任何作用。 NATURAL 定义一个联接，该联接自动将两个表中同名列的所有配对用作联接列。不需要显式联接条件。例如，如果 CATEGORY 和 EVENT 表都具有名为 CATID 的列，则这两个表的自然联接为基于其 CATID 列的联接。 如果指定 NATURAL 联接，但表中没有要联接的同名列配对，则查询默认为交叉联接。 *join\$1type* 指定下列类型的联接之一： + [INNER] JOIN + LEFT [OUTER] JOIN + RIGHT [OUTER] JOIN + FULL [OUTER] JOIN + CROSS JOIN 交叉联接是未限定的联接；它们返回两个表的笛卡尔乘积。 内部联接和外部联接是限定的联接。它们的限定方式包括：隐式（在自然联接中）；在 FROM 语句中使用 ON 或 USING 语法；或者使用 WHERE 子句条件。 内部联接仅基于联接条件或联接列的列表返回匹配的行。外部联接返回与内部联接相同的所有行，还返回“左侧”表和/或“右侧”表中的非匹配行。左侧表是第一个列出的表，右侧表是第二个列出的表。非匹配行包含 NULL 值以填补输出列中的空白。 ON *join\$1condition* 联接规范的类型，其中将联接列声明为紧跟 ON 关键字的条件。例如： ``` sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid ``` USING ( *join\$1column* [, ...] ) 联接规范的类型，其中用圆括号将列出的联接列括起来。如果指定多个联接列，则用逗号将它们分隔开。USING 关键字必须在列表之前。例如： ``` sales join listing using (listid,eventid) ``` PIVOT 将输出由行转换为列，以便以易于阅读的格式表示表格数据。在多个列中水平表示输出。PIVOT 类似于带有聚合的 GROUP BY 查询，它使用聚合表达式来指定输出格式。但是，与 GROUP BY 不同的是，它以列而非行的形式返回输出。 有关演示如何使用 PIVOT 和 UNPIVOT 查询的示例，请参阅 [PIVOT 和 UNPIVOT 示例](r_FROM_clause-pivot-unpivot-examples.md)。 UNPIVOT *使用 UNPIVOT 将列旋转为行* – 此运算符将输入表或查询结果中的结果列转换为行，以使输出更易于阅读。UNPIVOT 将其输入列的数据合并为两个结果列：名称列和值列。名称列包含输入中的列名称，例如行条目。值列包含输入列中的值，例如聚合结果。例如，不同类别中的项目计数。 *使用 UNPIVOT (SUPER) 反转置对象* - 您可以执行对象反转置，其中 *expression* 是引用另一个 FROM 子句项的 SUPER 表达式。有关更多信息，请参阅 [对象逆透视](query-super.md#unpivoting)。它还提供了一些示例，展示如何查询半结构化数据，例如 JSON 格式的数据。 *super\$1expression* 有效的 SUPER 表达式。Amazon Redshift 会为指定属性中的每个值返回一行。有关 SUPER 数据类型的更多信息，请参阅 [SUPER 类型](r_SUPER_type.md)。有关取消嵌套的 SUPER 值的更多信息，请参阅[取消嵌套查询](query-super.md#unnest)。 *attribute\$1name* SUPER 表达式中属性的名称。 *index\$1alias* 表示值在 SUPER 表达式中位置的索引的别名。 UNNEST 将嵌套结构（通常是 SUPER 数组）扩展为包含取消嵌套元素的列。有关取消嵌套 SUPER 数据的更多信息，请参阅[查询半结构化数据](query-super.md)。有关示例，请参阅 [UNNEST 示例](r_FROM_clause-unnest-examples.md)。 *unnested\$1column\$1name* 包含取消嵌套元素的列的名称。 UNNEST ... WITH OFFSET 将偏移列添加到取消嵌套的输出中，偏移表示数组中每个元素从零开始的索引。当您想要查看数组中元素的位置时，这个变量很有用。有关取消嵌套 SUPER 数据的更多信息，请参阅[查询半结构化数据](query-super.md)。有关示例，请参阅 [UNNEST 示例](r_FROM_clause-unnest-examples.md)。 *offset\$1column\$1name* 偏移列的自定义名称，可让您显式定义索引列将在输出中的显示方式。此参数为可选的。默认情况下，偏移列的名称为 `offset_col`。 ## 使用说明 联接列必须具有可比较的数据类型。 NATURAL 或 USING 联接仅将每对联接列中的一个联接列保留在中间结果集中。 使用 ON 语法的联接会将两个联接列都保留在其中间结果集中。 另请参阅 [WITH 子句](r_WITH_clause.md)。 # PIVOT 和 UNPIVOT 示例 PIVOT 和 UNPIVOT 是 FROM 子句中的参数，它们分别将查询输出从行轮换到列，以及从列轮换到行。它们以便于阅读的格式呈现表格查询结果。以下示例使用测试数据和查询来说明如何使用它们。 有关这些参数及其他参数的更多信息，请参阅 [FROM 子句](https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause30.html)。 ## PIVOT 示例 设置示例表和数据并使用它们运行后续示例查询。 ``` CREATE TABLE part ( partname varchar, manufacturer varchar, quality int, price decimal(12, 2) ); INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00); INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00); INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00); INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50); INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75); INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90); INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50); INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20); INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80); ``` `partname` 上的 PIVOT，在 `price` 上有一个 `AVG` 聚合。 ``` SELECT * FROM (SELECT partname, price FROM part) PIVOT ( AVG(price) FOR partname IN ('prop', 'rudder', 'wing') ); ``` 查询将生成以下输出。 ``` prop | rudder | wing ---------+----------+--------- 10.33 | 2.71 | 11.50 ``` 在前面的示例中，结果转换为列。以下示例显示了一个按行而不是按列返回平均价格的 `GROUP BY` 查询。 ``` SELECT partname, avg(price) FROM (SELECT partname, price FROM part) WHERE partname IN ('prop', 'rudder', 'wing') GROUP BY partname; ``` 查询将生成以下输出。 ``` partname | avg ----------+------- prop | 10.33 rudder | 2.71 wing | 11.50 ``` 一个 `PIVOT` 示例，将 `manufacturer` 作为隐式列。 ``` SELECT * FROM (SELECT quality, manufacturer FROM part) PIVOT ( count(*) FOR quality IN (1, 2, NULL) ); ``` 查询将生成以下输出。 ``` manufacturer | 1 | 2 | null -------------------+----+----+------ local parts co | 1 | 1 | 1 big parts co | 1 | 1 | 1 small parts co | 1 | 0 | 2 ``` `PIVOT` 定义中未引用的输入表列被隐式添加到结果表中。就是上一个示例中 `manufacturer` 列这种情况。示例还显示，对于 `IN` 运算符，`NULL` 为有效值。 `PIVOT`上述示例中的 返回与以下查询类似的信息，其中包含 `GROUP BY`。区别在于 `PIVOT` 为列 `2` 和 制造商 `small parts co` 返回值 `0`。`GROUP BY` 查询不包含相应的行。在大多数情况下，如果一行没有针对给定列的输入数据，则 `PIVOT` 会插入 `NULL`。但是，计数聚合不会返回 `NULL`，`0` 是默认值。 ``` SELECT manufacturer, quality, count(*) FROM (SELECT quality, manufacturer FROM part) WHERE quality IN (1, 2) OR quality IS NULL GROUP BY manufacturer, quality ORDER BY manufacturer; ``` 查询将生成以下输出。 ``` manufacturer | quality | count ---------------------+---------+------- big parts co | | 1 big parts co | 2 | 1 big parts co | 1 | 1 local parts co | 2 | 1 local parts co | 1 | 1 local parts co | | 1 small parts co | 1 | 1 small parts co | | 2 ``` PIVOT 运算符接受聚合表达式和 `IN` 运算符的每个值上的可选别名。使用别名自定义列名。如果没有聚合别名，则仅使用 `IN` 列表别名。否则，将聚合别名附加到列名，并使用下划线将其与列名分开。 ``` SELECT * FROM (SELECT quality, manufacturer FROM part) PIVOT ( count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na) ); ``` 查询将生成以下输出。 ``` manufacturer | high_count | low_count | na_count -------------------+-------------+-----------+---------- local parts co | 1 | 1 | 1 big parts co | 1 | 1 | 1 small parts co | 1 | 0 | 2 ``` 设置以下示例表和数据，并使用它们运行后续示例查询。该数据表示一系列酒店的预订日期。 ``` CREATE TABLE bookings ( booking_id int, hotel_code char(8), booking_date date, price decimal(12, 2) ); INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12); INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00); INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54); INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00); INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00); INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00); INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00); INSERT INTO bookings VALUES (8, 'FOREST_L', '02/22/2023', 85.00); INSERT INTO bookings VALUES (9, 'FOREST_L', '02/27/2023', 90.00); INSERT INTO bookings VALUES (10, 'DESERT_S', '02/01/2023', 98.00); INSERT INTO bookings VALUES (11, 'DESERT_S', '02/02/2023', 75.00); INSERT INTO bookings VALUES (12, 'DESERT_S', '02/04/2023', 85.00); INSERT INTO bookings VALUES (13, 'DESERT_S', '02/05/2023', 75.00); INSERT INTO bookings VALUES (14, 'DESERT_S', '02/06/2023', 34.00); INSERT INTO bookings VALUES (15, 'DESERT_S', '02/09/2023', 85.00); INSERT INTO bookings VALUES (16, 'DESERT_S', '02/12/2023', 23.00); INSERT INTO bookings VALUES (17, 'DESERT_S', '02/13/2023', 76.00); INSERT INTO bookings VALUES (18, 'DESERT_S', '02/14/2023', 85.00); INSERT INTO bookings VALUES (19, 'OCEAN_WV', '02/01/2023', 98.00); INSERT INTO bookings VALUES (20, 'OCEAN_WV', '02/02/2023', 75.00); INSERT INTO bookings VALUES (21, 'OCEAN_WV', '02/04/2023', 85.00); INSERT INTO bookings VALUES (22, 'OCEAN_WV', '02/06/2023', 75.00); INSERT INTO bookings VALUES (23, 'OCEAN_WV', '02/09/2023', 34.00); INSERT INTO bookings VALUES (24, 'OCEAN_WV', '02/12/2023', 85.00); INSERT INTO bookings VALUES (25, 'OCEAN_WV', '02/13/2023', 23.00); INSERT INTO bookings VALUES (26, 'OCEAN_WV', '02/14/2023', 76.00); INSERT INTO bookings VALUES (27, 'OCEAN_WV', '02/16/2023', 85.00); INSERT INTO bookings VALUES (28, 'CITY_BLD', '02/01/2023', 98.00); INSERT INTO bookings VALUES (29, 'CITY_BLD', '02/02/2023', 75.00); INSERT INTO bookings VALUES (30, 'CITY_BLD', '02/04/2023', 85.00); INSERT INTO bookings VALUES (31, 'CITY_BLD', '02/12/2023', 75.00); INSERT INTO bookings VALUES (32, 'CITY_BLD', '02/13/2023', 34.00); INSERT INTO bookings VALUES (33, 'CITY_BLD', '02/17/2023', 85.00); INSERT INTO bookings VALUES (34, 'CITY_BLD', '02/22/2023', 23.00); INSERT INTO bookings VALUES (35, 'CITY_BLD', '02/23/2023', 76.00); INSERT INTO bookings VALUES (36, 'CITY_BLD', '02/24/2023', 85.00); ``` 在此示例查询中，对预订记录进行统计以得出每周的总数。每周的结束日期成为列名。 ``` SELECT * FROM (SELECT booking_id, (date_trunc('week', booking_date::date) + '5 days'::interval)::date as enddate, hotel_code AS "hotel code" FROM bookings ) PIVOT ( count(booking_id) FOR enddate IN ('2023-02-04','2023-02-11','2023-02-18') ); ``` 查询将生成以下输出。 ``` hotel code | 2023-02-04 | 2023-02-11 | 2023-02-18 ------------+-------------+------------+---------- FOREST_L | 3 | 2 | 1 DESERT_S | 4 | 3 | 2 OCEAN_WV | 3 | 3 | 3 CITY_BLD | 3 | 1 | 2 ``` Amazon Redshift 不支持 CROSSTAB 对多列进行透视。但您可以将行数据更改为列，与使用 PIVOT 进行聚合类似，使用如下所示的查询。这使用与上一个示例相同的预订示例数据。 ``` SELECT booking_date, MAX(CASE WHEN hotel_code = 'FOREST_L' THEN 'forest is booked' ELSE '' END) AS FOREST_L, MAX(CASE WHEN hotel_code = 'DESERT_S' THEN 'desert is booked' ELSE '' END) AS DESERT_S, MAX(CASE WHEN hotel_code = 'OCEAN_WV' THEN 'ocean is booked' ELSE '' END) AS OCEAN_WV FROM bookings GROUP BY booking_date ORDER BY booking_date asc; ``` 示例查询会导致在表示已预订了哪些酒店的短语旁边列出预订日期。 ``` booking_date | forest_l | desert_s | ocean_wv ---------------+------------------+------------------+-------------------- 2023-02-01 | forest is booked | desert is booked | ocean is booked 2023-02-02 | forest is booked | desert is booked | ocean is booked 2023-02-04 | forest is booked | desert is booked | ocean is booked 2023-02-05 | | desert is booked | 2023-02-06 | | desert is booked | ``` 以下是 `PIVOT` 的使用说明： + `PIVOT` 可以应用于表、子查询和公用表表达式（CTE）。`PIVOT` 不可应用于任何 `JOIN` 表达式、递归 CTE、`PIVOT` 或 `UNPIVOT` 表达式。此外，也不支持 `SUPER` 取消嵌套的表达式和 Redshift Spectrum 嵌套表。 + `PIVOT` 支持 `COUNT`、`SUM`、`MIN`、`MAX` 和 `AVG` 聚合函数。 + `PIVOT` 聚合表达式必须是对受支持的聚合函数的调用。不支持聚合顶部的复杂表达式。聚合参数仅可包含对 `PIVOT` 输入表的引用。此外，也不支持对父查询的关联引用。聚合参数可能包含子查询。它们可以在内部关联或在 `PIVOT` 输入表上关联。 + `PIVOT IN` 列表值不得为列引用或子查询。每个值必须与 `FOR` 列引用类型兼容。 + 如果 `IN` 列表值没有别名，`PIVOT` 会生成默认的列名。对于常量 `IN` 值（例如“abc”或 5），默认列名是常量本身。对于任何复杂表达式，列名都是标准的 Amazon Redshift 默认名称，例如 `?column?`。 ## UNPIVOT 示例 设置示例数据并使用它来运行后续示例。 ``` CREATE TABLE count_by_color (quality varchar, red int, green int, blue int); INSERT INTO count_by_color VALUES ('high', 15, 20, 7); INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40); INSERT INTO count_by_color VALUES ('low', 10, 23, NULL); ``` `UNPIVOT`红、绿和蓝输入列上的 。 ``` SELECT * FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT ( cnt FOR color IN (red, green, blue) ); ``` 查询将生成以下输出。 ``` color | cnt -------+----- red | 15 red | 35 red | 10 green | 20 green | 23 blue | 7 blue | 40 ``` 默认情况下，将跳过输入列中的 `NULL` 值，且不会产生结果行。 以下示例显示了带有 `INCLUDE NULLS` 的 `UNPIVOT`。 ``` SELECT * FROM ( SELECT red, green, blue FROM count_by_color ) UNPIVOT INCLUDE NULLS ( cnt FOR color IN (red, green, blue) ); ``` 以下是结果输出。 ``` color | cnt -------+----- red | 15 red | 35 red | 10 green | 20 green | green | 23 blue | 7 blue | 40 blue | ``` 如果已设置 `INCLUDING NULLS` 参数，`NULL` 输入值将生成结果行。 带有 `quality` 的 `The following query shows UNPIVOT` 作为隐式列。 ``` SELECT * FROM count_by_color UNPIVOT ( cnt FOR color IN (red, green, blue) ); ``` 查询将生成以下输出。 ``` quality | color | cnt ---------+-------+----- high | red | 15 normal | red | 35 low | red | 10 high | green | 20 low | green | 23 high | blue | 7 normal | blue | 40 ``` `UNPIVOT` 定义中未引用的输入表列被隐式添加到结果表中。在该示例中，`quality` 列就是这种情况。 以下示例显示了带有 `UNPIVOT` 列表中值别名的 `IN`。 ``` SELECT * FROM count_by_color UNPIVOT ( cnt FOR color IN (red AS r, green AS g, blue AS b) ); ``` 上一查询将产生以下输出。 ``` quality | color | cnt ---------+-------+----- high | r | 15 normal | r | 35 low | r | 10 high | g | 20 low | g | 23 high | b | 7 normal | b | 40 ``` `UNPIVOT` 运算符接受每个 `IN` 列表值上的可选别名。每个别名会提供每个 `value` 列中的数据自定义。 以下是 `UNPIVOT` 的使用说明。 + `UNPIVOT` 可以应用于表、子查询和公用表表达式（CTE）。`UNPIVOT` 不可应用于任何 `JOIN` 表达式、递归 CTE、`PIVOT` 或 `UNPIVOT` 表达式。此外，也不支持 `SUPER` 取消嵌套的表达式和 Redshift Spectrum 嵌套表。 + `UNPIVOT IN` 列表必须仅包含输入表列引用。`IN` 列表列必须具有它们都与之兼容的常见类型。`UNPIVOT` 值列具有这一常见类型。`UNPIVOT` 名称列属于类型 `VARCHAR`。 + 如果 `IN` 列表值没有别名，`UNPIVOT` 则使用列名作为默认值。 # JOIN 示例 SQL JOIN 子句用于根据公共字段合并两个或多个表中的数据。根据指定的联接方法，结果可能会发生变化，也可能不发生变化。有关 JOIN 子句的语法的更多信息，请参阅[参数](r_FROM_clause30.md#r_FROM_clause30-parameters)。 以下示例使用 `TICKIT` 示例数据中的数据。有关数据库架构的更多信息，请参阅[示例数据库](c_sampledb.md)。要了解如何加载示例数据，请参阅《Amazon Redshift 入门指南》**中的[加载数据](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html)。 下面的查询是 LISTING 表和 SALES 表之间的内部联接（不带 JOIN 关键字），其中 LISTING 表中的 LISTID 介于 1 和 5 之间。此查询匹配 LISTING 表（左表）和 SALES 表（右表）中的 LISTID 列值。结果显示 LISTID 1、4 和 5 符合条件。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing, sales where listing.listid = sales.listid and listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 以下查询是一个左外部联接。当在其他表中找不到匹配项时，左外部联接和右外部联接保留某个已联接表中的值。左表和右表是语法中列出的第一个表和第二个表。NULL 值用于填补结果集中的“空白”。此查询匹配 LISTING 表（左表）和 SALES 表（右表）中的 LISTID 列值。结果表明 LISTID 2 和 3 不会生成任何销售额。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing left outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 以下查询是一个右外部联接。此查询匹配 LISTING 表（左表）和 SALES 表（右表）中的 LISTID 列值。结果显示 ListID 1、4 和 5 符合条件。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing right outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 以下查询是一个完全联接。当在其他表中找不到匹配项时，完全联接保留已联接表中的值。左表和右表是语法中列出的第一个表和第二个表。NULL 值用于填补结果集中的“空白”。此查询匹配 LISTING 表（左表）和 SALES 表（右表）中的 LISTID 列值。结果表明 LISTID 2 和 3 不会生成任何销售额。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 以下查询是一个完全联接。此查询匹配 LISTING 表（左表）和 SALES 表（右表）中的 LISTID 列值。结果中只有不会导致任何销售额的行（ListID 2 和 3）。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 and (listing.listid IS NULL or sales.listid IS NULL) group by 1 order by 1; listid | price | comm -------+--------+-------- 2 | NULL | NULL 3 | NULL | NULL ``` 以下示例是与 ON 子句的内部联接。在这种情况下，不返回 NULL 行。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 以下查询是 LISTING 表和 SALES 表的交叉联接或笛卡尔联接，其中包含限制结果的谓词。此查询匹配 SALES 表和 LISTING 表中的 LISTID 列值，对应于这两个表中的 LISTID 1、2、3、4 和 5。结果显示 20 个行符合条件。 ``` select sales.listid as sales_listid, listing.listid as listing_listid from sales cross join listing where sales.listid between 1 and 5 and listing.listid between 1 and 5 order by 1,2; sales_listid | listing_listid -------------+--------------- 1 | 1 1 | 2 1 | 3 1 | 4 1 | 5 4 | 1 4 | 2 4 | 3 4 | 4 4 | 5 5 | 1 5 | 1 5 | 2 5 | 2 5 | 3 5 | 3 5 | 4 5 | 4 5 | 5 5 | 5 ``` 以下示例是两个表之间的自然联接。在这种情况下，列 listid、sellerid、eventid 和 dateid 在两个表中具有相同的名称和数据类型，因此用作联接列。结果限制为 5 行。 ``` select listid, sellerid, eventid, dateid, numtickets from listing natural join sales order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+-----------+---------+--------+----------- 113 | 29704 | 4699 | 2075 | 22 115 | 39115 | 3513 | 2062 | 14 116 | 43314 | 8675 | 1910 | 28 118 | 6079 | 1611 | 1862 | 9 163 | 24880 | 8253 | 1888 | 14 ``` 以下示例是使用 USING 子句在两个表之间进行的联接。在这种情况下，列 listid 和 eventid 用作联接列。结果限制为 5 行。 ``` select listid, listing.sellerid, eventid, listing.dateid, numtickets from listing join sales using (listid, eventid) order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+----------+---------+--------+----------- 1 | 36861 | 7872 | 1850 | 10 4 | 8117 | 4337 | 1970 | 8 5 | 1616 | 8647 | 1963 | 4 5 | 1616 | 8647 | 1963 | 4 6 | 47402 | 8240 | 2053 | 18 ``` 以下查询是 FROM 子句中的两个子查询的内部联接。此查询查找不同类别的活动（音乐会和演出）的已售门票数和未售门票数：这些 FROM 子句子查询是*表* 子查询；它们可返回多个列和行。 ``` select catgroup1, sold, unsold from (select catgroup, sum(qtysold) as sold from category c, event e, sales s where c.catid = e.catid and e.eventid = s.eventid group by catgroup) as a(catgroup1, sold) join (select catgroup, sum(numtickets)-sum(qtysold) as unsold from category c, event e, sales s, listing l where c.catid = e.catid and e.eventid = s.eventid and s.listid = l.listid group by catgroup) as b(catgroup2, unsold) on a.catgroup1 = b.catgroup2 order by 1; catgroup1 | sold | unsold ----------+--------+-------- Concerts | 195444 |1067199 Shows | 149905 | 817736 ``` # UNNEST 示例 UNNEST 是 FROM 子句中的一个参数，用于将嵌套数据扩展为包含数据的取消嵌套元素的列。有关取消嵌套数据的信息，请参阅[查询半结构化数据](query-super.md)。 以下语句创建并填充 `orders` 表，该表包含 `products` 列，其中包含产品 ID 的数组。本节中的示例使用此表中的示例数据。 ``` CREATE TABLE orders ( order_id INT, products SUPER ); -- Populate table INSERT INTO orders VALUES (1001, JSON_PARSE('[ { "product_id": "P456", "name": "Monitor", "price": 299.99, "quantity": 1, "specs": { "size": "27 inch", "resolution": "4K" } } ] ')), (1002, JSON_PARSE(' [ { "product_id": "P567", "name": "USB Cable", "price": 9.99, "quantity": 3 }, { "product_id": "P678", "name": "Headphones", "price": 159.99, "quantity": 1, "specs": { "type": "Wireless", "battery_life": "20 hours" } } ] ')); ``` 以下是一些使用 PartiQL 语法对示例数据取消嵌套查询的示例。 ## 取消嵌套一个没有 OFFSET 列的数组 以下查询取消嵌套 products 列中的 SUPER 数组，每行代表订单 `order_id` 中内的一个项目。 ``` SELECT o.order_id, unnested_products.product FROM orders o, UNNEST(o.products) AS unnested_products(product); order_id | product ----------+----------------------------------------------------------------------------------------------------------------------------- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} (3 rows) ``` 以下查询查找每个订单中最昂贵的产品。 ``` SELECT o.order_id, MAX(unnested_products.product) FROM orders o, UNNEST(o.products) AS unnested_products(product); order_id | product ----------+----------------------------------------------------------------------------------------------------------------------------- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} (2 rows) ``` ## 取消嵌套带有隐式 OFFSET 列的数组 以下查询使用 `UNNEST ... WITH OFFSET` 参数来显示每个产品在其订单数组中从零开始的位置。 ``` SELECT o.order_id, up.product, up.offset_col FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product); order_id | product | offset_col ----------+-----------------------------------------------------------------------------------------------------------------------------+------------ 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1 (3 rows) ``` 由于该语句没有为偏移列指定别名，因此 Amazon Redshift 默认将其命名为 `offset_col`。 ## 取消嵌套带有显式 OFFSET 列的数组 以下查询还使用 `UNNEST ... WITH OFFSET` 参数来显示其订单数组中的产品。与上一个示例中的查询相比，此查询的不同之处在于，它使用别名 `idx` 显式命名偏移列。 ``` SELECT o.order_id, up.product, up.idx FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product, idx); order_id | product | idx ----------+-----------------------------------------------------------------------------------------------------------------------------+----- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1 (3 rows) ``` # WHERE 子句 WHERE 子句包含用于联接表或将谓词应用于表中的列的条件。可在 WHERE 子句或 FROM 子句中使用适当的语法对表进行内部联接。外部联接条件必须在 FROM 子句中指定。 ## 语法 ``` [ WHERE condition ] ``` ## *condition* 任何具有布尔型结果的搜索条件，例如，联接条件或表列上的谓词。以下示例是有效的联接条件： ``` sales.listid=listing.listid sales.listid<>listing.listid ``` 以下示例是表中的列上的有效条件： ``` catgroup like 'S%' venueseats between 20000 and 50000 eventname in('Jersey Boys','Spamalot') year=2008 length(catdesc)>25 date_part(month, caldate)=6 ``` 条件可以是简单条件或复杂条件；对于复杂条件，可以使用圆括号来分隔逻辑单元。在下面的示例中，用圆括号将联接条件括起来。 ``` where (category.catid=event.catid) and category.catid in(6,7,8) ``` ## 使用说明 您可在 WHERE 子句中使用别名来引用选择列表表达式。 不能限制 WHERE 子句中的聚合函数的结果；要实现此目的，请使用 HAVING 子句。 WHERE 子句中受限制的列必须派生自 FROM 子句中的表引用。 ## 示例 以下查询使用不同的 WHERE 子句限制的组合，包括 SALES 表和 EVENT 表的联接条件、EVENTNAME 列上的谓词以及 STARTTIME 列上的两个谓词。 ``` select eventname, starttime, pricepaid/qtysold as costperticket, qtysold from sales, event where sales.eventid = event.eventid and eventname='Hannah Montana' and date_part(quarter, starttime) in(1,2) and date_part(year, starttime) = 2008 order by 3 desc, 4, 2, 1 limit 10; eventname | starttime | costperticket | qtysold ----------------+---------------------+-------------------+--------- Hannah Montana | 2008-06-07 14:00:00 | 1706.00000000 | 2 Hannah Montana | 2008-05-01 19:00:00 | 1658.00000000 | 2 Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 1 Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 3 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 1 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 2 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 4 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 1 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 2 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 4 (10 rows) ``` # WHERE 子句中的 Oracle 样式的外部联接 为了与 Oracle 兼容，Amazon Redshift 支持 WHERE 子句联接条件中的 Oracle 外部联接运算符 (\$1)。此运算符仅用于定义外部联接条件；不要尝试在其他上下文中使用它。在大多数情况下，会无提示忽略此运算符的其他使用方式。 外部联接返回与内部联接相同的所有行，还返回一个或两个表中的非匹配行。在 FROM 子句中，可以指定左、右和完全外部联接。在 WHERE 子句中，只能指定左和右外部联接。 要对表 TABLE1 和 TABLE2 执行外部联接并返回 TABLE1 中的非匹配行（左外部联接），请在 FROM 子句中指定 `TABLE1 LEFT OUTER JOIN TABLE2`，或者将 (\$1) 运算符应用于 WHERE 子句中的 TABLE2 中的所有联接列。对于 TABLE1 中的在 TABLE2 中没有匹配行的所有行，在查询结果中，会为包含 TABLE2 中列的任何选择列表表达式显示 Null 值。 要为 TABLE2 中的在 TABLE1 中没有匹配行的所有行生成相同的行为，请在 FROM 子句中指定 `TABLE1 RIGHT OUTER JOIN TABLE2`，或者将 (\$1) 运算符应用于 WHERE 子句中的 TABLE1 中的所有联接列。 ## 基本语法 ``` [ WHERE { [ table1.column1 = table2.column1(+) ] [ table1.column1(+) = table2.column1 ] } ``` 第一个条件等效于： ``` from table1 left outer join table2 on table1.column1=table2.column1 ``` 第二个条件等效于： ``` from table1 right outer join table2 on table1.column1=table2.column1 ``` **注意** 此处显示的语法涵盖了基于一对联接列的 equijoin 的单个示例。不过，其他类型的比较条件和多个联接列对也有效。 例如，以下 WHERE 子句定义基于两个列对的外部联接。(\$1) 运算符必须附加到两个条件中的同一个表： ``` where table1.col1 > table2.col1(+) and table1.col2 = table2.col2(+) ``` ## 使用说明 如果可能，请在 WHERE 子句中使用标准 FROM 子句 OUTER JOIN 语法而非 (\$1) 运算符。包含 (\$1) 运算符的查询需遵循以下规则： + 只能在 WHERE 语句中以及对表或视图中的列的引用中使用 (\$1) 运算符。 + 不能对表达式应用 (\$1) 运算符。不过，表达式可以包含使用 (\$1) 运算符的列。例如，以下联接条件返回语法错误： ``` event.eventid*10(+)=category.catid ``` 不过，以下联接条件有效： ``` event.eventid(+)*10=category.catid ``` + 不能在同时包含 FROM 子句联接语法的查询块中使用 (\$1) 运算符。 + 如果两个表基于多个联接条件进行联接，则必须在所有这些条件中都使用或都不使用 (\$1) 运算符。使用混合语法样式的联接将作为内部联接执行，不会产生警告。 + 如果将外部查询中的表与内部查询所生成的表联接，则 (\$1) 运算符不会产生外部联接。 + 要使用 (\$1) 运算符来将表外部联接到自身，则必须在 FROM 子句中定义表别名并在联接条件中引用这些别名： ``` select count(*) from event a, event b where a.eventid(+)=b.catid; count ------- 8798 (1 row) ``` + 不能将包含 (\$1) 运算符的联接条件与 OR 条件或 IN 条件结合使用。例如： ``` select count(*) from sales, listing where sales.listid(+)=listing.listid or sales.salesid=0; ERROR: Outer join operator (+) not allowed in operand of OR or IN. ``` + 在对两个以上的表执行外部联接的 WHERE 子句中，只能将 (\$1) 运算符应用于指定的表一次。在下面的示例中，不能在两个连续联接中使用 (\$1) 运算符来引用 SALES 表。 ``` select count(*) from sales, listing, event where sales.listid(+)=listing.listid and sales.dateid(+)=date.dateid; ERROR: A table may be outer joined to at most one other table. ``` + 如果 WHERE 子句外部联接条件将 TABLE2 中的一个列与常量比较，则对该列应用 (\$1) 运算符。如果您包括运算符，则会消除 TABLE1 中的外部联接的行（受限制列包含 null 值）。请参阅下面的“示例”部分。 ## 示例 以下联接查询指定 SALES 和 LISTING 表的左外部联接（基于其 LISTID 列）： ``` select count(*) from sales, listing where sales.listid = listing.listid(+); count -------- 172456 (1 row) ``` 以下等效查询生成相同的结果，不过使用的是 FROM 子句联接语法： ``` select count(*) from sales left outer join listing on sales.listid = listing.listid; count -------- 172456 (1 row) ``` SALES 表不包含 LISTING 表中所有列表的记录，因为并非所有列表都生成销售值。以下查询对 SALES 和 LISTING 执行外部联接，并返回 LISTING 中的行（甚至在 SALES 表未报告给定列表 ID 的销售值也是如此）。PRICE 和 COMM 列（派生自 SALES 表）在结果集中为这些非匹配行包含 null 值。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing, sales where sales.listid(+) = listing.listid and listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm --------+--------+-------- 1 | 728.00 | 109.20 2 | | 3 | | 4 | 76.00 | 11.40 5 | 525.00 | 78.75 (5 rows) ``` 请注意，在使用 WHERE 子句联接运算符时，FROM 子句中表的顺序并不重要。 WHERE 子句中更复杂的外部联接条件的示例是，对两个表列之间的比较以及带常量的比较执行*与* 运算的条件： ``` where category.catid=event.catid(+) and eventid(+)=796; ``` 请注意，在两个位置使用 (\$1) 运算符：首先是在表之间的相等比较中，其次是在 EVENTID 列的比较条件中。此语法的结果是，在计算 EVENTID 的限制时保留外部联接的行。如果您从 EVENTID 限制中删除 (\$1) 运算符，则查询会将此限制视为筛选条件而不是视为外部联接条件的一部分。反过来，将从结果集中消除包含 EVENTID 的 null 值的外部联接的行。 下面是演示此行为的完整查询： ``` select catname, catgroup, eventid from category, event where category.catid=event.catid(+) and eventid(+)=796; catname | catgroup | eventid -----------+----------+--------- Classical | Concerts | Jazz | Concerts | MLB | Sports | MLS | Sports | Musicals | Shows | 796 NBA | Sports | NFL | Sports | NHL | Sports | Opera | Shows | Plays | Shows | Pop | Concerts | (11 rows) ``` 使用 FROM 子句语法的等效查询如下： ``` select catname, catgroup, eventid from category left join event on category.catid=event.catid and eventid=796; ``` 如果从该查询的 WHERE 子句版本中删除第二个 (\$1) 运算符，则它仅返回 1 行（其中包含 `eventid=796` 的行）。 ``` select catname, catgroup, eventid from category, event where category.catid=event.catid(+) and eventid=796; catname | catgroup | eventid -----------+----------+--------- Musicals | Shows | 796 (1 row) ``` # GROUP BY 子句 GROUP BY 子句标识查询的分组列。它用于将表中在所有列出的列中具有相同值的行组合在一起。列的列出顺序并不重要。结果是将每组具有共同值的行组合到一个组行中，而该组行表示组中的所有行。使用 GROUP BY 可消除输出中的冗余，并计算适用于这些组的聚合。必须在查询使用标准函数（例如，SUM、AVG 和 COUNT）计算聚合时声明分组列。有关更多信息，请参阅 [聚合函数](c_Aggregate_Functions.md)。 ## 语法 ``` [ GROUP BY expression [, ...] | ALL | aggregation_extension ] ``` 其中，*aggregation\$1extension* 为以下之一： ``` GROUPING SETS ( () | aggregation_extension [, ...] ) | ROLLUP ( expr [, ...] ) | CUBE ( expr [, ...] ) ``` ## 参数 *expression* 列或表达式的列表必须匹配查询的选择列表中的非聚合表达式的列表。例如，考虑以下简单查询。 ``` select listid, eventid, sum(pricepaid) as revenue, count(qtysold) as numtix from sales group by listid, eventid order by 3, 4, 2, 1 limit 5; listid | eventid | revenue | numtix -------+---------+---------+-------- 89397 | 47 | 20.00 | 1 106590 | 76 | 20.00 | 1 124683 | 393 | 20.00 | 1 103037 | 403 | 20.00 | 1 147685 | 429 | 20.00 | 1 (5 rows) ``` 在此查询中，选择列表包含两个聚合表达式。第一个聚合表达式使用 SUM 函数，第二个聚合表达式使用 COUNT 函数。必须将其余两个列（LISTID 和 EVENTID）声明为分组列。 GROUP BY 子句中的表达式也可以使用序号来引用选择列表。例如，上一个示例的缩略形式如下。 ``` select listid, eventid, sum(pricepaid) as revenue, count(qtysold) as numtix from sales group by 1,2 order by 3, 4, 2, 1 limit 5; listid | eventid | revenue | numtix -------+---------+---------+-------- 89397 | 47 | 20.00 | 1 106590 | 76 | 20.00 | 1 124683 | 393 | 20.00 | 1 103037 | 403 | 20.00 | 1 147685 | 429 | 20.00 | 1 (5 rows) ``` ALL ALL 表示按照在 SELECT 列表中指定的所有列进行分组，但聚合的列除外。例如，考虑以下查询，该查询按 `col1` 和 `col2` 分组，而不必在 GROUP BY 子句中单独指定它们。列 `col3` 是 `SUM` 函数的参数，因此不进行分组。 ``` SELECT col1, col2 sum(col3) FROM testtable GROUP BY ALL ``` 如果您在 SELECT 列表中 EXCLUDE 某列，则 GROUP BY ALL 子句不会根据该特定列对结果进行分组。 ``` SELECT * EXCLUDE col3 FROM testtable GROUP BY ALL ``` * *aggregation\$1extension* * 您可以使用聚合扩展 GROUPING SETS、ROLLUP 和 CUBE 在单个语句中执行多个 GROUP BY 操作的工作。有关聚合扩展和相关函数的更多信息，请参阅[聚合扩展](r_GROUP_BY_aggregation-extensions.md)。 ## 示例 下面的示例使用包含以下各列的 SALES 表：salesid、listid、sellerid、buyerid、eventid、dateid、qtysold、pricepaid、commission 和 saletime。有关 SALES 表的更多信息，请参阅[示例数据库](c_sampledb.md)。 以下示例查询按 `salesid` 和 `listid` 分组，而不必在 GROUP BY 子句中单独指定它们。列 `qtysold` 是 `SUM` 函数的参数，因此不进行分组。 ``` SELECT salesid, listid, sum(qtysold) FROM sales GROUP BY ALL; salesid | listid | sum --------+---------+------ 33095 | 36572 | 2 88268 | 100813 | 4 110917 | 127048 | 1 ... ``` 以下示例查询排除 SELECT 列表中的若干列，因此 GROUP BY ALL 仅对 salesid 和 listid 进行分组。 ``` SELECT * EXCLUDE sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, saletime FROM sales GROUP BY ALL; salesid | listid --------+--------- 33095 | 36572 88268 | 100813 110917 | 127048 ... ``` # 聚合扩展 Amazon Redshift 支持聚合扩展，以便在单个语句中完成多个 GROUP BY 操作。 聚合扩展的示例使用 `orders` 表，该表包含电子公司的销售数据。您可以使用以下项创建 `orders`。 ``` CREATE TABLE ORDERS ( ID INT, PRODUCT CHAR(20), CATEGORY CHAR(20), PRE_OWNED CHAR(1), COST DECIMAL ); INSERT INTO ORDERS VALUES (0, 'laptop', 'computers', 'T', 1000), (1, 'smartphone', 'cellphones', 'T', 800), (2, 'smartphone', 'cellphones', 'T', 810), (3, 'laptop', 'computers', 'F', 1050), (4, 'mouse', 'computers', 'F', 50); ``` ## *GROUPING SETS* 在单个语句中计算一个或多个分组集。分组集是单个 GROUP BY 子句的集合，这是一组 0 列或更多列，您可以通过这些列对查询的结果集进行分组。GROUP BY GROUPING SETS 等效于对一个按不同列分组的结果集运行 UNION ALL 查询。例如，GROUP BY GROUPING SETS((a), (b)) 等效于 GROUP BY a UNION ALL GROUP BY b。 以下示例返回按产品类别和所售产品类型分组的订单表产品的成本。 ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY GROUPING SETS(category, product); category | product | total ----------------------+----------------------+------- computers | | 2100 cellphones | | 1610 | laptop | 2050 | smartphone | 1610 | mouse | 50 (5 rows) ``` ## *ROLLUP* 假设在一个层次结构中，前面的列被视为后续列的父列。ROLLUP 按提供的列对数据进行分组，除了分组行之外，还返回额外的小计行，表示所有分组列级别的总计。例如，您可以使用 GROUP BY ROLLUP((a), (b)) 返回先按 a 分组的结果集，然后在假设 b 是 a 的一个子部分的情况下按 b 分组。ROLLUP 还会返回包含整个结果集而不包含分组列的行。 GROUP BY ROLLUP((a), (b)) 等效于 GROUP BY GROUPING SETS((a,b), (a), ())。 以下示例返回先按类别分组，然后按产品分组，且产品是类别细分项的订单表产品的成本。 ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY ROLLUP(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | | 3710 (6 rows) ``` ## *CUBE* 按提供的列对数据进行分组，除了分组行之外，还返回额外的小计行，表示所有分组列级别的总计。CUBE 返回与 ROLLUP 相同的行，同时为 ROLLUP 未涵盖的每个分组列组合添加额外的小计行。例如，您可以使用 GROUP BY CUBE ((a), (b)) 返回先按 a 分组的结果集，然后在假设 b 是 a 的一个子部分的情况下按 b 分组，再然后是单独按 b 分组的结果集。CUBE 还会返回包含整个结果集而不包含分组列的行。 GROUP BY CUBE((a), (b)) 等效于 GROUP BY GROUPING SETS((a, b), (a), (b), ())。 以下示例返回先按类别分组，然后按产品分组，且产品是类别细分项的订单表产品的成本。与前面的 ROLLUP 示例不同，该语句返回每个分组列组合的结果。 ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | laptop | 2050 | mouse | 50 | smartphone | 1610 | | 3710 (9 rows) ``` ## *GROUPING/GROUPING\$1ID 函数* ROLLUP 和 CUBE 为结果集添加 NULL 值以表示小计行。例如，GROUP BY ROLLUP((a), (b)) 返回 b 分组列中值为 NULL 的一行或多行，以表明它们是 a 分组列中字段的小计。这些 NULL 值仅用于满足返回元组的格式。 当您使用 ROLLUP 和 CUBE 对本身存储 NULL 值的关系运行 GROUP BY 操作时，这样生成的结果集会包含看起来具有相同分组列的行。返回前面的示例，如果 b 分组列包含存储的 NULL 值，则 GROUP BY ROLLUP((a), (b)) 返回 b 分组列中值为 NULL 且不是小计的行。 要区分由 ROLLUP 和 CUBE 创建的 NULL 值和在表本身存储的 NULL 值，可以使用 GROUPING 函数或其别名 GROUPING\$1ID。GROUPING 采用单个分组集作为其参数，并且对于结果集中的每一行，返回与该位置的分组列对应的 0 或 1 位值，然后将该值转换为整数。如果该位置的值是由聚合扩展创建的 NULL 值，则 GROUPING 返回 1。对于所有其他值（包括存储的 NULL 值），该示例返回 0。 例如，GROUPING(category, product) 为给定行返回以下值，具体取决于该行的分组列值。就本示例而言，表中的所有 NULL 值都是由聚合扩展创建的 NULL 值。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_GROUP_BY_aggregation-extensions.html) GROUPING 函数按照以下格式显示在查询的 SELECT 列表部分。 ``` SELECT ... [GROUPING( expr )...] ... GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ... ``` 以下示例与前面的 CUBE 示例相同，但为其分组集添加了 GROUPING 函数。 ``` SELECT category, product, GROUPING(category) as grouping0, GROUPING(product) as grouping1, GROUPING(category, product) as grouping2, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 3,1,2; category | product | grouping0 | grouping1 | grouping2 | total ----------------------+----------------------+-----------+-----------+-----------+------- cellphones | smartphone | 0 | 0 | 0 | 1610 cellphones | | 0 | 1 | 1 | 1610 computers | laptop | 0 | 0 | 0 | 2050 computers | mouse | 0 | 0 | 0 | 50 computers | | 0 | 1 | 1 | 2100 | laptop | 1 | 0 | 2 | 2050 | mouse | 1 | 0 | 2 | 50 | smartphone | 1 | 0 | 2 | 1610 | | 1 | 1 | 3 | 3710 (9 rows) ``` ## *部分 ROLLUP 和 CUBE* 您可以只使用小计的一部分来运行 ROLLUP 和 CUBE 操作。 部分 ROLLUP 和 CUBE 操作的语法如下所示。 ``` GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...]) ``` 在这里，GROUP BY 子句仅在 *expr2* 及更高级别创建小计行。 以下示例显示了对订单表执行的部分 ROLLUP 和 CUBE 操作，首先按产品是否为二手产品进行分组，然后对 category 和 product 列运行 ROLLUP 和 CUBE。 ``` SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | | 6 | 1100 T | | | 6 | 2610 (9 rows) SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 (13 rows) ``` 由于 ROLLUP 和 CUBE 操作中不包括 pre-owned 列，因此不存在包括所有其他行的总计行。 ## *连接分组* 您可以连接多个 GROUPING SETS/ROLLUP/CUBE 子句来计算不同级别的小计。连接分组返回所提供分组集的笛卡尔乘积。 连接 GROUPING SETS/ROLLUP/CUBE 子句的语法如下所示。 ``` GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]), {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...] ``` 考虑以下示例，看看小型连接分组如何生成大的最终结果集。 ``` SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ()) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 | cellphones | smartphone | 1 | 1610 | computers | laptop | 1 | 2050 | computers | mouse | 1 | 50 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 | cellphones | | 3 | 1610 | computers | | 3 | 2100 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 | | laptop | 5 | 2050 | | mouse | 5 | 50 | | smartphone | 5 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 | | | 7 | 3710 (22 rows) ``` ## *嵌套分组* 您可以使用 GROUPING SETS/ROLLUP/CUBE 操作作为 GROUPING SETS *expr* 来形成嵌套分组。展平嵌套 GROUPING SETS 内的子分组。 嵌套分组的语法如下所示。 ``` GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...]) ``` 考虑以下示例。 ``` SELECT category, product, pre_owned, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned)) ORDER BY 4,1,2,3; category | product | pre_owned | group_id | total ----------------------+----------------------+-----------+----------+------- cellphones | | | 3 | 1610 computers | | | 3 | 2100 | laptop | F | 4 | 1050 | laptop | T | 4 | 1000 | mouse | F | 4 | 50 | smartphone | T | 4 | 1610 | laptop | | 5 | 2050 | mouse | | 5 | 50 | smartphone | | 5 | 1610 | | F | 6 | 1100 | | T | 6 | 2610 | | | 7 | 3710 | | | 7 | 3710 (13 rows) ``` 请注意，由于 ROLLUP(category) 和 CUBE(product, pre\$1owned) 都包含分组集 ()，因此表示总计的行是重复的。 ## *使用说明*： + GROUP BY 子句最多支持 64 个分组集。对于 ROLLUP 和 CUBE，或者 GROUPING SETS、ROLLUP 和 CUBE 的某种组合，此限制适用于隐含的分组集数。例如，GROUP BY CUBE((a), (b)) 计为 4 个分组集，而不是 2 个。 + 使用聚合扩展时，不能使用常量作为分组列。 + 无法创建包含重复列的分组集。 # HAVING 子句 HAVING 子句将条件应用于查询返回的中间分组结果集。 ## 语法 ``` [ HAVING condition ] ``` 例如，您可以限制 SUM 函数的结果： ``` having sum(pricepaid) >10000 ``` 在应用所有 WHERE 子句条件并完成 GROUP BY 操作后，应用 HAVING 条件。 条件本身采用与任何 WHERE 子句条件相同的形式。 ## 使用说明 + HAVING 子句条件中引用的任何列必须为分组列或引用了聚合函数结果的列。 + 在 HAVING 子句中，无法指定： + 引用选择列表项的序号。仅 GROUP BY 和 ORDER BY 子句接受序号。 ## 示例 以下查询按名称计算所有活动的门票总销售额，然后消除总销售额小于 \$1800000 的活动。HAVING 条件应用于选择列表中聚合函数的结果：`sum(pricepaid)`。 ``` select eventname, sum(pricepaid) from sales join event on sales.eventid = event.eventid group by 1 having sum(pricepaid) > 800000 order by 2 desc, 1; eventname | sum -----------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 ``` 以下查询计算类似的结果集。不过，在本示例中，HAVING 条件将应用于未在选择列表中指定的聚合：`sum(qtysold)`。将从最终结果中消除未售出 2000 张以上的门票的活动。 ``` select eventname, sum(pricepaid) from sales join event on sales.eventid = event.eventid group by 1 having sum(qtysold) >2000 order by 2 desc, 1; eventname | sum -----------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 Chicago | 790993.00 Spamalot | 714307.00 ``` 以下查询按名称计算所有活动的门票总销售额，然后消除总销售额小于 \$1800000 的活动。HAVING 条件应用于选择列表中聚合函数的结果（对 `sum(pricepaid)` 使用别名 `pp`）。 ``` select eventname, sum(pricepaid) as pp from sales join event on sales.eventid = event.eventid group by 1 having pp > 800000 order by 2 desc, 1; eventname | pp -----------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 ``` # QUALIFY 子句 QUALIFY 子句根据用户指定的搜索条件，筛选先前计算的窗口函数的结果。您可以使用此子句将筛选条件应用于窗口函数的结果，而无需使用子查询。 此子句与 [HAVING 子句](https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html)类似，后者应用条件以进一步从 WHERE 子句筛选行。QUALIFY 和 HAVING 的区别在于，QUALIFY 子句的筛选结果可以基于对数据运行窗口函数的结果。在一个查询中可以同时使用 QUALIFY 和 HAVING 子句。 ## 语法 ``` QUALIFY condition ``` **注意** 如果您在 FROM 子句之后直接使用 QUALIFY 子句，则 FROM 关系名称必须在 QUALIFY 子句之前指定了别名。 ## 示例 本节中的示例使用下面的示例数据。 ``` create table store_sales (ss_sold_date date, ss_sold_time time, ss_item text, ss_sales_price float); insert into store_sales values ('2022-01-01', '09:00:00', 'Product 1', 100.0), ('2022-01-01', '11:00:00', 'Product 2', 500.0), ('2022-01-01', '15:00:00', 'Product 3', 20.0), ('2022-01-01', '17:00:00', 'Product 4', 1000.0), ('2022-01-01', '18:00:00', 'Product 5', 30.0), ('2022-01-02', '10:00:00', 'Product 6', 5000.0), ('2022-01-02', '16:00:00', 'Product 7', 5.0); ``` 以下示例演示了如何查找每天 12:00 之后售出的两件最昂贵的商品。 ``` SELECT * FROM store_sales ss WHERE ss_sold_time > time '12:00:00' QUALIFY row_number() OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) <= 2 ss_sold_date | ss_sold_time | ss_item | ss_sales_price --------------+--------------+-----------+---------------- 2022-01-01 | 17:00:00 | Product 4 | 1000 2022-01-01 | 18:00:00 | Product 5 | 30 2022-01-02 | 16:00:00 | Product 7 | 5 ``` 然后，您可以找到每天售出的最后一件商品。 ``` SELECT * FROM store_sales ss QUALIFY last_value(ss_item) OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) = ss_item; ss_sold_date | ss_sold_time | ss_item | ss_sales_price --------------+--------------+-----------+---------------- 2022-01-01 | 18:00:00 | Product 5 | 30 2022-01-02 | 16:00:00 | Product 7 | 5 ``` 以下示例返回与前一个查询相同的记录，即每天售出的最后一件商品，但它没有使用 QUALIFY 子句。 ``` SELECT * FROM ( SELECT *, last_value(ss_item) OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ss_last_item FROM store_sales ss ) WHERE ss_last_item = ss_item; ss_sold_date | ss_sold_time | ss_item | ss_sales_price | ss_last_item --------------+--------------+-----------+----------------+-------------- 2022-01-02 | 16:00:00 | Product 7 | 5 | Product 7 2022-01-01 | 18:00:00 | Product 5 | 30 | Product 5 ``` # UNION、INTERSECT 和 EXCEPT **Topics** + [语法](#r_UNION-synopsis) + [参数](#r_UNION-parameters) + [集合运算符的计算顺序](#r_UNION-order-of-evaluation-for-set-operators) + [使用说明](#r_UNION-usage-notes) + [示例 UNION 查询](c_example_union_query.md) + [示例 UNION ALL 查询](c_example_unionall_query.md) + [示例 INTERSECT 查询](c_example_intersect_query.md) + [示例 EXCEPT 查询](c_Example_MINUS_query.md) UNION、INTERSECT 和 EXCEPT *集合运算符* 用于比较和合并两个单独的查询表达式的结果。例如，如果您希望知道网站的哪些用户既是买家又是卖家且其用户名存储在单独的列或表中，则可查找这两类用户的*交集*。如果您希望知道哪些网站用户是买家而不是卖家，则可使用 EXCEPT 运算符查找这两个用户列表的*差集*。如果您希望构建一个所有用户的列表（无论角色如何），则可使用 UNION 运算符。 ## 语法 ``` query { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS } query ``` ## 参数 *query* 一个查询表达式，该表达式（采用其选择列表形式）对应于紧跟 UNION、INTERSECT 或 EXCEPT 运算符的第二个查询表达式。这两个表达式必须包含数量相同并且数据类型兼容的输出列；否则，无法比较和合并两个结果集。集合运算不允许不同类别的数据类型之间的隐式转换；有关更多信息，请参阅 [类型兼容性和转换](c_Supported_data_types.md#r_Type_conversion)。 您可以构建包含无限数量的查询表达式并任意组合使用 UNION、INTERSECT 和 EXCEPT 运算符来将这些表达式链接起来的查询。例如，假定表 T1、T2 和 T3 包含兼容的列集，则以下查询结构是有效的： ``` select * from t1 union select * from t2 except select * from t3 order by c1; ``` 联合 从两个查询表达式返回行的集合运算，无论行派生自一个查询表达式还是两个查询表达式。 INTERSECT 返回派生自两个查询表达式的行的集合运算。将丢弃未同时由两个表达式返回的行。 EXCEPT \$1 MINUS 返回派生自两个查询表达式之一的行的集合运算。要符合结果的要求，行必须存在于第一个结果表而不存在于第二个结果表中。MINUS 和 EXCEPT 完全同义。 ALL ALL 关键字保留由 UNION 生成的任何重复行。未使用 ALL 关键字时的默认行为是丢弃这些重复项。不支持 INTERSECT ALL、EXCEPT ALL 和 MINUS ALL。 ## 集合运算符的计算顺序 UNION 和 EXCEPT 集合运算符是左关联的。如果未指定圆括号来影响优先顺序，则将以从左到右的顺序来计算这些集合运算符的组合。例如，在以下查询中，首先计算 T1 和 T2 的 UNION，然后对 UNION 结果执行 EXCEPT 操作： ``` select * from t1 union select * from t2 except select * from t3 order by c1; ``` 在同一个查询中使用运算符组合时，INTERSECT 运算符优先于 UNION 和 EXCEPT 运算符。例如，以下查询将计算 T2 和 T3 的交集，然后计算得到的结果与 T1 的并集： ``` select * from t1 union select * from t2 intersect select * from t3 order by c1; ``` 通过添加圆括号，可以强制实施不同的计算顺序。在以下示例中，将 T1 和 T2 的并集结果与 T3 执行交集运算，并且查询可能会生成不同的结果。 ``` (select * from t1 union select * from t2) intersect (select * from t3) order by c1; ``` ## 使用说明 + 集合运算查询结果中返回的列名是来自第一个查询表达式中的表的列名（或别名）。由于这些列名可能会造成误解（因为列中的值派生自位于集合运算符任一侧的表），您可能需要为结果集提供有意义的别名。 + 集合运算符之前的查询表达式不应包含 ORDER BY 子句。仅在包含集合运算符的查询结尾处使用 ORDER BY 子句时，该子句才会生成有意义的排序结果。在这种情况下，ORDER BY 子句应用于所有集合运算的最终结果。最外层的查询也可以包含标准 LIMIT 和 OFFSET 子句。 + 当集合运算符查询返回小数结果时，将提升对应的结果列以返回相同的精度和小数位数。例如，在以下查询中，T1.REVENUE 为 DECIMAL(10,2) 列而 T2.REVENUE 为 DECIMAL(8,4) 列，小数结果将提升为 DECIMAL(12,4)： ``` select t1.revenue union select t2.revenue; ``` 小数位数为 `4`，因为这是两个列的最大小数位数。精度为 `12`，因为 T1.REVENUE 要求小数点左侧有 8 位数 (12 - 4 = 8)。此类提升可确保 UNION 两侧的所有值都适合结果。对于 64 位值，最大结果精度为 19，最大结果小数位数为 18。对于 128 位值，最大结果精度为 38，最大结果小数位数为 37。 如果生成的数据类型超出 Amazon Redshift 精度和小数位数限制，则查询将返回错误。 + 对于集合运算，如果对于每个相应的列对，两个数据值*相等* 或*都为 NULL*，则两个行将被视为相同。例如，如果表 T1 和 T2 都包含一列和一行，并且两个表中的行都为 NULL，则对这两个表执行的 INTERSECT 运算将返回该行。 # 示例 UNION 查询 在以下 UNION 查询中，SALES 表中的行将与 LISTING 表中的行合并。从每个表中选择三个兼容的列；在这种情况下，对应的列具有相同的名称和数据类型。 最终结果集按 LISTING 表中的第一列进行排序且最多包含 5 个具有最高 LISTID 值的行。 ``` select listid, sellerid, eventid from listing union select listid, sellerid, eventid from sales order by listid, sellerid, eventid desc limit 5; listid | sellerid | eventid --------+----------+--------- 1 | 36861 | 7872 2 | 16002 | 4806 3 | 21461 | 4256 4 | 8117 | 4337 5 | 1616 | 8647 (5 rows) ``` 以下示例说明如何将文本值添加到 UNION 查询的输出，以便您查看哪个查询表达式生成了结果集中的每一行。查询将第一个查询表达式中的行标识为“B”（针对买家），并将第二个查询表达式中的行标识为“S”（针对卖家）。 查询标识门票事务费用等于或大于 \$110000 的买家和卖家。UNION 运算符的任一侧的两个查询表达式之间的唯一差异就是 SALES 表的联接列。 ``` select listid, lastname, firstname, username, pricepaid as price, 'S' as buyorsell from sales, users where sales.sellerid=users.userid and pricepaid >=10000 union select listid, lastname, firstname, username, pricepaid, 'B' as buyorsell from sales, users where sales.buyerid=users.userid and pricepaid >=10000 order by 1, 2, 3, 4, 5; listid | lastname | firstname | username | price | buyorsell --------+----------+-----------+----------+-----------+----------- 209658 | Lamb | Colette | VOR15LYI | 10000.00 | B 209658 | West | Kato | ELU81XAA | 10000.00 | S 212395 | Greer | Harlan | GXO71KOC | 12624.00 | S 212395 | Perry | Cora | YWR73YNZ | 12624.00 | B 215156 | Banks | Patrick | ZNQ69CLT | 10000.00 | S 215156 | Hayden | Malachi | BBG56AKU | 10000.00 | B (6 rows) ``` 以下示例使用 UNION ALL 运算符，因为需要在结果中保留重复行（如果发现重复行）。对于一系列特定的活动 ID，查询为与每个活动关联的每个销售值返回 0 行或多个行，并为该活动的每个列表返回 0 行或 1 个行。活动 ID 对于 LISTING 和 EVENT 表中的每个行是唯一的，但对于 SALES 表中的活动和列表 ID 的相同组合，可能有多个销售值。 结果集中的第三个列标识行的来源。如果行来自 SALES 表，则在 SALESROW 列中将其标记为“Yes”。（SALESROW 是 SALES.LISTID 的别名。） 如果行来自 LISTING 表，则在 SALESROW 列中将其标记为“No”。 在本示例中，结果集包含针对列表 500，活动 7787 的三个销售行。换而言之，将针对此列表和活动组合执行三个不同的事务。其他两个列表（501 和 502）不生成任何销售值，因此只有查询为这些列表 ID 生成的行来自 LISTING 表 (SALESROW = 'No')。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union all select eventid, listid, 'No' from listing where listid in(500,501,502) order by listid asc; eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 7787 | 500 | Yes 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No (6 rows) ``` 如果运行不带 ALL 关键字的相同查询，则结果只保留其中一个销售交易。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union select eventid, listid, 'No' from listing where listid in(500,501,502) order by listid asc; eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No (4 rows) ``` # 示例 UNION ALL 查询 以下示例使用 UNION ALL 运算符，因为需要在结果中保留重复行（如果发现重复行）。对于一系列特定的活动 ID，查询为与每个活动关联的每个销售值返回 0 行或多个行，并为该活动的每个列表返回 0 行或 1 个行。活动 ID 对于 LISTING 和 EVENT 表中的每个行是唯一的，但对于 SALES 表中的活动和列表 ID 的相同组合，可能有多个销售值。 结果集中的第三个列标识行的来源。如果行来自 SALES 表，则在 SALESROW 列中将其标记为“Yes”。（SALESROW 是 SALES.LISTID 的别名。） 如果行来自 LISTING 表，则在 SALESROW 列中将其标记为“No”。 在本示例中，结果集包含针对列表 500，活动 7787 的三个销售行。换而言之，将针对此列表和活动组合执行三个不同的事务。其他两个列表（501 和 502）不生成任何销售值，因此只有查询为这些列表 ID 生成的行来自 LISTING 表 (SALESROW = 'No')。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union all select eventid, listid, 'No' from listing where listid in(500,501,502) order by listid asc; eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 7787 | 500 | Yes 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No (6 rows) ``` 如果运行不带 ALL 关键字的相同查询，则结果只保留其中一个销售交易。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union select eventid, listid, 'No' from listing where listid in(500,501,502) order by listid asc; eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No (4 rows) ``` # 示例 INTERSECT 查询 将以下示例与第一个 UNION 示例进行比较。这两个示例之间的唯一差异是所使用的集合运算符，但结果完全不同。仅其中一行相同： ``` 235494 | 23875 | 8771 ``` 这是在包含 5 行的有限结果中，同时在两个表中找到的唯一行。 ``` select listid, sellerid, eventid from listing intersect select listid, sellerid, eventid from sales order by listid desc, sellerid, eventid limit 5; listid | sellerid | eventid --------+----------+--------- 235494 | 23875 | 8771 235482 | 1067 | 2667 235479 | 1589 | 7303 235476 | 15550 | 793 235475 | 22306 | 7848 (5 rows) ``` 下面的查询查找 3 月份同时在纽约和洛杉矶举办的活动（已销售这些活动的门票）。这两个查询表达式之间的差异是 VENUECITY 列上的约束。 ``` select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='Los Angeles' intersect select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='New York City' order by eventname asc; eventname ---------------------------- A Streetcar Named Desire Dirty Dancing Electra Running with Annalise Hairspray Mary Poppins November Oliver! Return To Forever Rhinoceros South Pacific The 39 Steps The Bacchae The Caucasian Chalk Circle The Country Girl Wicked Woyzeck (16 rows) ``` # 示例 EXCEPT 查询 TICKIT 数据库中的 CATEGORY 表包含以下 11 行： ``` catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts (11 rows) ``` 假定 CATEGORY\$1STAGE 表（临时表）包含一个额外行： ``` catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts 12 | Concerts | Comedy | All stand up comedy performances (12 rows) ``` 返回两个表之间的差异。换而言之，返回 CATEGORY\$1STAGE 表中存在但 CATEGORY 表中不存在的行： ``` select * from category_stage except select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` 以下等效查询使用同义词 MINUS。 ``` select * from category_stage minus select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` 如果反转 SELECT 表达式的顺序，则查询不返回任何行。 # ORDER BY 子句 **Topics** + [语法](#r_ORDER_BY_clause-synopsis) + [参数](#r_ORDER_BY_clause-parameters) + [使用说明](#r_ORDER_BY_usage_notes) + [使用 ORDER BY 的示例](r_Examples_with_ORDER_BY.md) ORDER BY 子句对查询的结果集进行排序。 ## 语法 ``` [ ORDER BY expression [ ASC | DESC ] ] [ NULLS FIRST | NULLS LAST ] [ LIMIT { count | ALL } ] [ OFFSET start ] ``` ## 参数 *expression* 一个表达式，通常情况下通过指定选择列表中的一个或多个列，来定义查询结果集的排序顺序。根据二进制 UTF-8 排序方式返回结果。您也可以指定： + 未在选择列表中的列 + 由查询引用的表中存在的一个或多个列构成的表达式 + 表示选择列表条目的位置（如果不存在选择列表，则为表中列的位置）的序号 + 定义选择列表条目的别名 当 ORDER BY 子句包含多个表达式时，将根据第一个表达式对结果集进行排序，然后将第二个表达式应用于具有第一个表达式中的匹配值的行，以此类推。 ASC \$1 DESC 一个定义表达式的排序顺序的选项，如下所示： + ASC：升序（例如，按数值的从低到高的顺序和字符串的从 A 到 Z 的顺序）。如果未指定选项，则默认情况下将按升序对数据进行排序。 + DESC：降序（按数值的从高到低的顺序和字符串的从 Z 到 A 的顺序）。 NULLS FIRST \$1 NULLS LAST 一个选项，指定是应将 NULL 值排在最前（位于非 null 值之前）还是排在最后（位于非 null 值之后）。默认情况下，按 ASC 顺序最后对 NULL 值进行排序和排名，按 DESC 顺序首先对 NULL 值进行排序和排名。 LIMIT *number* \$1 ALL 一个选项，用于控制查询返回的排序行的数目。LIMIT 数字必须为正整数；最大值为 `2147483647`。 LIMIT 0 不返回任何行。可以使用此语法进行测试：检查查询运行（不显示任何行）或返回表中列的列表。如果使用 LIMIT 0 返回列的列表，则 ORDER BY 子句是多余的。默认值为 LIMIT ALL。 OFFSET *start* 一个选项，指定在开始返回行之前跳过 *start* 前的行数。OFFSET 数字必须为正整数；最大值为 `2147483647`。在与 LIMIT 选项结合使用时，将先跳过 OFFSET 行，然后再开始计算返回的 LIMIT 行数。如果不使用 LIMIT 选项，则结果集中的行数会减少跳过的行数。仍必须扫描 OFFSET 子句跳过的行，因此使用较大的 OFFSET 值可能会非常低效。 ## 使用说明 请注意，使用 ORDER BY 子句时预期会发生以下行为： + NULL 值被视为“高于”所有其他值。对于默认的升序排序顺序，NULL 值将排在最后。要更改此行为，请使用 NULLS FIRST 选项。 + 当查询不包含 ORDER BY 子句时，系统将返回具有不可预测的行顺序的结果集。同一查询执行两次可能会返回具有不同顺序的结果集。 + 可在不使用 ORDER BY 子句的情况下使用 LIMIT 和 OFFSET 选项；不过，要返回一致的行集，请将这两个选项与 ORDER BY 子句结合使用。 + 在任何并行系统（例如 Amazon Redshift）中，当 ORDER BY 不生成唯一排序时，行的顺序是不确定的。也就是说，如果 ORDER BY 表达式生成重复值，则这些行的返回顺序可能会因系统或 Amazon Redshift 运行而异。 + Amazon Redshift 不支持 ORDER BY 子句中的字符串文本。 # 使用 ORDER BY 的示例 返回 CATEGORY 表中的所有 11 行，这些行按第二列 CATGROUP 进行排序。对于具有相同 CATGROUP 值的结果，按字符串长度对 CATDESC 列值进行排序。然后，按列 CATID 和 CATNAME 排序。 ``` select * from category order by 2, length(catdesc), 1, 3; catid | catgroup | catname | catdesc ------+----------+-----------+---------------------------------------- 10 | Concerts | Jazz | All jazz singers and bands 9 | Concerts | Pop | All rock and pop music concerts 11 | Concerts | Classical | All symphony, concerto, and choir conce 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 5 | Sports | MLS | Major League Soccer 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association (11 rows) ``` 返回 SALES 表中的选定列（按最高的 QTYSOLD 值排序）。将结果限制为前 10 行： ``` select salesid, qtysold, pricepaid, commission, saletime from sales order by qtysold, pricepaid, commission, salesid, saletime desc limit 10; salesid | qtysold | pricepaid | commission | saletime --------+---------+-----------+------------+--------------------- 15401 | 8 | 272.00 | 40.80 | 2008-03-18 06:54:56 61683 | 8 | 296.00 | 44.40 | 2008-11-26 04:00:23 90528 | 8 | 328.00 | 49.20 | 2008-06-11 02:38:09 74549 | 8 | 336.00 | 50.40 | 2008-01-19 12:01:21 130232 | 8 | 352.00 | 52.80 | 2008-05-02 05:52:31 55243 | 8 | 384.00 | 57.60 | 2008-07-12 02:19:53 16004 | 8 | 440.00 | 66.00 | 2008-11-04 07:22:31 489 | 8 | 496.00 | 74.40 | 2008-08-03 05:48:55 4197 | 8 | 512.00 | 76.80 | 2008-03-23 11:35:33 16929 | 8 | 568.00 | 85.20 | 2008-12-19 02:59:33 (10 rows) ``` 通过使用 LIMIT 0 语法返回列的列表，但不返回行： ``` select * from venue limit 0; venueid | venuename | venuecity | venuestate | venueseats ---------+-----------+-----------+------------+------------ (0 rows) ``` # CONNECT BY 子句 CONNECT BY 子句指定层次结构中行之间的关系。您可以使用 CONNECT BY 按层次结构顺序选择行，方法是将表联接到表本身并处理分层数据。例如，您可以使用它以递归方式循环浏览组织结构图和列出数据。 分层查询按以下顺序处理： 1. 如果 FROM 子句有联接，则首先对其进行处理。 1. 对 CONNECT BY 子句求值。 1. 对 WHERE 子句求值。 ## 语法 ``` [START WITH start_with_conditions] CONNECT BY connect_by_conditions ``` **注意** 虽然 START 和 CONNECT 不是保留字，但如果您在查询中使用 START 和 CONNECT 作为表别名，请使用分隔标识符（双引号）或 AS，以避免在运行时失败。 ``` SELECT COUNT(*) FROM Employee "start" CONNECT BY PRIOR id = manager_id START WITH name = 'John' ``` ``` SELECT COUNT(*) FROM Employee AS start CONNECT BY PRIOR id = manager_id START WITH name = 'John' ``` ## 参数 *start\$1with\$1conditions* 指定层次结构根行的条件 *connect\$1by\$1conditions* 指定层次结构的父行和子行之间关系的条件。必须使用用于引用父行的 ` ` 一元运算符限定至少一个条件。 ``` PRIOR column = expression -- or expression > PRIOR column ``` ## 运算符 您可以在 CONNECT BY 查询中使用以下运算符。 *LEVEL* 返回层次结构中当前行级别的伪列。为根行返回 1，为根行的子行返回 2，依此类推。 *PRIOR* 一元运算符，用于计算层次结构中当前行的父行的表达式。 ## 示例 以下示例是 CONNECT BY 查询，该示例返回直接或间接向 John 报告的员工数量，不超过 4 个级别。 ``` SELECT id, name, manager_id FROM employee WHERE LEVEL < 4 START WITH name = 'John' CONNECT BY PRIOR id = manager_id; ``` 以下是查询的结果。 ``` id name manager_id ------+----------+-------------- 101 John 100 102 Jorge 101 103 Kwaku 101 110 Liu 101 201 Sofía 102 106 Mateo 102 110 Nikki 103 104 Paulo 103 105 Richard 103 120 Saanvi 104 200 Shirley 104 205 Zhang 104 ``` 此示例的表定义： ``` CREATE TABLE employee ( id INT, name VARCHAR(20), manager_id INT ); ``` 以下是插入到表中的行。 ``` INSERT INTO employee(id, name, manager_id) VALUES (100, 'Carlos', null), (101, 'John', 100), (102, 'Jorge', 101), (103, 'Kwaku', 101), (110, 'Liu', 101), (106, 'Mateo', 102), (110, 'Nikki', 103), (104, 'Paulo', 103), (105, 'Richard', 103), (120, 'Saanvi', 104), (200, 'Shirley', 104), (201, 'Sofía', 102), (205, 'Zhang', 104); ``` 以下是 John 所在部门的组织结构图。 ![\[John 所在部门的组织结构图。\]](http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/images/org-chart.png) # 子查询示例 以下示例说明子查询适合 SELECT 查询的不同方式。有关使用子查询的另一个示例，请参阅[JOIN 示例](r_Join_examples.md)。 ## SELECT 列表子查询 以下示例在 SELECT 列表中包含一个子查询。此子查询是*标量*：它只返回一列和一个值，该值将在从外部查询返回的每个行的结果中重复。此查询将子查询计算出的 Q1SALES 值与外部查询定义的 2008 年其他两个季度（第 2 季度和第 3 季度）的销售值进行比较。 ``` select qtr, sum(pricepaid) as qtrsales, (select sum(pricepaid) from sales join date on sales.dateid=date.dateid where qtr='1' and year=2008) as q1sales from sales join date on sales.dateid=date.dateid where qtr in('2','3') and year=2008 group by qtr order by qtr; qtr | qtrsales | q1sales -------+-------------+------------- 2 | 30560050.00 | 24742065.00 3 | 31170237.00 | 24742065.00 (2 rows) ``` ## WHERE 子句子查询 以下示例在 WHERE 子句中包含一个表子查询。此子查询生成多个行。在本示例中，行只包含一列，但表子查询可以包含多个列和行，就像任何其他表一样。 此查询查找门票销量排名前 10 位的卖家。前 10 位卖家的列表受子查询的限制，这将删除居住在设有售票点的城市的用户。可以使用不同的方式编写此查询；例如，可将子查询重新编写为主查询中的联接。 ``` select firstname, lastname, city, max(qtysold) as maxsold from users join sales on users.userid=sales.sellerid where users.city not in(select venuecity from venue) group by firstname, lastname, city order by maxsold desc, city desc limit 10; firstname | lastname | city | maxsold -----------+-----------+----------------+--------- Noah | Guerrero | Worcester | 8 Isadora | Moss | Winooski | 8 Kieran | Harrison | Westminster | 8 Heidi | Davis | Warwick | 8 Sara | Anthony | Waco | 8 Bree | Buck | Valdez | 8 Evangeline | Sampson | Trenton | 8 Kendall | Keith | Stillwater | 8 Bertha | Bishop | Stevens Point | 8 Patricia | Anderson | South Portland | 8 (10 rows) ``` ## WITH 子句子查询 请参阅 [WITH 子句](r_WITH_clause.md)。 # 关联的子查询 以下示例将*关联子查询* 包含在 WHERE 子句中；此类型的子查询包含其列与由外部查询生成的列之间的一个或多个关联。在本示例中，关联为 `where s.listid=l.listid`。对于外部查询生成的每一行，将执行子查询以限定或取消限定行。 ``` select salesid, listid, sum(pricepaid) from sales s where qtysold= (select max(numtickets) from listing l where s.listid=l.listid) group by 1,2 order by 1,2 limit 5; salesid | listid | sum --------+--------+---------- 27 | 28 | 111.00 81 | 103 | 181.00 142 | 149 | 240.00 146 | 152 | 231.00 194 | 210 | 144.00 (5 rows) ``` ## 不支持的关联子查询模式 查询计划程序使用名为“子查询去相关性”的查询重写方法来优化多个关联子查询模式以便在 MPP 环境中执行。有多种类型的关联子查询采用 Amazon Redshift 无法去相关性且不支持的模式。包含以下关联引用的查询会返回错误： + 跳过查询块的关联引用，也称为“跨级关联引用”。例如，在以下查询中，包含关联引用的块与跳过的块由 NOT EXISTS 谓词连接： ``` select event.eventname from event where not exists (select * from listing where not exists (select * from sales where event.eventid=sales.eventid)); ``` 在本示例中，跳过的块是针对 LISTING 表执行的子查询。关联引用将 EVENT 表和 SALES 表关联起来。 + 来自作为外部查询中 ON 子句的一部分的子查询的关联引用： ``` select * from category left join event on category.catid=event.catid and eventid = (select max(eventid) from sales where sales.eventid=event.eventid); ``` ON 子句包含从子查询中的 SALES 到外部查询中的 EVENT 的关联引用。 + 针对 Amazon Redshift 系统表的 Null 敏感型关联引用。例如： ``` select attrelid from stv_locks sl, pg_attribute where sl.table_id=pg_attribute.attrelid and 1 not in (select 1 from pg_opclass where sl.lock_owner = opcowner); ``` + 来自包含窗口函数的子查询内部的关联引用。 ``` select listid, qtysold from sales s where qtysold not in (select sum(numtickets) over() from listing l where s.listid=l.listid); ``` + GROUP BY 列中对关联查询结果的引用。例如： ``` select listing.listid, (select count (sales.listid) from sales where sales.listid=listing.listid) as list from listing group by list, listing.listid; ``` + 来自带聚合函数和 GROUP BY 子句（通过 IN 谓词连接到外部查询）的关联引用。（此限制不适用于 MIN 和 MAX 聚合函数。） 例如： ``` select * from listing where listid in (select sum(qtysold) from sales where numtickets>4 group by salesid); ```