时间戳

首页 > 解决方案 > Postgres 默认值是两条记录的差

sql - Postgres 默认值是两条记录的差

问题描述

我有一个带有列的 postgres 表：id，文本，时间戳，diff id 是自动生成的，文本被插入，时间戳是默认值，diff 应该是当前记录的时间戳和上一个记录的时间戳（前一个记录的时间戳）的差异，如果previous 与实际是同一天，如果不是，则应为 0。因此，插入文本时会自动生成所有列。你知道通过表达式等使这个差异默认的方法吗？我是 sql 的新手。

谢谢你。

标签： sqlpostgresqlexpressiondefault

解决方案

为此，您可以使用触发器：

演示：db<>小提琴

CREATE TABLE tbl (
    id int,
    ts timestamp DEFAULT now(),                                     -- 1
    diff float                                                      -- 2
);

CREATE FUNCTION set_ts_diff() RETURNS trigger AS $$
    DECLARE
        _last_ts timestamp;
    BEGIN
        SELECT COALESCE(MAX(ts), NEW.ts) FROM tbl INTO _last_ts;    -- 3

        IF (NEW.ts::date = _last_ts::date) THEN                     -- 4
            NEW.diff := EXTRACT(EPOCH FROM NEW.ts - _last_ts);      -- 5
        ELSE
            NEW.diff := 0;
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_set_ts_diff BEFORE INSERT ON tbl                  -- 6
    FOR EACH ROW EXECUTE PROCEDURE set_ts_diff();
  1. 创建你的表。默认ts获取当前时间戳 ( )。now()
  2. diff将用作以秒为单位的差异。因为可以在一秒钟内完成两次插入，所以float类型会有所帮助。另一种方法可能是interval时间戳减法的默认类型（见第 5 点）
  3. 触发器需要一个触发器函数来知道触发事件后要做什么。在这个函数中，我们得到最后ts存储在tbl前面的。如果不存在行，ts则NEW在COALESCE
  4. 然后检查最后一个日期和当前ts日期是否相同（通过将两个时间戳都转换为 来检查date）。
  5. 如果是这样，计算导致类型的差异interval。从interval使用中获取秒数EXTRACT(EPOCH FROM ...)。
  6. 创建触发器：在持久化新行 ( ON BEFORE INSERT) 之前，执行触发器函数并设置NEW.diff列。

注意：

您应该几乎不保留以后可以计算的数据。可以借助LAG窗口函数计算两个时间戳之间的差异，该函数将前一行的值取为当前行的值（演示）：

SELECT
    ts,
    EXTRACT(EPOCH FROM ts - lag(ts) OVER (ORDER BY ts)) as diff
FROM
    tbl;

如果您必须删除一行，则可以看出硬存储差异的缺点。下一个的差异现在有一个错误的值，因为它的引用丢失了。您的数据可能会变得不一致。

推荐阅读