SnowConvert: Redshift SELECT 문¶

SELECT¶

설명¶

테이블, 뷰 및 사용자 정의 함수의 행을 반환합니다. (Redshift SQL Language Reference SELECT 문)

문법 구문¶

 [ 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 Language Reference CONNECT BY 절)

CONNECT BY 절 은 Snowflake에서 지원됩니다.

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 Language Reference FROM 절)

경고

FROM 절은 Snowflake에서 부분적으로 지원됩니다. Object 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	마케팅
Paulo	마케팅
Richard	마케팅
Saanvi	Null
Shirley	Null
Sofía	Engineering
Zhang	Null

오른쪽 조인¶

DEPARTMENT_NAME	MANAGER_NAME
HR	Carlos
Sales	John
Engineering	Jorge
마케팅	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	마케팅
Paulo	마케팅
Richard	마케팅
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	마케팅
Paulo	마케팅
Richard	마케팅
Sofía	Engineering

왼쪽 조인¶

EMPLOYEE_NAME	DEPARTMENT_NAME
Carlos	Null
John	HR
Jorge	Sales
Kwaku	Sales
Liu	Sales
Mateo	Engineering
Nikki	마케팅
Paulo	마케팅
Richard	마케팅
Saanvi	Null
Shirley	Null
Sofía	Engineering
Zhang	Null

오른쪽 조인¶

DEPARTMENT_NAME	MANAGER_NAME
HR	Carlos
Sales	John
Engineering	Jorge
마케팅	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	마케팅
Paulo	마케팅
Richard	마케팅
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’	‘마케팅’
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’	‘마케팅’
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 Language Reference GROUP BY 절)

GROUP BY 절은 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

그룹화 세트

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

큐브별 그룹화

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

롤업별 그룹화

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 Language Reference HAVING 절)

HAVING 절 은 Snowflake에서 완전히 지원됩니다.

문법 구문¶

 [ 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 Language Reference Order By 절)

ORDER BY 절 은 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

알려진 문제는 없습니다.

QUALIFY 절

Description

QUALIFY 절은 사용자가 지정한 검색 조건에 따라 이전에 계산된 윈도우 함수의 결과를 필터링합니다. 이 절을 사용하면 하위 쿼리를 사용하지 않고 윈도우 함수의 결과에 필터링 조건을 적용할 수 있습니다. (Redshift SQL Language Reference QUALIFY 전)

QUALIFY 절 은 Snowflake에서 지원됩니다.

문법 구문¶

 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 Language Reference SELECT 목록)

쿼리 시작 옵션 은 Snowflake에서 완벽하게 지원됩니다. Snowflake에서는 DISTINCT 및 ALL 옵션이 쿼리 시작 부분에 있어야 한다는 점을 기억하십시오.

참고

애플리케이션에서 외래 키 또는 유효하지 않은 기본 키를 허용하는 경우 Redshift에서 쿼리 결과가 잘못 반환될 수 있습니다. 예를 들어, 기본 키 열에 모든 고유 값이 포함되어 있지 않은 경우 SELECT DISTINCT 쿼리는 중복 행을 반환할 수 있습니다. (Redshift SQL Language Reference SELECT 목록)

문법 구문¶

 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 _set 연산자_는 2개의 개별 쿼리 식의 결과를 비교하고 병합하는 데 사용됩니다. (Redshift SQL Language Reference Set 연산자)

Set 연산자 는 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 Language Reference WHERE 절)

WHERE 절은 Snowflake에서 완전히 지원됩니다.

문법 구문¶

 [ 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 절은 1개 이상의 _common_table_expression_을 정의합니다. 각 공통 테이블 식(CTE)은 뷰 정의와 유사한 임시 테이블을 정의합니다. FROM 절에서 이러한 임시 테이블을 참조할 수 있습니다. (Redshift SQL Language Reference WITH 절)

WITH 절 은 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

재귀 형식

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:

큐브별 그룹화

Input Code:

Output Code:

롤업별 그룹화

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¶

입력 코드:¶