hive做报表统计相关语句整理
程序员文章站
2022-03-01 22:33:39
...
1.case when then 做数据字典转换
2. ROUND(CEILING(t1.order_amount*1)/100,2) 上取整+保留位小数
3.substring(cast(t.trade_time as string),0,7) 日期转换,取到月份,如果是10则到日
4.unix_timestamp(create_time)+300<unix_timestamp(trade_time) 两个时间差,300秒
5.order by 条件和查询结果冲突情况下,最好先把order by 条件去掉
2. ROUND(CEILING(t1.order_amount*1)/100,2) 上取整+保留位小数
3.substring(cast(t.trade_time as string),0,7) 日期转换,取到月份,如果是10则到日
4.unix_timestamp(create_time)+300<unix_timestamp(trade_time) 两个时间差,300秒
5.order by 条件和查询结果冲突情况下,最好先把order by 条件去掉
select date1 ,XXXid1 ,paymethod1 , count1 ,orderamount1 , CASE when paymethod1='PayPal' then fee1*5 else fee1 END as fee2 from (select a.tradetime as date1,a.XXXId as XXXid1 ,a.paymethod as paymethod1,count(*) as count1,sum(a.orderamount) as orderamount1,sum(a.fee) as fee1 from (select t1.pay_seq as payseq, substring(cast(t.trade_time as string),0,10) as tradetime,t.pay_amount as payamount ,t.order_amount as orderamount ,CASE when t.XXX_id='YYY160823142921802' and t1.order_amount*6 >= 10 then ROUND(CEILING(t1.order_amount*0.6)/100,2) when t.XXX_id='YYY160823142921802' and t1.order_amount*6 < 10 then 0.01 when t.XXX_id='YYY160809193618262' and t1.order_amount*10 >= 10 then ROUND(CEILING(t1.order_amount*1)/100,2) when t.XXX_id='YYY160809193618262' and t1.order_amount*10 < 10 then 0.01 when t.XXX_id='YYY160712075256902' and t1.order_amount*10 >= 10 then ROUND(CEILING(t1.order_amount*1)/100,2) when t.XXX_id='YYY160712075256902' and t1.order_amount*10 < 10 then 0.01 when t.XXX_id='YYY171009125604524' and t1.order_amount*10 >= 10 then ROUND(CEILING(t1.order_amount*1)/100,2) when t.XXX_id='YYY171009125604524' and t1.order_amount*10 < 10 then 0.01 when t.XXX_id='YYY171121150246521' and t1.order_amount*10 >= 10 then ROUND(CEILING(t1.order_amount*1)/100,2) when t.XXX_id='YYY171121150246521' and t1.order_amount*10 < 10 then 0.01 end as fee , case when t.XXX_id='YYY160823142921802' THEN '机构A' when t.XXX_id='YYY170320123235979' THEN '机构B' END as XXXId, case when t2.pay_method='A0' THEN '余额' WHEN t2.pay_method='A1' THEN '支付宝' WHEN t2.pay_method='A2' THEN '微信' END as paymethod from odl_table_name_finance t,odl_table_name_payment_finance t1,odl_table_name_payment_detail_finance t2 where t.fund_order_id = t1.fund_order_id and t1.pay_seq = t2.pay_seq and substring(cast(t.trade_time as string),0,7) ='2017-12' and t.order_type='03' and t.order_status='S' and t1.pay_status='S' and t.order_status='S' ) a where 1=1 group by a.XXXId,a.tradetime,a.paymethod order by a.tradetime,a.XXXId,a.paymethod limit 1000) dd where 1=1 limit 1000
select a.alldate ,a.allamount as allamount,a.allcount as allcount,b.sucamount as sucamount,b.succount as succount,c.amount5 as amount5,c.count5 as count5 from (select sum(order_amount) allamount,count(*) allcount,partner_id as partnerid1,substring(cast(create_time as string),0,10) alldate from odl_table_name_finance where create_time between '2017-12-01' and '2018-01-01' and order_type='03' group by partner_id,substring(cast(create_time as string),0,10)) a left join (select sum(order_amount) sucamount,count(*) succount,partner_id as partnerid2,substring(cast(create_time as string),0,10) sucdate from odl_table_name_finance where create_time between '2017-12-01' and '2018-01-01' and order_type='03' and order_status='S' group by partner_id,substring(cast(create_time as string),0,10)) b on a.partnerid1=b.partnerid2 and a.alldate = b.sucdate left join (select sum(order_amount) amount5,count(*) count5,partner_id as partnerid3,substring(cast(create_time as string),0,10) sucdate from odl_table_name_finance where create_time between '2017-12-01' and '2018-01-01' and order_type='03' and order_status='S' and unix_timestamp(create_time)+300<unix_timestamp(trade_time) group by partner_id,substring(cast(create_time as string),0,10)) c on a.partnerid1=c.partnerid3 and a.alldate = c.sucdate limit 1000
下一篇: 韵达快递-最差的快递