SnowConvert : instruction SELECT de Redshift¶

SELECT¶

Description¶

Renvoie des lignes à partir de tables, de vues et de fonctions définies par l’utilisateur. (Référence linguistique Redshift SQL instruction SELECT)

Grammar Syntax¶

 [ 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

Pour plus d’informations, veuillez vous référer à chacun des liens suivants :

Clause WITH
Liste SELECT
Clause FROM
Clause WHERE
Clause CONNECT BY
Clause GROUP BY
Clause HAVING
Clause QUALIFY
UNION, INTERSECT et EXCEPT
Clause ORDER BY

Clause CONNECT BY¶

Description¶

La clause CONNECT BY spécifie la relation entre les lignes d’une hiérarchie. Vous pouvez utiliser CONNECT BY pour sélectionner des lignes dans un ordre hiérarchique en joignant la table à elle-même et en traitant les données hiérarchiques. (Référence linguistique Redshift SQL clause CONNECT BY)

La clauseCONNECT BY est prise en charge dans Snowflake.

Grammar Syntax

 [START WITH start_with_conditions]
CONNECT BY connect_by_conditions

Copy

Modèles d’échantillons de sources

Code d’entrée :

 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

Code de sortie :

 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

Il n’y a pas de problème connu.

Clause FROM

Description

La clause FROM d’une requête annonce les références des tables (tables, vues et sous-requêtes) à partir desquelles les données sont sélectionnées. Si plusieurs références de tables sont annoncées, les tables doivent être reliées en utilisant la syntaxe appropriée dans la clause FROM ou la clause WHERE. Si aucun critère de jointure n’est spécifié, le système traite la requête comme une jointure croisée. (Référence linguistique Redshift SQL clause FROM)

Avertissement

La clause FROM est partiellement prise en charge dans Snowflake. Le dépivotage d’objet n’est pas pris en charge actuellement.

Grammar Syntax¶

 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

Modèles d’échantillons de sources¶

Types de jonctions¶

Snowflake prend en charge tous les types de jonctions. Pour plus d’informations, voir la documentation JOIN.

Code d’entrée :¶

 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

Jointure intérieure¶

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

Jointure gauche¶

EMPLOYEE_NAME	DEPARTMENT_NAME
Carlos	null
John	HR
Jorge	Sales
Kwaku	Sales
Liu	Sales
Mateo	Engineering
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Saanvi	null
Shirley	null
Sofía	Engineering
Zhang	null

Jointure droite¶

DEPARTMENT_NAME	MANAGER_NAME
HR	Carlos
Sales	John
Engineering	Jorge
Marketing	Kwaku
null	Liu
null	Mateo
null	Nikki
null	Paulo
null	Richard
null	Saanvi
null	Shirley
null	Sofía
null	Zhang

Jointure complète¶

EMPLOYEE_NAME	DEPARTMENT_NAME
Carlos	null
John	HR
Jorge	Sales
Kwaku	Sales
Liu	Sales
Mateo	Engineering
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Saanvi	null
Shirley	null
Sofía	Engineering
Zhang	null

Code de sortie :¶

 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

Jointure intérieure¶

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

Jointure gauche¶

EMPLOYEE_NAME	DEPARTMENT_NAME
Carlos	null
John	HR
Jorge	Sales
Kwaku	Sales
Liu	Sales
Mateo	Engineering
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Saanvi	null
Shirley	null
Sofía	Engineering
Zhang	null

Jointure droite¶

DEPARTMENT_NAME	MANAGER_NAME
HR	Carlos
Sales	John
Engineering	Jorge
Marketing	Kwaku
null	Liu
null	Mateo
null	Nikki
null	Paulo
null	Richard
null	Saanvi
null	Shirley
null	Sofía
null	Zhang

Jointure complète¶

EMPLOYEE_NAME	DEPARTMENT_NAME
Carlos	null
John	HR
Jorge	Sales
Kwaku	Sales
Liu	Sales
Mateo	Engineering
Nikki	Marketing
Paulo	Marketing
Richard	Marketing
Saanvi	null
Shirley	null
Sofía	Engineering
Zhang	null

Clause pivot¶

Note

Les alias de colonne ne peuvent pas être utilisés dans la clause IN de la requête PIVOT dans Snowflake.

Code d’entrée :¶

 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”	Ventes	Ingénierie	Marketing
100	1	0	0	0
101	0	3	0	0
102	0	0	2	0
103	0	0	0	3

Code de sortie :¶

 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”	Ventes	Ingénierie	Marketing
100	1	0	0	0
101	0	3	0	0
102	0	0	2	0
103	0	0	0	3

Clause Unpivot¶

Note

Les alias de colonne ne peuvent pas être utilisés dans la clause IN de la requête UNPIVOT dans Snowflake.

Code d’entrée :¶

 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

Code de sortie :¶

 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 connexes¶

SSC-EWI-RS0005 : les alias de colonne ne peuvent pas être utilisés dans la clause IN de la requête PIVOT/UNPIVOT dans Snowflake.

Clause GROUP BY¶

Description¶

La clause GROUP BY identifie les colonnes de regroupement de la requête. Les colonnes de regroupement doivent être déclarées lorsque la requête calcule des agrégats avec des fonctions standard telles que SUM, AVG, et COUNT. (Référence linguistique Redshift SQL clause GROUP BY)

La clause GROUP BY est entièrement prise en charge dans 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

Regroupement des ensembles

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
null	1
null	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
null	1
null	13

Grouper par cube

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
null	1
null	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
null	1
null	13

Grouper par 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
null	1
null	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
null	1
null	13

Related EWIs

Il n’y a pas de problème connu.

Clause HAVING

Description

La clause HAVING applique une condition au jeu de résultats groupés intermédiaire renvoyé par une requête. (Référence linguistique Redshift SQL clause HAVING)

La clause HAVING est entièrement prise en charge dans Snowflake.

Grammar Syntax¶

 [ HAVING condition ]

Copy

Modèles d’échantillons de sources¶

Code d’entrée :¶

 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

Code de sortie :¶

 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 connexes¶

Il n’y a pas de problème connu.

Clause ORDER BY¶

Description¶

La clause ORDER BY permet de trier le jeu de résultats d’une requête. (Référence linguistique Redshift SQL clause Order By)

La clause ORDER BY est entièrement prise en charge dans 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

Il n’y a pas de problème connu.

Clause QUALIFY

Description

La clause QUALIFY filtre les résultats d’une fonction de fenêtre précédemment calculée en fonction des conditions de recherche spécifiées par l’utilisateur. Vous pouvez utiliser la clause pour appliquer des conditions de filtre au résultat d’une fonction de fenêtre sans utiliser de sous-requête. (Référence linguistique Redshift SQL clause QUALIFY)

La clauseQUALIFY est prise en charge dans Snowflake.

Grammar Syntax¶

 QUALIFY condition

Copy

Modèles d’échantillons de sources¶

Code d’entrée :¶

 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	Produit 4	1000
2022-01-01	18:00:00	Produit 5	30
2022-01-02	16:00:00	Produit 7	5

Code de sortie :¶

 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	Produit 7	5
2022-01-01	17:00:00	Produit 4	1000
2022-01-01	18:00:00	Produit 5	30

EWIs connexes¶

Il n’y a pas de problème connu.

Liste SELECT¶

Description¶

La liste SELECT nomme les colonnes, les fonctions et les expressions que vous souhaitez que la requête renvoie. La liste représente la sortie de la requête. (Référence linguistique (Redshift SQL liste SELECT)

Les options de démarrage des requêtes sont entièrement prises en charge dans Snowflake. Gardez à l’esprit que dans Snowflake, les options DISTINCT et ALL doivent être placées au début de la requête.

Note

Dans Redshift, si votre application autorise les clés étrangères ou les clés primaires non valides, cela peut amener les requêtes à renvoyer des résultats incorrects. Par exemple, une requête SELECT DISTINCT pourrait renvoyer des lignes en double si la colonne de clé primaire ne contient pas toutes les valeurs uniques. (Référence linguistique Redshift SQL liste SELECT)

Grammar Syntax¶

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

Copy

Modèles d’échantillons de sources¶

Clause Top¶

Code d’entrée :¶

 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	null
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

Code de sortie :¶

 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	null
101	John	100
102	Jorge	101
103	Kwaku	101
110	Liu	101

ALL¶

Code d’entrée :¶

SELECT ALL manager_id
FROM employee;

Copy

MANAGER_ID
null
100
101
101
101
102
103
103
103
104
104
102
104

Code de sortie :¶

 SELECT ALL manager_id
FROM
    employee;

Copy

MANAGER_ID
null
100
101
101
101
102
103
103
103
104
104
102
104

DISTINCT¶

Code d’entrée :¶

SELECT DISTINCT manager_id
FROM employee;

Copy

MANAGER_ID
null
100
101
102
103
104

Code de sortie :¶

SELECT DISTINCT manager_id
FROM 
    employee;

Copy

MANAGER_ID
null
100
101
102
103
104

EWIs connexes¶

Il n’y a pas de problème connu.

UNION, INTERSECT et EXCEPT¶

Description¶

Les opérateurs Set UNION, INTERSECT et EXCEPT sont utilisés pour comparer et fusionner les résultats de deux expressions de requête distinctes. (Référence linguistique Redshift SQL opérateurs Set)

Les opérateurs Set sont entièrement pris en charge dans 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

Il n’y a pas de problème connu.

Clause WHERE

Description

La clause WHERE contient des conditions qui joignent des tables ou appliquent des prédicats aux colonnes des tables. (Référence linguistique Redshift SQL clause WHERE )

La clause WHERE est entièrement prise en charge dans Snowflake.

Grammar Syntax¶

 [ WHERE condition ]

Copy

Modèles d’échantillons de sources¶

Code d’entrée :¶

 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

Code de sortie :¶

 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 connexes¶

Il n’y a pas de problème connu.

Clause WITH¶

Description¶

Une clause WITH est une clause facultative qui précède la liste SELECT dans une requête. La clause WITH définit une ou plusieurs common_table_expressions. Chaque expression de table commune (CTE) définit une table temporaire, qui est similaire à la définition d’une vue. Vous pouvez faire référence à ces tables temporaires dans la clause FROM. (Référence linguistique Redshift SQL clause WITH)

La clause WITH est entièrement prise en charge dans 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

Forme récursive

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

Forme non récursive

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	null
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	null

Related EWIs

Il n’y a pas de problème connu.