대칭 집계 이해

Looker의 대칭 집계는 매우 강력한 기능입니다. 하지만 대칭 집계는 약간 어려워 보일 수 있고 대부분 백그라운드에서 발생합니다. 따라서 이러한 집계를 접하는 것이 약간 혼란스러울 수 있습니다. 이 페이지에서는 대칭 집계에 대한 다음 정보를 제공합니다.

대칭 집계가 필요한 이유

데이터 분석 언어인 SQL은 매우 강력합니다. 하지만 큰 힘에는 큰 책임이 따르므로 분석가는 합계, 평균, 카운팅 등의 집계를 실수로 잘못 계산하는 일을 막아야 합니다.

이러한 계산을 잘못하기는 놀라울 만큼 쉬우며 이러한 잘못된 계산은 분석가들에게 큰 부담을 줄 수 있습니다. 다음 예시는 어떻게 잘못할 수 있는지 보여줍니다.

orders와 order_items라는 두 테이블이 있다고 가정해 보겠습니다. order_items 테이블은 주문 내 각 항목에 하나의 행을 기록하므로 테이블 간의 관계는 일대다입니다. 하나의 주문에 여러 항목이 포함될 수 있지만 각 항목은 하나의 주문에만 포함될 수 있으므로 관계는 일대다입니다. 조인의 올바른 관계 결정에 대한 안내는 관계 매개변수 올바르게 지정 권장사항 페이지를 참조하세요.

이 예시에서 orders 테이블이 다음과 같다고 가정해 보겠습니다.

order_id user_id total order_date
1 100 $ 50.36 2017-12-01
2 101 $ 24.12 2017-12-02
3 137 $ 50.36 2017-12-02

이 orders 테이블에서 total 열(SUM(total))의 값 합계는 124.84와 같습니다.

order_items 테이블에 6개의 행이 포함되어 있다고 가정해 보겠습니다.

order_id item_id quantity unit_price
1 50 1 $ 23.00
1 63 2 $ 13.68
2 63 1 $ 13.68
2 72 1 $ 5.08
2 79 1 $ 5.36
3 78 1 $ 50.36

주문한 항목의 개수를 쉽게 확인할 수 있습니다. quantity 열의 값 합계(SUM(quantity))는 7입니다.

이제 공유 열 order_id를 사용하여 orders 테이블과 order_items 테이블을 조인한다고 가정해 보겠습니다. 그러면 다음 테이블이 생성됩니다.

order_id user_id total order_date item_id quantity unit_price
1 100 $ 50.36 2017-12-01 50 1 $ 23.00
1 100 $ 50.36 2017-12-01 63 2 $ 13.68
2 101 $ 24.12 2017-12-02 63 1 $ 13.68
2 101 $ 24.12 2017-12-02 72 1 $ 5.08
2 101 $ 24.12 2017-12-02 79 1 $ 5.36
3 137 $ 50.36 2017-12-02 78 1 $ 50.36

위 표는 12월 1일(order_date 열의 2017-12-01)에 주문한 상품 2개와 12월 2일(2017-12-02)에 주문한 상품 4개와 같은 새로운 정보를 제공합니다. SUM(quantity) 계산과 같은 이전의 계산은 여전히 유효합니다. 그러나 총 지출액을 계산하려고 하면 문제가 발생합니다.

이전 계산인 SUM(total)을 사용하면 order_id 값이 1인 행에 대해 새 테이블의 총계 50.36이 2번 집계됩니다. 값이 50 및 63인 item_id의 2가지 상품이 주문에 포함되기 때문입니다. order_id가 2인 행의 총계 24.12은 3번 집계되는데, 이 주문에 3가지 상품이 포함되기 때문입니다. 따라서 이 테이블의 SUM(total) 계산 결과는 정답(124.84)이 아닌 223.44입니다.

2가지 작은 예시 테이블로 작업할 때는 이런 실수를 피하는 것이 쉽지만, 테이블 수가 많고 데이터가 많은 현실에서 이 문제를 해결하는 것이 훨씬 더 복잡합니다. 이는 누군가가 모르고 범하는 잘못된 계산법입니다. 이것이 대칭 집계로 해결되는 문제입니다.

대칭 집계의 작동 방식

대칭 집계는 분석가와 Looker를 사용하는 모든 사람이 합계, 평균, 카운팅 같은 집계를 실수로 잘못 계산하는 것을 방지합니다. 대칭 집계를 사용하면 분석가의 부담을 크게 줄일 수 있는데, 분석가는 사용자가 잘못된 데이터에 따른 비용을 미리 지불하지 않을 것이라고 신뢰할 수 있기 때문입니다. 대칭 집계는 계산의 각 사실을 정확한 횟수로 계산하고 계산 중인 항목을 추적하는 방식으로 이를 수행합니다.

