SHARE
TWEET

Untitled

a guest Oct 21st, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- 60 * 60 * 24 = 86400 // number of seconds in day
  2. WITH range AS (SELECT *,
  3.                       EXTRACT(EPOCH FROM min) min_e,
  4.                       EXTRACT(EPOCH FROM max) max_e
  5.                FROM
  6.                        (SELECT min(date) AS min, max(date) AS max FROM prices) s)
  7. SELECT
  8.     CASE WHEN prev_d <> min_e AND date <> g
  9.              THEN ROUND((m * g + ((price + prev_p) - m * (date + prev_d)) / 2)::NUMERIC, 2)
  10.          ELSE price END                                           price,
  11.     date <> g           interpolated,
  12.     TIMESTAMP WITHOUT TIME ZONE 'epoch' + g * INTERVAL '1 second' date
  13. FROM
  14.     (SELECT *,
  15.             generate_series(prev_d + 86400, date, 86400) g
  16.      FROM
  17.          (SELECT *,
  18.                  (price - prev_p) / (date - prev_d) m
  19.           FROM
  20.               (SELECT
  21.                    id,
  22.                    range.min_e,
  23.                    range.max_e,
  24.                    EXTRACT(EPOCH FROM
  25.                            (lag(date, 1, range.min) OVER (PARTITION BY entity_id ORDER BY date)))::BIGINT prev_d,
  26.                    EXTRACT(EPOCH FROM date)::BIGINT                                                       date,
  27.                    (lag(price, 1, price) OVER (PARTITION BY entity_id ORDER BY date))                     prev_p,
  28.                    price
  29.                FROM
  30.                    prices,
  31.                    range) a
  32.              where date <> prev_d) b) c;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top