主页 / dba / 问题 / 294747
Accepted
a_dog_with_no_master
Asked: 2021-06-25 02:23:18 +0800 CST

选择间隔前的第一个时间戳，如果没有合适的间隔，则选择当天的最后一个时间戳

  • 772

我有一个 TIMESTAMP 列：

dates
2021-06-24 05:47:05
2021-06-24 09:47:05
2021-06-24 13:47:05
2021-06-24 17:47:05

我想选择在同一天的下一个时间戳之前 3 小时或更长时间的给定日期的第一个时间戳。

expected output:

2021-06-24 05:47:05

但是，如果没有时间戳比任何其他时间戳（在给定日期）早 3 小时以上，则应返回当天的最后一个时间戳。

postgresql window-functions

1 个回答

  1. Best Answer

    这是一个完全修改过的答案，比前一个更有效。旧答案可以通过查看编辑历史或作为本文底部的脚注来查看。

    下面的所有代码的小提琴都可以在这里的小提琴中找到。

    所以，我们有我们的测试表：

    CREATE TABLE test
(
  the_date TIMESTAMP NOT NULL
);

    填充它 - 添加一天的记录，没有间隔 > 3 小时：

    INSERT INTO test VALUES


('2021-06-23 05:47:05'::TIMESTAMPTZ),     -- NO gaps > 3 hours on this date!
('2021-06-23 07:47:05'::TIMESTAMPTZ),
('2021-06-23 09:47:05'::TIMESTAMPTZ),
('2021-06-23 11:47:05'::TIMESTAMPTZ),
('2021-06-23 13:47:05'::TIMESTAMPTZ),
('2021-06-23 14:47:05'::TIMESTAMPTZ),  
('2021-06-23 16:47:05'::TIMESTAMPTZ),  
('2021-06-23 17:47:05'::TIMESTAMPTZ),
    

('2021-06-24 05:47:05'::TIMESTAMPTZ),  -- TWO gaps > 3 hours on this date

                                      -- 1st gap > 3 hours

('2021-06-24 09:47:05'::TIMESTAMPTZ),

                                      -- 2nd gap > 3 hours

('2021-06-24 13:47:05'::TIMESTAMPTZ),

('2021-06-24 14:47:05'::TIMESTAMPTZ),  -- added for testing
('2021-06-24 16:47:05'::TIMESTAMPTZ),  -- added for testing


('2021-06-24 17:47:05'::TIMESTAMPTZ);

    并且（演示逻辑）然后运行以下 SQL：

    SELECT
  the_date::DATE AS dat, 
  the_date AS td, 
  LEAD(the_date) 
    OVER (PARTITION BY the_date::DATE 
           ORDER BY the_date ASC) AS l_td,
  LEAD(the_date) 
    OVER (PARTITION BY the_date::DATE 
            ORDER BY the_date ASC) - the_date AS diff  -- for demonstration
FROM                                                   -- purposes - see diffs
  test                                                 -- > 3 HOUR - 2 on 24/06
ORDER BY dat, td;

    结果：

           dat                      td                   l_td   diff
2021-06-23  2021-06-23 05:47:05+01  2021-06-23 07:47:05+01  02:00:00
2021-06-23  2021-06-23 07:47:05+01  2021-06-23 09:47:05+01  02:00:00
2021-06-23  2021-06-23 09:47:05+01  2021-06-23 11:47:05+01  02:00:00
2021-06-23  2021-06-23 11:47:05+01  2021-06-23 13:47:05+01  02:00:00
2021-06-23  2021-06-23 13:47:05+01  2021-06-23 14:47:05+01  01:00:00
2021-06-23  2021-06-23 14:47:05+01  2021-06-23 16:47:05+01  02:00:00
2021-06-23  2021-06-23 16:47:05+01  2021-06-23 17:47:05+01  01:00:00
2021-06-23  2021-06-23 17:47:05+01  NULL                    NULL        
2021-06-24  2021-06-24 05:47:05+01  2021-06-24 09:47:05+01  04:00:00
2021-06-24  2021-06-24 09:47:05+01  2021-06-24 13:47:05+01  04:00:00
2021-06-24  2021-06-24 13:47:05+01  2021-06-24 14:47:05+01  01:00:00
2021-06-24  2021-06-24 14:47:05+01  2021-06-24 16:47:05+01  02:00:00
2021-06-24  2021-06-24 16:47:05+01  2021-06-24 17:47:05+01  01:00:00
2021-06-24  2021-06-24 17:47:05+01  NULL                    NULL        
14 rows

    我们已经使用了LEAD()窗口函数。窗口功能非常强大，我强烈建议您花一些精力来学习如何使用它们 - 他们会多次回报您的努力！

    • the_date它根据中的标准提供了 的值和它后面的值之间的比较- 您可以通过改变函数本身的子句ORDER BY来做很多聪明的事情- 可以在这里看到改变其他参数。ORDER BYLEAD()

    • 该PARTITION BY the_date::DATE子句是为数据集中的每个日期提供单独的结果。特别注意 NULL - 由于分区，您不能拥有跨越数天的 LEAD，因此任何给定日期的最后一个时间戳的 LEAD 值将始终是NULL- 这与要求有关 - 见下文。

    另外，请注意NULL减去任何东西NULL（NULL加号相同） - 我们说NULLs “传播”。

    所以，现在我们运行这个 SQL：

    WITH leads AS
