SnowConvert: Instrução Redshift SELECT¶

SELECT¶

Descrição¶

Retorna linhas de tabelas, visualizações e funções definidas pelo usuário. (Instrução SELECT de referência de linguagem Redshift SQL)

Sintaxe da gramática¶

 [ WITH with_subquery [, ...] ]
SELECT
[ TOP number | [ ALL | DISTINCT ]
* | expression [ AS output_name ] [, ...] ]
[ FROM table_reference [, ...] ]
[ WHERE condition ]
[ [ START WITH expression ] CONNECT BY expression ]
[ GROUP BY expression [, ...] ]
[ HAVING condition ]
[ QUALIFY condition ]
[ { UNION | ALL | INTERSECT | EXCEPT | MINUS } query ]
[ ORDER BY expression [ ASC | DESC ] ]
[ LIMIT { number | ALL } ]
[ OFFSET start ]

Copy

Para obter mais informações, consulte cada um dos links a seguir:

Cláusula WITH
Lista SELECT
Cláusula FROM
Cláusula WHERE
Cláusula CONNECT BY
Cláusula GROUP BY
Cláusula HAVING
Cláusula QUALIFY
UNION, INTERSECT e EXCEPT
Cláusula ORDER BY

Cláusula CONNECT BY¶

Descrição¶

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. (Cláusula CONNECT BY de referência de linguagem Redshift SQL)

A cláusulaCONNECT BY é compatível com o Snowflake.

Grammar Syntax

 [START WITH start_with_conditions]
CONNECT BY connect_by_conditions

Copy

Amostra de padrões da origem

Código de entrada:

 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);

SELECT COUNT(*)
FROM
Employee "start"
CONNECT BY PRIOR id = manager_id
START WITH name = 'John';

Copy

COUNT(*)
12

Código de saída:

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

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);

SELECT COUNT(*)
FROM
  Employee "start"
CONNECT BY PRIOR id = manager_id
START WITH RTRIM( name) = RTRIM( 'John');

Copy

COUNT(*)
12

Não há problemas conhecidos.

Cláusula FROM

Description

A cláusula FROM em uma consulta lista as referências de tabela (tabelas, exibições e subconsultas) das quais os dados são selecionados. Se várias referências de tabela forem listadas, as tabelas deverão ser unidas, usando a sintaxe apropriada na cláusula FROM ou na cláusula WHERE. Se nenhum critério de junção for especificado, o sistema processará a consulta como uma junção cruzada. (Cláusula FROM de referência de linguagem Redshift SQL)

Aviso

A cláusula FROMé parcialmente suportada no Snowflake. A desvinculação de objetos não é suportada no momento.

Sintaxe da gramática¶

 FROM table_reference [, ...]

<table_reference> ::=
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 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 ]

Copy

Amostra de padrões da origem¶

Tipos de junção¶

O Snowflake suporta todos os tipos de junções. Para obter mais informações, consulte a documentação JOIN.

Código de entrada:¶

 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);

