\
\
--========== click_log ==========--
/*
11 ad_101 2014-05-01 06:01:12.334+01
22 ad_102 2014-05-01 07:28:12.342+01
33 ad_103 2014-05-01 07:50:12.33+01
11 ad_104 2014-05-01 09:27:12.33+01
22 ad_103 2014-05-01 09:03:12.324+01
33 ad_102 2014-05-02 19:10:12.343+01
11 ad_101 2014-05-02 09:07:12.344+01
35 ad_105 2014-05-03 11:07:12.339+01
22 ad_104 2014-05-03 12:59:12.743+01
77 ad_103 2014-05-03 18:04:12.355+01
99 ad_102 2014-05-04 00:36:39.713+01
33 ad_101 2014-05-04 19:10:12.343+01
11 ad_101 2014-05-05 09:07:12.344+01
35 ad_102 2014-05-05 11:07:12.339+01
22 ad_103 2014-05-05 12:59:12.743+01
77 ad_104 2014-05-05 18:04:12.355+01
99 ad_105 2014-05-05 20:36:39.713+01
*/
CREATE EXTERNAL TABLE click_log (
cookie_id STRING
, ad_id STRING
, ts STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/tmp/db_case2/click_log';
select cookie_id, <strong>collect_set(ad_id)</strong> as orders
from click_log
--where ts > '2014-05-02'
group by cookie_id;
--========== ad_list ==========--
/*
ad_101 http://abcn.net/ catalog8|catalog1
ad_102 http://www.abcn.net/ catalog6|catalog3
ad_103 http://fxlive.de/ catalog7
ad_104 http://fxlive.fr/ catalog5|catalog1|catalog4|catalog9
ad_105 http://fxlive.eu/
*/
CREATE EXTERNAL TABLE ad_list (
ad_id STRING
, url STRING
, catalogs <strong>array<STRING></strong>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY '|'
LOCATION '/tmp/db_case2/ad_list';
select ad_id, catalog from ad_list <strong>LATERAL VIEW OUTER explode(catalogs) t AS catalog;</strong>
select ad_id, <strong>collect_set(catalog)</strong> from ad_list <strong>LATERAL VIEW OUTER explode(catalogs) t AS catalog</strong> group by ad_id;
select click.cookie_id, ad.catalog from click_log click --查看该用户看过的广告属于哪些类型
left outer join (
select ad_id, catalog from ad_list <strong>LATERAL VIEW OUTER explode(catalogs) t AS catalog</strong>
) ad
on (click.ad_id = ad.ad_id);
--查到值为NULL 则广告无类，做归类
create table cookie_cats as
select click.cookie_id, ad.catalog, <strong>count(1) as weight</strong> from click_log click
left outer join (
select ad_id, catalog from ad_list LATERAL VIEW OUTER explode(catalogs) t AS catalog
) ad
on (click.ad_id = ad.ad_id)
group by click.cookie_id, ad.catalog
order by cookie_id, weight desc;
select cookie_id, <strong>collect_set(catalog)</strong> from cookie_cats group by cookie_id; -- where catalog is not null 无序
select cookie_id,<strong> group_concat(catalog, '|')</strong> from cookie_cats group by cookie_id; -- impala group_concat 有序
\
\
\
\
