SnowConvert：Redshift SELECT 语句¶

SELECT¶

描述¶

从表、视图和用户定义的函数中返回行。（Redshift SQL 语言参考“SELECT 语句” (https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_synopsis.html)）

语法¶

 [ 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

有关更多信息，请参阅以下每个链接：

WITH 子句
SELECT 列表
FROM 子句
WHERE 子句
CONNECT BY 子句
GROUP BY 子句
HAVING 子句
QUALIFY 子句
UNION、INTERSECT 和 EXCEPT
ORDER BY 子句

CONNECT BY 子句¶

描述¶

CONNECT BY 子句指定了层次结构中的行之间的关系。通过将表与其自身联接并处理分层数据，您可以使用 CONNECT BY 按分层顺序选择行。（Redshift SQL 语言参考“CONNECT BY 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_CONNECT_BY_clause.html)）

Snowflake 支持 CONNECT BY 子句。

Grammar Syntax

 [START WITH start_with_conditions]
CONNECT BY connect_by_conditions

Copy

示例源模式

输入代码：

 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

输出代码：

 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

没有已知问题。

FROM 子句

Description

查询中的 FROM 子句列出了从中选择数据的表引用（表、视图和子查询）。如果列出了多个表引用，则必须在 FROM 子句或 WHERE 子句中使用相应语法联接这些表。如果未指定联接条件，则系统会将查询作为交叉联接进行处理。（Redshift SQL 语言参考“FROM 子句”） (https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause30.html)）

警告

Snowflake 部分支持 FROM 子句。目前不支持 Object 取消透视 (https://docs.aws.amazon.com/redshift/latest/dg/query-super.html#unpivoting)。

语法¶

 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

示例源模式¶

联接类型¶

Snowflake 支持所有类型的连接。有关更多信息，请参阅 JOIN 文档。

输入代码：¶

 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

内部联接¶

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

左联接¶

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

右联接¶

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

全连接¶

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

输出代码：¶

 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

内部联接¶

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

左联接¶

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

右联接¶

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

全连接¶

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

Pivot 子句¶

备注

在 Snowflake 中，PIVOT 查询的 IN 子句中不能使用列别名。

输入代码：¶

 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

输出代码：¶

 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

Unpivot 子句¶

备注

在 Snowflake 中，UNPIVOT 查询的 IN 子句中不能使用列别名。

输入代码：¶

 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

输出代码：¶

 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

GROUP BY 子句¶

描述¶

GROUP BY 子句标识了查询的分组列。当查询使用标准函数（如 SUM、AVG 和 COUNT）计算聚合时，必须声明分组列。（Redshift SQL 语言参考“GROUP BY 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_GROUP_BY_clause.html)）

Snowflake 完全支持 GROUP BY 子句。

Grammar Syntax

 GROUP BY group_by_clause [, ...]

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

Copy

Sample Source Patterns

分组集

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

Group by 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

Group by 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

没有已知问题。

HAVING 子句

Description

HAVING 子句可将条件应用于查询返回的中间分组结果集。（Redshift SQL 语言参考“HAVING 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html)）

Snowflake 完全支持 HAVING 子句。

语法¶

 [ HAVING condition ]

Copy

示例源模式¶

输入代码：¶

 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

输出代码：¶

 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

ORDER BY 子句¶

描述¶

ORDER BY 子句对查询的结果集进行排序。（Redshift SQL 语言参考“Order By 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_ORDER_BY_clause.html)）

Snowflake 完全支持 ORDER BY 子句。

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

没有已知问题。

QUALIFY 子句

Description

QUALIFY 子句根据用户指定的搜索条件筛选先前计算的窗口函数的结果。您可以使用该子句对窗口函数的结果应用筛选条件，而无需使用子查询。（Redshift SQL 语言参考“QUALIFY 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_QUALIFY_clause.html)）

Snowflake 支持 QUALIFY 子句。

语法¶

 QUALIFY condition

