时间戳

首页 > 解决方案 > 如何获取每条记录的匹配 ID？

sql - 如何获取每条记录的匹配 ID？

问题描述

我的表被称为platform_statuses，这是它的架构：

CREATE TABLE platform_statuses (
    id SERIAL,
    account INTEGER REFERENCES users (id),
    time TIMESTAMP DEFAULT NOW(),
    value_in_cents INTEGER NOT NULL,
    status INTEGER NOT NULL CHECK (
        0 < status
        AND status < 4
    ) DEFAULT 2,
    -- 1: Active, 2: Suspended, 3: Market is closed
    open_trades INTEGER NOT NULL,
    PRIMARY KEY (id)
);

这是我的查询，我还想获取返回记录的匹配 id。

SELECT
    max(timediff),
    time :: date,
    account
FROM
    (
        SELECT
            id,
            time,
            account,
            abs(time - date_trunc('day', time + '12 hours')) as timediff
        FROM
            platform_statuses
    ) AS subquery
GROUP BY
    account,
    time :: date

另请注意，您在查询中看到的 abs 函数是我从这个答案中得到的自定义函数。这是它的定义：

CREATE FUNCTION abs(interval) RETURNS interval AS $ $
SELECT
    CASE
        WHEN ($ 1 < interval '0') THEN - $ 1
        else $ 1
    END;

$ $ LANGUAGE sql immutable;

标签： sqlpostgresqldatetimesubquerygreatest-n-per-group

解决方案

我知道，对于每个帐户和每一天，您都想要最大的记录timediff。如果是这样，您可以distinct on()直接在现有子查询上使用：

select distinct on (account, time::date)
    id,
    time,
    account,
    abs(time - date_trunc('day', time + '12 hours')) as timediff
from platform_statuses
order by account, time::date, timediff desc

推荐阅读