CREATE TABLE department (
    id INT,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO department(id, name, manager_id) VALUES
(1, 'HR', 100),
(2, 'Sales', 101),
(3, 'Engineering', 102),
(4, 'Marketing', 103);

SELECT e.name AS employee_name, d.name AS department_name
FROM employee e
INNER JOIN department d ON e.manager_id = d.manager_id;

SELECT e.name AS employee_name, d.name AS department_name
FROM employee e
LEFT JOIN department d ON e.manager_id = d.manager_id;

SELECT d.name AS department_name, e.name AS manager_name
FROM department d
RIGHT JOIN employee e ON d.manager_id = e.id;

SELECT e.name AS employee_name, d.name AS department_name
FROM employee e
FULL JOIN department d ON e.manager_id = d.manager_id;

Copy

Junção interna¶

EMPLOYEE_NAME	DEPARTMENT_NAME
John	HR
Jorge	Sales
Kwaku	Sales
Liu	Sales
Mateo	Engineering
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Sofía	Engineering

Junção à esquerda¶

EMPLOYEE_NAME	DEPARTMENT_NAME
Carlos	nulo
John	HR
Jorge	Vendas
Kwaku	Vendas
Liu	Vendas
Mateo	Engenharia
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Saanvi	nulo
Shirley	nulo
Sofía	Engenharia
Zhang	nulo

Junção à direita¶

DEPARTMENT_NAME	MANAGER_NAME
HR	Carlos
Vendas	John
Engenharia	Jorge
Marketing	Kwaku
nulo	Liu
nulo	Mateo
nulo	Nikki
nulo	Paulo
nulo	Richard
nulo	Saanvi
nulo	Shirley
nulo	Sofía
nulo	Zhang

Junção total¶

EMPLOYEE_NAME	DEPARTMENT_NAME
Carlos	nulo
John	HR
Jorge	Vendas
Kwaku	Vendas
Liu	Vendas
Mateo	Engenharia
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Saanvi	nulo
Shirley	nulo
Sofía	Engenharia
Zhang	nulo

Código de saída:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

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);

CREATE TABLE department (
    id INT,
    name VARCHAR(50),
    manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

INSERT INTO department (id, name, manager_id) VALUES
(1, 'HR', 100),
(2, 'Sales', 101),
(3, 'Engineering', 102),
(4, 'Marketing', 103);

SELECT e.name AS employee_name, d.name AS department_name
FROM
employee e
INNER JOIN
  department d ON e.manager_id = d.manager_id;

SELECT e.name AS employee_name, d.name AS department_name
FROM
employee e
LEFT JOIN
  department d ON e.manager_id = d.manager_id;

SELECT d.name AS department_name, e.name AS manager_name
FROM
department d
RIGHT JOIN
  employee e ON d.manager_id = e.id;

SELECT e.name AS employee_name, d.name AS department_name
FROM
employee e
FULL JOIN
  department d ON e.manager_id = d.manager_id;

Copy

Junção interna¶

EMPLOYEE_NAME	DEPARTMENT_NAME
John	HR
Jorge	Vendas
Kwaku	Vendas
Liu	Vendas
Mateo	Engenharia
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Sofía	Engenharia

Junção à esquerda¶

EMPLOYEE_NAME	DEPARTMENT_NAME
Carlos	nulo
John	HR
Jorge	Vendas
Kwaku	Vendas
Liu	Vendas
Mateo	Engenharia
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Saanvi	nulo
Shirley	nulo
Sofía	Engenharia
Zhang	nulo

Junção à direita¶

DEPARTMENT_NAME	MANAGER_NAME
HR	Carlos
Vendas	John
Engenharia	Jorge
Marketing	Kwaku
nulo	Liu
nulo	Mateo
nulo	Nikki
nulo	Paulo
nulo	Richard
nulo	Saanvi
nulo	Shirley
nulo	Sofía
nulo	Zhang

Junção total¶

EMPLOYEE_NAME	DEPARTMENT_NAME
Carlos	nulo
John	HR
Jorge	Vendas
Kwaku	Vendas
Liu	Vendas
Mateo	Engenharia
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Saanvi	nulo
Shirley	nulo
Sofía	Engenharia
Zhang	nulo

Cláusula Pivot¶

Nota

Os aliases de coluna não podem ser usados na cláusula IN da consulta PIVOT no Snowflake.

Código de entrada:¶

 SELECT *
FROM
    (SELECT e.manager_id, d.name AS department, e.id AS employee_id
     FROM employee e
     JOIN department d ON e.manager_id = d.manager_id) AS SourceTable
PIVOT
    (
     COUNT(employee_id)
     FOR department IN ('HR', 'Sales', 'Engineering', 'Marketing')
    ) AS PivotTable;

Copy

MANAGER_ID	“HR”	“Sales”	“Engineering”	“Marketing”
100	1	0	0	0
101	0	3	0	0
102	0	0	2	0
103	0	0	0	3

Código de saída:¶

 SELECT *
FROM
    (SELECT e.manager_id, d.name AS department, e.id AS employee_id
     FROM
     employee e
     JOIN
         department d ON e.manager_id = d.manager_id) AS SourceTable
PIVOT
    (
     COUNT(employee_id)
     FOR department IN ('HR', 'Sales', 'Engineering', 'Marketing')
    ) AS PivotTable;

Copy

MANAGER_ID	“HR”	“Sales”	“Engineering”	“Marketing”
100	1	0	0	0
101	0	3	0	0
102	0	0	2	0
103	0	0	0	3

Cláusula Unpivot¶

Nota

Os aliases de coluna não podem ser usados na cláusula IN da consulta UNPIVOT no Snowflake.

Código de entrada:¶

 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);


SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
    cnt FOR color IN (red r, green as g, blue)
);

Copy

COLOR	CNT
RED	15
RED	35
RED	10
GREEN	20
GREEN	23
BLUE	7
BLUE	40

Código de saída:¶

 CREATE TABLE count_by_color (quality VARCHAR, red INT, green INT, blue INT)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

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);


SELECT *
FROM (SELECT red, green, blue FROM
            count_by_color
    ) UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

SELECT *
FROM (SELECT red, green, blue FROM
            count_by_color
) UNPIVOT (
    cnt FOR color IN (red
                          !!!RESOLVE EWI!!! /*** SSC-EWI-RS0005 - COLUMN ALIASES CANNOT BE USED IN THE IN CLAUSE OF THE PIVOT/UNPIVOT QUERY IN SNOWFLAKE. ***/!!!
 r, green
          !!!RESOLVE EWI!!! /*** SSC-EWI-RS0005 - COLUMN ALIASES CANNOT BE USED IN THE IN CLAUSE OF THE PIVOT/UNPIVOT QUERY IN SNOWFLAKE. ***/!!!
 as g, blue)
);

Copy

COLOR	CNT
RED	15
GREEN	20
BLUE	7
RED	35
BLUE	40
RED	10
GREEN	23

EWIs relacionados¶

SSC-EWI-RS0005: Os aliases de coluna não podem ser usados na cláusula IN da consulta PIVOT/UNPIVOT no Snowflake.

Cláusula GROUP BY¶

Descrição¶

A cláusula GROUP BY identifica as colunas de agrupamento da consulta. As colunas de agrupamento devem ser declaradas quando a consulta calcula os agregados com funções padrão, como SUM, AVG e COUNT. (Cláusula GROUP BY de referência de linguagem Redshift SQL)

A cláusula GROUP BYé totalmente compatível com o Snowflake.

Grammar Syntax

 GROUP BY group_by_clause [, ...]

group_by_clause := {
    expr |
    GROUPING SETS ( () | group_by_clause [, ...] ) |
    ROLLUP ( expr [, ...] ) |
    CUBE ( expr [, ...] )
    }

Copy

Sample Source Patterns

Conjuntos de agrupamento

Input Code:

 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);

SELECT 
    manager_id,
    COUNT(id) AS total_employees
FROM employee
GROUP BY GROUPING SETS 
    ((manager_id), ())
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
nulo	1
nulo	13

Output Code:

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

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);

SELECT
    manager_id,
    COUNT(id) AS total_employees
FROM
    employee
GROUP BY GROUPING SETS
    ((manager_id), ())
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
nulo	1
nulo	13

Agrupar por cubo

Input Code:

 SELECT
    manager_id,
    COUNT(id) AS total_employees
FROM
    employee
GROUP BY CUBE(manager_id)
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
nulo	1
nulo	13

Output Code:

 SELECT
    manager_id,
    COUNT(id) AS total_employees
FROM
    employee
GROUP BY CUBE(manager_id)
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
nulo	1
nulo	13

Agrupar por rollup

Input Code:

 SELECT
    manager_id,
    COUNT(id) AS total_employees
FROM
    employee
GROUP BY ROLLUP(manager_id)
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
nulo	1
nulo	13

