进入HIVE语句
ssh tangjingxiao1@10.4.248.1 -p22
select group:BI
select 0,5,2
WHERE t.ds = '2014-12-21' and hour<18
之后输入sql语句：
Drop table zyltest.tmp_cde_qianbao21;
create table zyltest.tmp_cde_qianbao21 AS
SELECT '2014-12-21' as date_id,
COUNT(CASE
WHEN (t.url LIKE
'http://cms.yhd.com/cmsPage/show.do?pageId=124341%')
AND t.link_position = '14818_13701762_1' THEN
t.id
ELSE
NULL
END) AS 1home_paeg_type1_firstban_pv,
COUNT(DISTINCT(CASE
WHEN (t.url LIKE
' http://cms.yhd.com/cmsPage/show.do?pageId=124341%')
AND t.link_position = '14818_13701762_1' THEN
t.gu_id
ELSE
NULL
END)) AS 1home_paeg_type1_firstban_uv,
COUNT(CASE
WHEN (t.url LIKE 'http://cms.yhd.com/cmsPage/show.do?pageId=124341%')
AND t.refpagetypeid = 63
AND t.refpagevalue = '100_1_-100_1'
AND t.link_position = '12125_13699486_3' THEN
t.id
ELSE
NULL
END) AS 2tuan_cms_paeg_pv,
COUNT(DISTINCT(CASE
WHEN (t.url LIKE 'http://cms.yhd.com/cmsPage/show.do?pageId=124341%')
AND t.refpagetypeid = 63
AND t.refpagevalue = '100_1_-100_1'
AND t.link_position = '12125_13699486_3' THEN
t.gu_id
ELSE
NULL
END)) AS 2tuan_cms_paeg_uv,
COUNT(CASE
WHEN t.pagetypeid = 4
AND t.refpagetypeid = 4
AND t.refpagevalue = 11111
AND t.pagevalue = 124341
AND t.link_position = 'cms_pic_11111_1954534_1' THEN
t.id
ELSE
NULL
END) AS 3da_banner_pv,
COUNT(DISTINCT(CASE
WHEN t.pagetypeid = 4
AND t.refpagetypeid = 4
AND t.refpagevalue = 11111
AND t.pagevalue = 124341
AND t.link_position = 'cms_pic_11111_1954534_1' THEN
t.gu_id
ELSE
NULL
END)) AS 3da_banner_uv,
COUNT(CASE
WHEN t.url LIKE 'http://cms.yhd.com/cmsPage/show.do?pageId=124341%' THEN
t.id
ELSE
NULL
END) AS 4cms_paeg_pv,
COUNT(DISTINCT(CASE
WHEN t.url LIKE 'http://cms.yhd.com/cmsPage/show.do?pageId=124341%' THEN
t.gu_id
ELSE
NULL
END)) AS 4cms_paeg_uv,
COUNT(CASE
WHEN t.pagetypeid = 102100003
AND t.pagevalue = 243
AND t.refpagetypeid = 4
AND t.refpagevalue = 124341
--- AND tpa = 740
THEN
t.id
ELSE
NULL
END) AS 5cms_chongzhi_pv,
COUNT(DISTINCT(CASE
WHEN t.pagetypeid = 102100003
AND t.pagevalue = 243
AND t.refpagetypeid = 4
AND t.refpagevalue = 124341
-- AND tpa = 740
THEN
t.gu_id
ELSE
NULL
END)) AS 5cms_chongzhi_uv,
COUNT(CASE
WHEN (t.pagetypeid = 102100002
AND t.ext_field6 = 55)
or (t.pagetypeid = 102100004 and t.pagevalue =228 ) THEN
----目前只找到这个规律
t.id
ELSE
NULL
END) AS 6yihaoqianbao_pv,
COUNT(DISTINCT(CASE
WHEN (t.pagetypeid = 102100002
AND t.ext_field6 = 55)
or (t.pagetypeid = 102100004 and t.pagevalue =228 )THEN
t.gu_id
ELSE
NULL
END)) AS 6yihaoqianbao_uv,
COUNT(CASE
WHEN t.pagetypeid = 102100002
AND t.refpagetypeid = 1
AND t.refpagevalue = 1
AND t.link_position = '2704_13698995_9' THEN
t.id
ELSE
NULL
END) AS 7home_paeg_type2_jinrong_pv,
COUNT(DISTINCT(CASE
WHEN t.pagetypeid = 102100002
AND t.refpagetypeid = 1
AND t.refpagevalue = 1
AND t.link_position = '2704_13698995_9' THEN
t.gu_id
ELSE
NULL
END)) AS 7home_paeg_type2_jinrong_uv,
COUNT(CASE
WHEN t.pagetypeid = 102100002
AND t.link_position = '6324_13697767_3' THEN
t.id
ELSE
NULL
END) AS 8yihaozhuanghang_yihaoqianbao_pv,
COUNT(DISTINCT(CASE
WHEN t.pagetypeid = 102100002
AND t.link_position = '6324_13697767_3' THEN
t.gu_id
ELSE
NULL
END)) AS 8yihaozhuanghang_yihaoqianbao_uv,
COUNT(CASE
WHEN t.url LIKE 'https://8.yhd.com/cfweb/home.action%' or t.pagetypeid='102100002'THEN
t.id
ELSE
NULL
END) AS 9qianbao_home_pv,
COUNT(DISTINCT(CASE
WHEN t.url LIKE 'https://8.yhd.com/cfweb/home.action%' ort.pagetypeid='102100002' THEN
t.gu_id
ELSE
NULL
END)) AS 9qianbao_home_uv,
COUNT(CASE
WHEN t.pagetypeid = 102100003
AND t.tpa = 778
AND t.refpagetypeid = 102100002 THEN
t.id
ELSE
NULL
END) AS 10lijitiayn_pv,
COUNT(DISTINCT(CASE
WHEN t.pagetypeid = 102100003
AND t.tpa = 778
AND t.refpagetypeid = 102100002 THEN
t.gu_id
ELSE
NULL
END)) AS 10lijitiayn_uv
FROM trackreal t
WHERE t.ds = '2014-12-21';
