代码之家  ›  专栏  ›  技术社区  ›  Sven Koschnicke

SQL:为距离特定时间最近的每一天选择一条记录

  •  3
  • Sven Koschnicke  · 技术社区  · 14 年前

    我有一个表,用于存储带有时间点的值:

    CREATE TABLE values
    (
        value DECIMAL,
        datetime DATETIME 
    )
    

    每一天可能有许多值,给定的一天也可能只有一个值。现在我想得到给定时间跨度(例如一个月)内每天的值,该时间跨度最接近一天中的给定时间。如果这一天有记录,我只想每天得到一个值;如果没有记录,我只想每天得到一个值。我的数据库是PostgreSQL。我被这件事困住了。我可以只获取时间跨度中的所有值,并以编程方式为每天选择最接近的值,但这意味着要从数据库中提取大量数据,因为一天可以有许多值。

    把它表述得更抽象一点:我有任意精度的数据(可能是一分钟,可能是两小时或两天),我想把它转换成一天的固定精度,一天中有一个特定的时间。

    (第二次更新)

    假设所需的时间是16:00,则这是来自具有正确postgresql类型对话的已接受答案的查询:

    SELECT datetime, value FROM values, (
      SELECT DATE(datetime) AS date, MIN(ABS(EXTRACT(EPOCH FROM TIME '16:00' - CAST(datetime AS TIME)))) AS timediff
      FROM values
      GROUP BY DATE(datetime)
      ) AS besttimes
    WHERE 
    CAST(values.datetime AS TIME) BETWEEN TIME '16:00' - CAST(besttimes.timediff::text || ' seconds' AS INTERVAL)
                                    AND TIME '16:00' + CAST(besttimes.timediff::text || ' seconds' AS INTERVAL) 
                                    AND DATE(values.datetime) = besttimes.date
    
    2 回复  |  直到 14 年前
        1
  •  5
  •   eumiro    14 年前

    往这个方向走怎么样?

    SELECT values.value, values.datetime
    FROM values,
    ( SELECT DATE(datetime) AS date, MIN(ABS(_WANTED_TIME_ - TIME(datetime))) AS timediff
      FROM values
      GROUP BY DATE(datetime)
    ) AS besttimes
    WHERE TIME(values.datetime) BETWEEN _WANTED_TIME_ - besttimes.timediff
                                    AND _WANTED_TIME_ + besttimes.timediff
    AND DATE(values.datetime) = besttimes.date
    

    我不确定日期/时间提取和abs(time)函数,所以您可能需要替换它们。

        2
  •  2
  •   ian    14 年前

    1. 一天有结果吗?

    如果没有结果,可以在第1部分简化流程,这样可以节省大量的执行时间。

    接下来要注意的是,您不必从数据库中提取数据,而是先使用PLSQL函数(或其他函数)在服务器上解决问题,直到得到答案为止。

    intervals