Output Code:

 SELECT
    manager_id,
    COUNT(id) AS total_employees
FROM
    employee
GROUP BY ROLLUP(manager_id)
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
100	1
101	3
102	2
103	3
104	3
nulo	1
nulo	13

Related EWIs

Não há problemas conhecidos.

Cláusula HAVING

Description

A cláusula HAVING aplica uma condição ao conjunto de resultados agrupados intermediários que uma consulta retorna. (Cláusula HAVING de referência de linguagem Redshift SQL)

A cláusulaHAVING é totalmente compatível com o Snowflake.

Sintaxe da gramática¶

 [ HAVING condition ]

Copy

Amostra de padrões da origem¶

Código de entrada:¶

 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);

SELECT manager_id, COUNT(id) AS total_employees
FROM
employee
GROUP BY manager_id
HAVING COUNT(id) > 2
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
101	3
103	3
104	3

Código de saída:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

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);

SELECT manager_id, COUNT(id) AS total_employees
FROM
employee
GROUP BY manager_id
HAVING COUNT(id) > 2
ORDER BY manager_id;

Copy

MANAGER_ID	TOTAL_EMPLOYEES
101	3
103	3
104	3

EWIs relacionados¶

Não há problemas conhecidos.

Cláusula ORDER BY¶

Descrição¶

A cláusula ORDER BY classifica o conjunto de resultados de uma consulta. (Cláusula Order By de referência de linguagem Redshift SQL)

A cláusulaORDER BY é totalmente compatível com o Snowflake.

Grammar Syntax

 [ ORDER BY expression [ ASC | DESC ] ]
[ NULLS FIRST | NULLS LAST ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]

Copy

Sample Source Patterns

Input Code:

 CREATE TABLE employee (
    id INT,
    name VARCHAR(20),
    manager_id INT,
    salary DECIMAL(10, 2)
);

INSERT INTO employee (id, name, manager_id, salary) VALUES
(100, 'Carlos', NULL, 120000.00),
(101, 'John', 100, 90000.00),
(102, 'Jorge', 101, 95000.00),
(103, 'Kwaku', 101, 105000.00),
(104, 'Paulo', 102, 110000.00),
(105, 'Richard', 102, 85000.00),
(106, 'Mateo', 103, 95000.00),
(107, 'Liu', 103, 108000.00),
(108, 'Zhang', 104, 95000.00);

SELECT id, name, manager_id, salary
FROM employee
ORDER BY salary DESC NULLS LAST, name ASC NULLS FIRST
LIMIT 5                                        
OFFSET 2;

Copy

ID	NAME	MANAGER_ID	SALARY
107	Liu	103	108000,00
103	Kwaku	101	105000,00
102	Jorge	101	95000,00
106	Mateo	103	95000,00
108	Zhang	104	95000,00

Output Code:

 CREATE TABLE employee (
    id INT,
    name VARCHAR(20),
    manager_id INT,
    salary DECIMAL(10, 2)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

INSERT INTO employee (id, name, manager_id, salary) VALUES
(100, 'Carlos', NULL, 120000.00),
(101, 'John', 100, 90000.00),
(102, 'Jorge', 101, 95000.00),
(103, 'Kwaku', 101, 105000.00),
(104, 'Paulo', 102, 110000.00),
(105, 'Richard', 102, 85000.00),
(106, 'Mateo', 103, 95000.00),
(107, 'Liu', 103, 108000.00),
(108, 'Zhang', 104, 95000.00);

SELECT id, name, manager_id, salary
FROM
    employee
ORDER BY salary DESC NULLS LAST, name ASC NULLS FIRST
LIMIT 5
OFFSET 2;

Copy

ID	NAME	MANAGER_ID	SALARY
107	Liu	103	108000,00
103	Kwaku	101	105000,00
102	Jorge	101	95000,00
106	Mateo	103	95000,00
108	Zhang	104	95000,00

Related EWIs

Não há problemas conhecidos.

Cláusula QUALIFY

Description

A cláusula QUALIFY filtra os resultados de uma função de janela calculada anteriormente 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. (Cláusula QUALIFY de referência de linguagem Redshift SQL)

A cláusulaQUALIFY é compatível com o Snowflake.

Sintaxe da gramática¶

 QUALIFY condition

Copy

Amostra de padrões da origem¶

Código de entrada:¶

 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);

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;

