Ruby on Rails converting Timescale SQL rate code to Ruby

Question

I'm trying to use the Rate function from timescale to generate graph data. Right now I have a database view that does this using the concepts from the SQL code below from TimescaleDocs:

SELECT
  time,
  (
    CASE
      WHEN bytes_sent >= lag(bytes_sent) OVER w
        THEN bytes_sent - lag(bytes_sent) OVER w
      WHEN lag(bytes_sent) OVER w IS NULL THEN NULL
      ELSE bytes_sent
    END
  ) / extract(epoch from time - lag(time) OVER w) AS "bytes_per_second"
  FROM net
  WHERE interface = 'eth0' AND time > NOW() - INTERVAL '1 day'
  WINDOW w AS (ORDER BY time)
  ORDER BY time

Is there a way to convert this directly to ruby code in a timeseries model to improve runtime?

What's the runtime now? I can help improve that on the SQL side potentially... is this what you're trying to graph or are you trying to do something different and this is just the start of your analysis? — davidk, Jul 28 '21 at 15:58

score 1 · Accepted Answer · answered Jul 28 '21 at 20:34

It's possible, but I bet building the same model in Ruby (MRI) will be much slower than over SQL runtime.

Would you mind sharing more details about what performance issues do you have?

If you want to build it with Ruby, I'd say that you should think about having some "window concept" that will be responsible for understanding the lag over some specific array.

Example:

a = [1,2,3,4,5]
lag = -> (i) {a[i-2] if i > 1} # => #<Proc:0x00007fcca6cf6aa0@(pry):12 a.each_with_index.map{|e,i|[e,lag[i]]}
# => [[1, nil], [2, nil], [3, 1], [4, 2], [5, 3]]

In this case, I'm using raw numbers but you can build a more complex rule with objects that contains the time attribute.

You can round time as time_bucket using some simple module around the time. Check some ideas here.

Ruby on Rails converting Timescale SQL rate code to Ruby

1 Answers1