20160510 GreenPlum4 SQL中求和相关语句的总结

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);