SQL 피벗: 행을 열로 변환
Databricks에서 이 Notebook을 사용해 보세요.
데이터 레이크하우스가 차세대 데이터 웨어하우스인 이유 전자 책에서 Databricks Lakehouse Platform의 내부 작동 방식을 알아보세요.
업데이트 날짜: 2018년 11월 10일
피벗은 Apache Spark 1.6에서 테이블 형식 표현식의 고유 값을 하나의 열에서 개별 열로 전환하여 테이블 형식 표현식을 회전할 수 있도록 하는 새로운 DataFrame 기능으로 처음 도입되었습니다.
Apache Spark 2.4 릴리스에서는 데이터 피벗의 강력한 기능이 SQL 사용자에게도 확장되었습니다. 이 블로그에서는 시애틀의 온도 기록을 사용하여 일반적인 SQL 피벗 기능을 통해 복잡한 데이터 변환을 수행하는 방법을 보여드리겠습니다.
피벗을 사용하여 여름 온도 살펴보기
올여름 시애틀의 기온이 불편한 수준으로 상승하여 7월에는 9일 동안 최고 80도 후반에서 90도까지 치솟았습니다.
| 날짜 | 온도(°F) |
|---|---|
| 07-22-2018 | 86 |
| 07-23-2018 | 90 |
| 07-24-2018 | 91 |
| 07-25-2018 | 92 |
| 07-26-2018 | 92 |
| 07-27-2018 | 88 |
| 07-28-2018 | 85 |
| 07-29-2018 | 94 |
| 07-30-2018 | 89 |
수은 수위 상승에 대한 과거 추세가 있는지 살펴보고 검토하려는 경우를 가정해 보겠습니다. 이러한 수치를 검토하고 제시하는 한 가지 직관적인 방법은 월을 열로 사용하고 각 연도의 월별 평균 최고 기온을 단일 행에 표시하는 것입니다. 이렇게 하면 인접한 달 사이의 가로 방향과 여러 해 사이의 세로 방향으로 온도를 쉽게 비교할 수 있습니다.
이제 Spark SQL에서 PIVOT 구문을 지원하므로 다음 SQL 쿼리를 통해 이를 달성할 수 있습니다.
위 쿼리는 다음과 같은 결과를 생성합니다.
| YEAR | JAN | FEB | MAR | APR | MAY | JUNE | JULY | AUG | SEPT | OCT | NOV | DEC |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2018 | 49.7 | 45.8 | 54.0 | 58.6 | 70.8 | 71.9 | 82.8 | 79.1 | NULL | NULL | NULL | NULL |
| 2017 | 43.7 | 46.6 | 51.6 | 57.3 | 67.0 | 72.1 | 78.3 | 81.5 | 73.8 | 61.1 | 51.3 | 45.6 |
| 2016 | 49.1 | 53.6 | 56.4 | 65.9 | 68.8 | 73.1 | 76.0 | 79.5 | 69.6 | 60.6 | 56.0 | 41.9 |
| 2015 | 50.3 | 54.5 | 57.9 | 59.9 | 68.0 | 78.9 | 82.6 | 79.0 | 68.5 | 63.6 | 49.4 | 47.1 |
음, 좋은 해와 나쁜 해가 있는 것 같습니다. 2016년은 에너지 친화적인 해였던 것 같습니다.
SQL에서 피벗
이 쿼리를 자세히 살펴보고 작동 방식을 이해해 보겠습니다. 먼저 피벗의 입력, 즉 피벗이 수행될 테이블 또는 하위 쿼리인 FROM 절을 지정해야 합니다. 이 경우 연도, 월 및 최고 기온이 중요하므로 하위 쿼리에 이러한 필드가 표시됩니다.
둘째, 쿼리의 또 다른 중요한 부분인 PIVOT 절을 고려해 보겠습니다. PIVOT 절의 첫 번째 인수는 집계 함수와 집계할 열입니다. 그런 다음 두 번째 인수로 FOR 하위 절에서 피벗 열을 지정하고 마지막 인수로 피벗 열 값을 포함하는 IN 연산자를 지정합니다.
피벗 열은 테이블이 회전되는 지점이며 피벗 열 값은 출력 테이블의 열로 바뀝니다. IN 절을 사용하면 각 피벗 값에 대한 별칭을 지정하여 보다 의미 있는 열 이름을 쉽게 생성할 수도 있습니다.
피벗에 대한 중요한 아이디어는 암시적 group-by 열 목록과 피벗 열을 기반으로 그룹화된 집계를 수행한다는 것입니다. 암시적 group-by 열은 집계 함수에 나타나지 않거나 피벗 열로 나타나지 않는 FROM 절의 열입니다.
위 쿼리에서 피벗 열은 월 열이고 암시적 group-by 열은 연도 열이므로 avg(temp) 표현식은 (year, month)의 각 고유 값 쌍에 대해 집계되며, 여기서 월은 지정된 피벗 열 값 중 하나와 같습니다. 결과적으로 이러한 집계된 각 값은 행 year 및 column 월의 해당 셀에 매핑됩니다.
이러한 암시적 group-by로 인해 피벗 출력의 일부가 되지 않도록 하려는 열은 FROM 절에서 제외해야 합니다. 그렇지 않으면 쿼리에서 원치 않는 결과가 생성됩니다.
ETL 시작하기
여러 집계 표현식 지정
위 예제에서는 PIVOT 절에서 하나의 집계 표현식만 사용되었지만 실제로 필요한 경우 여러 집계 표현식을 지정할 수 있습니다. 다시 위의 날씨 데이터를 사용하여 6월과 9월 사이의 평균 최고 기온과 함께 최대 최고 기온을 나열할 수 있습니다.
여러 집계 표현식의 경우 열은 피벗 열 값과 집계 표현식의 데카르트 곱이 되며 이름은 <value>_<aggexpr></aggexpr></value>입니다.
| year | JUN_avg | JUN_max | JUL_avg | JUL_max | AUG_avg | AUG_max | SEP_avg | SEP_max |
|---|---|---|---|---|---|---|---|---|
| 2018 | 71.9 | 88 | 82.8 | 94 | 79.1 | 94 | NULL | NULL |
| 2017 | 72.1 | 96 | 78.3 | 87 | 81.5 | 94 | 73.8 | 90 |
| 2016 | 73.1 | 93 | 76.0 | 89 | 79.5 | 95 | 69.6 | 78 |
| 2015 | 78.9 | 92 | 82.6 | 95 | 79.0 | 92 | 68.5 | 81 |
그룹화 열과 피벗 열
이제 일일 최저 기온 테이블에서 기온 추세를 탐색할 때 최저 기온을 포함하려는 경우를 가정해 보겠습니다.
| 날짜 | 온도(°F) |
|---|---|
| ... | ... |
| 08-01-2018 | 59 |
| 08-02-2018 | 58 |
| 08-03-2018 | 59 |
| 08-04-2018 | 58 |
| 08-05-2018 | 59 |
| 08-06-2018 | 59 |
| ... | ... |
이 테이블을 이전 일일 최고 기온 테이블과 결합하려면 “날짜” 열에서 이 두 테이블을 조인할 수 있습니다. 그러나 날짜를 기준으로 그룹화를 수행하는 피벗을 사용하므로 UNION ALL을 사용하여 두 테이블을 간단히 연결할 수 있습니다. 나중에 이 방법을 사용하면 더 많은 유연성을 얻을 수 있습니다.
이제 새 결합 테이블로 피벗 쿼리를 시도해 보겠습니다.
결과적으로 지난 4년 동안 각 월의 평균 최고 기온과 평균 최저 기온을 하나의 테이블에서 얻을 수 있습니다. 피벗 쿼리에 flag 열을 포함해야 합니다. 그렇지 않으면 avg(temp) 표현식이 최고 기온과 최저 기온의 혼합을 기반으로 합니다.
| year | H/L | JUN | JUL | AUG | SEP |
|---|---|---|---|---|---|
| 2018 | H | 71.9 | 82.8 | 79.1 | NULL |
| 2018 | L | 53.4 | 58.5 | 58.5 | NULL |
| 2017 | H | 72.1 | 78.3 | 81.5 | 73.8 |
| 2017 | L | 53.7 | 56.3 | 59.0 | 55.6 |
| 2016 | H | 73.1 | 76.0 | 79.5 | 69.9 |
| 2016 | L | 53.9 | 57.6 | 59.9 | 52.9 |
| 2015 | H | 78.9 | 82.6 | 79.0 | 68.5 |
| 2015 | L | 56.4 | 59.9 | 58.5 | 52.5 |
이제 각 연도에 대해 최고 기온과 최저 기온에 대한 두 개의 행이 있습니다. 피벗 입력에 flag 열을 하나 더 포함했기 때문입니다. 이 열은 원래 열 year 외에 또 다른 암시적 그룹화 열이 됩니다.
또는 flag는 그룹화 열 대신 피벗 열로 사용할 수도 있습니다. 이제 두 개의 피벗 열인 month와 flag가 있습니다.
이 쿼리는 각 연도에 대해 하나의 행이 있지만 각 월에 대해 두 개의 열이 있는 동일한 데이터의 다른 레이아웃을 제공합니다.
| year | JUN_hi | JUN_lo | JUL_hi | JUL_lo | AUG_hi | AUG_lo | SEP_hi | SEP_lo |
|---|---|---|---|---|---|---|---|---|
| 2018 | 71.9 | 53.4 | 82.8 | 58.5 | 79.1 | 58.5 | NULL | NULL |
| 2017 | 72.1 | 53.7 | 78.3 | 56.3 | 81.5 | 59.0 | 73.8 | 55.6 |
| 2016 | 73.1 | 53.9 | 76.0 | 57.6 | 79.5 | 57.9 | 69.6 | 52.9 |
| 2015 | 78.9 | 56.4 | 82.6 | 59.9 | 79.0 | 58.5 | 68.5 | 52.5 |
다음 단계
이 블로그에서 사용된 쿼리 예제를 실행하려면 첨부된 Notebook에서 피벗 SQL 예제를 확인하세요.
기여해 주신 Apache Spark 커뮤니티 기여자 여러분께 감사드립니다!
(이 글은 AI의 도움을 받아 번역되었습니다. 원문이 궁금하시다면 여기를 클릭해 주세요)