impala工作日志-日期问题1

mmyyiio

已于 2024-10-10 09:14:35 修改

阅读量440

点赞数 3

分类专栏：大数据开发日常工作文章标签：数据库

于 2024-09-02 16:31:48 首次发布

本文链接：https://blog.csdn.net/weixin_40490682/article/details/141820160

版权

大数据开发日常工作专栏收录该内容

2 篇文章 0 订阅

订阅专栏

impala的时间问题汇总

timestamp类型转指定类型的日期字符串：
FROM_UNIXTIME( UNIX_TIMESTAMP(XXX_DATE) , 'yyyyMMdd' )

把日期字符串转换为timestamp：
TO_TIMESTAMP(HOLIDAY_DATE, 'yyyyMMdd')

不使用侧视图以及辅助表，计算两个日期内，有多少个双休日

with st1 as (
    select creation_date, LAST_UPDATE_DATE
        ,unix_timestamp(creation_date, 'yyyy-MM-dd HH:mm:ss.S') creation_date_u
        ,unix_timestamp(LAST_UPDATE_DATE, 'yyyy-MM-dd HH:mm:ss.S') LAST_UPDATE_DATE_u
        ,(unix_timestamp(LAST_UPDATE_DATE, 'yyyy-MM-dd HH:mm:ss.S') - unix_timestamp(creation_date, 'yyyy-MM-dd HH:mm:ss.S')) / 86400 as d
        ,DATEDIFF(TO_TIMESTAMP(LAST_UPDATE_DATE, 'yyyy-MM-dd HH:mm:ss.S'), TO_TIMEsTAMP(creation_date, 'yyyy-MM-dd HH:mm:ss.S')) DD
        ,floor(( DAYOFWEEK(creation_date) + datediff(TO_TIMESTAMP(LAST_UPDATE_DATE, 'yyyy-MM-dd'), TO_TIMESTAMP(creation_date, 'yyyy-MM-dd')) ) / 7 ) * 2 AS weekds
        ,(UNIX_TIMESTAMP(days_add(SUBSTR(CREATION_DATE, 1, 10) ,1)) - unix_timestamp(creation_date, 'yyyy-MM-dd HH:mm:ss.S')) /86400 firstD
        ,(UNIX_TIMESTAMP(LAST_UPDATE_DATE, 'yyyy-MM-dd HH:mm:ss.S') - UNIX_TIMESTAMP(SUBSTR(LAST_UPDATE_DATE, 1, 10), 'yyyy-MM-dd'))/86400 lastd
        ,DAYOFWEEK(to_timestamp(creation_date, 'yyyy-MM-dd HH:mm:ss.S')) CREATION_WEEK
        ,DAYOFWEEK(to_timestamp(LAST_UPDATE_DATE, 'yyyy-MM-dd HH:mm:ss.S')) UPDATE_WEEK
        ,CASE WHEN SUBSTR(CREATION_DATE, 1,10) = SUBSTR(LAST_UPDATE_DATE, 1, 10) 
            THEN 'Y' ELSE 'N' END IF_ONE_DAY -- 是否同一天
    from XXX_TABLE
)
,

st2 as (
select st1.*
-- 先对结果+1 - 1
    ,IF(weekds = 0, 0
        ,IF (CREATION_WEEK > 1
            ,IF( update_week % 7 > 0, weekds, weekds - 1 )
            ,IF( update_week % 7 > 0, weekds + 1, weekds )  
        )
    ) as weekds2

from st1
)

select st2.*
-- 然后再对结果 加减 小数日期
    ,IF(WEEKDS2 = 0, 0
        ,IF (creation_week in (1,7)
            ,IF( UPDATE_WEEK IN (1,7) AND WEEKDS2 >= 2, WEEKDS2 - 2 + FIRSTd + LASTD, WEEKDS2 - 1 + firstd )
            ,IF( UPDATE_WEEK IN (1,7), WEEKDS2 - 1 + lastd, WEEKDS2 )  
        )
    ) as REAL_WEEKDS
    
    ,DECODE(IF_ONE_DAY,'Y', IF(CREATION_WEEK IN (1,7), 0, D),
        D - IF(WEEKDS2 = 0, 0
            ,IF (creation_week in (1,7)
                ,IF( UPDATE_WEEK IN (1,7) AND WEEKDS2 >= 2, WEEKDS2 - 2 + FIRSTd + LASTD, WEEKDS2 - 1 + firstd )
                ,IF( UPDATE_WEEK IN (1,7), WEEKDS2 - 1 + lastd, WEEKDS2 )  
            )
        ) 
    )as REAL_DAY_NOWEEK
from st2

步骤1

