TAM 세그먼트에 사용되는 Trait 속성 값을 수집/사용하기 위해 어떤 과정을 거칠까?

				
					// 사용자 프로파일 추출
WITH
e_profile AS (
    ...
)

// 페이지 정보 : dp_fst(상품코드), dp_lst(상품명), …

,t_page AS (
    SELECT      stat_date,client_seq,pfno,dpuid,sid
                ,ic
                ,IF(rn=1      ,dp ,NULL) AS dp_fst
                ,IF(rn=1      ,dpt,NULL) AS dpt_fst
                ,IF(rn=rn_last,dp ,NULL) AS dp_lst
                …
    FROM        (
        SELECT  stat_date,client_seq,pfno,dpuid,sid,vt,vt1
                ,ic,dp,dpt
                ,rn,LAST_VALUE(rn) OVER(session_window) AS rn_last
        FROM    e_page
        WINDOW  session_window AS (
                PARTITION BY client_seq,pfno,dpuid,sid ORDER BY vt ASC
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )
    )
)


// 랜딩 정보 : kw_outer(유입검색어), …
,t_land AS (
    SELECT      CAST(client_seq AS STRING) AS client_seq,pfno,dpuid,vt,dr_tp,dr_dd,dr_dq
                ,IF(dr_tp="O",
                    IF(REGEXP_CONTAINS(dr_dd,"(?i)(bing|daum|google)"),(SELECT STRING_AGG(value LIMIT 1) FROM UNNEST(dr_dq) WHERE key="q"),
                        IF(REGEXP_CONTAINS(dr_dd,"(?i)(naver|zum)"),(SELECT STRING_AGG(value LIMIT 1) FROM UNNEST(dr_dq) WHERE key="query"),
                            (SELECT STRING_AGG(value LIMIT 1) FROM UNNEST(dr_dq) WHERE key="keyword"))),NULL) AS kw_outer
    FROM        `data.land_*`
    LEFT JOIN   e_profile USING(pfno)
    WHERE       _TABLE_SUFFIX = "20221001"
      AND       client_seq IS NOT NULL
      AND       dpuid != ""
)


SELECT          *
FROM            {t_page} 혹은 {e_land}


추출된 데이터들을 자동완성 DB에 적재하기 위해 종합합니다.

WITH
### 추출
e_session AS (
    SELECT      client_seq,pfno
                ,...
                ,dr_dp,dp_fst,dpt_fst,dp_lst,dpt_lst
    FROM        `data.user_session_*`
    WHERE       _TABLE_SUFFIX = "20221001"
)
,e_behavior AS (
    SELECT      client_seq,pfno
                ,kw_outer,kw_inner
    FROM        `data.user_behavior_*`
    WHERE       _TABLE_SUFFIX = "20221001"
)
, …

### 변환
,t_dp_fst AS (
    SELECT      client_seq,pfno,"du" AS seg,attr,attr AS attr_nm
    FROM        e_session
    LEFT JOIN   UNNEST(dp_fst) AS attr
)
,t_dp_lst AS (
    SELECT      client_seq,pfno,"du" AS seg,attr,attr AS attr_nm
    FROM        e_session
    LEFT JOIN   UNNEST(dp_lst) AS attr
)
, …
,t_final AS (
    SELECT      attr,attr_nm,client_seq,pfno,seg
    FROM (
        …
        SELECT  *
        FROM    t_dp_fst
        UNION ALL
        SELECT  *
        FROM    t_dpt_fst
        UNION ALL
        SELECT  *
        FROM    t_dp_lst
        UNION ALL
        SELECT  *
        FROM    t_kw_outer
        
    )
    WHERE       attr IS NOT NULL AND attr != ""
    AND         client_seq IS NOT NULL
)

### 적재
SELECT          DISTINCT *
FROM            t_final
ORDER BY        client_seq,pfno,seg,attr