Snowpark Migration Accelerator: 그룹화 기준¶

설명¶

GROUP BY 절은 지정된 식에 따라 행을 그룹화하고 각 그룹에 대한 집계 함수를 계산합니다. Databricks SQL 은 GROUPING SETS, CUBE 및 ROLLUP 절을 통해 고급 그룹화 옵션을 제공하여 동일한 데이터 세트에 대한 여러 집계를 허용합니다. 정규식 그룹화 식을 GROUP BY 절에서 이러한 고급 옵션과 결합하고 GROUPING SETS 내에 중첩할 수 있습니다. (Databricks SQL Language Reference GROUP BY)

지정된 열에서 동일한 값을 공유하는 행을 그룹화하고 각 그룹에 대해 집계 함수(예: SUM, COUNT 또는 AVG)를 계산합니다. GROUP BY 절을 포함할 수 있습니다.

열의 이름
SELECT 목록에서 위치를 나타내는 숫자입니다.
유효한 식

확장:

GROUP BY CUBE, GROUP BY GROUPING SETS 및 GROUP BY ROLLUP

Snowflake SQL Language Reference GROUP BY

구문¶

GROUP BY ALL

GROUP BY group_expression [, ...] [ WITH ROLLUP | WITH CUBE ]

GROUP BY { group_expression | { ROLLUP | CUBE | GROUPING SETS } ( grouping_set [, ...] ) } [, ...]

grouping_set
   { expression |
     ( [ expression [, ...] ] ) }

Copy

SELECT ...
  FROM ...
  [ ... ]
  GROUP BY groupItem [ , groupItem [ , ... ] ]
  [ ... ]
  
SELECT ...
  FROM ...
  [ ... ]
  GROUP BY ALL
  [ ... ]
groupItem ::= { <column_alias> | <position> | <expr> }

SELECT ...
FROM ...
[ ... ]
GROUP BY CUBE ( groupCube [ , groupCube [ , ... ] ] )
[ ... ]

groupCube ::= { <column_alias> | <position> | <expr> }

SELECT ...
FROM ...
[ ... ]
GROUP BY GROUPING SETS ( groupSet [ , groupSet [ , ... ] ] )
[ ... ]

groupSet ::= { <column_alias> | <position> | <expr> }

SELECT ...
FROM ...
[ ... ]
GROUP BY ROLLUP ( groupRollup [ , groupRollup [ , ... ] ] )
[ ... ]

groupRollup ::= { <column_alias> | <position> | <expr> }

Copy

샘플 소스 패턴¶

설정 데이터¶

Databricks¶

CREATE TEMP VIEW dealer (id, city, car_model, quantity) AS
VALUES (100, 'Fremont', 'Honda Civic', 10),
       (100, 'Fremont', 'Honda Accord', 15),
       (100, 'Fremont', 'Honda CRV', 7),
       (200, 'Dublin', 'Honda Civic', 20),
       (200, 'Dublin', 'Honda Accord', 10),
       (200, 'Dublin', 'Honda CRV', 3),
       (300, 'San Jose', 'Honda Civic', 5),
       (300, 'San Jose', 'Honda Accord', 8);

Copy

Snowflake¶

CREATE TEMP TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
INSERT INTO dealer VALUES
        (100, 'Fremont', 'Honda Civic', 10),
        (100, 'Fremont', 'Honda Accord', 15),
        (100, 'Fremont', 'Honda CRV', 7),
        (200, 'Dublin', 'Honda Civic', 20),
        (200, 'Dublin', 'Honda Accord', 10),
        (200, 'Dublin', 'Honda CRV', 3),
        (300, 'San Jose', 'Honda Civic', 5),
        (300, 'San Jose', 'Honda Accord', 8);

Copy

패턴 코드¶

Databricks¶

-- 1. Sum of quantity per dealership. Group by `id`.
SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;

-- 2. Use column position in GROUP by clause.
SELECT id, sum(quantity) FROM dealer GROUP BY 1 ORDER BY 1;

-- 3. Multiple aggregations.
-- 3.1. Sum of quantity per dealership.
-- 3.2. Max quantity per dealership.
SELECT id, sum(quantity) AS sum, max(quantity) AS max
    FROM dealer GROUP BY id ORDER BY id;

-- 4. Count the number of distinct dealers in cities per car_model.
SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY car_model;

-- 5. Count the number of distinct dealers in cities per car_model, using GROUP BY ALL
SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY ALL;

-- 6. Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.
SELECT id,
         sum(quantity) FILTER (WHERE car_model IN ('Honda Civic', 'Honda CRV')) AS `sum(quantity)`
    FROM dealer
    GROUP BY id ORDER BY id;

-- 7. Aggregations using multiple sets of grouping columns in a single statement.
-- Following performs aggregations based on four sets of grouping columns.
-- 7.1. city, car_model
-- 7.2. city
-- 7.3. car_model
-- 7.4. Empty grouping set. Returns quantities for all city and car models.
SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
    ORDER BY city;

-- 8.Group by processing with `ROLLUP` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())
SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY city, car_model WITH ROLLUP
    ORDER BY city, car_model;

-- 9. Group by processing with `CUBE` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY city, car_model WITH CUBE
    ORDER BY city, car_model;

Copy

id	sum(quantity)
100	32
200	33
300	13

id	sum(quantity)
100	32
200	33
300	13

id	합계	max
100	32	15
200	33	20
300	13	8

