时间戳

首页 > 解决方案 > 如何在 postgreSQL 中获取条件的最后一个值？

postgresql - 如何在 postgreSQL 中获取条件的最后一个值？

问题描述

我在 postgres 中有一个包含三列的表，一列带有组，一列带有日期，最后一列带有值。

grp 我的约会 价值
一个 2021-01-27 5
一个 2021-01-23 10
一个 2021-01-15 15
乙 2021-01-26 7
乙 2021-01-24 12
乙 2021-01-15 17

我想根据组创建一个包含一系列日期和表上每个日期的最新值的视图。

grp 我的约会 价值
一个 2021-01-27 5
一个 2021-01-26 10
一个 2021-01-25 10
一个 2021-01-24 10
一个 2021-01-23 10
一个 2021-01-22 15
一个 2021-01-21 15
一个 2021-01-20 15
一个 2021-01-19 15
一个 2021-01-18 15
一个 2021-01-17 15
一个 2021-01-16 15
一个 2021-01-15 15
乙 2021-01-27 7
乙 2021-01-26 7
乙 2021-01-25 12
乙 2021-01-24 12
乙 2021-01-23 17
乙 2021-01-22 17
乙 2021-01-21 17
乙 2021-01-20 17
乙 2021-01-19 17
乙 2021-01-18 17
乙 2021-01-17 17
乙 2021-01-16 17
乙 2021-01-15 17

生成表的 SQL 代码：

CREATE TABLE foo (
    grp char(1),
    mydate date,
    value integer);

INSERT INTO foo VALUES
('A', '2021-01-27', 5),
('A', '2021-01-23', 10),
('A', '2021-01-15', 15),
('B', '2021-01-26', 7),
('B', '2021-01-24', 12),
('B', '2021-01-15', 17)

到目前为止，我已经成功地生成了一个带有不同组的日期序列的可视化，但是我没有得到最新的值。

SELECT DISTINCT(foo.grp), (date_trunc('day'::text, dd.dd))::date AS mydate
   FROM foo, generate_series((( SELECT min(foo.mydate) AS min
           FROM foo))::timestamp without time zone, (now())::timestamp without time zone, '1 day'::interval) dd(dd)

标签： postgresql

解决方案

分步演示：db<>fiddle

SELECT
    grp,
    gs::date as mydate,
    value
FROM (
    SELECT
        *,
        COALESCE(                                                             -- 2
             lead(mydate) OVER (PARTITION BY grp ORDER BY mydate) - 1,        -- 1 
             mydate
        ) as prev_date
    FROM foo
) s,
    generate_series(mydate, prev_date, interval '-1 day') as gs               -- 3
ORDER BY grp, mydate DESC                                                     -- 4
  1. lead()窗口函数将有序组（=分区）的下一个值转移到当前值。组已定义，顺序为date. 这可用于创建所需的日期范围。由于您不希望最后一个日期有两次（作为第一个范围的结束和下一个范围的开始），因此结束日期停止- 1（下一组开始前一天）
  2. 这是组的最后记录：他们没有以下记录，所以lead()yield NULL。为避免这种情况，COALESCE()请将它们设置为当前记录。
  3. 现在，您可以使用当前和下一个日期值创建日期范围generate_series()。
  4. 最后就可以生成需要的订单了

推荐阅读