(
    SELECT
      the_date::DATE AS dat, the_date AS td, LEAD(the_date)
          OVER (PARTITION BY the_date::DATE) AS l_td
    FROM
      test
)
SELECT DISTINCT ON(dat)
    dat AS "The date", td AS "Gap start or last ts"
FROM leads
WHERE l_td - td > INTERVAL '3 HOUR'
   OR l_td IS NULL
ORDER BY dat, td;

    结果：

    The date    Gap start or last ts
2021-06-23  2021-06-23 17:47:05+01
2021-06-24  2021-06-24 05:47:05+01

    想要的结果！但是，这是怎么回事？从这里：

    PostgreSQL 有一个非常有趣且强大的构造，称为 SELECT DISTINCT ON。不，这不是典型的 DISTINCT。这是不同的。当您拥有相似的数据组并希望根据特定的顺序从每个组中提取一条记录时，它是完美的。

    或者，换一种方式（来自同一个链接）：

    使用 DISTINCT ON，您告诉 PostgreSQL 为 ON 子句定义的每个不同组返回一行。返回该组中的哪一行由 ORDER BY 子句指定。

    或者从这里的 PostgreSQL 文档：

    SELECT DISTINCT ON ( expression [, ...] ) 只保留给定表达式计算结果为相等的每组行的第一行。DISTINCT ON 表达式使用与 ORDER BY 相同的规则进行解释（见上文）。请注意，除非使用 ORDER BY 来确保所需的行首先出现，否则每组的“第一行”是不可预测的。例如：

    SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

    检索每个位置的最新天气报告。但是，如果我们没有使用 ORDER BY 来强制每个位置的时间值降序排列，我们就会从每个位置的不可预测时间获得报告。

    DISTINCT ON 表达式必须匹配最左边的 ORDER BY 表达式。ORDER BY 子句通常包含附加表达式，这些表达式确定每个 DISTINCT ON 组中行的所需优先级。

    如您所见，这（如窗口函数）显然是 PostgreSQL 程序员武器库中非常强大的工具，非常值得花时间和精力去学习。

    一个有趣的替代方法是使用ROW_NUMBER()窗口函数，如果你想要前两个间隙或最后一个记录，如下所示：

    WITH leads AS
(
    SELECT
      the_date::DATE AS dat, the_date AS td,
      LEAD(the_date)
          OVER (PARTITION BY the_date::DATE) AS l_td
    FROM
      test
),
gaps AS
(
    SELECT
      dat, td,
      ROW_NUMBER()
          OVER (PARTITION BY dat ORDER BY td) AS rn
    FROM leads
    WHERE (l_td - td > INTERVAL '3 HOUR')
      OR (l_td IS NULL)
)
SELECT
    dat, td
FROM gaps
WHERE rn <= 2  -- NOTE 2!
ORDER BY dat, td;

    结果：

           dat                      td
2021-06-23  2021-06-23 17:47:05+01
2021-06-24  2021-06-24 05:47:05+01
2021-06-24  2021-06-24 09:47:05+01

    请注意，我们现在有 2021-06-24 的两条记录。

    最后，仅作记录，原始解决方案：

    WITH long_gaps AS
(
  SELECT dat, MIN(td) AS gap
  FROM
  (
    SELECT
      the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER (PARTITIION BY the_date::DATE) AS l_td
    FROM
      test
  ) AS t1
  WHERE l_td - td > INTERVAL '3 HOUR'
  GROUP BY dat
),
short_gaps AS
(
  SELECT the_date::DATE AS dat2, MAX(the_date)
  FROM test
  WHERE the_date::DATE NOT IN (SELECT dat FROM long_gaps)
  GROUP BY dat2

)
SELECT dat AS "The date", gap AS "Gap start or last ts" FROM long_gaps
UNION 
SELECT * FROM short_gaps
ORDER BY 1;  -- parameter 1 which ORDERs BY the first field in the query

    结果：

      The date     Gap start or last ts
2021-06-23   2021-06-23 17:47:05+01
2021-06-24   2021-06-24 05:47:05+01

    小提琴底部给出了 3 种解决方案的性能分析 - 它表明该DISTINCT ON解决方案的性能明显优于其他解决方案 - 但是ROW_NUMBER()有可能更加灵活！然而，警告一句——在我们无法控制的服务器上对一个非常小的数据集进行性能分析，也不知道其他地方发生了什么，这可能存在缺陷——我建议您在自己的硬件上使用合理的数据集进行基准测试。

    将来，当您提出这种性质的问题时，您能否提供一个涵盖所有案例的样本数据 - 即在这种情况下，哪些地方有差距，哪些地方没有。这减少了出错的可能性并消除了重复劳动——帮助我们为您提供帮助。另外，请始终包含您的 PostgreSQL 版本。

    • 4

相关问题