达梦数据库查询距离时间段内每天最近8点最近的数据

不甘平凡--liang

已于 2025-03-06 18:11:01 修改

阅读量294

点赞数 3

文章标签：数据库

于 2025-03-06 17:52:31 首次发布

本文链接：https://blog.csdn.net/weixin_44990255/article/details/146076543

版权

达梦数据库查询距离时间段内每天最近8点最近的数据

一、限定时间范围8点前后半小时内，并将结果时间改成8点

WITH ranked_data AS (

  SELECT

    tm,

    val,

    -- 生成目标时间（当日8点）

    TRUNC(tm, 'DD') + INTERVAL '8' HOUR AS target_time,

    -- 计算与8点的时间差（秒），并限制在±30分钟内

    ABS(DATEDIFF(SECOND, TRUNC(tm, 'DD') + INTERVAL '8' HOUR, tm)) AS time_diff,

    -- 按天分区，按时间差排序

    ROW_NUMBER() OVER (

      PARTITION BY TRUNC(tm, 'DD')

      ORDER BY ABS(DATEDIFF(SECOND, TRUNC(tm, 'DD') + INTERVAL '8' HOUR, tm))

    ) AS rn

  FROM his_water_33z1s33czuxlxcql8jf

  WHERE

    -- 限定在8点前后30分钟

    ABS(DATEDIFF(MINUTE, TRUNC(tm, 'DD') + INTERVAL '8' HOUR, tm)) <= 30

    -- 整体时间范围过滤

    AND tm BETWEEN '2025-03-02 22:02:11' AND '2025-03-06 22:02:11'

)

SELECT

  target_time AS time,  -- 返回标准化时间（当日8点）

  val

FROM ranked_data

WHERE rn = 1;  -- 取每组最近的一条

二、不限定时间范围

WITH ranked_data AS (

  SELECT

    tm,

    val,

    -- 按天分组，计算每条记录与当日8点的时间差（单位：秒）

    ABS(

      DATEDIFF(SECOND,

        TRUNC(tm, 'DD') + INTERVAL '8' HOUR,  -- 当日8点

        tm

      )

    ) AS time_diff,

    -- 按天分区，按时间差排序

    ROW_NUMBER() OVER (

      PARTITION BY TRUNC(tm, 'DD')

      ORDER BY ABS(DATEDIFF(SECOND, TRUNC(tm, 'DD') + INTERVAL '8' HOUR, tm))

    ) AS rn

  FROM his_water_33z1s33czuxlxcql8jf

  WHERE tm BETWEEN '2025-03-02 22:02:11' AND '2025-03-06 22:02:11'

)

SELECT tm, val

FROM ranked_data

WHERE rn = 1;

三、取时间段内每个整点的数据，限定整点前后5分钟之内的数据



WITH aligned_data AS (
  SELECT 
    tm,
    val,
    -- 对齐到最近的整点（仅保留前后5分钟内的数据）
    CASE 
      -- 如果时间在整点前5分钟内，对齐到该整点
      WHEN DATEDIFF(MINUTE, TRUNC(tm, 'HH'), tm) <= 5 THEN TRUNC(tm, 'HH')
      -- 如果时间在整点后5分钟内，对齐到下一个整点
      WHEN DATEDIFF(MINUTE, tm, TRUNC(tm, 'HH') + INTERVAL '1' HOUR) <=5 THEN TRUNC(tm, 'HH') + INTERVAL '1' HOUR
      -- 其他情况忽略（超出5分钟范围）
      ELSE NULL 
    END AS aligned_hour
  FROM his_water_33z1s33czuxlxcql8jf
  WHERE tm BETWEEN  '2025-03-02 22:02:11' AND '2025-03-06 22:02:11'
),
filtered_data AS (
  -- 过滤掉不符合条件的记录
  SELECT * FROM aligned_data WHERE aligned_hour IS NOT NULL
),
ranked_data AS (
  SELECT 
    *,
    -- 按对齐后的整点分组，按时间差排序
    ROW_NUMBER() OVER (
      PARTITION BY aligned_hour
      ORDER BY ABS(DATEDIFF(SECOND, tm, aligned_hour))
    ) AS rn
  FROM filtered_data
)
-- 提取结果
SELECT 
  aligned_hour AS time,  -- 标准化为整点时间
  val
FROM ranked_data
WHERE rn = 1;  -- 每组取最近的一条