How to reduce result rows of SQL query equally in full range?

Question

I have values related to dates. I need to select data for example for 10 years. Standard query result has approximately 3000 rows, but I need to show them on graph with 400px width and send them over the rest api to mobile phone. So it would be nice to reduce the data to let's say 400 values.

Is there any build in method how to reduce data equally in full range in SQL Server or PostgreSQL? How should I do this?

`limit 400` or other techniques related to [tag:greatest-n-per-group]? — , Apr 29 '16 at 21:51
`where`, `group by`, etc... how you go about filtering the data is up to you, and there's not likely to be a built-in function that does exactly what you want. — Marc B, Apr 29 '16 at 21:51
for SQL Server you could use "SELECT TOP 400 * FROM MyTable" , I guess it depends on the specifics of your requirements. — Ricardo C, Apr 29 '16 at 21:53
Is this question really need answers in two rdbms? Becase the exact answer may differ in PostgreSQL and in SQL server — pozs, Apr 29 '16 at 21:55
No, hint for one engine is enough. I expect the principle would be the same. — Fanda, Apr 29 '16 at 22:00

score 4 · Answer 1 · edited May 23 '17 at 11:45

In Postgres, width_bucket() is just the thing you are looking for - to granulate any number of rows (N) in the underlying table into a given (preferably smaller) number of data points (n). You might add the count of rows contributing to each data point to signify weight.

One minor obstacle: The variant of width_bucket() we need operates on double precision or numeric numbers, not on timestamp et al. Just extract the epoch to work with.

Assuming this table definition and a current Postgres version:

CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY
, value  numeric NOT NULL
, created_at timestamptz NOT NULL
);

Query:

SELECT width_bucket(extract(epoch FROM t.created_at), x.min_epoch, x.max_epoch, 400) AS pix
     , round(avg(t.value), 2) AS avg  -- round is optional
     , count(*) AS weight
FROM   big t
CROSS  JOIN (SELECT extract(epoch FROM min(created_at)) AS min_epoch
                  , extract(epoch FROM max(created_at)) AS max_epoch FROM big) x
GROUP  BY 1
ORDER  BY 1;

Result:

pix | avg    | weight
----+--------+------
1   | 152.58 | 7
2   | 155.16 | 8
3   | 148.89 | 7
...

Returns 400 rows - unless N < n, in which case you get N rows.

How to reduce result rows of SQL query equally in full range?

1 Answers1

Linked