counts와 cnt 차이

Question

안녕하세요. Weekly retention을 구하고자 아래와 같이 쿼리를 작성했습니다.

WITH base AS
  (SELECT
    DISTINCT
      user_id,
      event_name,
      DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date,
      user_pseudo_id
  FROM `advanced.app_logs`
  WHERE
    event_date BETWEEN "2022-08-01" AND "2022-11-03"
  ),

  first_week_and_diff AS (
  SELECT 
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
      SELECT
        DISTINCT
          user_pseudo_id,
          DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
          DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
      FROM base
      )
  ), user_cnt AS (
    SELECT
      diff_of_week,
      COUNT(DISTINCT user_pseudo_id) AS user_cnt
    FROM first_week_and_diff
    GROUP BY diff_of_week
    )

SELECT 
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 2) AS retention_rate
  FROM (
    SELECT
      diff_of_week,
      user_cnt,
      FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt
    FROM user_cnt
    );

이렇게 쿼리 작성을 하니 오류가 발생했고

user_cnt AS (
    SELECT
      diff_of_week,
      COUNT(DISTINCT user_pseudo_id) AS user_cnt
    FROM first_week_and_diff
    GROUP BY diff_of_week
    )

이 부분에서 테이블 이름을 user_counts로 수정하니 제대로 동작함을 확인했습니다. 왜 이름 때문에 오류가 발생하는 것인지 궁금합니다.

카일스쿨 · Answer

안녕하세요. 이 현상의 이슈는 user_cnt라는 것이 2곳에서 사용되고 있기 때문에 발생합니다.

CTE 표현의 user_cnt
user_cnt CTE의 user_cnt 컬럼

  ), user_cnt AS ( # <- 여기의 user_cnt
    SELECT
      diff_of_week,
      COUNT(DISTINCT user_pseudo_id) AS user_cnt # <- user_cnt의 user_cnt
    FROM first_week_and_diff
    GROUP BY diff_of_week
    )

BigQuery에선 같은 이름을 사용하는 것이 있다면, 구조체로 변경합니다. CTE로 사용하는 user_cnt가 있고, 그 안에 user_cnt 컬럼이 있는 구조체로 변합니다.

그래서 지금 작성하신 쿼리에서는

No matching signature for function SAFE_DIVIDE Argument types: STRUCT<diff_of_week INT64, user_ent INT64>, STRUCT<diff_of week INT64, usercnt INT64> Signature: SAFE_DIVIDE(FLOAT64, FLOAT64) Argument 1: Unable to coerce type STRUCT<diff_of_week INT64, user_cnt INT64> to expected type FLOAT64 Signature: SAFE_DIVIDE(NUMERIC, NUMERIC) Argument 1: Unable to coerce type STRUCT<diff_of_week INT64, user_ent INT64> to expected type NUMERIC Signature: SAFE_DIVIDE(BIGNUMERIC, BIGNUMERIC) Argument 1: Unable to coerce type STRUCT<diff ofweek INT64, user_ent INT64> to expected type BIGNUMERIC at [36:9]

이런 오류가 생기고 있습니다. 이 오류를 보면 STRUCT가 있어서 오류가 발생했습니다. 이 STRUCT가 아닌 컬럼에 있는 것을 명시해야 합니다.

오류가 발생하지 않는 쿼리는 다음과 같습니다

WITH base AS
  (SELECT
    DISTINCT
      user_id,
      event_name,
      DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date,
      user_pseudo_id
  FROM `advanced.app_logs`
  WHERE
    event_date BETWEEN "2022-08-01" AND "2022-11-03"
  ),

  first_week_and_diff AS (
  SELECT 
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
      SELECT
        DISTINCT
          user_pseudo_id,
          DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
          DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
      FROM base
      )
  ), user_cnt AS (
    SELECT
      diff_of_week,
      COUNT(DISTINCT user_pseudo_id) AS user_cnt
    FROM first_week_and_diff
    GROUP BY diff_of_week
    )

SELECT 
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 2) AS retention_rate
  FROM (
    SELECT
      diff_of_week,
      user_cnt.user_cnt, 
      FIRST_VALUE(user_cnt.user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt
    FROM user_cnt
    );

여길 보면 user_cnt.user_cnt와 FIRST_VALUE(user_cnt.user_cnt)를 볼 수 있습니다. 구조체에서 컬럼에 접근하기 위해 저렇게 사용했습니다.

사실 CTE와 컬럼의 이름을 같이 표시하는 것은 좋지 않은 것 같아요. 헷갈릴 수 있거든요, 명시적으로 user_counts_cte 이렇게 표현하고 FROM 절에 이 표현을 쓰는 것이 헷갈리지 않아 더 가독성 있는 쿼리라고 볼 수 있을 것 같아요

인프런 AI 인턴 · Answer

안녕하세요, 인프런 AI 인턴입니다.

yseohyun11님, 질문해 주신 내용에 대해 답변 드리겠습니다.

SQL 쿼리에서 테이블 이름이나 컬럼 이름은 사용 중인 SQL 환경에서 예약어로 사용할 수 없는 경우가 있습니다. cnt라는 단어가 다른 환경에서는 문제가 없을 수 있지만, 일부 SQL 환경에서는 예약어로 인식되거나 혼동을 주어 오류가 발생할 수 있습니다.

다음은 유사한 질문과 답변 링크입니다. 이 링크에서는 비슷한 SQL 쿼리 구조에 관한 문제를 다루고 있습니다:

리텐션 SQL 작성하기
- 해당 게시물에서는 리텐션 쿼리 작성 시 DISTINCT의 사용 이유에 대한 이해를 도와드리고 있습니다. 하지만 네이밍이나 예약어 충돌에 대한 문제는 다르지만, SQL 쿼리 작성 시 주의 사항에 대해 확인할 수 있습니다.
3-7. Weekly 리텐션 쿼리 작성 및 질문
- 리텐션 쿼리 작성 중 발생할 수 있는 여러 문제점들에 대한 논의가 이루어졌습니다. yseohyun11님이 가진 문제와 유사한 오타나 예약어 문제를 고려할 수 있습니다.

보다 자세한 정보를 위해서는 사용 중인 SQL 플랫폼의 예약어 리스트를 확인하거나, 다른 이름을 사용하는 것이 안전할 수 있습니다. SQL 플랫폼에 따라 cnt가 예약어로 등록되어 있을 수 있으니 유의하시기 바랍니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

인프런 커뮤니티 질문&답변

counts와 cnt 차이