(주)카카오 모델별 리뷰/클릭 데이터 추출

리뷰카운트 데이터 추출

select ctgr.bcateid, ctgr.bcatename, mdl.brandtext, mdl.modelid, mdl.modelname, rev.cnt				
from shopcmp_snapshot.model_1d mdl,				
	(				
	select modelid, count(reviewid) as cnt				
	from shopmongo.user_review				
	where cdate < 20211228000000	
	and modelid is not null				
	group by modelid				
) rev,				
	shopcmp_snapshot.category_1d ctgr,				
	shopcmp_snapshot.catemodelmap_1d mpp				
where rev.modelid = mdl.modelid				
and mdl.modelid = mpp.modelid				
and mpp.catekey = ctgr.catekey				
and ctgr.type = 'I'				
and ctgr.bcateid ='100100'	
order by cnt desc				
limit 200				
;				

클릭카운트 데이터 추출

select distinct ctgr.bcateid, ctgr.bcatename, mdl.modelid, mdl.barndtext, mdl.modelname, click.cnt
from shopcmp_snapshot.model_1d mdl,
	(
    select prodid as modelid, count(*) as cnt
    from shophow.gclicklogresult
    where prodid rlike '^A|^B|^C|^D|^E'
    and year ='2021'
    and month ='12'
    group by prodid
    order by cnt desc
    limit 5000
    ) click,
    hopcmp_snapshot.category_1d ctgr,
    shopcmp_snapshot.catemodelmap_1d cpp
where click.modelid = cpp.modelid
and cpp.catekey = ctgr.catekey
and ctgr.type ='I'
and ctgr.bcateid ='100106'
order by cnt desc
limit 200
;