如何在PostgreSQL中按条件获取最后一个值？

Question

我在Postgres中有一张表，包含三列：一列组名、一列日期和最后一列值。

grp	mydate	value
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

我想创建一个视图，其中包含一个日期序列，并显示每个日期对应的最近的组的值。

组	日期	数值
A	2021-01-27	5
A	2021-01-26	10
A	2021-01-25	10
A	2021-01-24	10
A	2021-01-23	10
A	2021-01-22	15
A	2021-01-21	15
A	2021-01-20	15
A	2021-01-19	15
A	2021-01-18	15
A	2021-01-17	15
A	2021-01-16	15
A	2021-01-15	15
B	2021-01-27	7
B	2021-01-26	7
B	2021-01-25	12
B	2021-01-24	12
B	2021-01-23	17
B	2021-01-22	17
B	2021-01-21	17
B	2021-01-20	17
B	2021-01-19	17
B	2021-01-18	17
B	2021-01-17	17
B	2021-01-16	17
B	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)  

- Rafael Leite

---

这个回答解决了你的问题吗？在每个GROUP BY组中选择第一行？ - Bergi

我猜你期望的输出是错误的：在B组中，如果你按照其余部分使用的模式，你想要从第24天到第16天的值为“12”。 - S-Man

@S-Man，小组A的情况是错误的。已经进行了更正，谢谢。 - Rafael Leite

@Bergi 这个问题有点不同，因为它是在寻找全局的第一个或最后一个。我正在寻找确定日期之前的最后一个记录。我尝试使用一些解决方案，但没有成功。 - Rafael Leite

修改了答案以适应新的要求。 - S-Man

Answer 1

逐步演示：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() 返回 NULL 。为了避免这种情况，COALESCE() 将它们设置为当前记录的值。
3. 现在，您可以使用 generate_series() 创建当前日期值和下一个日期值的日期范围。
4. 最后，您可以生成所需的排序。

- S-Man

---

工作得很好！我唯一需要改变的是在COALESCE函数的第二个参数中使用“NOW()”，因为我不希望列表停留在最后注册日期，而是以今天的日期结束。 - Rafael Leite