Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- +-----+-------------
- | ID | rainfall |
- +-----+----------- |
- | 1 | 110.2 |
- | 2 | 56.6 |
- | 3 | 65.6 |
- | 4 | 75.9 |
- +-----+------------
- SELECT min(id) as first_id, max(id) AS last_id, avg(rainfall) AS avg_this_16
- FROM (
- SELECT id, rainfall, row_number() OVER (order by id) AS n
- FROM the_table
- ) x(id,rainfall,n)
- GROUP BY n/16
- ORDER BY n/16;
- SELECT id, avg(rainfall) OVER (ORDER BY id ROWS 15 PRECEDING)
- FROM the_table;
- SELECT id AS greatest_id_in_group, avg_last_16_inclusive FROM (
- SELECT
- id,
- avg(rainfall) OVER (ORDER BY id ROWS 15 PRECEDING) AS avg_last_16_inclusive,
- row_number() OVER (ORDER BY id) AS n
- FROM the_table
- ) x WHERE n % 16 = 0;
- SELECT avg(rainfall),string_agg(id::text, ',')
- FROM the_table
- GROUP BY (id - 1) / 16;
Add Comment
Please, Sign In to add comment