car_model	카운트
Honda Civic	3
Honda CRV	2
Honda Accord	3

car_model	카운트
Honda Civic	3
Honda CRV	2
Honda Accord	3

id	sum(quantity)
100	17
200	23
300	5

도시	car_model	합계
NULL	Honda Civic	35
NULL	Honda Accord	33
NULL	NULL	78
NULL	Honda CRV	10
Dublin	Honda Civic	20
Dublin	NULL	33
Dublin	Honda CRV	3
Dublin	Honda Accord	10
Fremont	Honda Accord	15
Fremont	Honda Civic	10
Fremont	NULL	32
Fremont	Honda CRV	7
San Jose	Honda Accord	8
San Jose	NULL	13
San Jose	Honda Civic	5

도시	car_model	합계
NULL	NULL	78
Dublin	NULL	33
Dublin	Honda Accord	10
Dublin	Honda CRV	3
Dublin	Honda Civic	20
Fremont	NULL	32
Fremont	Honda Accord	15
Fremont	Honda CRV	7
Fremont	Honda Civic	10
San Jose	NULL	13
San Jose	Honda Accord	8
San Jose	Honda Civic	5

도시	car_model	합계
NULL	NULL	78
NULL	Honda Accord	33
NULL	Honda CRV	10
NULL	Honda Civic	35
Dublin	NULL	33
Dublin	Honda Accord	10
Dublin	Honda CRV	3
Dublin	Honda Civic	20
Fremont	NULL	32
Fremont	Honda Accord	15
Fremont	Honda CRV	7
Fremont	Honda Civic	10
San Jose	NULL	13
San Jose	Honda Accord	8
San Jose	Honda Civic	5

Snowflake¶

-- 1. Sum of quantity per dealership. Group by `id`.
SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;

-- 2. Use column position in GROUP by clause.
SELECT id, sum(quantity) FROM dealer GROUP BY 1 ORDER BY 1;

-- 3. Multiple aggregations.
-- 3.1. Sum of quantity per dealership.
-- 3.2. Max quantity per dealership.
SELECT id, sum(quantity) AS sum, max(quantity) AS max
    FROM dealer GROUP BY id ORDER BY id;

-- 4. Count the number of distinct dealers in cities per car_model.
SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY car_model;

-- 5. Count the number of distinct dealers in cities per car_model, using GROUP BY ALL
SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY ALL;

-- 6. Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.
SELECT 
    id,
    SUM(CASE WHEN car_model='Honda Civic' OR car_model='Honda CRV' THEN quantity ELSE NULL END) AS `sum(quantity)`
    FROM dealer
    GROUP BY id ORDER BY id;

-- 7. Aggregations using multiple sets of grouping columns in a single statement.
-- Following performs aggregations based on four sets of grouping columns.
-- 7.1. city, car_model
-- 7.2. city
-- 7.3. car_model
-- 7.4. Empty grouping set. Returns quantities for all city and car models.
SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
    ORDER BY city NULLS FIRST;
 

-- 8. Group by processing with `ROLLUP` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())
SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY ROLLUP (city, car_model)
    ORDER BY city NULLS FIRST, car_model NULLS FIRST;

-- 9. Group by processing with `CUBE` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY CUBE (city, car_model)
    ORDER BY city NULLS FIRST, car_model NULLS FIRST;

Copy

id	sum(quantity)
100	32
200	33
300	13

id	sum(quantity)
100	32
200	33
300	13

id	합계	max
100	32	15
200	33	20
300	13	8

car_model	카운트
Honda Civic	3
Honda CRV	2
Honda Accord	3

car_model	카운트
Honda Civic	3
Honda CRV	2
Honda Accord	3

id	sum(quantity)
100	17
200	23
300	5

도시	car_model	합계
NULL	Honda Civic	35
NULL	Honda Accord	33
NULL	NULL	78
NULL	Honda CRV	10
Dublin	Honda Civic	20
Dublin	NULL	33
Dublin	Honda CRV	3
Dublin	Honda Accord	10
Fremont	Honda Accord	15
Fremont	Honda Civic	10
Fremont	NULL	32
Fremont	Honda CRV	7
San Jose	Honda Accord	8
San Jose	NULL	13
San Jose	Honda Civic	5

도시	car_model	합계
NULL	NULL	78
Dublin	NULL	33
Dublin	Honda Accord	10
Dublin	Honda CRV	3
Dublin	Honda Civic	20
Fremont	NULL	32
Fremont	Honda Accord	15
Fremont	Honda CRV	7
Fremont	Honda Civic	10
San Jose	NULL	13
San Jose	Honda Accord	8
San Jose	Honda Civic	5

도시	car_model	합계
NULL	NULL	78
NULL	Honda Accord	33
NULL	Honda CRV	10
NULL	Honda Civic	35
Dublin	NULL	33
Dublin	Honda Accord	10
Dublin	Honda CRV	3
Dublin	Honda Civic	20
Fremont	NULL	32
Fremont	Honda Accord	15
Fremont	Honda CRV	7
Fremont	Honda Civic	10
San Jose	NULL	13
San Jose	Honda Accord	8
San Jose	Honda Civic	5

Known Issues¶

발견된 문제 없음

Snowpark Migration Accelerator: 그룹화 기준¶

설명¶

구문¶

샘플 소스 패턴¶

설정 데이터¶

Databricks¶

Snowflake¶

패턴 코드¶

Databricks¶

Snowflake¶

Known Issues¶

관련 EWIs¶