Pivot on Multiple Columns using Tablefunc
The problem with your query is that b and c share the same timestamp 2012-01-02 00:00:00, and you have the timestamp column timeof first in your query, so - even though you added bold emphasis - b and c are just extra columns that fall in the same group 2012-01-02 00:00:00. Only the first (b) is returned since (quoting the manual):
The
row_namecolumn must be first. Thecategoryandvaluecolumns must be the last two columns, in that order. Any columns betweenrow_nameandcategoryare treated as "extra". The "extra" columns are expected to be the same for all rows with the samerow_namevalue.
Bold emphasis mine.
Just revert the order of the first two columns to make entity the row name and it works as desired:
SELECT * FROM crosstab(
'SELECT entity, timeof, status, ct
FROM t4
ORDER BY 1'
,'VALUES (1), (0)')
AS ct (
"Attribute" character
,"Section" timestamp
,"status_1" int
,"status_0" int);
entity must be unique, of course.
Reiterate
-
row_namefirst - (optional)
extracolumns next -
category(as defined by the second parameter) andvaluelast.
Extra columns are filled from the first row from each row_name partition. Values from other rows are ignored, there is only one column per row_name to fill. Typically those would be the same for every row of one row_name, but that's up to you.
For the different setup in your answer:
SELECT localt, entity
, msrmnt01, msrmnt02, msrmnt03, msrmnt04, msrmnt05 -- , more?
FROM crosstab(
'SELECT dense_rank() OVER (ORDER BY localt, entity)::int AS row_name
, localt, entity -- additional columns
, msrmnt, val
FROM test
-- WHERE ??? -- instead of LIMIT at the end
ORDER BY localt, entity, msrmnt
-- LIMIT ???' -- instead of LIMIT at the end
, $$SELECT generate_series(1,5)$$) -- more?
AS ct (row_name int, localt timestamp, entity int
, msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more?
)
LIMIT 1000 -- ??!!
No wonder the queries in your test perform terribly. Your test setup has 14M rows and you process all of them before throwing most of it away with LIMIT 1000. For a reduced result set add WHERE conditions or a LIMIT to the source query!
Plus, the array you work with is needlessly expensive on top of it. I generate a surrogate row name with dense_rank() instead.
db<>fiddle here - with a simpler test setup and fewer rows.
In my original question I should have used this for my sample data:
CREATE TEMP TABLE t4 (
timeof date
,entity integer
,status integer
,ct integer);
INSERT INTO t4 VALUES
('2012-01-01', 1, 1, 1)
,('2012-01-01', 1, 0, 2)
,('2012-01-01', 3, 0, 3)
,('2012-01-02', 2, 1, 4)
,('2012-01-02', 3, 1, 5)
,('2012-01-02', 3, 0, 6);
With this I have to pivot on both timeof and entity. Since tablefunc only uses one column for the pivoting, you need to find a way to stuff both dimensions in that column. (http://www.postgresonline.com/journal/categories/24-tablefunc). I went with the array, just like the example on in that link.
SELECT (timestamp 'epoch' + row_name[1] * INTERVAL '1 second')::date
as localt,
row_name[2] As entity, status1, status0
FROM crosstab('SELECT ARRAY[extract(epoch from timeof), entity] as row_name,
status, ct
FROM t4
ORDER BY timeof, entity, status'
,$$VALUES (1::text), (0::text)$$)
as ct (row_name integer[], status1 int, status0 int)
FWIW, I tried using a character array and so far it looks like this is faster for my setup; 9.2.3 Postgresql.
This is the result and desired output.
localt | entity | status1 | status0
--------------------------+---------+--------
2012-01-01 | 1 | 1 | 2
2012-01-01 | 3 | | 3
2012-01-02 | 2 | 4 |
2012-01-02 | 3 | 5 | 6
I'm curious how this performs on a much larger data-set and will report back at a later date.
Ok, so I ran this on a table closer to my use case. Either I'm doing it wrong or crosstab is not suitable for my use.
First I made some similar data:
CREATE TABLE public.test (
id serial primary key,
msrmnt integer,
entity integer,
localt timestamp,
val double precision
);
CREATE INDEX ix_test_msrmnt
ON public.test (msrmnt);
CREATE INDEX ix_public_test_201201_entity
ON public.test (entity);
CREATE INDEX ix_public_test_201201_localt
ON public.test (localt);
insert into public.test (msrmnt, entity, localt, val)
select *
from(
SELECT msrmnt, entity, localt, random() as val
FROM generate_series('2012-01-01'::timestamp, '2012-01-01 23:59:00'::timestamp, interval '1 minutes') as localt
join
(select *
FROM generate_series(1, 50, 1) as msrmnt) as msrmnt
on 1=1
join
(select *
FROM generate_series(1, 200, 1) as entity) as entity
on 1=1) as data;
Then I ran the crosstab code a couple times:
explain analyze
SELECT (timestamp 'epoch' + row_name[1] * INTERVAL '1 second')::date As localt, row_name[2] as entity
,msrmnt01,msrmnt02,msrmnt03,msrmnt04,msrmnt05,msrmnt06,msrmnt07,msrmnt08,msrmnt09,msrmnt10
,msrmnt11,msrmnt12,msrmnt13,msrmnt14,msrmnt15,msrmnt16,msrmnt17,msrmnt18,msrmnt19,msrmnt20
,msrmnt21,msrmnt22,msrmnt23,msrmnt24,msrmnt25,msrmnt26,msrmnt27,msrmnt28,msrmnt29,msrmnt30
,msrmnt31,msrmnt32,msrmnt33,msrmnt34,msrmnt35,msrmnt36,msrmnt37,msrmnt38,msrmnt39,msrmnt40
,msrmnt41,msrmnt42,msrmnt43,msrmnt44,msrmnt45,msrmnt46,msrmnt47,msrmnt48,msrmnt49,msrmnt50
FROM crosstab('SELECT ARRAY[extract(epoch from localt), entity] as row_name, msrmnt, val
FROM public.test
ORDER BY localt, entity, msrmnt',$$VALUES ( 1::text),( 2::text),( 3::text),( 4::text),( 5::text),( 6::text),( 7::text),( 8::text),( 9::text),(10::text)
,(11::text),(12::text),(13::text),(14::text),(15::text),(16::text),(17::text),(18::text),(19::text),(20::text)
,(21::text),(22::text),(23::text),(24::text),(25::text),(26::text),(27::text),(28::text),(29::text),(30::text)
,(31::text),(32::text),(33::text),(34::text),(35::text),(36::text),(37::text),(38::text),(39::text),(40::text)
,(41::text),(42::text),(43::text),(44::text),(45::text),(46::text),(47::text),(48::text),(49::text),(50::text)$$)
as ct (row_name integer[],msrmnt01 double precision, msrmnt02 double precision,msrmnt03 double precision, msrmnt04 double precision,msrmnt05 double precision,
msrmnt06 double precision,msrmnt07 double precision, msrmnt08 double precision,msrmnt09 double precision, msrmnt10 double precision
,msrmnt11 double precision, msrmnt12 double precision,msrmnt13 double precision, msrmnt14 double precision,msrmnt15 double precision,
msrmnt16 double precision,msrmnt17 double precision, msrmnt18 double precision,msrmnt19 double precision, msrmnt20 double precision
,msrmnt21 double precision, msrmnt22 double precision,msrmnt23 double precision, msrmnt24 double precision,msrmnt25 double precision,
msrmnt26 double precision,msrmnt27 double precision, msrmnt28 double precision,msrmnt29 double precision, msrmnt30 double precision
,msrmnt31 double precision, msrmnt32 double precision,msrmnt33 double precision, msrmnt34 double precision,msrmnt35 double precision,
msrmnt36 double precision,msrmnt37 double precision, msrmnt38 double precision,msrmnt39 double precision, msrmnt40 double precision
,msrmnt41 double precision, msrmnt42 double precision,msrmnt43 double precision, msrmnt44 double precision,msrmnt45 double precision,
msrmnt46 double precision,msrmnt47 double precision, msrmnt48 double precision,msrmnt49 double precision, msrmnt50 double precision)
limit 1000
Obtaining this on the third try:
QUERY PLAN
Limit (cost=0.00..20.00 rows=1000 width=432) (actual time=110236.673..110237.667 rows=1000 loops=1)
-> Function Scan on crosstab ct (cost=0.00..20.00 rows=1000 width=432) (actual time=110236.672..110237.598 rows=1000 loops=1)
Total runtime: 110699.598 ms
Then I ran the standard solution a couple times:
explain analyze
select localt, entity,
max(case when msrmnt = 1 then val else null end) as msrmnt01
,max(case when msrmnt = 2 then val else null end) as msrmnt02
,max(case when msrmnt = 3 then val else null end) as msrmnt03
,max(case when msrmnt = 4 then val else null end) as msrmnt04
,max(case when msrmnt = 5 then val else null end) as msrmnt05
,max(case when msrmnt = 6 then val else null end) as msrmnt06
,max(case when msrmnt = 7 then val else null end) as msrmnt07
,max(case when msrmnt = 8 then val else null end) as msrmnt08
,max(case when msrmnt = 9 then val else null end) as msrmnt09
,max(case when msrmnt = 10 then val else null end) as msrmnt10
,max(case when msrmnt = 11 then val else null end) as msrmnt11
,max(case when msrmnt = 12 then val else null end) as msrmnt12
,max(case when msrmnt = 13 then val else null end) as msrmnt13
,max(case when msrmnt = 14 then val else null end) as msrmnt14
,max(case when msrmnt = 15 then val else null end) as msrmnt15
,max(case when msrmnt = 16 then val else null end) as msrmnt16
,max(case when msrmnt = 17 then val else null end) as msrmnt17
,max(case when msrmnt = 18 then val else null end) as msrmnt18
,max(case when msrmnt = 19 then val else null end) as msrmnt19
,max(case when msrmnt = 20 then val else null end) as msrmnt20
,max(case when msrmnt = 21 then val else null end) as msrmnt21
,max(case when msrmnt = 22 then val else null end) as msrmnt22
,max(case when msrmnt = 23 then val else null end) as msrmnt23
,max(case when msrmnt = 24 then val else null end) as msrmnt24
,max(case when msrmnt = 25 then val else null end) as msrmnt25
,max(case when msrmnt = 26 then val else null end) as msrmnt26
,max(case when msrmnt = 27 then val else null end) as msrmnt27
,max(case when msrmnt = 28 then val else null end) as msrmnt28
,max(case when msrmnt = 29 then val else null end) as msrmnt29
,max(case when msrmnt = 30 then val else null end) as msrmnt30
,max(case when msrmnt = 31 then val else null end) as msrmnt31
,max(case when msrmnt = 32 then val else null end) as msrmnt32
,max(case when msrmnt = 33 then val else null end) as msrmnt33
,max(case when msrmnt = 34 then val else null end) as msrmnt34
,max(case when msrmnt = 35 then val else null end) as msrmnt35
,max(case when msrmnt = 36 then val else null end) as msrmnt36
,max(case when msrmnt = 37 then val else null end) as msrmnt37
,max(case when msrmnt = 38 then val else null end) as msrmnt38
,max(case when msrmnt = 39 then val else null end) as msrmnt39
,max(case when msrmnt = 40 then val else null end) as msrmnt40
,max(case when msrmnt = 41 then val else null end) as msrmnt41
,max(case when msrmnt = 42 then val else null end) as msrmnt42
,max(case when msrmnt = 43 then val else null end) as msrmnt43
,max(case when msrmnt = 44 then val else null end) as msrmnt44
,max(case when msrmnt = 45 then val else null end) as msrmnt45
,max(case when msrmnt = 46 then val else null end) as msrmnt46
,max(case when msrmnt = 47 then val else null end) as msrmnt47
,max(case when msrmnt = 48 then val else null end) as msrmnt48
,max(case when msrmnt = 49 then val else null end) as msrmnt49
,max(case when msrmnt = 50 then val else null end) as msrmnt50
from sample
group by localt, entity
limit 1000
Obtaining this on the third try:
QUERY PLAN
Limit (cost=2257339.69..2270224.77 rows=1000 width=24) (actual time=19795.984..20090.626 rows=1000 loops=1)
-> GroupAggregate (cost=2257339.69..5968242.35 rows=288000 width=24) (actual time=19795.983..20090.496 rows=1000 loops=1)
-> Sort (cost=2257339.69..2293339.91 rows=14400088 width=24) (actual time=19795.626..19808.820 rows=50001 loops=1)
Sort Key: localt
Sort Method: external merge Disk: 478568kB
-> Seq Scan on sample (cost=0.00..249883.88 rows=14400088 width=24) (actual time=0.013..2245.247 rows=14400000 loops=1)
Total runtime: 20197.565 ms
So, for my case, it appears so far that crosstab is not a solution. And this is just one day when I'll have multiple years. In fact, I will probably have to go with wide format (not normalized) tables, despite the fact that which measurements are made for entities is variable and new ones are introduced, but I won't go into that here.
Here's some of my settings using Postgres 9.2.3:
name setting
max_connections 100
shared_buffers 2097152
effective_cache_size 6291456
maintenance_work_mem 1048576
work_mem 262144