odps相关技巧

一、说在前面

无论是做数据分析，还是做算法，甚至工程、产品、运营的同学来说，能够熟练使用hive SQL是件很必要的事。本篇文章就是记录一些odps中一些通用或者稍微复杂的技巧方法

二、内建函数

2.1 日期相关

--获取当前的日期
SELECT GETDATE(); --2023-08-10 10:39:15

--13位时间戳转为日期（yyyy-MM-dd HH:mm:ss）
SELECT FROM_UNIXTIME(CAST(timestamp/1000 AS BIGINT)) AS times;

--日期(yyyy-MM-dd HH:mm:ss)转时间戳
SELECT UNIX_TIMESTAMP('2023-08-10 10:39:15'); --1691635155

--时间戳转日期字符串yyyymmdd
SELECT TO_CHAR(FROM_UNIXTIME(CAST(1691635155021/1000 AS BIGINT)), 'yyyymmdd'); --20230810

--将yyyymmdd转为yyyy-MM-dd HH:mm:ss
SELECT TO_DATE('${bdp.system.bizdate}','yyyymmdd'); --2023-08-09 00:00:00

--yyyymmdd减少N天变成yyyy-MM-dd HH:mm:ss
SELECT DATEADD(TO_DATE('20230809','yyyymmdd'), - 7,'dd'); --2023-08-02 00:00:00

--yyyymmdd增加N天变成yyyy-MM-dd HH:mm:ss
SELECT DATEADD(TO_DATE('20230809','yyyymmdd'), 1,'dd'); --2023-08-10 00:00:00

--yyyymmdd减少N天变成yyyymmdd
SELECT TO_CHAR(DATEADD(TO_DATE('20230809','yyyymmdd'), - 7,'dd'),'yyyymmdd'); --20230802

--两个日期之间相差的天数
SELECT DATEDIFF('2023-08-11 09:39:15','2023-08-09 00:39:15','dd'); --2 

--两个日期之间相差的小时
SELECT DATEDIFF('2023-08-11 09:39:15','2023-08-11 08:39:15','hh'); --1 

--两个日期之间相差的秒数
SELECT DATEDIFF('2023-08-11 09:39:15','2023-08-11 08:39:15','ss'); --3600 

--返回周几
SELECT WEEKDAY('2023-08-10 09:39:15'); --3 ,实际周三 

--返回是本年第几周 
SELECT WEEKOFYEAR('2023-08-10 09:39:15'); --32 

2.2 窗口函数

--排序
SELECT ROW_NUMBER() OVER (PARTITION BY item_id, user_id ORDER BY times) AS rn 

--累计求和
SELECT SUM(score) OVER(PARTITION BY item_id, user_id ORDER BY times) AS score_all

2.3 字符串相关

未完待续