Copy

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

Código de saída:¶

 CREATE TABLE store_sales
(
    ss_sold_date DATE,
    ss_sold_time TIME,
    ss_item TEXT,
    ss_sales_price FLOAT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

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);

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;

Copy

SS_SOLD_DATE	SS_SOLD_TIME	SS_ITEM	SS_SALES_PRICE
2022-01-02	16:00:00	Product 7	5
2022-01-01	17:00:00	Product 4	1000
2022-01-01	18:00:00	Product 5	30

EWIs relacionados¶

Não há problemas conhecidos.

Lista SELECT¶

Descrição¶

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. (Lista SELECT de referência de linguagem Redshift SQL)

As opções de início de consulta são totalmente compatíveis com o Snowflake. Lembre-se de que, no Snowflake, as opções DISTINCT e ALL devem estar no início da consulta.

Nota

No Redshift, se seu aplicativo permitir chaves estrangeiras ou chaves primárias inválidas, isso pode fazer com que as consultas retornem resultados incorretos. Por exemplo, uma consulta SELECT DISTINCT poderia retornar linhas duplicadas se a coluna de chave primária não contivesse todos os valores exclusivos. (Lista SELECT de referência de linguagem Redshift SQL)

Sintaxe da gramática¶

 SELECT
[ TOP number ]
[ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]

Copy

Amostra de padrões da origem¶

Cláusula superior¶

Código de entrada:¶

 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);
  
SELECT TOP 5 id, name, manager_id
FROM employee;

Copy

ID	NAME	MANAGER_ID
100	Carlos	nulo
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

Código de saída:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

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);

SELECT TOP 5 id, name, manager_id
FROM
    employee;

Copy

ID	NAME	MANAGER_ID
100	Carlos	nulo
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

ALL¶

Código de entrada:¶

SELECT ALL manager_id
FROM employee;

Copy

MANAGER_ID
nulo
100
101
101
101
102
103
103
103
104
104
102
104

Código de saída:¶

 SELECT ALL manager_id
FROM
    employee;

Copy

MANAGER_ID
nulo
100
101
101
101
102
103
103
103
104
104
102
104

DISTINCT¶

Código de entrada:¶

SELECT DISTINCT manager_id
FROM employee;

Copy

MANAGER_ID
nulo
100
101
102
103
104

Código de saída:¶

SELECT DISTINCT manager_id
FROM 
    employee;

Copy

MANAGER_ID
nulo
100
101
102
103
104

EWIs relacionados¶

Não há problemas conhecidos.

UNION, INTERSECT e EXCEPT¶

Descrição¶

Os set operators UNION, INTERSECT e EXCEPT são usados para comparar e mesclar os resultados de duas expressões de consulta separadas. (Set Operators de referência de linguagem Redshift SQL)

Os operadores de conjunto são totalmente compatíveis com o Snowflake.

Grammar Syntax

 query
{ UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query

Copy

Sample Source Patterns

Input Code:

 SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

UNION

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 102

UNION ALL

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

INTERSECT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 103

EXCEPT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 104;

Copy

ID	NAME	MANAGER_ID
103	Kwaku	101
110	Liu	101
102	Jorge	101
106	Mateo	102
201	Sofía	102

Output Code:

 SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

UNION

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 102

UNION ALL

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 101

INTERSECT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 103

EXCEPT

SELECT id, name, manager_id
FROM
employee
WHERE manager_id = 104;

Copy

ID	NAME	MANAGER_ID
102	Jorge	101
103	Kwaku	101
110	Liu	101
106	Mateo	102
201	Sofía	102

Related EWIs

Não há problemas conhecidos.

Cláusula WHERE

Description

A cláusula WHERE contém condições que unem tabelas ou aplicam predicados a colunas em tabelas. (Cláusula WHERE de referência de linguagem Redshift SQL)

A cláusula WHEREé totalmente compatível com o Snowflake.

Sintaxe da gramática¶

 [ WHERE condition ]

Copy

Amostra de padrões da origem¶

Código de entrada:¶

 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);