Copy

示例源模式¶

输入代码：¶

 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

输出代码：¶

 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

SELECT 列表¶

描述¶

SELECT 列表会命名您希望查询返回的列、函数和表达式。该列表表示查询的输出。（Redshift SQL 语言参考“SELECT 列表” (https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_list.html)）

Snowflake 完全支持查询起始选项。请记住，在 Snowflake 中，DISTINCT 和 ALL 选项必须放在查询的开头。

备注

在 Redshift 中，如果应用程序允许使用外键或无效的主键，则可能导致查询返回不正确的结果。例如，如果“主键”列中不包含所有唯一值，则 SELECT DISTINCT 查询可能会返回重复行。（Redshift SQL 语言参考“SELECT 列表” (https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_list.html)）

语法¶

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

Copy

示例源模式¶

Top 子句¶

输入代码：¶

 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

输出代码：¶

 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¶

输入代码：¶

SELECT ALL manager_id
FROM employee;

Copy

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

输出代码：¶

 SELECT ALL manager_id
FROM
    employee;

Copy

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

DISTINCT¶

输入代码：¶

SELECT DISTINCT manager_id
FROM employee;

Copy

MANAGER_ID
null
100
101
102
103
104

输出代码：¶

SELECT DISTINCT manager_id
FROM 
    employee;

Copy

MANAGER_ID
null
100
101
102
103
104

UNION、INTERSECT 和 EXCEPT¶

描述¶

UNION、INTERSECT 和 EXCEPT 集合运算符 用于比较和合并两个单独的查询表达式的结果。（Redshift SQL 语言参考“集合运算符” (https://docs.aws.amazon.com/redshift/latest/dg/r_UNION.html)）

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

没有已知问题。

WHERE 子句

Description

WHERE 子句中包含联接表或将谓词应用于表列的条件。（Redshift SQL 语言参考“WHERE 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_WHERE_clause.html)）

Snowflake 完全支持 WHERE 子句。

语法¶

 [ WHERE condition ]

Copy

示例源模式¶

输入代码：¶

 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

输出代码：¶

 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

WITH 子句¶

描述¶

WITH 子句是查询中的可选子句，需置于 SELECT 列表之前。WITH 子句定义了一个或多个 common_table_expressions。每个公用表表达式 (CTE) 都定义了一个临时表，这类似于视图定义。您可以在 FROM 子句中引用这些临时表。（Redshift SQL 语言参考“WITH 子句” (https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html)）

Snowflake 完全支持 WITH 子句。

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

递归形式

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

非递归形式

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

没有已知问题。

SnowConvert：Redshift SELECT 语句¶

SELECT¶

描述¶

语法¶

CONNECT BY 子句¶

描述¶

Grammar Syntax

示例源模式

输入代码：

输出代码：

相关的 EWIs

FROM 子句

Description

语法¶

示例源模式¶

联接类型¶

输入代码：¶

内部联接¶

左联接¶

右联接¶

全连接¶

输出代码：¶

内部联接¶

左联接¶

右联接¶

全连接¶

Pivot 子句¶

输入代码：¶

输出代码：¶

Unpivot 子句¶

输入代码：¶

输出代码：¶

相关的 EWIs¶

GROUP BY 子句¶

描述¶

Grammar Syntax

Sample Source Patterns

分组集

Input Code:

Output Code:

Group by Cube

Input Code:

Output Code:

Group by Rollup

Input Code:

Output Code:

Related EWIs

HAVING 子句

Description

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

相关的 EWIs¶

ORDER BY 子句¶

描述¶

Grammar Syntax

Sample Source Patterns

Input Code:

Output Code:

Related EWIs

QUALIFY 子句

Description

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

相关的 EWIs¶

SELECT 列表¶

描述¶

语法¶

示例源模式¶

Top 子句¶

输入代码：¶

输出代码：¶

ALL¶

输入代码：¶

输出代码：¶

DISTINCT¶

输入代码：¶