oracle常用命令（hive转换）

(1)hive比较两个日期相差的月份：

when floor(months_between(c.repair_date,c.invoice_date)) between 0 and 12 then '0'
when floor(months_between(c.repair_date,c.invoice_date)) between 12 and 24 then '1'

(2)hive比较两个月份相差的月份：

when months_between(concat(substr(c.repair_date, 1, 8),'01'),concat(substr(c.invoice_date, 1, 8),'01') ) between 0 and 12 then '0'
when months_between(concat(substr(c.repair_date, 1, 8),'01'),concat(substr(c.invoice_date, 1, 8),'01') ) between 12 and 24 then '1'

---

select * from tt_table where to_char(fkdat,'yyyy-MM-dd') < '2019-01-01'
#改成hive语句
select * from tt_table where date_format(fkdat,'yyyy-MM-dd') < '2019-01-01'

select max(to_char(balance_time,'yyyy-mm-dd hh24:mi:ss')),min(to_char(balance_time,'yyyy-mm-dd hh24:mi:ss')) from tt_sales_records 
#改成hive语句
select max(balance_time),min(balance_time) from asmp.tt_sales_records_tmp 

select max(from_unixtime(cast(balance_time/1000 as bigint),'yyyy-MM-dd HH:mm:ss')),min(from_unixtime(cast(balance_time/1000 as bigint),'yyyy-MM-dd HH:mm:ss')) from tt_sales_records

----------------------建表语句------------------------
字段类型：
名称：SERIES_NAME VARCHAR2(200)
数字：ONWAY NUMBER
标识：FLAG NUMBER(1)
日期：DATE_DAY VARCHAR2(20)

create table CUSTOMER_COUNT
(
  UPDATE_DATE   DATE not null,
  RSSC_ID       VARCHAR2(100) not null,
  COUNT         NUMBER not null,
  RSSC_NAME     VARCHAR2(100) not null,
  PROVINCE_ID   VARCHAR2(100) not null,
  PROVINCE_NAME VARCHAR2(100) not null
);
comment on table CUSTOMER_COUNT
  is '潜客总数';
comment on column CUSTOMER_COUNT.UPDATE_DATE
  is '计算结果的前一个正点时间';
comment on column DACU.CUSTOMER_COUNT.RSSC_ID
  is '大区id';
comment on column DACU.CUSTOMER_COUNT.COUNT
  is '计算结果';
comment on column DACU.CUSTOMER_COUNT.RSSC_NAME
  is '大区名称';
comment on column DACU.CUSTOMER_COUNT.PROVINCE_ID
  is '省份id';
comment on column DACU.CUSTOMER_COUNT.PROVINCE_NAME
  is '省份名称';

-----------------------通用方法------------------------
条件函数

if(1=2,100,200)作用：当条件为TRUE时，返回100

case id when '001' then '1111' else '2222' end
case when age='20' then 1 else 0 end

trunc函数

select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数后面的截去
select trunc(123.567,-2) from dual;--100,表示将小数点左边指定位数后面的部分截去，即均以0记
select trunc(123.567) from dual;--123,默认截去小数点后面的部分

TO_CHAR函数

TO_CHAR (d.bill_date, 'yyyy-mm')  --把日期或数字转换为字符串

TO_DATE函数

TO_DATE ('20180401', 'yyyymmdd') --把字符串转换为数据库中得日期类型
to_date(concat(year(date_sub(CURRENT_DATE,1)),'-01-01')) --2018-01-01

substr函数

select substr('abcde',3)      --cde
select substring('abcde',3) --cde
select substr('abcde',-1)    --e
select substr('abcde',1,2) --ab
select substr('abcde',0,2) --ab 默认都是从第一位开始取

NVL函数

 NVL(sum(l.fuwu_lamount),0) --从两个表达式返回一个非 null 值

非空函数

SELECT COALESCE(NULL, NULL, 1) -- return 1 返回参数中第一个非null的值

日期处理函数

