Entity Attribute Timestamp Value in CockroachDB or PostgreSQL

00_readme.md

This demonstrates the use of the last_value() window function over a entity-attribute-timestamp-value table in CockroachDB v22.2 to pivot it into a more conventional form. This works in PostgreSQL, too.

It is important to note that even though the last_value() aggregate is operating on windows of data OVER (PARTITION BY entity, attribute ...) the use of the ORDER BY updated_at causes the default window frame to include data only up to the current row in the window. In order to create the desired lookahead behavior, the window frame must be expanded with ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING so that last_value() can indeed see the last value in the window.

10_eatv.sql

CREATE TABLE eatv (
    entity INT8, attribute TEXT, updated_at TIMESTAMP, value TEXT,
    PRIMARY KEY (entity, attribute, updated_at)
);

  WITH attributes (name) AS (VALUES ('foo'), ('bar'), ('baz'), ('quux')),
       row_numbers (r) AS (SELECT generate_series(1, 1000))
INSERT
  INTO eatv (entity, attribute, updated_at, value)
       (
        SELECT r % 101 AS entity,
               (SELECT name FROM attributes LIMIT 1 OFFSET r % 4) AS attribute,
               now() + r * '1 minute'::INTERVAL,
               r::TEXT
          FROM row_numbers
       );

    WITH latest_values AS (
                        SELECT DISTINCT ON (entity, attribute)
                               entity,
                               attribute,
                               last_value(updated_at) OVER (
                                PARTITION BY entity, attribute
                                    ORDER BY updated_at ASC
                                        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                               ) AS updated_at,
                               last_value(value) OVER (
                                PARTITION BY entity, attribute
                                    ORDER BY updated_at ASC
                                        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                               ) AS value
                          FROM eatv
                       )
  SELECT entity,
         max(CASE WHEN (attribute = 'foo') THEN value ELSE NULL END) AS foo,
         max(CASE WHEN (attribute = 'bar') THEN value ELSE NULL END) AS bar,
         max(CASE WHEN (attribute = 'baz') THEN value ELSE NULL END) AS baz,
         max(CASE WHEN (attribute = 'quux') THEN value ELSE NULL END) AS quux
    FROM latest_values
GROUP BY entity;

15_validation.txt

root@localhost:26257/defaultdb> select * from eatv where entity=100;
  entity | attribute |         updated_at         | value
---------+-----------+----------------------------+--------
     100 | bar       | 2023-02-08 20:36:59.865099 | 201
     100 | bar       | 2023-02-08 20:43:43.865099 | 605
     100 | baz       | 2023-02-08 20:38:40.865099 | 302
     100 | baz       | 2023-02-08 20:45:24.865099 | 706
     100 | foo       | 2023-02-08 20:35:18.865099 | 100
     100 | foo       | 2023-02-08 20:42:02.865099 | 504
     100 | foo       | 2023-02-08 20:48:46.865099 | 908
     100 | quux      | 2023-02-08 20:40:21.865099 | 403
     100 | quux      | 2023-02-08 20:47:05.865099 | 807
(9 rows)

20_results.txt

  entity | foo  | bar | baz | quux
---------+------+-----+-----+-------
       0 | 808  | 909 | 606 | 707
.....
     100 | 908  | 605 | 706 | 807
(101 rows)

30_explain.txt

Explain plan for the above

                                                 info
-------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • group (hash)
  │ estimated row count: 101
  │ group by: entity
  │
  └── • render
      │
      └── • render
          │
          └── • distinct
              │ estimated row count: 404
              │ distinct on: entity, attribute
              │
              └── • window
                  │ estimated row count: 1,000
                  │
                  └── • scan
                        estimated row count: 1,000 (100% of the table; stats collected 2 minutes ago)
                        table: eatv@eatv_pkey
                        spans: FULL SCAN
(22 rows)