查找当前行是否是要从数据库中选择的最后一行

问题描述

我正在从数据库中选择期间列表。如果当前行是第一行，则周期以日期开头，我可以找到周期开始之间的间隔，如下所示：

I am selecting list of periods from database. If current row is first row then the period starts with date and I can find the interval between period start like this:

SELECT
...
CASE WHEN row_number() OVER(ORDER BY r.created_at ASC) = 1 THEN r.created_at - r.created_at::date ELSE NULL END AS period
...
FROM mytable r

我如何对 last 行？以查找最后一行的r.created_at与日期的午夜之间的时间。

How can I do the same to last row? To find the time between the r.created_at of last row and midnight of its date.

我知道<$ c $ PostgreSQL中的c> first 和 last 函数（），但它们是合计函数，在这种情况下无济于事。

I am aware of first and last functions in PostgreSQL (https://wiki.postgresql.org/wiki/First/last_(aggregate)), but they are aggregate functions and do not help in this case.

编辑：
这个问题有2个很好的答案。在我的情况下，它们都不起作用，因为我作为问题的一部分提出的这一行是较大查询的一部分，以编程方式组合在一起，使用提供的解决方案将迫使我更改很多代码，而我不愿意这样做。这点。如果遇到扩展问题，那么我肯定会重新考虑。

This question has 2 great answers. Neither of them help in my case, as this single line i presented as part of my question is part of bigger query, put together programmatically and using the solutions offered would force me to alter alot of code, which i am not willing to do at this point. Should the scaling problems hit - then i will certainly reconsider.

推荐答案

这可能比窗口函数要快：

This might be faster than window functions:

with r as (
    select
        min(created_at) as min_created_at,
        max(created_at) as max_created_at
    from mytable
)
select
    case when (select min_created_at from r) = created_at
    then created_at - created_at::date else null
    end as period_min,
    case when (select max_created_at from r) = created_at
    then created_at - created_at::date else null
    end as period_max
from mytable

这篇关于查找当前行是否是要从数据库中选择的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！