代码解释：creation_date和LAST_UPDATE_DATE是我需要进行统计的时间点；
d：表示这两段时间实际的相距时间，单位天数，使用unix_timestamp转换并计算；
DD：表示这两段时间相距的天数，只计算天，忽略第一天和最后一天以及过去了多久；并且只是对两个时间求差，没有+1，可以理解为第一天或者最后一天没有算上；
weekds：使用了公式（creation_date的星期数DAYOFWEEK 表示取某个时间的星期几，周日开始算1一直到周六为7 + 相距的天数）/ 7 * 2；可以理解为，这段时间，它涉及了几个星期；比如2024年8月30日 - 2024年9月6日，转换成公式：floor( (6 星期 + 7 天数) / 7 ) * 2 = 1 * 2 = 2 也就是，这段时间涉及了一个星期，而一个星期就会有一对周末，得到结果2天；不过很明显，时间段其实跨了两个周，后面那个周也就是结束时间所在周先不算进来，后续再做处理；

这里要注意，如果开始和结束时间处于同一周，那么按照这个算法，得到的结果是0；舍弃的这个结束时间所在的周，后面会做特殊情况处理；

firstD：计算第一天实际统计到内的时间，使用当天的第二天0时0分0秒减去当前的时间；开始时间是个点，该点往后的时间才是我们需要统计的，比如 2024-09-02 18:00:00 那么这一天当然不能统计为1，应该是2024-09-03 00:00:00 减去 2024-09-02 18:00:00 才能算作实际经历的时间；
lastD：同理，最后一天，也不能直接统计为1天，应当是当前时间减去当天 0时0分0秒的时间；
creation_week：开始时间的星期数
update_week：结束时间的星期数

步骤2

开始排除特殊情况，weeks = 0 说明开始时间和结束时间是同一天，那就先不处理；CREATION_WEEK > 1 说明，开始时间再礼拜2开始了，那么之前计算weekds时，每个星期都算了两天，这时候，就应该减掉一天；同理，如果最后一天不是周六（impala默认是周日 - 周六算作一周）也就是update_week % 7 > 0 ，则需要加一天，因为计算weekds没有把最后一天所在的星期计算在内，因此，需要再加1；这两个条件两两交叉，就能构成如下的case when语句：

,IF(weekds = 0, 0
    ,IF (CREATION_WEEK > 1
        ,IF( update_week % 7 > 0, weekds, weekds - 1 )
        ,IF( update_week % 7 > 0, weekds + 1, weekds )  
    )
) as weekds2

步骤3

将处理好的weekds2转换为实际的双休天数，如果开始时间或者结束时间正好是双休日，那么计算双休日的天数时，不能计算为1，而是按照时间点计算当天实际的天数值；首先排除开始日期和结束日期是同一天的情况，然后分别按照两天是不是周末，如果是的话扣掉天数转换为实际开始时间firstD或者实际结束时间lastD；最后拿实际的时间间距D 减去处理后的周末时间，得到结果，并且结果是精确到时分秒的天数；

D - IF(WEEKDS2 = 0, 0
    ,IF (creation_week in (1,7)
        ,IF( UPDATE_WEEK IN (1,7) AND WEEKDS2 >= 2, WEEKDS2 - 2 + FIRSTd + LASTD, WEEKDS2 - 1 + firstd )
        ,IF( UPDATE_WEEK IN (1,7), WEEKDS2 - 1 + lastd, WEEKDS2 )  
    )
) 
)as REAL_DAY_NOWEEK

使用辅助表，计算双休日和假期

    WITH DATE_TMP AS (
    -- 所有的双休日
    SELECT 
        DATE_ADD( YEARS_ADD(NOW(), -1), T.CN - 1) AS DT -- 今年连续的时间
        ,DAYOFWEEK( DATE_ADD( YEARS_ADD(NOW(), -1), T.CN - 1) ) WK
        ,SUBSTR( CAST( DATE_ADD( YEARS_ADD(NOW(), -1), T.CN - 1) AS STRING) , 1, 10)
            AS DT_STR
    FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY PI_NO) CN
        FROM table1
        LIMIT 367
    ) T
    WHERE DAYOFWEEK( DATE_ADD( YEARS_ADD(NOW(), -1), T.CN - 1) ) IN (1,7)
    UNION ALL
    -- 非周末的节假日
    SELECT  day_date  DT
        ,DAYOFWEEK(DAY_DATE) WK
        ,SUBSTR( CAST(DAY_DATE AS STRING), 1, 10) AS DT_STR
    FROM holiday
    WHERE DAYOFWEEK(DAY_DATE) NOT IN (1, 7)
    GROUP BY 1
    )

代码中，首先找一张数据量在367以上的表，然后计数；在外层开始累加天，计算当天的时间 - 连续的计数，然后这里只需要计算一年的时间，因此当前时间往前倒 367 - 1 天；最后，只筛选周六和周日的时间。
第二部分，这时一个给定的节假日表，直接过滤，选择非双休的节假日，就可以避免重复计算；
最后，我需要将这些时间聚合，然后用开始时间和结束时间的差减去 这段时间内 应该休息的时间；那逻辑可以这样组织

