1 若输出表格式为:
城市 开通量
a市
b市
c县
d市
e县
f市
g市
h市
合计
#select substr(trim(login_no),1,2),count(*)
from xh2
group by substr(trim(login_no),1,2)
order by substr(trim(login_no),1,2);
这里合计得自己用EXCEL求
2 若数据格式为:
横向表头为城市
纵向表头为类型
#select trim(no_type),
sum(case when trim(quyu_name)='a市' then 1 else 0 end) a市,
sum(case when trim(quyu_name)='b市' then 1 else 0 end) b市,
sum(case when trim(quyu_name)='c县' then 1 else 0 end) c县,
sum(case when trim(quyu_name)='d市' then 1 else 0 end) d市,
sum(case when trim(quyu_name)='e县' then 1 else 0 end)e县,
sum(case when trim(quyu_name)='f市' then 1 else 0 end) f市,
sum(case when trim(quyu_name)='g市' then 1 else 0 end) g市,
sum(case when trim(quyu_name)='h市' then 1 else 0 end) h市
from xh3
group by trim(no_type) order by trim(no_type);
select 什么后面就得group by 什么
3 大神级写法,其实根本不需要一张表一张表的建,只要一句select 写好了就可以拿出任何想要的东西,大神一般都会建一个临时表,拿出所有想要的东西,然后在括号中一点一点限制条件,比如下面这个我自己写的话要好几个表,但是大神是这样做的,接下来的方向就是尽量不建表,直接一步就拿出想要的东西:
#select a.county_id,trim(a.product_name),
b.prod_prcid,
case when b.prod_prcid='22CAZ02652' then '4G上网流量包30元(全网)'
when b.prod_prcid='22CAZ02654' then '4G上网流量包50元(全网)'
when b.prod_prcid='22CAZ02655' then '4G上网流量包70元(全网)'
when b.prod_prcid='22CAZ1000242' then '4G飞享套餐38元' end sell_name,
count(CASE when substr(b.op_time,1,8)='20160329' then a.user_id end) counts,
count(CASE when substr(b.op_time,1,8)>'20160304' then a.user_id end) sumcounts,
count(CASE when substr(b.op_time,1,8)>'20160304' and substr(b.exp_date,1,8)<='20160329' then a.user_id end) qxcounts
from dw_flow_package_user a, dwv.DWV_D_KH_USER_PROD_433 b
where a.user_id = b.user_id
and b.eff_date<>b.exp_date
and b.acct_date_id='20160329'
and b.prod_prcid in ('22CAZ02652','22CAZ02654','22CAZ02655','22CAZ1000242')
and ((trim(trim(a.product_name))='存量5元流量套餐用户' and b.prod_prcid='22CAZ02652')
or (trim(trim(a.product_name))='存量10元流量套餐用户' and b.prod_prcid='22CAZ02652')
or (trim(a.product_name)='存量20元流量套餐用户' and b.prod_prcid='22CAZ02652')
or (trim(a.product_name)='存量30元流量套餐用户' and b.prod_prcid='22CAZ02654')
or (trim(a.product_name)='存量50元流量套餐用户' and b.prod_prcid='22CAZ02655')
or (trim(a.product_name)='校园存量4G拍照用户' and b.prod_prcid='22CAZ1000242'))
group by a.county_id,trim(a.product_name),b.prod_prcid;
4 今天师傅教了一个嵌套写法,应该用三张表,现在只需要一张表就可以了,十分方便,只是需要脑子非常清醒,以后也应该练习用一张表
#drop table xh9;
create table xh9 as select a.*,b.yue201604
from xh8 a
left outer join (select b1.*,b2.acct_id,b3.yue201604
from xh8 b1
left outer join(
select * from dwv.dwv_d_kh_b_user_info_433
where acct_date_id='20160509'
)b2
on trim(b1.device_number)=trim(b2.device_number)
left outer join(
select acct_id,sum(cur_balance)/100 yue201604
from dwv.dwv_m_zw_acctbook_info_433
where acct_month_id='201604'
group by acct_id
)b3
on trim(b2.acct_id)=trim(b3.acct_id)
)b
on trim(a.device_number)=trim(b.device_number)
distributed by(device_number);