presto group by 注意事项

1

--渠道方进件数
select 
a.etl_date
,if(b.platform_nm='风豹科技' ,'风豹','自有资产')
,count(a.loan_id) as c1
,count(case when a.intorisk_state=10190 then 1 else null end) c2
,count(case when a.intorisk_state=10210 then 1 else null end) c3
from 
(
select *  from edw_dwd.d_ph_jj_on_flow_fact_d_ss 
where etl_date >= '2021-08-01' 
and substring(app_loan_time,1,10) = etl_date --申请时间
) a 
 join 
 (
 select *  from dm_fk.agg_speed_withhold_aggregation 
where etl_date >= '2021-08-01' 
and platform_nm in('风豹科技','北京小贷', '猪八戒小贷','华北小贷')
 ) b 
 on a.loan_id = b.intpc_id 
 group by 
a.etl_date
,b.platform_nm
 order by 
a.etl_date
,b.platform_nm

 

2

--渠道方进件数
select 
a.etl_date
,if(b.platform_nm='风豹科技' ,'风豹','自有资产')
,count(a.loan_id) as c1
,count(case when a.intorisk_state=10190 then 1 else null end) c2
,count(case when a.intorisk_state=10210 then 1 else null end) c3
from 
(
select *  from edw_dwd.d_ph_jj_on_flow_fact_d_ss 
where etl_date >= '2021-08-01' 
and substring(app_loan_time,1,10) = etl_date --申请时间
) a 
 join 
 (
 select *  from dm_fk.agg_speed_withhold_aggregation 
where etl_date >= '2021-08-01' 
and platform_nm in('风豹科技','北京小贷', '猪八戒小贷','华北小贷')
 ) b 
 on a.loan_id = b.intpc_id 
 group by 
a.etl_date
,if(b.platform_nm='风豹科技' ,'风豹','自有资产')
 order by 
a.etl_date
,if(b.platform_nm='风豹科技' ,'风豹','自有资产')

 

3

--渠道方进件数
select 
a.etl_date
,if(b.platform_nm='风豹科技' ,'风豹','自有资产')
,count(a.loan_id) as c1
,count(case when a.intorisk_state=10190 then 1 else null end) c2
,count(case when a.intorisk_state=10210 then 1 else null end) c3
from 
(
select *  from edw_dwd.d_ph_jj_on_flow_fact_d_ss 
where etl_date >= '2021-08-01' 
and substring(app_loan_time,1,10) = etl_date --申请时间
) a 
 join 
 (
 select *  from dm_fk.agg_speed_withhold_aggregation 
where etl_date >= '2021-08-01' 
and platform_nm in('风豹科技','北京小贷', '猪八戒小贷','华北小贷')
 ) b 
 on a.loan_id = b.intpc_id 
 group by 
1,2
 order by 
1,2

 结论:

1、group by 1,2可以指代select后面的列

2、group by 的列涉及到计算或者判断时,不能只写列名.