MaxCompute常用时间周期SQL

同道大叔

于 2024-08-02 10:52:27 发布

阅读量95

点赞数 1

分类专栏： Hive 文章标签： odps sql 大数据

本文链接：https://blog.csdn.net/weixin_44765495/article/details/140866162

版权

Hive 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

相对时间：近3年、近1年、近6月、近3月、近1月
绝对时间：去年、本年、上月、本月、上周、本周

SELECT  DATE_SUB(TO_DATE('${bizdate}','yyyyMMdd'),365) AS year_1y
        ,TO_DATE(TO_DATE('${bizdate}','yyyyMMdd')) AS bizdate
;

SELECT  TO_DATE(order_dt) > DATE_SUB(TO_DATE('${bizdate}','yyyyMMdd'),420)
        ,TO_DATE(order_dt) <= TO_DATE(TO_DATE('${bizdate}','yyyyMMdd'))
;

SELECT  '${bizdate}' AS d
        ,TO_CHAR(DATE_SUB(TO_DATE('${bizdate}','yyyyMMdd'),30),'yyyyMM') AS 1m
        ,TO_CHAR(DATE_SUB(TO_DATE('${bizdate}','yyyyMMdd'),30 * 3),'yyyyMM') AS 3m
        ,TO_CHAR(DATE_SUB(TO_DATE('${bizdate}','yyyyMMdd'),30 * 6),'yyyyMM') AS 6m
        ,TO_CHAR(DATE_SUB(TO_DATE('${bizdate}','yyyyMMdd'),365),'yyyyMM') AS 1yy
        ,TO_CHAR(DATE_SUB(TO_DATE('${bizdate}','yyyyMMdd'),365 * 3),'yyyyMM') AS 3yy
UNION ALL
SELECT  '${bizdate}' AS d
        ,TO_CHAR(TO_DATE(ADD_MONTHS(TO_DATE('${bizdate}','yyyyMMdd'),-1)),'yyyyMM') AS 1m2
        ,TO_CHAR(TO_DATE(ADD_MONTHS(TO_DATE('${bizdate}','yyyyMMdd'),-3)),'yyyyMM') AS 3m2
        ,TO_CHAR(TO_DATE(ADD_MONTHS(TO_DATE('${bizdate}','yyyyMMdd'),-6)),'yyyyMM') AS 6m2
        ,TO_CHAR(TO_DATE(ADD_MONTHS(TO_DATE('${bizdate}','yyyyMMdd'),-12)),'yyyyMM') AS 1yy2
        ,TO_CHAR(TO_DATE(ADD_MONTHS(TO_DATE('${bizdate}','yyyyMMdd'),-36)),'yyyyMM') AS 3yy2
;

SELECT  TO_DATE(DATETRUNC(TO_DATE('${bizdate}','yyyyMMdd'),'yyyy')) -- this-year
,DATETRUNC(CAST(ADD_MONTHS(TO_DATE('${bizdate}','yyyyMMdd'),-12) AS DATE),'yyyy') -- last-year
,DATE_SUB(DATETRUNC(TO_DATE('${bizdate}','yyyyMMdd'),'yyyy'),1)-- last-year
,TO_DATE(ADD_MONTHS(TO_DATE('${bizdate}','yyyyMMdd'),-12)) -- 1y
,TO_DATE(ADD_MONTHS(TO_DATE('${bizdate}','yyyyMMdd'),-6)) -- 6m
,TO_DATE(ADD_MONTHS(TO_DATE('${bizdate}','yyyyMMdd'),-3)) -- 3m
,TO_DATE(DATETRUNC(TO_DATE('${bizdate}','yyyyMMdd'),'mm')) -- this-month
,DATETRUNC(CAST(ADD_MONTHS(TO_DATE('${today}','yyyyMMdd'),-1) AS DATE),'mm') -- last-month
,DATE_SUB(DATETRUNC(TO_DATE('${today}','yyyyMMdd'),'mm'),1) -- last-month
,DATE_ADD(NEXT_DAY(TO_DATE('${today}','yyyyMMdd'),"MO"),-7) -- this-week
,DATE_ADD(NEXT_DAY(TO_DATE('${today}','yyyyMMdd'),"MO"),-14) -- last-week
, DATE_ADD(NEXT_DAY(TO_DATE('${today}','yyyyMMdd'),"MO"),-8)-- last-week
;