投稿した質問数 5

1

안녕하십니까,

명절 관계로 답변이 좀 늦었습니다.

cnt에 해당하는 user_id가 정확히 어떤 의미인지를 잘 모르겠군요.

group by 를 prod_01, prod_02로 하였기 때문에 cnt는 temp_01 집합의 prod_01 + prod_02의 건수입니다.

다만 추측컨데 temp_01 집합에 대해서 prod_01 + prod_02 레벨에 해당하는 user_id 정보를 추출하고자 하는걸로 가정해서 말씀드리겠습니다.

먼저 아래 temp_02 집합에서 group by가 prod_01 + prod_02로 집합 레벨(유일 집합)이 변경되었으므로 user_id는 ANSI-SQL 상으로는 추출할 수가 없습니다.

temp_02 as (

select prod_01, prod_02, array_to_string(array_agg(user_id), ',') customer_array, count(*) as cnt

from temp_01

group by prod_01, prod_02

)

다만 유일한 prod_01 + prod_02 에 해당하는 user_id 하나를 대표 user_id로 max(user_id)와 같이 추출하는 방법이 있을수 있고,

다음으로는 특정 집합에 group by 를 적용하기 전 집합 레벨로 특정 컬럼들을 array 형태로 추출하는 함수를 DBMS 별로 제공하고 있습니다. PostgreSQL은 array_agg()함수를 사용하는데 보통은 이 array를 string으로 변환하는 array_to_string()과 함께 사용합니다.

그래서 temp_02를 생성할 때 아래와 같이 array_to_stirng(array_agg(user_id), delimeter 문자열)을 사용하게 되면 prod_0a + prod_02 레벨에 해당하는 user_id를 배열 형태의 문자열로 반환하게 됩니다.

temp_02 as (

select prod_01, prod_02, array_to_string(array_agg(user_id), ',') as user_id_array, count(*) as cnt

from temp_01

group by prod_01, prod_02

)

아래는 전체 SQL이니 참조하시기 바랍니다.

with

-- user_id는 order_items에 없으므로 order_items와 orders를 조인하여 user_id 추출.

temp_00 as (

select b.user_id,a.order_id, a.product_id

from order_items a

join orders b on a.order_id = b.order_id

),

-- temp_00을 user_id로 셀프 조인하면 M:M 조인되면서 개별 user_id별 주문 상품별로 연관된 주문 상품 집합을 생성

temp_01 as

(

select a.user_id, a.product_id as prod_01, b.product_id as prod_02

from temp_00 a

join temp_00 b on a.user_id = b.user_id

where a.product_id != b.product_id

),

-- prod_01 + prod_02 레벨로 group by 건수를 추출.

temp_02 as (

select prod_01, prod_02, array_to_string(array_agg(user_id), ',') as user_id_array, count(*) as cnt

from temp_01

group by prod_01, prod_02

),

temp_03 as (

select prod_01, prod_02, user_id_array, cnt

-- prod_01별로 가장 많은 건수를 가지는 prod_02를 찾기 위해 cnt가 높은 순으로 순위추출.

, row_number() over (partition by prod_01 order by cnt desc) as rnum

from temp_02

)

-- 순위가 1인 데이터만 별도 추출.

select prod_01, prod_02, user_id_array, cnt, rnum

from temp_03

where rnum = 1

;

감사합니다.