O Amazon Redshift não permitirá mais a criação de UDFs do Python a partir do Patch 198. As UDFs do Python existentes continuarão a funcionar normalmente até 30 de junho de 2026. Para ter mais informações, consulte a [publicação de blog ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # SELECT Retorna linhas de tabelas, exibições e funções definidas pelo usuário. **nota** O tamanho máximo de uma única instrução SQL é 16 MB. ## Sintaxe ``` [ 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** + [ ## Sintaxe ](#r_SELECT_synopsis-synopsis) + [ # Cláusula WITH ](r_WITH_clause.md) + [ # Lista SELECT ](r_SELECT_list.md) + [ # EXCLUDE column\$1list ](r_EXCLUDE_list.md) + [ # Cláusula FROM ](r_FROM_clause30.md) + [ # Cláusula WHERE ](r_WHERE_clause.md) + [ # Cláusula GROUP BY ](r_GROUP_BY_clause.md) + [ # Cláusula HAVING ](r_HAVING_clause.md) + [ # Cláusula QUALIFY ](r_QUALIFY_clause.md) + [ # UNION, INTERSECT e EXCEPT ](r_UNION.md) + [ # Cláusula ORDER BY ](r_ORDER_BY_clause.md) + [ # Cláusula CONNECT BY ](r_CONNECT_BY_clause.md) + [ # Exemplos de subconsulta ](r_Subquery_examples.md) + [ # Subconsultas correlacionadas ](r_correlated_subqueries.md) # Cláusula WITH Uma cláusula WITH é uma cláusula opcional que precede a lista SELECT em uma consulta. A cláusula WITH define um ou mais *common\$1table\$1expressions*. Cada expressão de tabela comum (CTE) define uma tabela temporária, que é semelhante à definição de visualização. Você pode fazer referência a essas tabelas temporárias na cláusula FROM. Eles são usados apenas enquanto a consulta a que pertencem é executada. Cada CTE na cláusula WITH especifica um nome de tabela, uma lista opcional de nomes de coluna e uma expressão de consulta que é avaliada como uma tabela (uma instrução SELECT). Quando você faz referência ao nome da tabela temporária na cláusula FROM da mesma expressão de consulta que a define, o CTE é recursivo. Subconsultas da cláusula WITH são uma forma eficiente de definir tabelas que podem ser usadas ao longo da execução de uma consulta. Em todos os casos, os mesmos resultados podem ser obtidos usando subconsultas no corpo principal da instrução SELECT, mas pode ser mais simples fazer leituras ou gravações de subconsultas da cláusula WITH. Sempre que possível, subconsultas da cláusula WITH por várias vezes referidas são aperfeiçoadas como subexpressões comuns, ou seja, é possível avaliar uma subconsulta WITH uma vez e reutilizar seus resultados. (Observe que subexpressões comuns não estão limitadas àquelas definidas na cláusula WITH.) ## Sintaxe ``` [ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ] ``` Onde *common\$1table\$1expression* pode ser não recursivo ou recursivo. Segue-se a forma não recursiva: ``` CTE_table_name [ ( column_name [, ...] ) ] AS ( query ) ``` Segue-se a forma recursiva de *common\$1table\$1expression*: ``` CTE_table_name (column_name [, ...] ) AS ( recursive_query ) ``` ## Parâmetros RECURSIVE Palavra-chave que identifica a consulta como um CTE recursivo. Esta palavra-chave é necessária se qualquer *common\$1table\$1expression* definido na cláusula WITH for recursivo. Você só pode especificar a palavra-chave RECURSIVE uma vez, imediatamente após a palavra-chave WITH, mesmo quando a cláusula WITH contém várias CTEs recursivas. Em geral, um CTE recursivo é uma subconsulta UNION ALL com duas partes. *common\$1table\$1expression* Define uma tabela temporária que você pode fazer referência no [Cláusula FROM](r_FROM_clause30.md) e é usado somente durante a execução da consulta a qual pertence. *CTE\$1table\$1name* Um nome exclusivo para uma tabela temporária que define os resultados da subconsulta de cláusula WITH. Você não pode usar nomes duplicados em uma única cláusula WITH. Cada subconsulta deve ter um nome de tabela que pode mencionado em [Cláusula FROM](r_FROM_clause30.md). *column\$1name* Uma lista de nomes de colunas de saída para a subconsulta da cláusula WITH, separados por vírgulas. O número de nomes de coluna especificados deve ser igual ou menor que o número de colunas definido pela subconsulta. Para um CTE que não é recursivo, a cláusula *column\$1name* é opcional. Para um CTE recursivo, a lista *column\$1name* é necessária. *query* Qualquer consulta SELECT compatível com o Amazon Redshift. Consulte [SELECT](r_SELECT_synopsis.md). *recursive\$1query* Uma consulta UNION ALL que consiste em duas subconsultas SELECT: + A primeira subconsulta SELECT não tem uma referência recursiva para o mesmo *CTE\$1table\$1name*. Ele retorna um conjunto de resultados que é a semente inicial da recursão. Esta parte é chamada de membro inicial ou membro semente. + A segunda subconsulta SELECT faz referência ao mesmo *CTE\$1table\$1name* em sua cláusula FROM. Isso é chamado de membro recursivo. A *recursive\$1query* contém uma condição WHERE para finalizar a *recursive\$1query*. ## Observações de uso Você pode usar a cláusula WITH nas seguintes instruções SQL: + SELECT + SELECT INTO + CREATE TABLE AS + CREATE VIEW + DECLARE + EXPLAIN + INSERT INTO...SELECT + PREPARE + UPDATE (em uma subconsulta cláusula WHERE não é possível definir um CTE recursivo na subconsulta. O CTE recursivo deve preceder a cláusula UPDATE.) + DELETE Se a cláusula FROM de uma consulta que contém a cláusula WITH não fizer referência a qualquer das tabelas definidas pela cláusula WITH, a cláusula WITH será ignorada e a consulta será executada como normal. Uma tabela definida por uma subconsulta de cláusula WITH somente pode ser referida no escopo da consulta SELECT iniciada pela cláusula WITH. Por exemplo, você pode fazer referência a essa tabela na cláusula FROM da subconsulta na lista SELECT, na cláusula WHERE ou na cláusula HAVING. Você não pode usar a cláusula WITH em uma subconsulta e fazer referência à sua tabela na cláusula FROM da consulta principal ou de outra subconsulta. Este padrão de consulta resulta em uma mensagem de erro do formulário `relation table_name doesn't exist` para a tabela da cláusula WITH. Você não pode especificar outra cláusula WITH em uma subconsulta de cláusula WITH. Você não pode fazer referência antecipada a tabelas definidas por subconsultas da cláusula WITH. Por exemplo, a consulta a seguir retorna um erro devido à referência antecipada para a tabela W2 na definição da tabela W1: ``` with w1 as (select * from w2), w2 as (select * from w1) select * from sales; ERROR: relation "w2" does not exist ``` A subconsulta de cláusula WITH pode não consistir em uma instrução SELECT INTO. No entanto, você pode usar uma cláusula WITH em uma instrução SELECT INTO. ## Expressões de tabela comuns recursivas Uma *expressão de tabela comum (CTE)* recursiva é um CTE que faz referência a si próprio. Um CTE recursivo é útil na consulta de dados hierárquicos, como organogramas que mostram relações de relatório entre funcionários e gerentes. Consulte [Exemplo: CTE recursivo](#r_WITH_clause-recursive-cte-example). Outro uso comum é uma lista de materiais multinível, quando um produto consiste em muitos componentes e cada componente também consiste em outros componentes ou submontagens. Certifique-se de limitar a profundidade da recursão incluindo uma cláusula WHERE na segunda subconsulta SELECT da consulta recursiva. Para ver um exemplo, consulte [Exemplo: CTE recursivo](#r_WITH_clause-recursive-cte-example). Caso contrário, um erro pode ocorrer semelhante ao seguinte: + `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.` **nota** `max_recursion_rows` é um parâmetro que define o número máximo de linhas que um CTE recursivo pode retornar para evitar loops de recursão infinita. Não recomendamos alterar esse parâmetro para um valor maior do que o padrão. Isso impede que problemas de recursão infinita em suas consultas ocupem espaço excessivo em seu cluster. Você pode especificar uma ordem de classificação e limitar o resultado do CTE recursivo. Você pode incluir opções de grupo por e distintas no resultado final do CTE recursivo. Você não pode especificar outra cláusula WITH em uma subconsulta de cláusula WITH. A *recursive\$1query* não pode incluir uma cláusula de ordem por ou limite. ## Exemplos O exemplo a seguir mostra o caso mais simples possível de uma consulta que contém uma cláusula WITH. A consulta WITH com o nome VENUECOPY seleciona todas as linhas da tabela VENUE. Por sua vez, a consulta principal seleciona todas as linhas de VENUECOPY. A tabela VENUECOPY existe somente durante a consulta. ``` 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) ``` O exemplo a seguir mostra uma cláusula WITH que produz duas tabelas, chamadas VENUE\$1SALES e TOP\$1VENUES. A segunda tabela de consulta WITH seleciona a partir da primeira. Por sua vez, a cláusula WHERE do bloco principal de consulta contém um subconsulta que restringe a tabela 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) ``` Os dois exemplos a seguir demonstram as regras para o escopo de referências de tabela com base subconsultas da cláusula WITH. A primeira consulta é executada, mas a segunda falha com um erro esperado. A primeira consulta tem a subconsulta de cláusula WITH na lista SELECT da consulta principal. A tabela definida pela cláusula WITH (HOLIDAYS) é referida na cláusula FROM da subconsulta na lista SELECT: ``` 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) ``` A segunda consulta falha porque tenta fazer referência à tabela HOLIDAYS na consulta principal, assim como na subconsulta da lista SELECT. As referências principais da consulta estão fora do escopo. ``` 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 ``` ## Exemplo: CTE recursivo Veja a seguir um exemplo de um CTE recursivo que retorna os funcionários que respondem direta ou indiretamente a John. A consulta recursiva contém uma cláusula WHERE para limitar a profundidade da recursão a menos de 4 níveis. ``` --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; ``` A seguir é o resultado da consulta. ``` 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 ``` A seguir está um organograma para o departamento de John. ![\[Um organograma do departamento de John.\]](http://docs.aws.amazon.com/pt_br/redshift/latest/dg/images/org-chart.png) # Lista SELECT **Topics** + [ ## Sintaxe ](#r_SELECT_list-synopsis) + [ ## Parâmetros ](#r_SELECT_list-parameters) + [ ## Observações de uso ](#r_SELECT_list_usage_notes) + [ ## Exemplos ](#r_SELECT_list-examples) A lista SELECT nomeia as colunas, funções e expressões que você deseja que a consulta retorne. A lista representa o resultado da consulta. Para obter mais informações sobre funções SQL, consulte [Referência de funções SQL](c_SQL_functions.md). Para obter mais informações sobre expressões, consulte [Expressões condicionais](c_conditional_expressions.md). ## Sintaxe ``` SELECT [ TOP number ] [ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...] ``` ## Parâmetros TOP *número* TOP pega um inteiro positivo como argumento, que define o número de linhas retornadas para o cliente. O comportamento da cláusula TOP é o mesmo da cláusula LIMIT. O número de linhas retornado é fixo, mas o conjunto de linhas não. Para retornar um conjunto consistente de linhas, use TOP ou LIMIT em conjunto com uma cláusula ORDER BY. ALL Palavra-chave redundante que define o comportamento padrão se você não especificar DISTINCT. `SELECT ALL *` é o mesmo que `SELECT *` (seleciona todas as linhas para todas as colunas e retém duplicações). DISTINCT Opção que elimina linhas duplicadas do conjunto de resultados, com base em valores correspondentes em uma ou mais colunas. Se sua aplicação permitir chaves primárias ou chaves estrangeiras inválidas, isso pode fazer com que algumas consultas retornem resultados incorretos. Por exemplo, uma consulta SELECT DISTINCT pode retornar linhas duplicadas se a coluna chave primária não contiver todos os valores exclusivos. Para obter mais informações, consulte [Definir restrições de tabela](https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html). \$1 (asterisco) Retorna o conteúdo total da tabela (todas as colunas e todas as linhas). *expressão* Expressão formada por uma ou mais colunas que existem em tabelas referidas pela consulta. Uma expressão pode conter funções SQL. Por exemplo: ``` avg(datediff(day, listtime, saletime)) ``` AS *alias\$1coluna* Nome temporário da coluna que é usada no conjunto de resultados finais. A palavra-chave AS é opcional. Por exemplo: ``` avg(datediff(day, listtime, saletime)) as avgwait ``` Se você não especificar um alias para uma expressão que não for um nome de coluna simples, o resultado definido aplicará um nome padrão à coluna. O alias é reconhecido logo após ser definido na lista de destino. É possível usar um alias em outras expressões definidas depois dele na mesma lista de destino. Isso é ilustrado no exemplo a seguir. ``` select clicks / impressions as probability, round(100 * probability, 1) as percentage from raw_data; ``` O benefício da referência do alias lateral é que você não precisa repetir a expressão usada como alias ao criar expressões mais complexas na mesma lista de destino. Quando o Amazon Redshift analisa esse tipo de referência, ele apenas alinha os aliases definidos anteriormente. Se houver uma coluna com o mesmo nome definido na cláusula `FROM` como a expressão usada como alias anteriormente, a coluna na cláusula `FROM` terá prioridade. Por exemplo, se na consulta acima houver uma coluna chamada “probabilidade” na tabela raw\$1data, a “probabilidade” na segunda expressão da lista de destino faz referência àquela coluna, em vez do nome do alias “probabilidade”. ## Observações de uso TOP é uma extensão SQL que fornece uma alternativa ao comportamento de LIMIT. Você não pode usar TOP e LIMIT na mesma consulta. ## Exemplos O exemplo a seguir retorna dez linhas da tabela SALES. Embora a consulta use a cláusula TOP, ela ainda retorna um conjunto imprevisível de linhas porque nenhuma cláusula ORDER BY foi especificada, ``` select top 10 * from sales; ``` A consulta a seguir é funcionalmente equivalente, mas usa uma cláusula LIMIT em vez de uma cláusula TOP: ``` select * from sales limit 10; ``` O exemplo a seguir retorna as dez primeiras linhas da tabela SALES usando a cláusula TOP, classificadas pela coluna QTYSOLD em ordem decrescente. ``` 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) ``` O exemplo a seguir retorna os dois primeiros valores de QTYSOLD e SELLERID da tabela SALES, classificados pela coluna QTYSOLD: ``` select top 2 qtysold, sellerid from sales order by qtysold desc, sellerid; qtysold | sellerid --------+---------- 8 | 518 8 | 520 (2 rows) ``` O exemplo a seguir mostra a lista de grupos de categorias distintos da tabela 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) ``` O exemplo a seguir retorna o conjunto distinto de números da semana de dezembro de 2008. Sem a cláusula DISTINCT, a declaração retornaria 31 linhas, ou uma para cada dia do mês. ``` 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 O EXCLUDE column\$1list nomeia as colunas excluídas dos resultados da consulta. Usar a opção EXCLUDE é útil quando somente um subconjunto de colunas precisa ser excluído de uma tabela *ampla*, que é uma tabela que contém muitas colunas. **Topics** + [ ## Sintaxe ](#r_EXCLUDE_list-synopsis) + [ ## Parâmetros ](#r_EXCLUDE_list-parameters) + [ ## Exemplos ](#r_EXCLUDE_list-examples) ## Sintaxe ``` EXCLUDE column_list ``` ## Parâmetros *column\$1list* Uma lista separada por vírgulas de um ou mais nomes de coluna que existem nas tabelas usadas como referência pela consulta. A *column\$1list* pode ser colocada entre parênteses. Somente nomes de coluna são aceitos na lista de exclusão de nomes de coluna, não expressões, como `upper(col1)`, ou asterisco (\$1). ``` column-name, ... | ( column-name, ... ) ``` Por exemplo: ``` SELECT * EXCLUDE col1, col2 FROM tablea; ``` ``` SELECT * EXCLUDE (col1, col2) FROM tablea; ``` ## Exemplos Os exemplos a seguir usam a tabela SALES que contém as colunas: salesid, listid, sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission e saletime. Para ter mais informações sobre a tabela SALES, consulte [Banco de dados de exemplo](c_sampledb.md). O exemplo a seguir exibe linhas da tabela SALES, mas exclui a coluna 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 ... ``` O exemplo a seguir exibe linhas da tabela SALES, mas exclui as colunas QTYSOLD e 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 ... ``` O exemplo a seguir cria uma visão que exibe linhas da tabela SALES, mas exclui a coluna 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 ... ``` O exemplo a seguir seleciona somente as colunas não excluídas em uma tabela temporária. ``` 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 ... ``` # Cláusula FROM A cláusula FROM em uma consulta lista as referências de tabela (tabelas, exibições e subconsultas) de onde os dados são selecionados. Se as referências de várias tabelas estiverem listadas, as tabelas devem ser juntadas, usando a sintaxe apropriada na cláusula FROM ou WHERE. Se nenhum critério de junção for especificado, o sistema processará a consulta como uma junção cruzada (produto cartesiano). **Topics** + [ ## Sintaxe ](#r_FROM_clause30-synopsis) + [ ## Parâmetros ](#r_FROM_clause30-parameters) + [ ## Observações de uso ](#r_FROM_clause_usage_notes) + [ # Exemplos de PIVOT e UNPIVOT ](r_FROM_clause-pivot-unpivot-examples.md) + [ # Exemplos de JOIN ](r_Join_examples.md) + [ # Exemplos de UNNEST ](r_FROM_clause-unnest-examples.md) ## Sintaxe ``` FROM table_reference [, ...] ``` onde *referência\$1tabela* é uma das seguintes: ``` 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] ) ``` O *table\$1alias* opcional pode ser usado para fornecer nomes temporários a tabelas e referências de tabelas complexas e, se desejado, também às respectivas colunas, da forma a seguir: ``` [ AS ] alias [ ( column_alias [, ...] ) ] ``` ## Parâmetros *com\$1subconsulta\$1nome\$1tabela* Tabela definida por uma subconsulta em [Cláusula WITH](r_WITH_clause.md). *table\$1name* Nome de uma tabela ou exibição. *alias* Nome alternativo temporário para uma tabela ou exibição. Um alias deve ser fornecido para uma tabela derivada de uma subconsulta. Em outras referências de tabela, os alias são opcionais. A palavra-chave AS é sempre opcional. Os alias de tabela oferecem um atalho conveniente para tabelas de identificação em outras partes de uma consulta, como a cláusula WHERE. Por exemplo: ``` select * from sales s, listing l where s.listid=l.listid ``` *column\$1alias* Nome alternativo temporário para uma coluna em uma tabela ou exibição. *subconsulta* Uma expressão de consulta que avalia para uma tabela. A tabela existe somente pela duração da consulta e geralmente recebe um nome ou *alias*. No entanto, um alias não é necessário. Você também pode definir nomes de colunas para tabelas que derivam de subconsultas. Nomear aliases de coluna é importante quando você deseja participar dos resultados de subconsultas a outras tabelas e quando você deseja selecionar ou restringir essas colunas em outro lugar da consulta. Uma subconsulta pode conter uma cláusula ORDER BY, mas essa cláusula poderá não ter qualquer efeito se uma cláusula LIMIT ou OFFSET também não estiver especificada. NATURAL Define um junção que usa automaticamente todos os pares de colunas com nomes idênticos em duas tabelas como colunas de junção. Nenhuma condição explícita de junção é necessária. Por exemplo, se as tabelas CATEGORY e EVENT apresentam colunas com nome CATID, um junção natural dessas tabelas é um junção pelas colunas CATID. Se uma junção NATURAL for especificada mas não existirem pares de colunas com o mesmo nome nas tabelas a serem juntadas, a junção padrão da consulta usada será a junção cruzada. *join\$1type* Especifique um dos seguintes tipos de junção: + [INNER] JOIN + LEFT [OUTER] JOIN + RIGHT [OUTER] JOIN + FULL [OUTER] JOIN + CROSS JOIN As junções cruzadas são junções não qualificadas; elas retornam o produto cartesiano das duas tabelas. As junções internas e externas são junções qualificadas. Elas podem ser qualificadas implicitamente (em junções naturais); com a sintaxe ON ou USING na cláusula FROM; ou com a condição de cláusula WHERE. Uma junção interna retorna somente linhas correspondentes, com base na condição de junção ou na lista de colunas de junção. Uma junção externa retorna todas as linhas que a junção interna equivalente deve retornar e linhas não correspondentes da tabela "esquerda", da tabela "direita" ou de ambas. A tabela esquerda é a primeira tabela listada, e a tabela direita é a segunda tabela listada. As linhas não correspondentes contêm valores NULL para preencher lacunas entre as colunas resultantes. ON *condição\$1junção* Tipo de especificação de junção em que as colunas a serem juntadas são exibidas como uma condição que acompanha a palavra-chave ON. Por exemplo: ``` sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid ``` USING ( *coluna\$1junção* [, ...] ) Tipo de especificação de junção em que as colunas a serem juntadas estão listadas entre parênteses. Se várias colunas a serem juntadas forem especificadas, elas serão separadas por vírgulas. A palavra-chave USING deve preceder a lista. Por exemplo: ``` sales join listing using (listid,eventid) ``` PIVOT Alterna a saída de linhas para colunas, com a finalidade de representar dados tabulares em um formato de fácil leitura. A saída é representada horizontalmente em várias colunas. PIVOT é semelhante a uma consulta GROUP BY com uma agregação, usando uma expressão agregada para especificar um formato de saída. Porém, diferente de GROUP BY, os resultados são retornados em colunas em vez de linhas. Para obter exemplos que mostrem como consultar com PIVOT e UNPIVOT, consulte [Exemplos de PIVOT e UNPIVOT](r_FROM_clause-pivot-unpivot-examples.md). UNPIVOT *Transformar colunas em linhas com UNPIVOT*: o operador transforma as colunas de resultados de uma tabela de entrada ou os resultados de uma consulta em linhas, para facilitar a leitura da saída. UNPIVOT combina os dados das colunas de entrada em duas colunas de resultado: uma coluna de nome e uma coluna de valor. A coluna name contém nomes de coluna da entrada, como entradas de linha. A coluna value contém valores das colunas de entrada, como resultados de uma agregação. Por exemplo, as contagens de itens em várias categorias. *Desagregar objetos com UNPIVOT (SUPER)*: é possível desagregar objetos; nesse caso, a *expressão* é uma expressão SUPER referente a outro item da cláusula FROM. Para obter mais informações, consulte [Transformar colunas em linhas de objetos](query-super.md#unpivoting). Também há exemplos que mostram como consultar dados semiestruturados, como dados formatados em JSON. *super\$1expression* Uma expressão SUPER válida. O Amazon Redshift exibe uma linha para cada valor no atributo especificado. Para ter mais informações sobre o tipo de dado SUPER, consulte [Tipo SUPER](r_SUPER_type.md). Para ter mais informações sobre valores SUPER desaninhados, consulte [Desaninhar consultas](query-super.md#unnest). *attribute\$1name* O nome de um atributo na expressão SUPER. *index\$1alias* Alias para o índice que significa a posição do valor na expressão SUPER. UNNEST Expande uma estrutura aninhada, normalmente uma matriz SUPER, em colunas que contêm os elementos não aninhados. Para ter mais informações sobre desagrupamento de dados SUPER, consulte [Consultar dados semiestruturados](query-super.md). Para obter exemplos, consulte [Exemplos de UNNEST](r_FROM_clause-unnest-examples.md). *unnested\$1column\$1name* O nome da coluna que contém os elementos não aninhados. UNNEST ... WITH OFFSET Adiciona uma coluna de deslocamento à saída não aninhada, e o deslocamento representa o índice baseado em zero de cada elemento na matriz. Essa variante é útil quando você deseja ver a posição dos elementos em uma matriz. Para ter mais informações sobre desagrupamento de dados SUPER, consulte [Consultar dados semiestruturados](query-super.md). Para obter exemplos, consulte [Exemplos de UNNEST](r_FROM_clause-unnest-examples.md). *offset\$1column\$1name* Um nome personalizado para a coluna de deslocamento que permite definir explicitamente como a coluna de índice aparecerá na saída. Esse parâmetro é opcional. Por padrão, o nome da coluna de deslocamento é `offset_col`. ## Observações de uso Colunas de junção devem ter tipos de dados comparáveis. Uma junção NATURAL ou USING retém somente um de cada par de colunas de junção no conjunto de resultados intermediário. Uma junção com a sintaxe ON retém ambas as colunas de junção em seu conjunto de resultados intermediário. Consulte também [Cláusula WITH](r_WITH_clause.md). # Exemplos de PIVOT e UNPIVOT PIVOT e UNPIVOT são parâmetros na cláusula FROM que trocam a saída da consulta de linhas para colunas e colunas para linhas, respectivamente. Eles representam resultados de consultas tabulares em um formato fácil de ler. Os exemplos a seguir usam consultas e dados de teste para mostrar como usá-los. Para obter mais informações sobre esses parâmetros, consulte [FROM clause](https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause30.html). ## Exemplos de PIVOT Configure a tabela e os dados de exemplo e use-os para executar as consultas de exemplo subsequentes. ``` 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); ``` PIVOT em `partname` com um agregação de `AVG` em `price`. ``` SELECT * FROM (SELECT partname, price FROM part) PIVOT ( AVG(price) FOR partname IN ('prop', 'rudder', 'wing') ); ``` A consulta resulta na saída a seguir. ``` prop | rudder | wing ---------+----------+--------- 10.33 | 2.71 | 11.50 ``` No exemplo anterior, os resultados são transformados em colunas. O exemplo a seguir mostra uma consulta `GROUP BY` que retorna os preços médios em linhas, em vez de em colunas. ``` SELECT partname, avg(price) FROM (SELECT partname, price FROM part) WHERE partname IN ('prop', 'rudder', 'wing') GROUP BY partname; ``` A consulta resulta na saída a seguir. ``` partname | avg ----------+------- prop | 10.33 rudder | 2.71 wing | 11.50 ``` Um exemplo de `PIVOT` com `manufacturer` como uma coluna implícita. ``` SELECT * FROM (SELECT quality, manufacturer FROM part) PIVOT ( count(*) FOR quality IN (1, 2, NULL) ); ``` A consulta resulta na saída a seguir. ``` manufacturer | 1 | 2 | null -------------------+----+----+------ local parts co | 1 | 1 | 1 big parts co | 1 | 1 | 1 small parts co | 1 | 0 | 2 ``` Colunas da tabela de entrada que não são referenciadas na definição `PIVOT` são adicionadas implicitamente à tabela de resultados. Este é o caso da coluna `manufacturer` no exemplo anterior. O exemplo também mostra que `NULL` é um valor válido para o operador `IN`. `PIVOT` no exemplo acima retorna informações semelhantes à consulta a seguir, que inclui `GROUP BY`. A diferença é que `PIVOT` retorna o valor `0` para a coluna `2` e o fabricante `small parts co`. A consulta `GROUP BY` não contém uma linha correspondente. Na maioria dos casos, `PIVOT` insere `NULL` se uma linha não tem dados de entrada para determinada coluna. Porém, o agregado de contagem não retorna `NULL` e `0` é o valor padrão. ``` 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; ``` A consulta resulta na saída a seguir. ``` 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 ``` O operador PIVOT aceita aliases opcionais na expressão agregada e em cada valor para o operador `IN`. Use aliases para personalizar os nomes das colunas. Se não houver um alias agregado, somente os aliases da lista `IN` serão usados. Caso contrário, o alias agregado será anexado ao nome da coluna com um sublinhado para separar os nomes. ``` SELECT * FROM (SELECT quality, manufacturer FROM part) PIVOT ( count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na) ); ``` A consulta resulta na saída a seguir. ``` 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 ``` Configure a tabela e os dados de exemplo a seguir e use-os para executar as consultas de exemplo subsequentes. Os dados representam datas de reserva para um grupo de hotéis. ``` 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); ``` Nesse exemplo de consulta, os registros de reservas são calculados para fornecer um total de cada semana. A data de término de cada semana se torna um nome de coluna. ``` 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') ); ``` A consulta resulta na saída a seguir. ``` 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 ``` O Amazon Redshift não é compatível com CROSSTAB para girar em várias colunas. No entanto, é possível alterar dados de linha em colunas, de forma semelhante a uma agregação com PIVOT, com uma consulta como a seguinte. Isso usa os mesmos dados de exemplo de reserva como no exemplo anterior. ``` 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; ``` O exemplo de consulta resulta em datas de reserva listadas ao lado de frases curtas que indicam quais hotéis estão reservados. ``` 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 | ``` Veja a seguir as observações de uso do `PIVOT`: + `PIVOT` pode ser aplicado a tabelas, subconsultas e expressões de tabela comuns (CTEs). `PIVOT` não pode ser aplicado a expressões `JOIN`, CTEs recursivos, `PIVOT` ou expressões `UNPIVOT`. Também não são compatíveis expressões `SUPER` não aninhadas e tabelas aninhadas do Redshift Spectrum. + `PIVOT` é compatível com funções agregadas `COUNT`, `SUM`, `MIN`, `MAX` e `AVG`. + A expressão agregada `PIVOT` deve ser uma chamada de uma função agregada compatível. Expressões complexas na parte superior do agregado não são compatíveis. Os argumentos agregados não podem conter referências a tabelas diferentes da tabela de entrada do `PIVOT`. Referências correlacionadas a uma consulta principal também não são compatíveis. O argumento agregado pode conter subconsultas. Elas podem ser correlacionadas internamente ou na tabela de entrada `PIVOT`. + Os valores da lista `PIVOT IN` não podem ser referências de coluna ou subconsultas. Cada valor deve ser compatível com a referência de coluna `FOR`. + Se os valores de lista `IN` não tiverem aliases, `PIVOT` gerará nomes de coluna padrão. Por valores `IN` constantes, como 'abc' ou 5, o nome da coluna padrão é a constante em si. Para qualquer expressão complexa, o nome da coluna é um nome padrão do Amazon Redshift, como `?column?`. ## Exemplos de UNPIVOT Configure os dados de exemplo e use-os para executar os exemplos subsequentes. ``` 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` nas colunas de entrada vermelho, verde e azul. ``` SELECT * FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT ( cnt FOR color IN (red, green, blue) ); ``` A consulta resulta na saída a seguir. ``` color | cnt -------+----- red | 15 red | 35 red | 10 green | 20 green | 23 blue | 7 blue | 40 ``` Por padrão, os valores `NULL` na coluna de entrada são ignorados e não produzem uma linha de resultado. O exemplo a seguir mostra `UNPIVOT` com `INCLUDE NULLS`. ``` SELECT * FROM ( SELECT red, green, blue FROM count_by_color ) UNPIVOT INCLUDE NULLS ( cnt FOR color IN (red, green, blue) ); ``` A seguir está a saída resultante. ``` color | cnt -------+----- red | 15 red | 35 red | 10 green | 20 green | green | 23 blue | 7 blue | 40 blue | ``` Se o parâmetro `INCLUDING NULLS` estiver definido, os valores de entrada `NULL` geram linhas de resultados. `The following query shows UNPIVOT` com `quality` como uma coluna implícita. ``` SELECT * FROM count_by_color UNPIVOT ( cnt FOR color IN (red, green, blue) ); ``` A consulta resulta na saída a seguir. ``` quality | color | cnt ---------+-------+----- high | red | 15 normal | red | 35 low | red | 10 high | green | 20 low | green | 23 high | blue | 7 normal | blue | 40 ``` Colunas da tabela de entrada que não são referenciadas na definição `UNPIVOT` são adicionadas implicitamente à tabela de resultados. No exemplo, este é o caso da coluna `quality`. O exemplo a seguir mostra `UNPIVOT` com aliases para valores na lista `IN`. ``` SELECT * FROM count_by_color UNPIVOT ( cnt FOR color IN (red AS r, green AS g, blue AS b) ); ``` A consulta anterior resulta na saída a seguir. ``` quality | color | cnt ---------+-------+----- high | r | 15 normal | r | 35 low | r | 10 high | g | 20 low | g | 23 high | b | 7 normal | b | 40 ``` O operador `UNPIVOT` aceita aliases opcionais em cada valor de lista `IN`. Cada alias fornece personalização dos dados em cada coluna `value`. Veja a seguir as observações de uso do `UNPIVOT`. + `UNPIVOT` pode ser aplicado a tabelas, subconsultas e expressões de tabela comuns (CTEs). `UNPIVOT` não pode ser aplicado a expressões `JOIN`, CTEs recursivos, `PIVOT` ou expressões `UNPIVOT`. Também não são compatíveis expressões `SUPER` não aninhadas e tabelas aninhadas do Redshift Spectrum. + A lista `UNPIVOT IN` deve conter apenas referências de coluna da tabela de entrada. As colunas da lista `IN` devem ter um tipo comum com o qual todas sejam compatíveis. A coluna de valor `UNPIVOT` tem esse tipo comum. A coluna de nome `UNPIVOT` é do tipo `VARCHAR`. + Se um valor de lista `IN` não tiver um alias, `UNPIVOT` usará o nome da coluna como valor padrão. # Exemplos de JOIN Uma cláusula SQL JOIN é usada para combinar os dados de duas ou mais tabelas com base em campos comuns. Os resultados podem ou não mudar dependendo do método de junção especificado. Para obter mais informações sobre a sintaxe da cláusula JOIN, consulte [Parâmetros](r_FROM_clause30.md#r_FROM_clause30-parameters). O exemplo a seguir usa dados dos dados de amostra `TICKIT`. Para obter mais informações sobre o esquema de banco de dados, consulte [Banco de dados de exemplo](c_sampledb.md). Para saber como carregar dados de exemplo, consulte [Carregamento de dados](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html) no *Guia de conceitos básicos do Amazon Redshift*. A consulta a seguir é uma junção interna (sem a palavra-chave JOIN) entre a tabela LISTING e a tabela SALES, onde o LISTID da tabela LISTING está entre 1 e 5. Essa consulta corresponde aos valores da coluna LISTID na tabela LISTING (a tabela à esquerda) e na tabela SALES (tabela à direita). Os resultados mostram que LISTID 1, 4 e 5 correspondem aos critérios. ``` 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 ``` A consulta a seguir é uma junção externa à esquerda. Junções externas esquerdas e direitas retêm valores de uma das tabelas de junção quando nenhuma correspondência é encontrada na outra tabela. As tabelas esquerdas e direitas são a primeiras e a segunda listadas na sintaxe. Os valores NULL são usados para preencher "lacunas" no conjunto de resultados. Essa consulta corresponde aos valores da coluna LISTID na tabela LISTING (a tabela à esquerda) e na tabela SALES (tabela à direita). Os resultados mostram que LISTIDs 2 e 3 não resultaram em nenhuma venda. ``` 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 ``` A consulta a seguir é uma junção externa à direita. Essa consulta corresponde aos valores da coluna LISTID na tabela LISTING (a tabela à esquerda) e na tabela SALES (tabela à direita). Os resultados mostram que LISTIDs 1, 4 e 5 correspondem aos critérios. ``` 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 ``` A consulta a seguir é uma junção completa. As junções completas retêm valores das tabelas unidas quando nenhuma correspondência é encontrada na outra tabela. As tabelas esquerdas e direitas são a primeiras e a segunda listadas na sintaxe. Os valores NULL são usados para preencher "lacunas" no conjunto de resultados. Essa consulta corresponde aos valores da coluna LISTID na tabela LISTING (a tabela à esquerda) e na tabela SALES (tabela à direita). Os resultados mostram que LISTIDs 2 e 3 não resultaram em nenhuma venda. ``` 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 ``` A consulta a seguir é uma junção completa. Essa consulta corresponde aos valores da coluna LISTID na tabela LISTING (a tabela à esquerda) e na tabela SALES (tabela à direita). Somente linhas que não resultam em vendas (LISTIDs 2 e 3) estão nos resultados. ``` 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 ``` O exemplo a seguir é uma junção interna com a cláusula ON. Nesse caso, as linhas NULL não são retornadas. ``` 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 ``` A consulta a seguir é uma junção cruzada ou junção cartesiana da tabela LISTING e da tabela SALES com um predicado para limitar os resultados. Essa consulta corresponde aos valores da coluna LISTID na tabela SALES e na tabela LISTING para LISTIDs 1, 2, 3, 4 e 5 em ambas as tabelas. Os resultados mostram que 20 linhas correspondem aos critérios. ``` 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 ``` O exemplo a seguir é uma junção natural entre duas tabelas. Nesse caso, as colunas listid, sellerid, eventid e dateid têm nomes e tipos de dados idênticos em ambas as tabelas e, portanto, são usadas como colunas de junção. Os resultados são limitados a cinco linhas. ``` 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 ``` O exemplo a seguir é uma junção entre duas tabelas com a cláusula USING. Nesse caso, as colunas listid e eventid são usadas como colunas de junção. Os resultados são limitados a cinco linhas. ``` 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 ``` A consulta a seguir é uma junção interna de duas subconsultas na cláusula FROM. A consulta encontra o número de ingressos vendidos e não vendidos para categorias diferentes de eventos (shows e apresentações). As subconsultas da cláusula FROM são subconsultas da *tabela*. Elas podem retornar várias colunas e linhas. ``` 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 ``` # Exemplos de UNNEST UNNEST é um parâmetro na cláusula FROM que expande os dados aninhados em colunas que contêm os elementos não aninhados dos dados. Para ter informações sobre como desagrupar dados, consulte [Consultar dados semiestruturados](query-super.md). A instrução a seguir cria e preenche a tabela `orders`, que contém uma coluna `products` com matrizes de IDs de produto. Os exemplos desta seção utilizam os dados de amostra nessa tabela. ``` 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" } } ] ')); ``` Veja a seguir alguns exemplos de consulta desaninhada com os dados de amostra usando a sintaxe partiQL. ## Desagrupar uma matriz sem uma coluna OFFSET A consulta a seguir desagrupa as matrizes SUPER na coluna de produtos, e cada linha representa um item do pedido em `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) ``` A consulta a seguir localiza o produto mais caro em cada pedido. ``` 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) ``` ## Desagrupar uma matriz com uma coluna OFFSET implícita A consulta a seguir usa o parâmetro `UNNEST ... WITH OFFSET` para mostrar a posição com base em zero de cada produto na respectiva matriz de pedidos. ``` 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) ``` Como a declaração não especifica um alias para a coluna de deslocamento, o Amazon Redshift a denomina `offset_col` por padrão. ## Desagrupar uma matriz com uma coluna OFFSET explícita A consulta a seguir também usa o parâmetro `UNNEST ... WITH OFFSET` para mostrar os produtos nas respectivas matrizes de pedidos. A diferença nessa consulta em comparação com a consulta no exemplo anterior é que ela nomeia explicitamente a coluna de deslocamento com o alias `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) ``` # Cláusula WHERE A cláusula WHERE contém as condições que juntam as tabelas ou aplicam predicados às colunas nas tabelas. Tabelas internas que foram juntadas usando a sintaxe apropriada, seja com a cláusula WHERE ou com a cláusula FROM. Os critérios de junção externa devem ser especificados na cláusula FROM. ## Sintaxe ``` [ WHERE condition ] ``` ## *condição* Qualquer condição de pesquisa com um resultado booleano, como uma condição de junção ou um predicado em uma coluna de tabela. Os exemplos a seguir são condições de junção válidas: ``` sales.listid=listing.listid sales.listid<>listing.listid ``` Os exemplos a seguir são condições válidas nas colunas em tabelas: ``` catgroup like 'S%' venueseats between 20000 and 50000 eventname in('Jersey Boys','Spamalot') year=2008 length(catdesc)>25 date_part(month, caldate)=6 ``` As condições podem ser simples ou complexas; para condições complexas, você pode usar parênteses para isolar unidades lógicas. No exemplo a seguir, a condição de junção está entre parênteses. ``` where (category.catid=event.catid) and category.catid in(6,7,8) ``` ## Observações de uso É possível usar aliases na cláusula WHERE para fazer referência a expressões da lista de seleção. Não é possível restringir os resultados de funções agregadas na cláusula WHERE; use a cláusula HAVING para essa finalidade. Colunas restringidas na cláusula WHERE devem ser derivadas de referências da tabela na cláusula FROM. ## Exemplo A consulta a seguir usa uma combinação de diferentes restrições da cláusula WHERE, incluindo uma condição de junção para as tabelas SALES e EVENT, um predicado na coluna EVENTNAME e dois predicados na coluna 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) ``` # Junções externas do estilo Oracle na cláusula WHERE Para compatibilidade com a Oracle, o Amazon Redshift oferece suporte ao operador de junção externa da Oracle (\$1) nas condições de junção da cláusula WHERE. Esse operador deve ser usado apenas para definir condições de junção externas; não tente usar em outros contextos. Outros usos para este operador são ignorados silenciosamente na maioria dos casos. Uma junção externa retorna todas as linhas que a junção interna equivalente deve retornar e linhas não correspondentes de uma ou de ambas as tabelas. Na cláusula FROM, você pode especificar junções esquerdas, direitas e externas. Na cláusula WHERE, você pode especificar somente junções externas esquerdas e direitas. Para juntar as tabelas externas TABLE1 e TABLE2 e retornar linhas não correspondentes da TABLE1 (junção externa esquerda), especifique `TABLE1 LEFT OUTER JOIN TABLE2` na cláusula ou aplique o operador (\$1) a todas as colunas de junção de TABLE2 na cláusula WHERE. Para todas as linhas na TABLE1 que não têm linhas correspondentes na TABLE2, o resultado da consulta contém nulos para quaisquer expressões de lista de seleção contendo colunas da TABLE2. Para produzir o mesmo comportamento em todas as linhas na TABLE2 que não têm linhas correspondentes na TABLE1, especifique `TABLE1 RIGHT OUTER JOIN TABLE2` na cláusula FROM ou aplique o operador (\$1) a todas as colunas de junção da TABLE1 na cláusula WHERE. ## Sintaxe básica ``` [ WHERE { [ table1.column1 = table2.column1(+) ] [ table1.column1(+) = table2.column1 ] } ``` A primeira condição equivale a: ``` from table1 left outer join table2 on table1.column1=table2.column1 ``` A segunda condição equivale a: ``` from table1 right outer join table2 on table1.column1=table2.column1 ``` **nota** A sintaxe mostrada aqui abrange o caso simples de uma junção equivalente em um par de colunas de junção. Porém, outros tipos de condições de comparação e diversos pares de colunas de junção também são válidos. Por exemplo, a cláusula WHERE a seguir define um junção externa em relação a dois pares de colunas. O operador (\$1) deve ser vinculado à mesma tabela em ambas as condições: ``` where table1.col1 > table2.col1(+) and table1.col2 = table2.col2(+) ``` ## Observações de uso Sempre que possível, use a sintaxe OUTER JOIN da cláusula FROM padrão em vez do operador (\$1) na cláusula WHERE. Consultas contendo o operador (\$1) estão sujeitas às seguintes regras: + Você só pode usar o operador (\$1) na cláusula WHERE, e somente em referência a colunas de tabelas ou exibições. + Você não pode aplicar o operador (\$1) a expressões. No entanto, uma expressão pode conter colunas que usam o operador (\$1). Por exemplo, a condição de junção a seguir retorna um erro de sintaxe: ``` event.eventid*10(+)=category.catid ``` No entanto, a seguinte condição de junção é válida: ``` event.eventid(+)*10=category.catid ``` + Você não pode usar o operador (\$1) em um bloco de consulta que também contenha a sintaxe de junção da cláusula FROM. + Se duas tabelas são adicionadas em diversas condições de junção, você deve usar o operador (\$1) em todas ou em nenhuma dessas condições. Uma junção com estilos mistos de sintaxe é executada como uma junção interna, sem aviso. + O operador (\$1) não produzirá um uma junção externa se você juntar uma tabela na consulta externa com uma tabela que resulte de uma consulta interna. + Para usar o operador (\$1) para juntar uma tabela externa na própria tabela, você deve definir aliases da tabela na cláusula FROM e fazer referência a eles na condição de junção: ``` select count(*) from event a, event b where a.eventid(+)=b.catid; count ------- 8798 (1 row) ``` + Você não pode combinar uma condição de junção que contenha o operador (\$1) com uma condição OR ou IN. Por exemplo: ``` 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. ``` + Em uma cláusula WHERE faz junções externas de mais de duas tabelas, o operador (\$1) pode ser aplicado somente uma vez a uma tabela específica. No exemplo a seguir, não é possível fazer referência à tabela SALES com o operador (\$1) em duas junções sucessivas. ``` 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. ``` + Se a condição de junção externa da cláusula WHERE se comparar a uma coluna da TABLE2 com uma constante, aplique o operador (\$1) à coluna. Se você não incluir o operador, as linhas de junções externas da TABLE1 que contêm nulos para a coluna restringida serão eliminadas. Consulte a seção de Exemplos abaixo. ## Exemplos A seguinte consulta de junção especifica uma junção esquerda externa das tabelas SALES e LISTING em suas colunas LISTID: ``` select count(*) from sales, listing where sales.listid = listing.listid(+); count -------- 172456 (1 row) ``` A seguinte consulta equivalente produz o mesmo resultado, mas usa a sintaxe de junção da cláusula FROM: ``` select count(*) from sales left outer join listing on sales.listid = listing.listid; count -------- 172456 (1 row) ``` A tabela SALES não contém registros de todas as listagens na tabela LISTING, pois nem todas as listagens resultam em vendas. A consulta a seguir junta externamente as tabelas SALES e LISTING e retorna linhas da tabela LISTING mesmo quando a tabela SALES não retorna vendas para determinado ID de lista. As colunas PRICE e COMM, derivadas da tabela SALES, contêm nulos no conjunto de resultados para linhas não correspondentes. ``` 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) ``` Observe que quando o operador de junção da cláusula WHERE é usado, a ordem das tabelas na cláusula FROM não importa. Um exemplo de uma condição de junção externa mais complexa na cláusula WHERE é o caso em que a condição consiste em uma comparação entre duas tabelas comuns *e* uma comparação com uma constante: ``` where category.catid=event.catid(+) and eventid(+)=796; ``` Observe que o operador (\$1) é usado em dois lugares: primeiro na comparação de correspondência entre as tabelas e depois na condição de comparação para a coluna EVENTID. O resultado da sintaxe é a preservação das linhas de junção externa quando a restrição em EVENTID é avaliada. Se você remover o operador (\$1) da restrição EVENTID, a consulta trata a restrição como um filtro, não como parte da condição de junção externa. Por sua vez, as colunas de junção externa que contêm nulos para EVENTID são eliminadas do conjunto de resultados. Veja aqui uma consulta completa que ilustra esse comportamento: ``` 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) ``` A consulta equivalente usando a sintaxe de cláusula FROM é: ``` select catname, catgroup, eventid from category left join event on category.catid=event.catid and eventid=796; ``` Se você remover o segundo operador (\$1) da versão da cláusula WHERE desta consulta, ela retornará somente 1 linha (a linha de `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) ``` # Cláusula GROUP BY A cláusula GROUP BY identifica as colunas de agrupamento para a consulta. É usada para agrupar as linhas em uma tabela que têm os mesmos valores em todas as colunas listadas. A ordem em que as colunas são listadas não importa. O resultado é a combinação de cada conjunto de linhas com valores comuns em uma linha que representa todas as linhas do grupo. Use GROUP BY para eliminar a redundância na saída e calcular agregados que se aplicam aos grupos. As colunas de agrupamento devem ser declaradas quando a consulta computa agregadas com funções padrão como SUM, AVG e COUNT. Para obter mais informações, consulte [Funções agregadas](c_Aggregate_Functions.md). ## Sintaxe ``` [ GROUP BY expression [, ...] | ALL | aggregation_extension ] ``` em que *aggregation\$1extension* é uma das seguintes: ``` GROUPING SETS ( () | aggregation_extension [, ...] ) | ROLLUP ( expr [, ...] ) | CUBE ( expr [, ...] ) ``` ## Parâmetros *expressão* A lista de colunas ou de expressões deve corresponder à lista de expressões não agregadas na lista de seleção da consulta. Por exemplo, considere a seguinte consulta simples. ``` 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) ``` Nesta consulta, a lista de seleção consiste em duas expressões agregadas. A primeira usa a função SUM e a segunda usa a função COUNT. As duas colunas restantes, LISTID e EVENTID, devem ser declaradas como colunas de agrupamento. As expressões na cláusula GROUP BY também podem fazer referência à lista de seleção usando números ordinais. O exemplo anterior poderia ser abreviado da seguinte forma. ``` 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 indica agrupar por todas as colunas especificadas na lista SELECT, exceto as agregadas. Por exemplo, pense na consulta a seguir, que agrupa por `col1` e `col2` sem precisar especificá-las individualmente na cláusula GROUP BY. A coluna `col3` é o argumento da função `SUM` e, portanto, não está agrupada. ``` SELECT col1, col2 sum(col3) FROM testtable GROUP BY ALL ``` Se você excluir uma coluna na lista SELECT, a cláusula GROUP BY ALL não agrupará os resultados com base nessa coluna específica. ``` SELECT * EXCLUDE col3 FROM testtable GROUP BY ALL ``` * *aggregation\$1extension* * Você pode usar as extensões de agregação GROUPING SETS, ROLLUP e CUBE para realizar o trabalho de várias operações GROUP BY em uma única instrução. Para obter mais informações sobre extensões de agregação e funções relacionadas, consulte [Extensões de agregação](r_GROUP_BY_aggregation-extensions.md). ## Exemplos Os exemplos a seguir usam a tabela SALES que contém as colunas: salesid, listid, sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission e saletime. Para ter mais informações sobre a tabela SALES, consulte [Banco de dados de exemplo](c_sampledb.md). O exemplo de consulta a seguir agrupa por `salesid` e `listid` sem precisar especificá-las individualmente na cláusula GROUP BY. A coluna `qtysold` é o argumento da função `SUM` e, portanto, não está agrupada. ``` SELECT salesid, listid, sum(qtysold) FROM sales GROUP BY ALL; salesid | listid | sum --------+---------+------ 33095 | 36572 | 2 88268 | 100813 | 4 110917 | 127048 | 1 ... ``` O exemplo de consulta a seguir exclui várias colunas na lista SELECT, então GROUP BY ALL agrupa somente salesid e listid. ``` SELECT * EXCLUDE sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, saletime FROM sales GROUP BY ALL; salesid | listid --------+--------- 33095 | 36572 88268 | 100813 110917 | 127048 ... ``` # Extensões de agregação O Amazon Redshift oferece suporte a extensões de agregação para realizar o trabalho de várias operações GROUP BY em uma única instrução. Os exemplos de extensões de agregação usam a tabela `orders`, que contém dados de vendas de uma empresa de dispositivos eletrônicos. Você pode criar `orders` com o seguinte. ``` 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* Calcula um ou mais conjuntos de agrupamento em uma única instrução. Um conjunto de agrupamento é o conjunto de uma única cláusula GROUP BY, um conjunto de 0 ou mais colunas pelo qual você pode agrupar o conjunto de resultados de uma consulta. GROUP BY GROUPING SETS é equivalente a executar uma consulta UNION ALL em um conjunto de resultados agrupado por colunas diferentes. Por exemplo, GROUP BY GROUPING SETS((a), (b)) é equivalente a GROUP BY a UNION ALL GROUP BY b. O exemplo a seguir retorna o custo dos produtos da tabela de pedidos agrupados de acordo com as categorias de produtos e o tipo de produto vendido. ``` 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* Assume uma hierarquia em que as colunas anteriores são consideradas pais das colunas subsequentes. ROLLUP agrupa os dados pelas colunas fornecidas, retornando linhas de subtotal extras representando os totais em todos os níveis de colunas de agrupamento, além das linhas agrupadas. Por exemplo, você pode usar GROUP BY ROLLUP((a), (b)) para retornar um conjunto de resultados agrupado primeiro por a, depois por b, assumindo que b é uma subseção de a. ROLLUP também retorna uma linha com todo o conjunto de resultados sem colunas de agrupamento. GROUP BY ROLLUP((a), (b)) é equivalente a GROUP BY GROUPING SETS((a,b), (a), ()). O exemplo a seguir retorna o custo dos produtos da tabela de pedidos agrupados primeiro por categoria, depois por produto, com o produto como uma subdivisão da categoria. ``` 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* Agrupa os dados pelas colunas fornecidas, retornando linhas de subtotal extras representando os totais em todos os níveis de colunas de agrupamento, além das linhas agrupadas. CUBE retorna as mesmas linhas que ROLLUP, enquanto inclui linhas de subtotal adicionais para cada combinação de coluna de agrupamento não contemplada por ROLLUP. Por exemplo, você pode usar GROUP BY CUBE((a), (b)) para retornar um conjunto de resultados agrupado primeiro por a, depois por b, assumindo que b é uma subseção de a, depois apenas por b. CUBE também retorna uma linha com todo o conjunto de resultados sem colunas de agrupamento. GROUP BY CUBE((a), (b)) é equivalente a GROUP BY GROUPING SETS((a,b), (a), (b), ()). O exemplo a seguir retorna o custo dos produtos da tabela de pedidos agrupados primeiro por categoria, depois por produto, com o produto como uma subdivisão da categoria. Ao contrário do exemplo anterior para ROLLUP, a instrução retorna resultados para cada combinação de coluna de agrupamento. ``` 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) ``` ## *Funções GROUPING/GROUPING\$1ID* ROLLUP e CUBE adicionam valores NULL ao conjunto de resultados para indicar linhas de subtotal. Por exemplo, GROUP BY ROLLUP((a), (b)) retorna uma ou mais linhas que têm um valor NULL na coluna de agrupamento b para indicar que são subtotais de campos na coluna de agrupamento a. Esses valores NULL servem apenas para satisfazer o formato das tuplas de retorno. Quando você executa operações GROUP BY com ROLLUP e CUBE em relações que armazenam valores NULL em si, isso pode produzir conjuntos de resultados com linhas que parecem ter colunas de agrupamento idênticas. Voltando ao exemplo anterior, se a coluna de agrupamento b contiver um valor NULL armazenado, GROUP BY ROLLUP((a), (b)) retornará uma linha com um valor NULL na coluna de agrupamento b que não é um subtotal. Para distinguir entre valores NULL criados por ROLLUP e CUBE e os valores NULL armazenados nas próprias tabelas, você pode usar a função GROUPING ou seu alias GROUPING\$1ID. GROUPING usa um único conjunto de agrupamento como argumento e, para cada linha no conjunto de resultados, retorna um valor de bit 0 ou 1 correspondente à coluna de agrupamento nessa posição, depois converte esse valor em um inteiro. Se o valor nessa posição for um valor NULL criado por uma extensão de agregação, GROUPING retornará 1. Retornará 0 para todos os outros valores, incluindo valores NULL armazenados. Por exemplo, GROUPING(category, product) pode retornar os seguintes valores para determinada linha, dependendo dos valores da coluna de agrupamento dessa linha. Neste exemplo, todos os valores NULL na tabela são valores NULL criados por uma extensão de agregação. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/redshift/latest/dg/r_GROUP_BY_aggregation-extensions.html) As funções GROUPING aparecem na parte da lista SELECT da consulta no formato a seguir. ``` SELECT ... [GROUPING( expr )...] ... GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ... ``` O exemplo a seguir é igual ao exemplo anterior para CUBE, mas com a adição de funções GROUPING para seus conjuntos de agrupamento. ``` 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 e CUBE parciais* Você pode executar operações ROLLUP e CUBE com apenas uma parte dos subtotais. A sintaxe para operações parciais de ROLLUP e CUBE é a seguinte. ``` GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...]) ``` Aqui, a cláusula GROUP BY cria apenas linhas de subtotal no nível de *expr2* e em diante. Os exemplos a seguir mostram operações parciais de ROLLUP e CUBE na tabela de pedidos, primeiro agrupando os produtos seminovos, depois executando ROLLUP e CUBE nas colunas category e product. ``` 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) ``` Como a coluna pre-owned não está incluída nas operações ROLLUP e CUBE, não há uma linha de total geral que inclua todas as outras linhas. ## *Agrupamento concatenado* Você pode concatenar várias cláusulas GROUPING SETS/ROLLUP/CUBE para calcular diferentes níveis de subtotal. Os agrupamentos concatenados retornam o produto cartesiano dos conjuntos de agrupamento fornecidos. A sintaxe para concatenar as cláusulas GROUPING SETS/ROLLUP/CUBE é a seguinte. ``` GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]), {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...] ``` Considere o exemplo a seguir para ver como um pequeno agrupamento concatenado pode produzir um grande conjunto de resultados finais. ``` 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) ``` ## *Agrupamento aninhado* Você pode usar as operações GROUPING SETS/ROLLUP/CUBE como *expr* de seu GROUPING SETS para formar um agrupamento aninhado. O subagrupamento dentro da GROUPING SETS aninhada é nivelado. A sintaxe para agrupamento aninhado é a seguinte. ``` GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...]) ``` Considere o exemplo a seguir. ``` 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) ``` Observe que, como ROLLUP(category) e CUBE(product, pre\$1owned) contêm o conjunto de agrupamento (), a linha que representa o total geral é duplicada. ## *Observações de uso* + A cláusula GROUP BY é compatível com até 64 conjuntos de agrupamento. No caso de ROLLUP e CUBE, ou alguma combinação de GROUPING SETS, ROLLUP e CUBE, essa limitação se aplica ao número implícito de conjuntos de agrupamento. Por exemplo, GROUP BY CUBE((a), (b)) conta como 4 conjuntos de agrupamento, não 2. + Não é possível usar constantes como colunas de agrupamento ao usar extensões de agregação. + Não é possível fazer um conjunto de agrupamento que contém colunas duplicadas. # Cláusula HAVING A cláusula HAVING aplica uma condição a um conjunto de resultados agrupados intermediários retornados por uma consulta. ## Sintaxe ``` [ HAVING condition ] ``` Por exemplo, você pode restringir os resultados de uma função SUM: ``` having sum(pricepaid) >10000 ``` A condição HAVING é aplicada depois que todas as condições da cláusula WHERE forem aplicadas e as operações GROUP BY concluídas. A própria condição leva a mesma forma que qualquer condição da cláusula WHERE. ## Observações de uso + Qualquer coluna referida na condição da cláusula HAVING deve ser uma coluna de agrupamento ou uma coluna que faz referência ao resultado de uma função agregada. + Em uma cláusula HAVING, você não pode especificar: + Número ordinal que se refere a um item na lista de seleção. Somente as cláusulas GROUP BY e ORDER BY aceitam números ordinais. ## Exemplos A consulta a seguir calcula as vendas de ingressos globais para todos os eventos por nome e depois elimina eventos em que as vendas globais tenham sido menos de \$1 800.000. A condição HAVING é aplicada aos resultados da função agregada na lista de seleção: `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 ``` A consulta a seguir calcula um conjunto de resultados semelhante. Nesse caso, no entanto, a condição HAVING é aplicada a um valor agregado não especificado na lista de seleção: `sum(qtysold)`. Os eventos que não tenham vendido mais de 2.000 ingressos são eliminados dos resultados finais. ``` 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 ``` A consulta a seguir calcula as vendas de ingressos globais para todos os eventos por nome e depois elimina eventos em que as vendas globais tenham sido menos de \$1 800.000. A condição HAVING é aplicada aos resultados da função agregada na lista de seleção usando o alias `pp` para `sum(pricepaid)`. ``` 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 ``` # Cláusula QUALIFY A cláusula QUALIFY filtra os resultados de uma função de janela previamente calculada de acordo com as condições de pesquisa especificadas pelo usuário. Você pode usar a cláusula para aplicar condições de filtragem ao resultado de uma função de janela sem usar uma subconsulta. É semelhante à [cláusula HAVING](https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html), que aplica uma condição para filtrar ainda mais as linhas de uma cláusula WHERE. A diferença entre QUALIFY e HAVING é que os resultados filtrados da cláusula QUALIFY podem ser baseados no resultado da execução de funções de janela nos dados. Você pode usar as cláusulas QUALIFY e HAVING na mesma consulta. ## Sintaxe ``` QUALIFY condition ``` **nota** Se você estiver usando a cláusula QUALIFY diretamente após a cláusula FROM, o nome da relação FROM deverá ter um alias especificado antes da cláusula QUALIFY. ## Exemplos Os exemplos desta seção utilizam os dados de amostra a seguir. ``` 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); ``` O exemplo a seguir demonstra como encontrar os dois itens mais caros vendidos após as 12h de cada dia. ``` 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 ``` Depois, você pode encontrar o último item vendido em cada dia. ``` 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 ``` O exemplo a seguir retorna os mesmos registros que a consulta anterior, do último item vendido em cada dia, mas não usa a cláusula 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 e EXCEPT **Topics** + [ ## Sintaxe ](#r_UNION-synopsis) + [ ## Parâmetros ](#r_UNION-parameters) + [ ## Ordem de avaliação para operadores de conjunto ](#r_UNION-order-of-evaluation-for-set-operators) + [ ## Observações de uso ](#r_UNION-usage-notes) + [ # Exemplos de consultas UNION ](c_example_union_query.md) + [ # Exemplos de consultas UNION ALL ](c_example_unionall_query.md) + [ # Exemplos de consultas INTERSECT ](c_example_intersect_query.md) + [ # Exemplos de consultas EXCEPT ](c_Example_MINUS_query.md) Os *operadores de conjunto* UNION, INTERSECT e EXCEPT são usados para comparar e mesclar os resultados de duas expressões de consulta separadas. Por exemplo, se você quiser saber quais usuários de um site compram e vendem, mas os nomes de usuários estiverem armazenados em colunas ou tabelas separadas, você pode encontrar a *interseção* desses dois tipos de usuários. Se você quiser saber quais usuários do site compram, mas não vendem, você pode usar o operador EXCEPT para encontrar a *diferença* entre as duas listas de usuários. Se quiser criar uma lista com todos os usuários, independentemente da função, use o operador UNION. ## Sintaxe ``` query { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS } query ``` ## Parâmetros *query* Uma expressão de consulta que corresponde, na forma de sua lista de seleção, a uma segunda expressão de consulta que segue o operador UNION, INTERSECT ou EXCEPT. As duas expressões devem conter o mesmo número de colunas de saída com tipos de dados compatíveis. Caso contrário, os dois conjuntos de resultados não poderão ser comparados e mesclados. Operações de conjunto não permitem a conversão implícita entre categorias diferentes de tipos de dados. Para obter mais informações, consulte [Compatibilidade e conversão dos tipos](c_Supported_data_types.md#r_Type_conversion). Você pode criar consultas contendo um número ilimitado de expressões de consulta e conectá-las aos operadores UNION, INTERSECT e EXCEPT em qualquer combinação. Por exemplo, a estrutura de consulta a seguir é válida, pressupondo que as tabelas T1, T2 e T3 contenham conjuntos compatíveis de colunas: ``` select * from t1 union select * from t2 except select * from t3 order by c1; ``` UNION Operação de conjunto que retorna linhas de duas expressões de consulta, independentemente das linhas se derivarem de uma ou ambas as expressões. INTERSECT Operação de conjunto que retorna linhas derivadas de duas expressões de consulta. As linhas que não forem retornadas por ambas as expressões serão descartadas. EXCEPT \$1 MINUS Operação de conjunto que retorna linhas derivadas de uma das duas expressões de consulta. Para se qualificar para o resultado, as linhas precisam existir na primeira tabela de resultados, mas não na segunda. MINUS e EXCEPT são sinônimos. ALL A palavra-chave ALL retém todas as linhas duplicadas produzidas por UNION. O comportamento padrão quando a palavra-chave ALL não é utilizada é descartar essas linhas duplicadas. INTERSECT ALL, EXCEPT ALL e MINUS ALL não são compatíveis. ## Ordem de avaliação para operadores de conjunto Os operadores de conjunto UNION e EXCEPT se associam à esquerda. Se não houver parênteses especificados para influenciar a ordem de precedência, uma combinação desses operadores de conjunto será avaliada da esquerda para a direita. Por exemplo, na consulta a seguir, o operador UNION de T1 e T2 é avaliado primeiro, seguido pela operação EXCEPT, que é executada no resultado de UNION: ``` select * from t1 union select * from t2 except select * from t3 order by c1; ``` O operador INTERSECT tem precedência sobre os operadores UNION e EXCEPT quando uma combinação de operadores for usada na mesma consulta. Por exemplo, a consulta a seguir avalia a interseção de T2 e T3, e depois une o resultado com T1: ``` select * from t1 union select * from t2 intersect select * from t3 order by c1; ``` Adicionando parênteses, você pode aplicar uma ordem diferente de avaliação. No caso a seguir, o resultado da união de T1 e T2 é cruzado com T3, e a consulta provavelmente produzirá um resultado diferente. ``` (select * from t1 union select * from t2) intersect (select * from t3) order by c1; ``` ## Observações de uso + Os nomes de colunas obtidos no resultado de uma consulta de operação de conjunto são os nomes de colunas (ou aliases) das tabelas na primeira expressão de consulta. Como esses nomes de coluna podem induzir a erros, os valores na coluna derivam de tabelas em ambos os lados do operador de conjunto, você pode querer fornecer aliases significativos para o conjunto de resultados. + Uma expressão de consulta que preceda um operador de conjunto não deve conter uma cláusula ORDER BY. Uma cláusula ORDER BY produz resultados significativos classificados somente quando é usada no final de uma consulta que contenha operadores de conjunto. Nesse caso, a cláusula ORDER BY se aplica a resultados finais de todas as operações de conjunto. A consulta mais externa também pode conter as cláusulas LIMIT e OFFSET padrão. + Quando as consultas do operador de conjunto retornam resultados decimais, as colunas de resultados correspondentes são promovidas para retornar a mesma precisão e escala. Por exemplo, na consulta a seguir, em que T1.REVENUE é uma coluna DECIMAL(10,2) e T2.REVENUE é uma coluna DECIMAL(8,4), o resultado decimal é atualizado para DECIMAL(12,4): ``` select t1.revenue union select t2.revenue; ``` A escala é `4` porque é a escala máxima das duas colunas. A precisão é `12` porque T1.REVENUE requer 8 dígitos à esquerda do ponto decimal (12 - 4 = 8). Essa promoção de tipo garante que todos os valores de ambos os lados de UNION se encaixem no resultado. Para valores de 64 bits, a precisão máxima de resultado é 19 e a escala máxima de resultado é 18. Para valores de 128-bits, a precisão máxima de resultado é 38 e a escala máxima de resultado é 37. Se o tipo de dados resultante ultrapassar os limites de precisão e escala do Amazon Redshift, a consulta retornará um erro. + Para operações de conjunto, duas linhas são tratadas como idênticas se, para cada par de colunas correspondente, os dois valores de dados forem *iguais* ou *ambos NULL*. Por exemplo, se as tabelas T1 e T2 contiverem uma coluna e uma linha, e a linha for NULL em ambas as tabelas, uma operação INTERSECT sobre essas tabelas retornará essa linha. # Exemplos de consultas UNION Na consulta UNION a seguir, as linhas na tabela SALES são mescladas com as linhas na tabela LISTING. Três colunas compatíveis de cada tabela são selecionadas. Nesse caso, as colunas correspondentes têm os mesmos nomes e tipos de dados. O conjunto de resultados finais é classificado pela primeira coluna na tabela LISTING e limitado a 5 linhas com o valor de LISTID mais alto. ``` 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) ``` O exemplo a seguir mostra como você pode adicionar um valor literal de saída de uma consulta UNION para ver qual expressão de consulta produziu cada linha no conjunto de resultados. A consulta identifica linhas da primeira expressão de consulta como “B” (para compradores) e linhas da segunda expressão de consulta como “S” (para vendedores). A consulta identifica compradores e vendedores para as transações de ingressos que custem \$110.000 ou mais. A única diferença entre as duas expressões de consulta em ambos os lados do operador UNION é a coluna de junção para a tabela 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) ``` O exemplo a seguir usa um operador UNION ALL porque se forem encontradas linhas duplicadas, elas devem ser mantidas no resultado. Para uma série específica de IDs de evento, a consulta retorna 0 ou mais linhas para cada venda associada a cada evento, e 0 ou 1 linha para cada lista desse evento. Os IDs de evento são exclusivos para cada linha nas tabelas LISTING e EVENT, mas pode haver várias vendas para a mesma combinação de IDs de evento e de lista na tabela SALES. A terceira coluna no conjunto de resultados identifica a origem da linha. Se vier da tabela SALES, “Yes” é marcado na coluna SALESROW. (SALESROW é um alias para SALES.LISTID.) Se a linha vier da tabela LISTING, “No” é marcado na coluna SALESROW. Nesse caso, o conjunto de resultados consiste em três linhas de vendas para a lista 500, evento 7787. Em outras palavras, três transações diferentes ocorreram para essa combinação de lista e evento. Outras duas listas, 501 e 502, não produziram vendas. Dessa forma, a única linha que a consulta produz para esses IDs de lista vem de tabela 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) ``` Se você executar a mesma consulta sem a palavra-chave ALL, o resultado manterá somente uma das transações de vendas. ``` 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) ``` # Exemplos de consultas UNION ALL O exemplo a seguir usa um operador UNION ALL porque se forem encontradas linhas duplicadas, elas devem ser mantidas no resultado. Para uma série específica de IDs de evento, a consulta retorna 0 ou mais linhas para cada venda associada a cada evento, e 0 ou 1 linha para cada lista desse evento. Os IDs de evento são exclusivos para cada linha nas tabelas LISTING e EVENT, mas pode haver várias vendas para a mesma combinação de IDs de evento e de lista na tabela SALES. A terceira coluna no conjunto de resultados identifica a origem da linha. Se vier da tabela SALES, “Yes” é marcado na coluna SALESROW. (SALESROW é um alias para SALES.LISTID.) Se a linha vier da tabela LISTING, “No” é marcado na coluna SALESROW. Nesse caso, o conjunto de resultados consiste em três linhas de vendas para a lista 500, evento 7787. Em outras palavras, três transações diferentes ocorreram para essa combinação de lista e evento. Outras duas listas, 501 e 502, não produziram vendas. Dessa forma, a única linha que a consulta produz para esses IDs de lista vem de tabela 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) ``` Se você executar a mesma consulta sem a palavra-chave ALL, o resultado manterá somente uma das transações de vendas. ``` 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) ``` # Exemplos de consultas INTERSECT Compare o exemplo a seguir com o primeiro exemplo de UNION. A única diferença entre os dois exemplos é o operador de conjunto usado, mas os resultados são muito diferentes. Somente uma das linhas é a mesma: ``` 235494 | 23875 | 8771 ``` Essa é a única linha no resultado limitado de 5 linhas encontrada em ambas as tabelas. ``` 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) ``` A consulta a seguir encontra eventos (em que foram vendidos ingressos) que ocorreram em locais em Nova York e Los Angeles em março. A diferença entre as duas expressões de consulta é a restrição na coluna 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) ``` # Exemplos de consultas EXCEPT A tabela CATEGORY no banco de dados TICKIT contém as seguintes 11 linhas: ``` 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) ``` Pressuponha que uma tabela CATEGORY\$1STAGE (tabela de preparação) contém uma linha adicional: ``` 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) ``` Retorne a diferença entre as duas tabelas. Em outras palavras, retorne as linhas que estão na tabela CATEGORY\$1STAGE, mas não na tabela CATEGORY: ``` select * from category_stage except select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` A consulta equivalente a seguir usa o sinônimo MINUS. ``` select * from category_stage minus select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` Se você reverter a ordem das expressões SELECT, a consulta não retornará qualquer linha. # Cláusula ORDER BY **Topics** + [ ## Sintaxe ](#r_ORDER_BY_clause-synopsis) + [ ## Parâmetros ](#r_ORDER_BY_clause-parameters) + [ ## Observações de uso ](#r_ORDER_BY_usage_notes) + [ # Exemplos com ORDER BY ](r_Examples_with_ORDER_BY.md) A cláusula ORDER BY classifica o conjunto de resultados de uma consulta. ## Sintaxe ``` [ ORDER BY expression [ ASC | DESC ] ] [ NULLS FIRST | NULLS LAST ] [ LIMIT { count | ALL } ] [ OFFSET start ] ``` ## Parâmetros *expressão* Expressão que define a ordem de classificação do conjunto de resultados da consulta, geralmente especificando uma ou mais colunas na lista de seleção. Os resultados são obtidos com base na ordem binária UTF-8. Também é possível especificar o seguinte: + Colunas que não estiverem na lista de seleção + Expressões formadas por uma ou mais colunas que existem em tabelas referidas pela consulta + Números ordinais que representam a posição de entradas da lista de seleção (ou a posição das colunas na tabela se não houver lista de seleção) + Aliases que definem entradas da lista de seleção Quando a cláusula ORDER BY tiver várias expressões, o conjunto de resultados será classificado de acordo com a primeira expressão, e a segunda expressão será aplicada a linhas que tenham valores correspondentes com os da primeira expressão, e assim por diante. ASC \$1 DESC Opção que define a ordem de classificação para a expressão, da seguinte forma: + ASC: ascendente (por exemplo, de valores numéricos menores para maiores e de "A" a "Z" para strings de caracteres). Se nenhuma opção é especificada, os dados são classificados na ordem ascendente por padrão. + DESC: descendente (de valores numéricos maiores para menores; de "Z" a "A" para strings). NULLS FIRST \$1 NULLS LAST Opção que especifica se valores NULL devem ser classificados primeiro, antes de valores não nulos, ou por último, depois de valores não nulos. Por padrão, os valores NULL são ordenados e classificados por último na ordem ASC e são ordenados e classificados primeiro na ordem DESC. LIMIT *number* \$1 ALL Opção que controla o número de linhas classificadas que a consulta retorna. O número LIMIT deve ser um inteiro positivo. O valor máximo é `2147483647`. LIMIT 0 não retorna linhas. Você pode usar essa sintaxe para fins de teste: para garantir que uma consulta seja executada (sem exibir qualquer linha) ou obter uma lista de colunas de uma tabela. Uma cláusula ORDER BY é redundante se você estiver usando LIMIT 0 para obter uma lista de colunas. O valor padrão é LIMIT ALL. OFFSET *start* Opção que especifica para ignorar o número de linhas antes de *start* antes de começar a retornar linhas. O número OFFSET deve ser um inteiro positivo. O valor máximo é `2147483647`. Quando usadas com a opção de LIMIT, as linhas OFFSET são ignoradas antes de iniciar a contagem de linhas LIMIT que são retornadas. Se a opção LIMIT não for usada, o número de linhas no conjunto de resultados será reduzido para o número de linhas ignoradas. As linhas ignoradas por uma cláusula OFFSET ainda precisam passar por varredura, e pode não ser eficiente usar um valor OFFSET grande. ## Observações de uso Observe o seguinte comportamento esperado com cláusulas ORDER BY: + Os valores NULL são considerados "mais altos" que todos os demais valores. Com a ordem de classificação crescente padrão, os valores NULL são classificados no final. Para alterar esse comportamento, use a opção NULLS FIRST. + Quando uma consulta não tiver uma cláusula ORDER BY, o sistema retornará conjuntos de resultados sem uma classificação previsível das linhas. A mesma consulta executada duas vezes pode retornar o conjunto de resultados em uma ordem diferente. + As opções LIMIT e OFFSET podem ser usadas sem uma cláusula ORDER BY. No entanto, para obter um conjunto consistente de linhas, use essas opções em conjunto com ORDER BY. + Em qualquer sistema paralelo como o Amazon Redshift, quando uma cláusula ORDER BY não produz uma classificação exclusiva dos dados, a ordem das linhas não é determinística. Portanto, se a expressão ORDER BY produz valores duplicados, a ordem de retorno dessas linhas pode variar de outros sistemas ou de uma execução do Amazon Redshift para outra. + O Amazon Redshift não oferece suporte a literais de string nas cláusulas ORDER BY. # Exemplos com ORDER BY Retorne todas as 11 linhas da tabela CATEGORY, classificada pela segunda coluna, CATGROUP. Para os resultados que têm o mesmo valor de CATGROUP, classifique os valores da coluna CATDESC pelo tamanho da string. Depois, organize pelas colunas CATID e 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) ``` Retorne colunas selecionadas da tabela SALES, classificada pelos valores mais altos de QTYSOLD. Limite o resultado às 10 primeiras linhas: ``` 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) ``` Retorne uma lista de colunas e nenhuma linha usando a sintaxe LIMIT 0: ``` select * from venue limit 0; venueid | venuename | venuecity | venuestate | venueseats ---------+-----------+-----------+------------+------------ (0 rows) ``` # Cláusula CONNECT BY A cláusula CONNECT BY especifica a relação entre as linhas em uma hierarquia. Você pode usar CONNECT BY para selecionar linhas em uma ordem hierárquica unindo a tabela a ela mesma e processando os dados hierárquicos. Por exemplo, você pode usá-la para percorrer recursivamente um organograma e listar dados. As consultas hierárquicas são processadas na seguinte ordem: 1. Se a cláusula FROM tiver uma união, ela será processada primeiro. 1. A cláusula CONNECT BY é avaliada. 1. A cláusula WHERE é avaliada. ## Sintaxe ``` [START WITH start_with_conditions] CONNECT BY connect_by_conditions ``` **nota** Embora START e CONNECT não sejam palavras reservadas, use identificadores delimitados (aspas duplas) ou AS se estiver usando START e CONNECT como aliases de tabela em sua consulta para evitar falhas no runtime. ``` 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' ``` ## Parâmetros *start\$1with\$1conditions* Condições que especificam a(s) linha(s) raiz da hierarquia *connect\$1by\$1conditions* Condições que especificam a relação entre as linhas pais e as linhas filhas da hierarquia. Pelo menos uma condição deve ser qualificada com o operador unário ` ` usado para se referir à linha pai. ``` PRIOR column = expression -- or expression > PRIOR column ``` ## Operadores É possível usar os seguintes operadores na consulta CONNECT BY. *LEVEL* Pseudocoluna que retorna o nível da linha atual na hierarquia. Retorna 1 para a linha raiz, 2 para a filha da linha raiz e assim por diante. *PRIOR* Operador unário que avalia a expressão da linha pai da linha atual na hierarquia. ## Exemplos O exemplo a seguir é uma consulta CONNECT BY que retorna o número de funcionários subordinados direta ou indiretamente a John, até o máximo de 4 níveis. ``` SELECT id, name, manager_id FROM employee WHERE LEVEL < 4 START WITH name = 'John' CONNECT BY PRIOR id = manager_id; ``` A seguir é o resultado da consulta. ``` 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 ``` Definição da tabela para esse exemplo: ``` CREATE TABLE employee ( id INT, name VARCHAR(20), manager_id INT ); ``` A seguir estão as linhas inseridas na tabela. ``` 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); ``` A seguir está um organograma para o departamento de John. ![\[Um organograma do departamento de John.\]](http://docs.aws.amazon.com/pt_br/redshift/latest/dg/images/org-chart.png) # Exemplos de subconsulta Os exemplos a seguir mostram diferentes maneiras em que subconsultas se encaixam em consultas SELECT. Consulte [Exemplos de JOIN](r_Join_examples.md) para obter outros exemplos de uso de subconsultas. ## Subconsulta da lista SELECT O exemplo a seguir contém um subconsulta na lista SELECT. Esta subconsulta é *escalar*: retorna somente uma coluna e um valor, que é repetido nos resultados para cada linha retornada da consulta exterior. A consulta compara o valor Q1SALES que a subconsulta computa com valores de vendas de outros dois trimestres (2 e 3) em 2008, como definido pela consulta externa. ``` 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) ``` ## Subconsulta da cláusula WHERE O exemplo a seguir contém um subconsulta de tabela na cláusula WHERE. Essa subconsulta produz várias linhas. Nesse caso, as linhas contêm apenas uma coluna, mas as subconsultas da tabela podem conter várias colunas e linhas, assim como qualquer outra tabela. A consulta encontra os 10 principais vendedores em termos quantidade máxima de ingressos vendidos. A lista dos 10 principais é restringida pela subconsulta, que remove usuários que vivem em cidades onde há locais de venda de ingressos. Essa consulta pode ser gravada de diferentes maneiras. Por exemplo, a subconsulta pode ser regravada como uma junção na consulta principal. ``` 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) ``` ## Subconsultas da cláusula WITH Consulte [Cláusula WITH](r_WITH_clause.md). # Subconsultas correlacionadas O exemplo a seguir contém uma *subconsulta correlacionada* na cláusula WHERE. Esse tipo de subconsulta contém uma ou mais correlações entre as colunas e as colunas produzidas pela consulta externa. Nesse caso, a correlação é `where s.listid=l.listid`. Para cada linha que a consulta externa produz, a subconsulta é executada para qualificar ou desqualificar a linha. ``` 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) ``` ## Padrões de subconsultas correlacionadas não compatíveis O planejador de consultas usa um método de regravação de consulta chamado decorrelação de subconsultas para otimizar vários padrões de subconsultas correlacionadas para execução em um ambiente de processamento paralelo massivo (MPP). Alguns tipos de subconsultas correlacionadas seguem padrões cuja correlação o Amazon Redshift não pode anular e aos quais ele não oferece suporte. Consultas que contenham erros de retorno das seguintes referências de correlação: + Referências de correlação que ignoram um bloco de consultas, também conhecidas como "referências de correlação para ignorar consultas". Por exemplo, na consulta a seguir, o bloco contendo a referência de correlação e o bloco ignorado estão conectados por um predicado NOT EXISTS: ``` select event.eventname from event where not exists (select * from listing where not exists (select * from sales where event.eventid=sales.eventid)); ``` O bloco ignorado nesse caso é a subconsulta na tabela LISTING. A referência de correlação correlaciona as tabelas EVENT e SALES. + Referências de correlação de uma subconsulta que é parte de uma cláusula ON em uma consulta externa: ``` select * from category left join event on category.catid=event.catid and eventid = (select max(eventid) from sales where sales.eventid=event.eventid); ``` A cláusula ON contém uma referência de correlação de SALES na subconsulta de EVENT na consulta externa. + Referências de correlação nulo-sensíveis a uma tabela do sistema Amazon Redshift. Por exemplo: ``` 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); ``` + Referências de correlação de dentro de uma subconsulta que contém uma função de janela. ``` select listid, qtysold from sales s where qtysold not in (select sum(numtickets) over() from listing l where s.listid=l.listid); ``` + Referências em uma coluna GROUP BY para os resultados de um subconsulta correlacionada. Por exemplo: ``` select listing.listid, (select count (sales.listid) from sales where sales.listid=listing.listid) as list from listing group by list, listing.listid; ``` + Referências de correlação de uma subconsulta com uma função agregada e uma cláusula GROUP BY, conectada à consulta externa por um predicado IN. (Essa restrição não se aplica a funções agregadas MIN e MAX.) Por exemplo: ``` select * from listing where listid in (select sum(qtysold) from sales where numtickets>4 group by salesid); ```