hive 两次操作时间间隔大于0.5小时(LAG)

原创已于 2024-12-23 15:21:01 修改 · 357 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hadoop #数据仓库

于 2024-12-23 13:23:52 首次发布

需求

明细表A记录工人的操作记录，create_time 是操作时间，需要统计操作时间间隔大于0.5小时的次数

WITH ordered_actions AS (
  SELECT
    waybill_no,
    create_time,
    -- 使用 LAG 函数获取上一条记录的 create_time
    LAG(create_time) OVER (PARTITION BY waybill_no ORDER BY create_time) AS prev_create_time
  FROM
    A
)
-- 计算时间间隔，并筛选出间隔大于 0.5 小时的记录
SELECT
  COUNT(*) AS interval_count
FROM
  ordered_actions
WHERE
  -- 计算时间差（单位为小时），并筛选出大于 0.5 小时的记录
  DATEDIFF(create_time, prev_create_time) * 24 > 0.5
  -- 排除第一个记录，因为第一个记录没有上一条记录
  AND prev_create_time IS NOT NULL;

需求

明细表A记录工人的操作记录，create_time 是操作时间，需要按设备统计操作时间间隔大于0.5小时的次数，相邻的操作必须是同一个设备

WITH ordered_actions AS (
  SELECT
    device_id,
    create_time,
    -- 使用 LAG 函数获取上一条记录的 device_id 和 create_time
    LAG(device_id) OVER (ORDER BY create_time) AS prev_device_id,
    LAG(create_time) OVER (ORDER BY create_time) AS prev_create_time
  FROM
   export.tmp_zjs_08  WHERE dt='20241213' AND employee_no ='xxxx'
)
-- 计算时间间隔，并筛选出间隔大于 0.5 小时的记录
SELECT
  COUNT(*) AS interval_count
FROM
  ordered_actions
WHERE
  -- 检查是否为同一设备，并且时间间隔大于 0.5 小时
  (device_id = prev_device_id) AND
  (unix_timestamp(create_time) - unix_timestamp(prev_create_time)) > 1800
  -- 排除第一个记录，因为第一个记录没有上一条记录
  AND prev_create_time IS NOT NULL;