SELECT id, name, manager_id
FROM employee
WHERE name LIKE 'J%';

Copy

ID	NAME	MANAGER_ID
101	John	100
102	Jorge	101

Código de saída:¶

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

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);

SELECT id, name, manager_id
FROM
  employee
WHERE name LIKE 'J%' ESCAPE '\\';

Copy

ID	NAME	MANAGER_ID
101	John	100
102	Jorge	101

EWIs relacionados¶

Não há problemas conhecidos.

Cláusula WITH¶

Descrição¶

A cláusula WITH é uma cláusula opcional que precede a lista SELECT em uma consulta. A cláusula WITH define uma ou mais common_table_expressions. Cada expressão de tabela comum (CTE) define uma tabela temporária, que é semelhante a uma definição de visualização. Você pode fazer referência a essas tabelas temporárias na cláusula FROM. (Cláusula WITH de referência de linguagem Redshift SQL)

A cláusulaWITH é totalmente compatível com o Snowflake.

Grammar Syntax

 [ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]

--Where common_table_expression can be either non-recursive or recursive. 
--Following is the non-recursive form:
CTE_table_name [ ( column_name [, ...] ) ] AS ( query )

--Following is the recursive form of common_table_expression:
CTE_table_name (column_name [, ...] ) AS ( recursive_query )

Copy

Sample Source Patterns

Forma recursiva

Input Code:

 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);
  

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;

Copy

ID	NAME	MANAGER_ID
101	John	100
110	Liu	101
102	Jorge	101
103	Kwaku	101
201	Sofía	102
106	Mateo	102
105	Richard	103
104	Paulo	103
110	Nikki	103
205	Zhang	104
120	Saanvi	104
200	Shirley	104

Output Code:

 CREATE TABLE employee (
  id INT,
  name VARCHAR(20),
  manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/05/2024",  "domain": "test" }}';

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);


WITH RECURSIVE john_org(id, name, manager_id, level) AS
( SELECT id, name, manager_id, 1 AS level
  FROM
    employee
  WHERE
    RTRIM( name) = RTRIM( '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;

Copy

ID	NAME	MANAGER_ID
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101
106	Mateo	102
201	Sofía	102
110	Nikki	103
104	Paulo	103
105	Richard	103
120	Saanvi	104
200	Shirley	104
205	Zhang	104

Forma não recursiva

Input Code:

 WITH ManagerHierarchy AS (
    SELECT id AS employee_id, name AS employee_name, manager_id
    FROM employee
)
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM ManagerHierarchy e
LEFT JOIN ManagerHierarchy m ON e.manager_id = m.employee_id;

Copy

EMPLOYEE	MANAGER
Carlos	nulo
John	Carlos
Jorge	John
Kwaku	John
Liu	John
Mateo	Jorge
Sofía	Jorge
Nikki	Kwaku
Paulo	Kwaku
Richard	Kwaku
Saanvi	Paulo
Shirley	Paulo
Zhang	Paulo

Output Code:

 WITH ManagerHierarchy AS (
    SELECT id AS employee_id, name AS employee_name, manager_id
    FROM
    employee
)
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM
    ManagerHierarchy e
LEFT JOIN
    ManagerHierarchy m ON e.manager_id = m.employee_id;

Copy

EMPLOYEE	MANAGER
John	Carlos
Jorge	John
Kwaku	John
Liu	John
Mateo	Jorge
Sofía	Jorge
Nikki	Kwaku
Paulo	Kwaku
Richard	Kwaku
Saanvi	Paulo
Shirley	Paulo
Zhang	Paulo
Carlos	nulo

Related EWIs

Não há problemas conhecidos.