SUM & GROUP BY on composite type array
I have a column with an array of composite type (text, decimal, timestamp)
as data type. I want to create a query to sum the sum of a double column of a composite type. Also I want to execute a group by date (day-month-year) date.
Can anyone show me an example explaining how to do this?
Definition of table and type:
create type stage as (
Stage_Name text,
Stage_Distance decimal,
Stage_Start_Time timestamp
);
CREATE TABLE "Event" (
"Id" serial NOT NULL,
"Location" text,
"Date_Range" daterange,
"Surface" text,
"Stage_Information" stage[],
CONSTRAINT "PK_Event" PRIMARY KEY ("Id")
);
Sample data
{"(Newtownards,1.5,\"2015-04-03 18:28:00\")"
,"(\"Bulls Brook\",13.4,\"2015-04-04 09:04:00\")"}
Expected results:
Sum (1.5 + 13.4) = 14.9
Group by 2015-04-03, 2015-04-04
source to share
Assuming the current Postgres version is 9.4 due to lack of information.
Proper design
First, let's look at database normalization . An additional table instead of a column is "Stage_Information"
usually an excellent solution:
CREATE TABLE stage (
stage_id serial PRIMARY KEY
, event_id int NOT NULL REFERENCES event
, name text -- possibly NOT NULL
, distance numeric -- possibly NOT NULL
, starttime timestamp -- possibly NOT NULL
);
It also doesn't take up much more disk space, and the array redundancy is similar to table overhead. Only the additional index requires more space. But many queries on the base table will be faster, updates will be much cheaper, and things will be cleaner and easier.
Don't mix quoted and disordered capitalization with your IDs. It is very error prone. Use unquoted, legal lowercase names if you can.
Then the request will look like this:
SELECT e.id, s.starttime::date AS day
, sum(s.distance) AS sum_distance
FROM "Event" e
LEFT JOIN stage s ON s.event_id = e.id
WHERE e.id = 1
GROUP BY 1, 2;
Solution to the problem.
As long as you stick to your current design, you need the unnest()
array to apply aggregated functions to its elements. Then you need to decompose the composite values. Use LATERAL
join:
SELECT e.id, (s.st).stage_start_time::date AS day
, sum((s.st).stage_distance) AS sum_distance
FROM "Event" e
LEFT JOIN LATERAL unnest(e."Stage_Information") s(st) ON true
WHERE e.id = 1
GROUP BY 1, 2;
Note the parentheses around (s.st)
(column alias for unset column). You need these elements to access elements of composite type (string type) .
Why LEFT JOIN LATERAL ... ON true
?
source to share