daily pastebin goal
35%
SHARE
TWEET

Untitled

a guest Feb 13th, 2018 140 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. +-----+-------------
  2. | ID  |  rainfall  |
  3. +-----+----------- |
  4. |  1  |  110.2     |
  5. |  2  |  56.6      |
  6. |  3  |  65.6      |
  7. |  4  |  75.9      |
  8. +-----+------------
  9.    
  10. SELECT min(id) as first_id, max(id) AS last_id, avg(rainfall) AS avg_this_16
  11. FROM (
  12.   SELECT id, rainfall, row_number() OVER (order by id) AS n
  13.   FROM the_table
  14. ) x(id,rainfall,n)
  15. GROUP BY n/16
  16. ORDER BY n/16;
  17.    
  18. SELECT id, avg(rainfall) OVER (ORDER BY id ROWS 15 PRECEDING)
  19. FROM the_table;
  20.    
  21. SELECT id AS greatest_id_in_group, avg_last_16_inclusive FROM (
  22.   SELECT
  23.     id,
  24.     avg(rainfall) OVER (ORDER BY id ROWS 15 PRECEDING) AS avg_last_16_inclusive,
  25.     row_number() OVER (ORDER BY id) AS n
  26.   FROM the_table
  27. ) x WHERE n % 16 = 0;
  28.    
  29. SELECT avg(rainfall),string_agg(id::text, ',')
  30. FROM the_table
  31. GROUP BY (id - 1) / 16;
RAW Paste Data
Top