Misa Simic <misa.simic@xxxxxxxxx> wrote:
> So I wonder - is there some kind of aggregate window function
> what does desired results?
Not built in, but PostgreSQL makes it pretty easy to do so. With a
little effort to define your own aggregate function, your query can
look like this:
SELECT
thing_id,
category,
int4range_list(period_id)
FROM thing
GROUP BY thing_id, category
ORDER BY 1, 2;
I've attached a couple files -- one which creates the desired
aggregate function, and the other loads a table with two of your
sample data sets and runs the above. This is just intended as a
quick example of the capabilities available to you.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
CREATE OR REPLACE FUNCTION int4range_list_accum(rangelist_in int4range[], newvalue int)
RETURNS int4range[]
LANGUAGE plpgsql
STRICT
IMMUTABLE
AS $$
DECLARE
range int4range;
index int;
x boolean;
BEGIN
index = 0;
FOREACH range IN ARRAY rangelist_in LOOP
index := index + 1;
IF newvalue <@ range THEN
RETURN rangelist_in;
END IF;
IF newvalue = (lower(range) - 1) THEN
-- extend range lower
RETURN rangelist_in[1:index - 1]
|| int4range(newvalue, upper(range))
|| rangelist_in[index + 1:array_upper(rangelist_in, 1)];
END IF;
IF newvalue < (lower(range)) THEN
-- add range before
RETURN rangelist_in[1:index - 1]
|| int4range(newvalue, newvalue + 1)
|| rangelist_in[index:array_upper(rangelist_in, 1)];
END IF;
IF newvalue = (upper(range)) THEN
x := CASE
WHEN array_upper(rangelist_in, 1) <= index THEN FALSE
WHEN newvalue < lower(rangelist_in[index + 1]) - 1 THEN FALSE
ELSE TRUE
END;
IF x THEN
-- combine adjacent ranges
RETURN rangelist_in[1:index - 1]
|| int4range(lower(range), upper(rangelist_in[index + 1]))
|| rangelist_in[index + 2:array_upper(rangelist_in, 1)];
ELSE
-- extend range higher
RETURN rangelist_in[1:index - 1]
|| int4range(lower(range), newvalue + 1)
|| rangelist_in[index + 1:array_upper(rangelist_in, 1)];
END IF;
END IF;
END LOOP;
-- add range at end
RETURN rangelist_in || int4range(newvalue, newvalue + 1);
END;
$$;
CREATE OR REPLACE FUNCTION int4range_list_final(rangelist int4range[])
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
range int4range;
result text = '';
separator text = '';
BEGIN
IF coalesce(array_upper(rangelist, 1), 0) = 0 THEN
RETURN NULL;
END IF;
FOREACH range IN ARRAY rangelist LOOP
result = result
|| separator
|| lower(range)::text
|| CASE
WHEN upper(range) = lower(range) + 1 THEN ''::text
ELSE '-'::text || (upper(range) - 1)::text
END;
separator := ', ';
END LOOP;
RETURN result;
END;
$$;
DROP AGGREGATE IF EXISTS int4range_list(int);
CREATE AGGREGATE int4range_list(int)
(
SFUNC = int4range_list_accum,
STYPE = int4range[],
FINALFUNC = int4range_list_final,
INITCOND = '{}'
);
DROP TABLE IF EXISTS thing;
CREATE TABLE thing
(
id int NOT NULL,
thing_id int NOT NULL,
category varchar(1) NOT NULL,
period_id int NOT NULL
);
COPY thing FROM STDIN;
1 1 A 1
2 1 A 2
3 1 A 3
4 1 A 4
5 1 B 5
6 1 B 6
7 1 B 7
8 1 A 8
9 1 A 9
10 2 A 1
11 2 A 2
12 2 A 3
13 2 A 4
\.
SELECT
thing_id,
category,
int4range_list(period_id)
FROM thing
GROUP BY thing_id, category
ORDER BY 1, 2;
TRUNCATE TABLE thing;
COPY thing FROM STDIN;
1 1 A 1
2 1 A 2
3 1 A 3
4 1 A 7
5 1 A 8
6 1 A 9
7 2 A 1
8 2 A 2
9 2 A 3
10 2 A 4
\.
SELECT
thing_id,
category,
int4range_list(period_id)
FROM thing
GROUP BY thing_id, category
ORDER BY 1, 2;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
