sql - 这个sql算法的性能更好？

Question

我正在寻找替代的 SQL 代码

我的表有 4 列:Id、邮政编码、时间戳和用户值。

行示例:2299898;"40";"2011-03-28 00:45:00+02";1.9

我想为时间等于或早于时间戳的每个邮政编码获取一天的结果集。显然还有每一行的用户数据。

到目前为止我的查询:

SELECT DISTINCT
    FIRST_VALUE(timestamp) OVER (
        PARTITION BY zipcode, DATE(timestamp)
        ORDER BY timestamp DESC
    ) AS timestamp,
    zipcode,
    FIRST_VALUE(userdata) OVER (
        PARTITION BY zipcode, DATE(timestamp)
        ORDER BY timestamp DESC
    ) AS userdata

Answer 1

你不需要窗口函数:

select distinct on (zipcode, timestamp::date)
    timestamp,
    zipcode,
    userdata
from t
order by zipcode, timestamp::date desc, timestamp desc

检查 distinct on