oracle改造常见问题
时间：2021-07-01 10:21:17
帮助过：7人阅读
日期到字符操作
select to_char(sysdate,‘yyyy-MM-dd HH24:mi:ss‘) from dual;
二、 to_date:日期转换 to_data(date&time,format)
字符到日期操作
select to_date(‘2005-01-01 13:14:20‘,‘yyyy-MM-dd HH24:mi:ss‘) from dual;
字符到日期操作
- select to_date(’2003-10-17 21:15:37’,’yyyy-mm-dd hh24:mi:ss’) from dual ；
TO_DATE,就是转换数据类型，转换成DATE的数据类型，然后存在表中吧。
三、 row_number:
ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）。
四、 months_betweern:
MONTHS_BETWEEN函数返回两个日期之间的月份数。如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数，否则，返回数值带小数，以每天1/31月来计算月中剩余天数。
关于month_between函数的用法，求解释！
select months_between(lase_day(sysdate),to_Date(‘20120129‘,‘yyyymmdd‘)) from dual = 1
select months_between(lase_day(sysdate),to_Date(‘20120130‘,‘yyyymmdd‘)) from dual = 0.989
select months_between(lase_day(sysdate),to_Date(‘20120131‘,‘yyyymmdd‘)) from dual = 1
为什么中间那个值小于1 而最后一个值又等于1？
------解决方案--------------------
MONTHS_BETWEEN函数返回两个日期之间的月份数。如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数，否则，返回数值带小数，以每天1/31月来计算月中剩余天数。
------解决方案--------------------
假设d1和d2分别和months_between()函数的两个实参。
如果d1和d2是月份中相同的一天，或都是这个月的最后一天，则会返回整数。
否则会返回带小数的结果。
------解决方案--------------------
如果两个日期月份内天数相同(你第一条sql)，
或者都是某个月的最后一天（你第三条sql），
返回一个整数，
否则（你第二条sql），
返回数值带小数，以每天1/31月来计算月中剩余天数。
五、add_month:
add_months 函数主要是对日期函数进行操作，举例子进行说明
add_months 有两个参数，第一个参数是日期，第二个参数是对日期进行加减的数字(以月为单位的)
如：3个月以后的时间，可以用下列语句
SQL> select add_months(sysdate,3) from dual;
ADD_MONTHS(SYSDATE,3)
---------------------
2012-5-16 下午 02:30:47
3个月以前的时间，可以用下列语句
SQL> select add_months(sysdate,-3) from dual;
ADD_MONTHS(SYSDATE,-3)
----------------------
2011-11-16 下午 02:32:39
有时需要用to_date()函数进行转换
SELECT ADD_MONTHS(TO_DATE(‘2012-02-16 19:15:26‘,‘yyyy-mm-dd HH24:MI:SS‘), 2) FROM dual;
ADD_MONTHS(TO_DATE(‘2012-02-16
------------------------------
2012-4-16 下午 07:15:26
六、minux:去同存异
minus运算的主要功能是： 在进行两个表格或者两个查询结果的时候，
返回在第一个表格/查询结果中与第二个表格/查询结果不相同的记录
比如A表中数据如下：
ID NAME ADDRESS TEL
1 ABC AAAA 123456
2 ABD BBBB 123455
3 ABE CCCCC 111111
比如B表中数据如下：
ID NAME ADDRESS TEL
1 ABC AAAC 123456
2 ABD BBBB 123455
那么
（SELECT * FROM A）
MINUS
（SELECT * FROM B）
的结果是：
1 ABC AAAA 123456
3 ABE CCCCC 111111
结果中的第一行数据是由于 A，B 表中的ADDRESS不相等
结果中的第一行数据是由于 A表中的数据在B表中不存在
七、last_day:
LAST_DAY函数返回指定日期对应月份的最后一天。
例：last_day(to_date(‘1999.11.29‘,‘yyyy.mm.dd‘)) 返回 1999年11月31日
例：last_day(to_date(‘2000.02.01‘,‘yyyy.mm.dd‘)) 返回 2000年2月29日
八、substr:
取得字符串中指定起始位置和长度的字符串 substr( string, start_position, [ length ]）
如:
substr(‘This is a test‘, 6, 2) would return ‘is‘
substr(‘This is a test‘, 6) would return ‘is a test‘
substr(‘TechOnTheNet‘, -3, 3) would return ‘Net‘
substr(‘TechOnTheNet‘, -6, 3) would return ‘The‘
select substr(‘Thisisatest‘, -4, 2) value from dual 结果是 te
select substr(‘emros‘,-3,1) value from dual 结果是 r
九、unix_timestamp：日期转换为UNIX时间戳 --------- 返回的是秒数
select UNIX_TIMESTAMP(‘2014-09-10 11:01:46‘);
unix_timestamp()函数的作用是返回一个确切的时间点的UNIX时间戳，这个Unix时间戳是一个无符号整数。unix_timestamp()函数有两种重载形式，一是不带任何参数，另外一个是带有一个Date或DateTime或TimeStamp类型的参数。
unix_timestamp()，返回自1970-1-1 8:00:00开始到当前系统时间为止的秒数。
unix_timestamp(date)，返回1970-1-1 8:00:00开始到date所代表的时间为止的秒数，对于早于1970-1-1 8:00:00的时间，总是返回 0 。
注意：因为中国是东八区，所以是8:00:00.
十、from_unixtime(): UNIX时间戳转换为日期
select FROM_UNIXTIME(1410318106);
FROM_UNIXTIME(unix_timestamp,format)
返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符。
>SELECT FROM_UNIXTIME( 1249488000, ‘%Y%m%d‘ )
->20071120
>SELECT FROM_UNIXTIME( 1249488000, ‘%Y年%m月%d‘ )
->2007年11月20
十一、REGEXP_REPLACE：
SELECT REPLACE(‘Joe Smith‘,‘ ‘, ‘ ‘)
AS replace
FROM dual
REPLACE
---------
Joe Smith
REGEXP_REPLACE 函数把替换功能向前推进了一步，其语法在表 9 中列出。以下查询用单个空格替换了任意两个或更多的空格。( ) 子表达式包含了单个空格，它可以按 {2,} 的指示重复两次或更多次。
表 9： REGEXP_REPLACE 函数
| 语法 |
说明 |
REGEXP_REPLACE(source_string, pattern
[, replace_string [, position
[,occurrence, [match_parameter]]]]) |
该函数用一个指定的 replace_string 来替换匹配的模式，从而允许复杂的"搜索并替换"操作。 |
SELECT REGEXP_REPLACE(‘Joe Smith‘,
‘( ){2,}‘, ‘ ‘)
AS RX_REPLACE
FROM dual
RX_REPLACE
----------
Joe Smith
一般函数：
十二、 left join
十三、join
十四、 right join
十五、常见易混淆的概念：
1.
2.hive中union all不可以使用在主查询中，必须用在子查询中。 select a from (select a from b union all select a from c ) m
3.hive中没有NVL2函数，但可以用if语句实现其功能：NVL2(CLUST_SPC_CD_B, 0, 1) 等价于 if(CLUST_SPC_CD_B is not null, 0, 1)
4.hive中每个子查询后面都要起别名。
5.Hsql中对某些字段有回车换行符号处理，会导致后续表字段错位，目前发现客户表 cert_nbr, 产品实例表 Imsi 、install_addr_desc
Sql：select regexp_replace(cert_nbr,chr(13),’’) from dual ;
Hiveql：select regexp_replace(cert_nbr,’\r’,’’) from dual;
6.hive中没有SYSDATE，但可以用 from_unixtime(unix_timestamp(),“yyyy/MM/dd HH:mm:ss”)实现。其中yyyy/MM/dd HH:mm:ss是日期的格式， 和mysql一致
7.Hive不支持where子句中的子查询，SQL常用的exist in子句需要改写
十六、较难解决的问题：
decode(months_between(to_date(to_char(c.exp_date,‘‘yyyymm‘‘),‘‘yyyymm‘‘),to_date(to_char(add_months(c.eff_date,1),‘‘yyyymm‘‘),‘‘yyyymm‘‘)),12,1,24,2,36,3,0) As agrmnt_year
nvl(to_char(exp_date,‘‘yyyymm‘‘),‘‘300001‘‘) > ‘‘‘||v_date||‘‘‘
cycle_id = ‘||to_number(substr(v_date,1,6))||‘
Delete from ‘||v_aim_table||‘
Where date_id=‘||v_date||‘
and std_latn_cd =‘‘‘||v_latn||‘‘‘
and std_prvnce_cd =‘‘‘||v_prov||‘‘‘
and cycle_id = ‘||to_number(substr(v_date,1,6))||‘
and report_id = ‘||v_report_id_b||‘‘;
v_sql := ‘Delete from ‘||v_aim_table||‘@to_ctgdap_ctbw
Where date_id=‘||v_date||‘
and std_latn_cd =‘‘‘||v_latn||‘‘‘
and std_prvnce_cd =‘‘‘||v_prov||‘‘‘
and cycle_id = ‘||to_number(substr(v_date,1,6))||‘
and report_id = ‘||v_report_id_d||‘‘;
TO_CHAR(B.OPEN_DATE,‘‘YYYYMM‘‘) = ‘‘201209‘‘
WHEN TRANSLATE(C.RECV_RATE,‘‘1234567890‘‘||C.RECV_RATE,‘‘1234567890‘‘) / 1024 <= 2 THEN 1
WHEN TRANSLATE(C.RECV_RATE,‘‘1234567890‘‘||C.RECV_RATE,‘‘1234567890‘‘) / 1024 > 2 AND TRANSLATE(C.RECV_RATE,‘‘1234567890‘‘||C.RECV_RATE,‘‘1234567890‘‘) / 1024 <= 8 THEN 2
WHEN TRANSLATE(C.RECV_RATE,‘‘1234567890‘‘||C.RECV_RATE,‘‘1234567890‘‘) / 1024 > 8 AND TRANSLATE(C.RECV_RATE,‘‘1234567890‘‘||C.RECV_RATE,‘‘1234567890‘‘) / 1024 <= 20 THEN 3
WHEN TRANSLATE(C.RECV_RATE,‘‘1234567890‘‘||C.RECV_RATE,‘‘1234567890‘‘) / 1024 > 20 THEN 4
SELECT /*+PARALLEL(T1,5)+*/ T1.STD_PRVNCE_CD, T1.STD_LATN_CD, T1.PROD_INST_ID, T1.PD_INST_STATE_CD
FROM TF_PRD_PD_INST_‘||V_DATE||‘ T1
WHERE T1.STD_PRVNCE_CD = ‘‘‘||V_PROV||‘‘‘
AND T1.STD_LATN_CD = ‘‘‘||V_LATN||‘‘‘
MINUS
SELECT /*+PARALLEL(T2,5)+*/ T2.STD_PRVNCE_CD, T2.STD_LATN_CD, T2.PROD_INST_ID, T2.PD_INST_STATE_CD
FROM TF_PRD_PD_INST_‘||V_DATE_LAST||‘ T2
WHERE T2.STD_PRVNCE_CD = ‘‘‘||V_PROV||‘‘‘
AND T2.STD_LATN_CD = ‘‘‘||V_LATN||‘‘‘
to_char(s.inout_time,‘‘yyyymm‘‘) = ‘‘‘||v_date||‘‘‘
----------------未完待续oracle改造常见问题
标签：存在 art if语句 相等 连续 aac 字段 转化 lap