이전 예시에서 대칭 집계 함수는 total이 order_items가 아닌 orders의 속성임을 인식하므로 올바른 답을 얻으려면 각 주문의 합계를 한 번만 집계하면 됩니다. 이 함수는 분석가가 Looker에서 정의한 고유한 기본 키를 사용하여 이 작업을 수행합니다. 즉, Looker가 조인된 테이블에서 계산을 수행할 때 order_id 값이 1인 행이 2개 있더라도 총합이 이미 계산에 포함되었기 때문에 두 번 계산해서는 안 되며 order_id 값이 2인 3개 행에 대해서는 총합을 한 번만 계산해야 합니다.

대칭 집계는 고유 기본 키와 모델에 지정된 올바른 조인 관계에 따라 달라집니다. 따라서 결과가 틀린 경우 분석가에게 상의하여 모든 설정이 제대로 되었는지 확인하세요.

대칭 집계가 복잡해 보이는 이유

대칭 집계의 모양은 다소 혼란스러울 수 있습니다. 대칭 집계가 없으면 일반적으로 Looker는 잘 작동하는 다음 SQL을 작성합니다. 예를 들면 다음과 같습니다. 

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price"
FROM order_items AS order_items

GROUP BY 1,2
ORDER BY 1
LIMIT 500

대칭 집계를 사용하면 SQL Looker 쓰기가 다음 예시와 같이 표시될 수 있습니다. 

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price",
  (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(users.age ,0)
  *(1000000*1.0)) AS DECIMAL(38,0))) +
  CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0)) )
  - SUM(DISTINCT CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))) ) AS DOUBLE PRECISION)
  / CAST((1000000*1.0) AS DOUBLE PRECISION), 0)
  / NULLIF(COUNT(DISTINCT CASE WHEN users.age IS NOT NULL THEN users.id
  ELSE NULL END), 0)) AS "users.average_age
FROM order_items AS order_items
LEFT JOIN users AS users ON order_items.user_id = users.id

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

대칭 집계가 사용하는 정확한 형식은 Looker에서 작성하는 SQL 언어에 따라 다르지만 모든 형식은 동일한 기본 작업을 수행합니다. 여러 행의 기본 키가 동일한 경우 대칭 집계 함수는 해당 함수를 한 번만 계산합니다. 이를 위해 SQL 표준의 일부인 잘 알려지지 않은 SUM DISTINCT 및 AVG DISTINCT 함수를 사용합니다.

이러한 일이 발생하는 과정을 보려면 이전에 했던 계산 방법을 사용하여 대칭 집계를 적용하면 됩니다. 조인된 테이블의 7개 열 중에서 집계 중인 열(total)과 주문에 대한 고유 기본 키(order_id)만 있으면 됩니다.

order_id total
1 $ 50.36
1 $ 50.36
2 $ 24.12
2 $ 24.12
2 $ 24.12
3 $ 50.26

대칭 집계는 기본 키(이 경우 order_id)를 사용하여 각각에 대해 매우 큰 숫자를 생성하므로 고유성이 보장되고 항상 동일한 입력에 동일한 출력을 제공합니다. (일반적으로 해싱 함수를 사용하여 이를 수행합니다. 자세한 내용은 이 페이지의 범위를 벗어납니다.) 결과는 다음과 같습니다.

big_unique_number total
802959190063912 $ 50.36
802959190063912 $ 50.36
917651724816292 $ 24.12
917651724816292 $ 24.12
917651724816292 $ 24.12
110506994770727 $ 50.36

그런 다음 각 행에 대해 Looker가 다음을 수행합니다. 

SUM(DISTINCT big_unique_number + total) - SUM(DISTINCT big_unique_number)

이렇게 하면 각 총계가 정확한 횟수로 집계되어 올바르게 집계된 총계가 제공됩니다. Looker의 대칭 집계 함수는 반복되는 행이나 총계가 같은 여러 주문에 쉽게 속지 않습니다. 대칭 집계의 작동 방식을 더 잘 이해할 수 있도록 직접 계산해 보세요.

이 작업을 수행하는 데 필요한 SQL은 보기에는 적합하지 않습니다. CAST(), md5(), SUM(DISTINCT), STRTOL()의 경우 SQL을 직접 작성해서는 안 됩니다. 하지만 다행히 Looker에서 SQL을 대신 작성할 수 있습니다.

대칭 집계가 없어도 집계가 제대로 작동하면 Looker에서 이를 자동으로 감지하고 이 함수를 사용하지 않습니다. 대칭 집계에는 성능 비용이 발생하므로 Looker의 사용 시기와 사용 시기를 식별하는 기능은 대칭 집계를 통해 Looker에서 생성하는 SQL을 더욱 최적화하고 정답을 보장하는 동시에 최대한 효율적으로 만듭니다.