Pivot & Unpivot: Databases for Developers #14

여기에 데이터 테이블이 있습니다 하지만 행을 열로 변환하고 싶습니다

다행히도 이것은 SQL로 쉽습니다 PIVOT을 할 때입니다! 나는 내 모습을보고있어 내가 얼마나 먹는지 추적하고있다 에 대한 내가 소비하는 모든 것 나는 기록하고있다 : 그것이 무엇인지, 날짜와 시간, 그리고 어떻게 많은 칼로리 이것은 내가 먹은 음식의 큰 목록을 제공합니다 그러나 어려운 일이 될 수 있습니다

내가 일일 칼로리 한계 내에서 머물고 있는지 확인해 이를 극복하기 위해 우리는 하루에 그룹화하고 칼로리를 합산하십시오 이게 낫다 하지만 내가 정기적으로 토요일에 과식 해 보니 까다 롭습니다 특히 내가 주말에 더 많이 먹을 수있는 식단

이 일을 돕기 위해 우리는 꼭대기를 가로 질러 옆과 날들 아래에 주를 가진 테이블을 원합니다 이렇게하면 방금 저항 할 수없는 날이 있는지 쉽게 알 수 있습니다 내 얼굴을 때려 눕혀 라! 다행히도 이것은 SQL로 쉽습니다 먼저 몇 가지가 있습니다 우리가 필요로하는 것들

to_char를 사용하여 날짜에서 추출 할 수있는 요일 매주 첫날; 너는 trunc로 얻을 수있다 우리는 일주일에 한 줄을 원합니다 그러기 위해 일주일의 첫 번째 날까지 그룹화하십시오 그럼 우리는 7 칼로리 카운트; 매일 하나씩 그러나 당신이 그들을 추가하면 당신은 매일 매일 주간 합계! 현재 날짜가 각 합계 체크 내에서이를 수정하려면 월요일, 화요일, 수요일 등입니다

그것은 칼로리를 반환하는 경우 그 날의 경우는 null 이제 우리는 매일 행을 매주 변환했습니다 열 그러나 SQL은 다소 엉망입니다 그리고 합계에서 우리는 7 개의 유사한 그러나 실수를 저지르는 것과 같은 표현은 아닙니다 다행히 피벗 절을 사용하면 훨씬 쉽게 처리 할 수 ​​있습니다

이 시작을 사용하려면 from 절 다음에 피벗을 계속 반복하여 쿼리합니다 여기서 당신은 세개 먼저 새 열에 입력 할 값입니다 피벗 일반적으로 많은 행을 하나로 결합합니다 따라서 이것은 집합체 여야합니다

기능 여기 열량의 합계입니다 다음으로 피벗을 정의합니다 날짜의 to_char 인 열 그리고 마지막으로 모든 값의 목록 당신의 결과에서 칼럼이되는 것들

이리 그것은 요일입니다 하지만 이렇게하면 문제가 발생할 수 있습니다 피벗 열로 함수를 사용할 수 없습니다! 실제 열만 그러나 우리는 테이블에 요일이있다 이 문제를 극복하려면 하위 쿼리가 필요합니다

변환 그런 다음 결과를 피벗합니다 우리는 또한 일주일에 우리도 여기에 넣을 수 있습니다 그러나 이제는 또 다른 문제가 있습니다 매주 한 번씩 음식을 먹는다

그건 우리가 원하는 것이 아닙니다 우리는 매주 한 행을 원했습니다 왜 그런가요? 그럼 오라클 데이터베이스는 암시 적 그룹을 피벗에 추가하십시오 테이블 또는 하위 쿼리에있는 모든 열 피벗에 있지 않아이 그룹의 일부가됩니다 우리는 음식을 선택했으나 피벗에 포함되어 있습니다

그래서 그것은 결과의 칼럼이됩니다 이 문제를 해결하려면 하위 쿼리에서 원하지 않는 열을 찾습니다 또는 피벗에 추가 할 수 있습니다 에 대한 예를 들어 우리가 가진 모든 것들의 목록을 보여주는 열을 가질 수도 있습니다 매일 먹는다

Listagg는이 작업을 수행하는 편리한 기능으로 쉼표로 구분 된 하나의 문자열로 행 이것을하고 그것은 분명하다 나는 너무 많이 먹고있다 주말에 그러나 나는 건강한 과일을 적재하고 있는가? 아니면 내 뚱뚱한 얼굴을 케익으로 채우는거야? 이것을 파악하는 데 도움이되는 것은 매일 매일하는 것입니다

