3

I have a simple database schema (Timescaledb) with a few tables. Each user has one main sensor with multiple metrics, each metric has its own table with user_id and timestamp.

Schema

Table 1

| user_id | timestamp | val1 | val... |:---- |:------:| -----:| -----: Table ...

user_id timestamp val1 val...

Question

I want to know how can I merge these tables in a view with the time_bucket function. So, if I sample it every 10 seconds to show something like View

user_id timestamp agg val from table 1 agg val from table 2 agg val from table X
Jean Toniolli
  • 35
  • 4

1 Answers1

4

Yes! it's possible!

You can try it by using regular SQL. Here is some example:

select time_bucket('1m', a.time) as bucket_a,
          a.symbol || '/' || b.symbol as pair,
          LAST(a.price, a.time) as last_price_a,
          LAST(b.price, b.time) as last_price_b
          FROM ticks a
          LEFT JOIN ticks b
          ON time_bucket('1m', a.time) = time_bucket('1m', b.time)
            AND a.symbol = 'SYMBOL' and b.symbol = 'ANOTHER'
          GROUP BY 1, 2;

Note that I'm joining the table ticks twice, you can just join another table with the other metrics.

Another detail is that instead of filtering the symbol on where clause, it's now part of the JOIN clause.

jonatasdp
  • 1,072
  • 6
  • 8
  • Thank you @jonatasdp I think that worked. My query: SELECT t1.user_id, time_bucket('10 seconds', t1.timestamp) as timestamp_bucket, LAST(t1.accuracy, t1.timestamp) as last_acc, LAST(t2.status, t2.timestamp) as last_status FROM tabble1 t1 LEFT JOIN table2 t2 on t1.user_id = t2.user_id and time_bucket('10 seconds', t1.timestamp) = time_bucket('10 seconds', t2.timestamp) WHERE t1.user_id = 'user_X' GROUP BY t1.user_id, timestamp_bucket, t2.timestamp ORDER BY timestamp_bucket; – Jean Toniolli Oct 29 '21 at 13:31
  • However that solves it for two tables, what if I have 20 tables with the same users in each table? Is it still scalable to write a join for 20 tables and 20 `time_buckets`? Also what does last as an aggregator with time bucket does exactly? My understanding is that it's only gonna get the last value in the aggregation, is that correct? – Jean Toniolli Oct 29 '21 at 13:31
  • 1
    @JeanToniolli take a look at the FILTER option: https://kb.objectrocket.com/postgresql/how-to-use-the-filter-clause-in-postgresql-881 – jonatasdp Nov 01 '21 at 15:00