SELECT 
    T.其他维度字段
    ,T.START_DATE
    ,SUM(
            CASE WHEN DT.DT_STR = SUBSTR( CAST(CURRENT_TIMESTAMP() AS STRING), 1, 10)
                -- 当前时间 - 今天的0时0刻
                THEN ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(SUBSTR(CAST(NOW() AS STRING), 1, 10)) ) /86400  
            WHEN DT.DT_STR = SUBSTR( CAST(T.SUBMIT_DATE AS STRING) , 1, 10)
                -- start_date第二天的0时0刻 - start_date
                THEN ( UNIX_TIMESTAMP( SUBSTR( CAST(DAYS_ADD(T.START_DATE, 1) AS STRING) , 1, 10) ) - UNIX_TIMESTAMP(T.START_DATE) ) / 86400
            WHEN DT.DT IS NULL THEN 0
            ELSE 1.0 END
        ) AS CURRENT_TO_START_DATE
FROM TABLE1 T
LEFT JOIN  DATE_TMP D ON D.DT BETWEEN BETWEEN SUBSTR(CAST(T.START_DATE AS STRING),1,10)  AND CURRENT_TIMESTAMP()
GROUP BY T.其他维度字段, T.START_DATE

代码解释：

1 在连接DATE_TMP的时候，使用上一步整理的时间集合 between 在开始时间和结束时间的范围内，这里的业务要求，给定一个开始时间start_date 和当前的时间取差值，并去掉中间的双休或者节假日；因此，如此的不等式连接可以筛选出所有时间范围内的休息日；
2 连接完成之后，我们需要对关联的结果进行聚合，因为一条数据的一段时间，可能会涉及到多个休息日；而在聚合节假日时，也要考虑到，开始日期和结束日期不是完整的一天，而是有零有整的天数；因此在SUM时，要区分情况，如果节假日等于结束日期CURRENT_TIMESTAMP() 则需要计算为当天时间 - 当天0时0分0秒的差值，也就是( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(SUBSTR(CAST(NOW() AS STRING), 1, 10)) ) /86400；相对的，如果这个节假日是开始的那个时间，则需要统计START_DATE第二天的0时0分0秒减去 START_DATE；如果关联不到就记为0，最后ELSE 的情况就是完整的天了，直接按1.0 进行统计；这样current_to_start_date 就表示从当前到start_date之间，有多少时间算作是节假日的；
最后，直接用两个时间点的差减去这个聚合的节假日时间，就可以算出剔除掉双休、节假日的实际天数。

计算同一个分组下，对时间进行排序，如果两个时间间隔超过90天，则即使是同一组，也重新排序

思路：
在序列号MATERIAL_SN相同的数据中，按照维修日期CUSTOMER_CONFIRMATION_TIME进行排序，同一个序列号可能有多次维修记录，需要新加一列显示维修的次数；但是，如果上一次和下一次时间超过90天，那么即使是同一个序列号MATERIAL_SN之后的数据也需要重新排序；
1 首先给每一条数据按照MATERIAL_SN分组，并偏移到上一条数据的时间；（没有上一条则为空）
2 添加新的一列，划分时间间隔，这一步是想要对数据进行再次分组，也就是在时间断点处使用SUM累加1，如果不是断点应该归到同一组，则累加0；（断点的规则就是：两个时间超过90天或者是第一天没有偏移到上一天，则直接作为新的分组）
在这里插入图片描述
从图片里看数据，material_sn有两个，且每一个material_sn都有多条时间记录；分组并排序之后，获取上一条的偏移last_dt，这里的时间间隔我按照1天来计算，同一天的进行排序，第二天的即使是同一个material_sn也要重新排序；ms，进行累加序号，如果是同一天累加0，则表示这些是排到同一个组中的；第一天或者超过时间间隔的会累加1，表示属于新的分组；
3 最后再开窗取ROW_NUMBER，按照序列号MATERIAL_SN以及上一步得到的累加MS进行分组，按照时间排序，就可以获得对应的序号；

SELECT *,
	-- 3 使用序列号、上一步的MS 重新进行分组，按照时间升序排序
    ROW_NUMBER() OVER(PARTITION BY MATERIAL_SN, MS ORDER BY CUSTOMER_CONFIRMATION_TIME) FILT_NO
FROM(
    SELECT R.*
		-- 2 对同一组数据进行再分组，让所有时间断点处累加1，否则累加0，那么连续的时间就会归到一组中
        ,SUM( 
            CASE 
                WHEN R.LAST_DT IS NULL THEN 1
                WHEN DATEDIFF( R.CUSTOMER_CONFIRMATION_TIME , R.LAST_DT) > 90 THEN 1
            ELSE 0 END
        ) OVER (PARTITION BY  MATERIAL_SN ORDER BY R.CUSTOMER_CONFIRMATION_TIME) MS
        ,DATEDIFF( R.CUSTOMER_CONFIRMATION_TIME , R.LAST_DT) BT_DT
    FROM (

        SELECT T.*
            -- 1 偏移上一条的时间
            ,LAG(CUSTOMER_CONFIRMATION_TIME) OVER(PARTITION BY MATERIAL_SN ORDER BY CUSTOMER_CONFIRMATION_TIME) LAST_DT
        FROM (
            SELECT MATERIAL_SN, CUSTOMER_CONFIRMATION_TIME
            FROM XXX
        ) T
    ) R
) Z