#当前日期上个月201810
SELECT date_format(add_months(CURRENT_DATE,-1),'yyyyMM')

#当前日期-1天日期的上个月201901
select concat(year(date_sub(CURRENT_DATE,day(CURRENT_DATE))),lpad(month(date_sub(CURRENT_DATE,day(CURRENT_DATE))),2,0))

---------------------Oracle转Hive------------------------

SELECT *
 FROM OMD.TT_DEPOSIT_ORDER
 WHERE MATCH_STATUS <> '4'
 AND TO_CHAR(CREATE_DATE, 'yyyy-MM') = TO_CHAR(sysdate, 'yyyy-MM')
#改成hive语句后
SELECT * 
FROM TT_ORDER 
WHERE MATCH_STATUS <> '4' 
AND from_unixtime(unix_timestamp(CREATE_DATE),'yyyy-MM') = '2015-01'

分页查询

查询返回前10行：
select * from TT_ORDER where rownum<=10;
查询结果返回中间的10到100行：
select * from OB_TT_ORDER where rownum<101  minus  select * from OB_CALL_DATA_LOG rownum>9;

#改成hive语句后
select * from TT_ORDER limit 10;
select * from (select row_number() over (order by xx) as rnum ,o.* from TT_ORDER o) t where rnum > 9 AND rnum < 101;

查看用户所有表

select * from user_tables;
//select * from all_tables where owner='USER';
#改成hive语句后
show tables;

oracle为表增加字段和注释

--指定表中添加多个字段
alter table DACU.tt_shipping
add total_p NUMBER
add amount_oil_p NUMBER
add amount_tyre_p NUMBER

-- 逐条执行，添加字段的注释
COMMENT ON COLUMN DACU.tt_shipping.total_p IS '总计total';
COMMENT ON COLUMN DACU.tt_shipping.amount_oil_p IS '字段oil';
COMMENT ON COLUMN DACU.tt_shipping.amount_tyre_p IS '字段tyre';

附加日志（如果某列的值为YES就是开了）

--全量日志，标识键日志，最小日志
select supplemental_log_data_all, supplemental_log_data_pk, supplemental_log_data_min from v$database;

Oracle行转列示例：

select * from 
(
select v.sst_code,t.sst_name,date_day, sum(newcar_sales_pro)+sum(service_sales) num from dacu.TT_PRESALE_SALES_VOLUME v
join dacu.tm_sst t on t.sst_code=v.sst_code
where date_day >='2020-08-01'
group by v.sst_code,t.sst_name,date_day
) aa
pivot ( sum(num) for date_day in('2020-08-01' as "2020-08-01",'2020-08-02' as "2020-08-02",'2020-08-03' as "2020-08-03",'2020-08-04' as "2020-08-04",'2020-08-05' as "2020-08-05",'2020-08-06' as "2020-08-06",'2020-08-07' as "2020-08-07",'2020-08-08' as "2020-08-08",'2020-08-09' as "2020-08-09",'2020-08-10' as "2020-08-10",'2020-08-11' as "2020-08-11",'2020-08-12' as "2020-08-12",'2020-08-13' as "2020-08-13",'2020-08-14' as "2020-08-14",'2020-08-15' as "2020-08-15",'2020-08-16' as "2020-08-16",'2020-08-17' as "2020-08-17",'2020-08-18' as "2020-08-18",'2020-08-19' as "2020-08-19",'2020-08-20' as "2020-08-20") );

oracle左右关联问题(+)

#oracle中哪边有（+）哪边就允许为空 
where  a.pripid = b.pripid(+) 
#等同于 from a left join b on a.pripid = b.pripid; 
where  a.pripid(+) = b.pripid
#等同于 from a right join b on a.pripid = b.pripid; 

oracle字符串连接函数

a.entname||'%'    
#------转换成hive-------
concat(a.entname,'%') 

oracle和hive的功能是一样的

#oracle substr(字符串,截取开始位置,截取长度)
substr('Hello World',2,4) //返回结果为 'ello'
#hive union all和 oracle的结果一样
union all