hive ETL之广告行业-用户行为归类sql

-- case2 ----========== click_log ==========--/*11    ad_101    2014-05-01 06:01:12.334+0122    ad_102    2014-05-01 07:28:12.342+0133    ad_103    2014-05-01 07:50:12.33+0111    ad_104    2014-05-01 09:27:12.33+0122    ad_103    2014-05-01 09:03:12.324+0133    ad_102    2014-05-02 19:10:12.343+0111    ad_101    2014-05-02 09:07:12.344+0135    ad_105    2014-05-03 11:07:12.339+0122    ad_104    2014-05-03 12:59:12.743+0177    ad_103    2014-05-03 18:04:12.355+0199    ad_102    2014-05-04 00:36:39.713+0133    ad_101    2014-05-04 19:10:12.343+0111    ad_101    2014-05-05 09:07:12.344+0135    ad_102    2014-05-05 11:07:12.339+0122    ad_103    2014-05-05 12:59:12.743+0177    ad_104    2014-05-05 18:04:12.355+0199    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, collect_set(ad_id) as ordersfrom click_log--where ts > '2014-05-02'group by cookie_id;select cookie_id, group_concat(ad_id, '|') as ordersfrom click_log--where ts > '2014-05-02'group by cookie_id;--========== ad_list ==========--/*ad_101    http://abcn.net/    catalog8|catalog1ad_102    http://www.abcn.net/    catalog6|catalog3ad_103    http://fxlive.de/    catalog7ad_104    http://fxlive.fr/    catalog5|catalog1|catalog4|catalog9ad_105    http://fxlive.eu/    */CREATE EXTERNAL TABLE ad_list (    ad_id    STRING  , url      STRING  , catalogs array)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '\t'COLLECTION ITEMS TERMINATED BY '|'LOCATION '/tmp/db_case2/ad_list';CREATE EXTERNAL TABLE ad_list_string (   ad_id    STRING , url      STRING , catalogs STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'LOCATION '/tmp/db_case2/ad_list';select click.cookie_id, click.ad_id, click.amount, ad_list_string.catalogs as ordersfrom (  select cookie_id, ad_id, count(1) as amount from click_log group by cookie_id, ad_id) clickjoin ad_list_stringon (ad_list_string.ad_id = click.ad_id);select ad_id, catalog from ad_list LATERAL VIEW OUTER explode(catalogs) t AS catalog;select ad_id, collect_set(catalog) from ad_list LATERAL VIEW OUTER explode(catalogs) t AS catalog group by ad_id;select click.cookie_id, ad.catalog from click_log clickleft outer join (  select ad_id, catalog from ad_list LATERAL VIEW OUTER explode(catalogs) t AS catalog) adon (click.ad_id = ad.ad_id);create table cookie_cats asselect click.cookie_id, ad.catalog, count(1) as weight from click_log clickleft outer join (  select ad_id, catalog from ad_list LATERAL VIEW OUTER explode(catalogs) t AS catalog) adon (click.ad_id = ad.ad_id)group by click.cookie_id, ad.catalogorder by cookie_id, weight desc;select cookie_id, collect_set(catalog) from cookie_cats group by cookie_id; -- where catalog is not nullselect cookie_id, group_concat(catalog, '|') from cookie_cats group by cookie_id; -- impala group_concat