SHARE
TWEET

Untitled

a guest Jun 16th, 2019 78 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. id |  item_id  | date       | amount
  2. -------------------------------------
  3. 1       1        2019-01-01     1  
  4. 2       1        2019-01-02     2
  5. 3       1        2019-01-03     3
  6. 4       1        2019-01-04     4
  7. 5       1        2019-01-05     5
  8. 6       2        2019-01-01     1
  9. 7       2        2019-01-01     2
  10. 8       2        2019-01-01     3
  11. 9       2        2019-01-01     4
  12. 10      2        2019-01-01     5
  13. 11      3        2019-01-01     1
  14. 12      3        2019-01-01     2
  15. 13      3        2019-01-01     3
  16. 14      3        2019-01-01     4
  17. 15      3        2019-01-01     5
  18.      
  19. SELECT
  20.     x.item_id AS id,avg(x.amount) AS result
  21. FROM
  22.     (SELECT
  23.          il.item_id, il.amount,  
  24.          ROW_NUMBER() OVER (PARTITION BY il.item_id  ORDER BY il.date DESC) rn
  25.      FROM
  26.          item_prices il) x
  27. WHERE
  28.     x.rn BETWEEN 1 AND 50
  29. GROUP BY
  30.     x.item_id
  31.      
  32. item_id |  average
  33.    1         3
  34.    2         3
  35.    3         3
  36.      
  37. item_id |  average
  38.    1         2.5
  39.    2         2.5
  40.    3         2.5
  41.      
  42. id |  item_id  | date       | amount | average
  43. 5       1        2019-01-05     5        3
  44. 10      2        2019-01-05     5        3
  45. 15      3        2019-01-05     5        3
  46.      
  47. CREATE OR REPLACE VIEW v_item_prices AS
  48. SELECT t.*,avg(t.amount) OVER ( PARTITION BY item_id order by date)
  49.   AS average FROM item_prices t
  50.   order by item_id,date
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