각 음식의 총 칼로리 이렇게하기 위해 우리는에있는 모든 음식을 쓸 수 있습니다 명부 그러나이 목록은 크고 주마다 약간 변경 될 가능성이 큽니다 당신은 내가 정기적으로 먹는 음식을 나열 할 수 있습니다

그러나 이것은 마지막으로 그리울 수있다 밤 늦은 밤 나쵸 진탕 정말로이 목록이 동적이기를 원합니다 선택한 데이터에 따라 열이 변경됩니다 이것이 where 절인 경우 하위 쿼리를 사용하여이 작업을 수행 할 수 있습니다

슬프게도 이것은 피벗과 함께 작동하지 않습니다 당신이있어 명시 적으로 값을 나열합니다 그것이 당신과 함께 놀만큼 충분히 용감하지 않으면 XML XML 피벗을 수행하면 결과가이 형식으로 표시됩니다 그리고 너는 할 수있다

목록에서 하위 쿼리로 바꿉니다 또는 동적으로 지정하는 any 키워드를 사용하십시오 값을 생성합니다 이것은 쉽게 보이지만 각 열에 대해 별도의 열 대신 표시됩니다 가치를 얻을 수 있습니다

여기에는 XML 문서가 들어 있습니다 각 값은 자체적으로 있습니다 항목 태그 따라서 열이되기를 원한다면 무엇을 알 필요가 있습니다 가치가 있으며 XML 조작에 능숙합니다! 따라서 열 목록을 동적으로 만들려면 문제가 될 수 있습니다

이것을 극복 할 수있는 다른 방법이 있습니다 문제도 그러나 각각에는 그들 자신의 단점이있다 일반적으로 원하십니까? 피벗에 대해 정기적으로 변경할 열 목록은 아마도 재고 할 필요가있을 것입니다 귀하의 요구 사항! 이제는 일반적인 식품 중 일부를 나열 해 보겠습니다

그러나 그것이 우리 모두가하는 것이면 우리는 매일 합계를 얻습니다 우리는 단지 이것을보고 싶었습니다 토요일 그래서 where 절이 필요합니다 피벗은 from 및 where 절 사이에 있습니다

따라서 결과를 필터링하려는 경우 끝에 배치하십시오 아니면 하위 쿼리에서 행을 토요일로 제한하십시오 이것은 좋은 생각이다 가능한 한 적은 수의 행에 최대한 빨리 액세스 할 수 있습니다 이와 같이 일찍 필터링하는 것은 SQL을 따르는 위대한 원칙입니다

데이터 집합이 쿼리가 더 빠를 가능성이 높습니다 다음과 같이하면됩니다 나는 너무 많은 케이크를 먹고있다 그러나 나는 작은 간식을 하루 종일 먹고 있습니까? 또는 하나 괴물 케이크 난교? 우리가 음식으로이 테이블을 칼럼으로 시작한다면 다시 행으로 변환 할 수 있습니다 UNPIVOT을 할 때입니다! 이를 수행하려면 피벗을 피벗으로 교체하십시오

또 다시 세 가지가 필요합니다 먼저 열을 저장하는 열 각 음식에 대한 칼로리 합계 다음으로, 우리는이 가치들이 나온 음식을 알기 때문에, 다른 열은 소스 열 이름을 표시합니다 마지막으로 이름을 나열하십시오 행이 될 열의 수입니다

여기 그것이 음식입니다 이제 우리는 각 날짜와 음식에 대한 행이있는 테이블을 갖게됩니다 하지만 소스 테이블에는 내가 물건을 먹을 시간이 없었어요 그래서 우리는 내가 가지고 있는지 알지 못한다 하루 종일 작은 간식을 먹거나 내 케이크를 모두 먹고 있습니다

이것은 unpopot의 일반적인 문제점입니다 테이블을 반전하지 않으면 집계 된 값이 포함되어 있으므로이를 집계하는 일반적인 방법이 없습니다 테이블로 시작한 다음 피벗 한 다음 다시 unpivot하면이 문제가 발생할 수 있습니다 우리가 보았 듯이 피벗은 행을 변환하는 강력한 연산입니다 일, 주 또는 월에 대한 열 합계로 변환합니다 하지만 다른 많은 것들이 있습니다

스포츠 경기 결과를 리그 테이블로 변환하는 것과 같은 응용 프로그램도 있습니다 Univoting은 응용 프로그램이 적지 만 SQL 툴킷에는 유용합니다 보고 주셔서 감사합니다! 당신이 이것을 즐기고 더 많이보고 싶다면 SQL 비디오는 이제 화면의 링크를 클릭합니다