고유 레코드를 효과적으로 피벗

피벗 쿼리는 데이터를 표 형식으로 변환하여 간편한 데이터 분석을 가능하게 하는 데 중요한 역할을 합니다. 그러나 고유 레코드를 처리할 때 피벗 쿼리의 기본 동작에 문제가 있을 수 있습니다.

문제: 고유 값 무시

다음 표를 고려하세요.

------------------------------------------------------
| Id    Code  percentage  name  name1   activity     |
-----------------------------------------------------
| 1   Prashant  43.43    James  James_  Running      |
| 1   Prashant  70.43    Sam    Sam_    Cooking      |
| 1   Prashant  90.34    Lisa   Lisa_   Walking      |
| 1   Prashant  0.00     James  James_  Stealing     |
| 1   Prashant  0.00     James  James_  Lacking      |
| 1   Prashant  73       Sam     Sam_   Cooking 1    |
------------------------------------------------------

다음과 같은 전통적인 피벗 쿼리:

SELECT Id,Code,
    MAX(CASE WHEN name = 'James' THEN activity END) AS James,
    MAX(CASE WHEN name1 = 'James_' THEN percentage END) AS James_,
    MAX(CASE WHEN name = 'Sam' THEN activity END) AS Sam,
    MAX(CASE WHEN name1 = 'Sam_' THEN percentage END) AS Sam_,
    MAX(CASE WHEN name = 'Lisa' THEN activity END) AS Lisa,
    MAX(CASE WHEN name1 = 'Lisa_' THEN percentage END) AS Lisa_
FROM A
GROUP BY Id, Code

는 다음 표와 같습니다.

-------------------------------------------------------------------
Id  Code        James    James_  Sam        Sam_    Lisa      Lisa_
-------------------------------------------------------------------
1   Prashant    Running  43.43  Cooking     3.43    Walking   90.34
1   Prashant    Stealing 0.0    NULL        NULL    NULL      NULL
-------------------------------------------------------------------

여기서 문제는 이름이 반복되고 백분율이 0일 때 피벗 쿼리가 name1에 대한 고유 값을 무시한다는 것입니다. 이 경우 James의 "부족한" 활동이 손실됩니다.

해결책: 정확성을 위해 ROW_NUMBER() 사용

이 문제를 해결하기 위해 다음을 소개할 수 있습니다. ROW_NUMBER():

;with cte as 
(
    select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
    from A
)
...

ROW_NUMBER()를 사용하여 이름을 기준으로 데이터를 분할하고 각 행에 해당 파티션 내의 고유 번호를 할당합니다. 이를 통해 이름이 반복되는 경우에도 활동과 백분율 간의 연관성을 유지할 수 있습니다.

결과 테이블은 다음과 같습니다.

----------------------------------------------------------
| Id  Code        James       James_  Sam         Sam_    Lisa    Lisa_
----------------------------------------------------------
| 1   Prashant    Running     43.43   Cooking 1   73      Walking 90.34
| 1   Prashant    Stealing    0.00    Cooking     3.43    NULL    NULL
| 1   Prashant    Lacking     0.00    NULL        NULL    NULL    NULL
----------------------------------------------------------

James의 '부족함'을 포함한 모든 활동이 이제 피벗 테이블에 표시됩니다. 이 기술을 사용하면 고유한 값이 보존되어 정확한 분석 데이터를 제공할 수 있습니다.