create table tmp_huadan(t_area char(50),t_type char(50),t_dianji int,t_sousuo int)go
load table tmp_huadan(t_area '|',t_type '|',t_dianji '|',t_sousuo 0x0a)using file '/xwtec/data/dujiaqu.txt'quotes offescapes offgo
select L.t_area,sum(L.t_dianji_new) as t_dianji,sum(L.t_sousuo_new) as t_sousuo from (select t_area,case when t_type='住宿设施' then t_dianji*0.4 when t_type='景点' then t_dianji*0.3 when t_type='餐饮设施' then t_dianji*0.2 else t_dianji*0.1 end as t_dianji_new ,case when t_type='住宿设施' then t_sousuo*0.4 when t_type='景点' then t_sousuo*0.3 when t_type='餐饮设施' then t_sousuo*0.2 else t_sousuo*0.1 end as t_sousuo_new from tmp_huadan where convert(char(20),t_dianji)<>'' or convert(char(20),t_sousuo)<>'')L group by L.t_area
2. 度假区综合排名
select t_area,t_dianji*0.7+t_sousuo*0.3 from (select L.t_area,sum(L.t_dianji_new) as t_dianji,sum(L.t_sousuo_new) as t_sousuo from (select t_area,case when t_type='住宿设施' then t_dianji*0.4 when t_type='景点' then t_dianji*0.3 when t_type='餐饮设施' then t_dianji*0.2 else t_dianji*0.1 end as t_dianji_new ,case when t_type='住宿设施' then t_sousuo*0.4 when t_type='景点' then t_sousuo*0.3 when t_type='餐饮设施' then t_sousuo*0.2 else t_sousuo*0.1 end as t_sousuo_new from tmp_huadan where convert(char(20),t_dianji)<>'' or convert(char(20),t_sousuo)<>'')L group by L.t_area)L group by L.t_area)M
3. EXCEL中行转化为列
select t_type,t_area,t_url,case when t_type='大众点评' then t_dianji else NULL end as t_dianji_1,case when t_type='大众点评' then t_sousuo else NULL end as t_sousuo_1,case when t_type='艺龙' then t_dianji else NULL end as t_dianji_2,case when t_type='艺龙' then t_sousuo else NULL end as t_sousuo_2 from tmp_dujiaqu
4. 度假区满意度
select t_area,sum(t_dianji1_2) as fenshu from (select t_area,t_type,case when t_type='住宿设施' then t_dianji1*0.4 when t_type='景点' then t_dianji1*0.3 when t_type='餐饮设施' then t_dianji1*0.2 else t_dianji1 *0.1 end as t_dianji1_2 from tmp_huadan3)L group by t_areaselect t_area,sum(L.t_sousuo_2) as t_sousuo from (select t_area,t_sousuo,t_type,case when t_type='住宿设施' then t_sousuo*0.4 when t_type='景点' then t_sousuo*0.3 when t_type='餐饮设施' then t_sousuo*0.2 else t_sousuo *0.1 end as t_sousuo_2 from tmp_huadan where t_sousuo<>0)L