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 条件去掉
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
分享到:
相关推荐
可以从hive元数据生成建表语句的资源。包括表结构、分区等信息
hive建表语句hive建表语句hive建表语句hive建表语句hive建表语句hive建表语句
生成hive建表语句
自己平时用到的hive语句做进一步总结和汇总! 方便我们开发人员开发查阅!
根据excel中表的字段清单,批量生成建表语句; 打开工具,按格式填入表名、字段、类型等内容,点击“视图”-->“宏”-->“查看宏”-->“执行”
mysql数据抽取,自动生成hive建表语句,只需要输入表名即可
hadoop数据库中的hive技术,支持一般SQL语句操作数据库,但是也有部分不同,文档中总结了常用的一些操作指令。
java根据excel生成 hive建表语句 可以根据自己的格式修改
数据库相关操作 Hive配置单元包含一个名为 default 默认的数据库. create database [if not exists] ;---创建数据库 show databases; --显示所有数据库 drop database if exists <database name> ...
在excel模板中填写自己表的字段英文名、字段类型、字段中文名、分区英文名、分区数据类型、分区中文名、表英文名、表中文名,自动生成建表语句和查询语句,提高日常数据效率。 前提:本机安装java1.7,并且配置了...
#(执行下面的语句 *.*:所有库下的所有表 %:任何IP地址或主机都可以连接) GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; FLUSH PRIVILEGES; 6. Jline包版本不一致的问题...
hive安装及相关知识整理 hive安装及相关知识整理 hive安装及相关知识整理 hive安装及相关知识整理
基于oracle或者mysql生成自动建表同步源数据表结构及注释
Hive优化方法整理 hive 数据倾斜 内连接
4 Hive 表相关语句 3 4.1 Hive 建表: 3 4.1.1使用LIKE关键字创建一个与已有表模式相同的新表: 4 4.2 Hive 修改表 4 4.2.1 Hive 新增一个字段: 4 4.2.2 Hive 修改字段名/字段类型/字段位置/字段注释: 4 4.2.3 ...
Hive表生成工具,Hive表生成工具Hive表生成工具
数据包括运行代码和数据类型,启动hive导入表和数据就可以运行结果
Hive.sql
hive常用命令及用法
Hive常用的命令整理,非常有用,可供开发人员备用查阅