Reputation: 627
Postgresql 9.3: How to use crosstab with multiple indexes?
Here is a sqlFiddle that shows what I'm trying to do.
Here is @lad2025 sqlFiddle that shows it better
I have two indexes on my table plus a column with the column names and a column with the values.
In the fiddle I show a query that does what I want to do. But it is very slow.
I have a crosstab request that does almost the same thing, very fast, but with few errors. (it will fuse some lines)
SELECT
end_user_id,
tms,
coalesce(max(IN_VEHICLE), 0) as IN_VEHICLE,
coalesce(max(ON_BICYCLE), 0) as ON_BICYCLE,
coalesce(max(ON_FOOT), 0) as ON_FOOT,
coalesce(max(RUNNING), 0) as RUNNING,
coalesce(max(STILL), 0) as STILL,
coalesce(max(TILTING), 0) as TILTING,
coalesce(max(UNKNOWN), 0) as UNKNOWN,
coalesce(max(WALKING), 0) as WALKING
FROM
crosstab (
'SELECT end_user_id, tms, type, max(confidence) FROM activities group by 1,2,3 ',
'SELECT DISTINCT type FROM activities order by type'
)as newtable (
end_user_id text,
tms timestamp,
IN_VEHICLE float,
ON_BICYCLE float,
ON_FOOT float,
RUNNING float,
STILL float,
TILTING float,
UNKNOWN float,
WALKING float
)
GROUP BY end_user_id, tms
ORDER BY end_user_id, tms
I don't know why postgres asks me to GROUP BY end_user_id, tms at the end... It is supposed to be unique.
Also I don't know why, but if I don't group by in the crosstab query, I'll only have one row per end_user_id :(
How can I correct that crosstab request ?
EDIT: @lad2025 response is a better example than mine, more elegant and I'm sure faster. Still, I want to know how to do it with a crosstab.
Upvotes: 4
Views: 1365
Answers (1)
Reputation: 175616
You could avoid crosstab/multiple left join as in your Fiddle and use simple conditional aggregation:
SELECT
end_user_id,
tms,
COALESCE(MAX(CASE WHEN type = 'IN_VEHICLE' THEN confidence END),0) AS IN_VEHICLE,
COALESCE(MAX(CASE WHEN type = 'ON_BICYCLE' THEN confidence END),0) AS ON_BICYCLE,
COALESCE(MAX(CASE WHEN type = 'ON_FOOT' THEN confidence END),0) AS ON_FOOT,
COALESCE(MAX(CASE WHEN type = 'RUNNING' THEN confidence END),0) AS RUNNING,
COALESCE(MAX(CASE WHEN type = 'STILL' THEN confidence END),0) AS STILL,
COALESCE(MAX(CASE WHEN type = 'TILTING' THEN confidence END),0) AS TILTING,
COALESCE(MAX(CASE WHEN type = 'UNKNOWN' THEN confidence END),0) AS UNKNOWN,
COALESCE(MAX(CASE WHEN type = 'WALKING' THEN confidence END),0) AS WALKING
FROM activities
GROUP BY end_user_id, tms
ORDER BY end_user_id, tms;
Output:
╔═══════════════════╦════════════════════════════╦═════════════╦═════════════╦══════════╦══════════╦════════╦══════════╦══════════╦═════════╗
║ end_user_id ║ tms ║ in_vehicle ║ on_bicycle ║ on_foot ║ running ║ still ║ tilting ║ unknown ║ walking ║
╠═══════════════════╬════════════════════════════╬═════════════╬═════════════╬══════════╬══════════╬════════╬══════════╬══════════╬═════════╣
║ 64e8394876a5b7f1 ║ October, 28 2015 08:24:20 ║ 21 ║ 8 ║ 2 ║ 0 ║ 2 ║ 0 ║ 68 ║ 2 ║
║ 64e8394876a5b7f1 ║ October, 28 2015 08:24:41 ║ 15 ║ 0 ║ 3 ║ 0 ║ 72 ║ 0 ║ 10 ║ 3 ║
║ 64e8394876a5b7f1 ║ October, 28 2015 08:25:17 ║ 5 ║ 0 ║ 5 ║ 0 ║ 77 ║ 100 ║ 13 ║ 5 ║
║ 64e8394876a5b7f1 ║ October, 28 2015 08:25:32 ║ 0 ║ 0 ║ 0 ║ 0 ║ 100 ║ 0 ║ 0 ║ 0 ║
║ 64e8394876a5b7f1 ║ October, 28 2015 08:25:36 ║ 0 ║ 0 ║ 0 ║ 0 ║ 92 ║ 0 ║ 8 ║ 0 ║
║ 64e8394876a5b7f1 ║ October, 28 2015 08:27:24 ║ 48 ║ 48 ║ 0 ║ 0 ║ 0 ║ 0 ║ 5 ║ 0 ║
║ 64e8394876a5b7f1 ║ October, 28 2015 08:27:54 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║ 100 ║ 0 ║ 0 ║
║ 64e8394876a5b7f1 ║ October, 28 2015 08:28:11 ║ 62 ║ 8 ║ 3 ║ 0 ║ 15 ║ 0 ║ 13 ║ 3 ║
║ 64e8394876a5b7f1 ║ October, 28 2015 08:28:53 ║ 35 ║ 0 ║ 6 ║ 0 ║ 37 ║ 0 ║ 23 ║ 6 ║
║ 64e8394876a5b7f1 ║ October, 28 2015 08:29:16 ║ 54 ║ 2 ║ 0 ║ 0 ║ 10 ║ 0 ║ 35 ║ 0 ║
║ e86b0b91546194cc ║ October, 28 2015 08:24:41 ║ 13 ║ 13 ║ 69 ║ 3 ║ 0 ║ 100 ║ 5 ║ 67 ║
║ e86b0b91546194cc ║ October, 28 2015 08:33:33 ║ 0 ║ 0 ║ 100 ║ 0 ║ 0 ║ 0 ║ 0 ║ 100 ║
║ e86b0b91546194cc ║ October, 28 2015 08:33:38 ║ 0 ║ 0 ║ 100 ║ 0 ║ 0 ║ 0 ║ 0 ║ 100 ║
║ e86b0b91546194cc ║ October, 28 2015 08:34:06 ║ 19 ║ 6 ║ 31 ║ 2 ║ 29 ║ 0 ║ 16 ║ 29 ║
║ e86b0b91546194cc ║ October, 28 2015 08:34:34 ║ 3 ║ 0 ║ 0 ║ 0 ║ 95 ║ 0 ║ 3 ║ 0 ║
╚═══════════════════╩════════════════════════════╩═════════════╩═════════════╩══════════╩══════════╩════════╩══════════╩══════════╩═════════╝
COALESCE is also redundant(if only positive/zero values are allowed):
SELECT
end_user_id,
tms,
MAX(CASE WHEN type = 'IN_VEHICLE' THEN confidence ELSE 0 END) AS IN_VEHICLE,
MAX(CASE WHEN type = 'ON_BICYCLE' THEN confidence ELSE 0 END) AS ON_BICYCLE,
MAX(CASE WHEN type = 'ON_FOOT' THEN confidence ELSE 0 END) AS ON_FOOT,
MAX(CASE WHEN type = 'RUNNING' THEN confidence ELSE 0 END) AS RUNNING,
MAX(CASE WHEN type = 'STILL' THEN confidence ELSE 0 END) AS STILL,
MAX(CASE WHEN type = 'TILTING' THEN confidence ELSE 0 END) AS TILTING,
MAX(CASE WHEN type = 'UNKNOWN' THEN confidence ELSE 0 END) AS UNKNOWN,
MAX(CASE WHEN type = 'WALKING' THEN confidence ELSE 0 END) AS WALKING
FROM activities
GROUP BY end_user_id, tms
ORDER BY end_user_id, tms;
You could also consider making lookup table for type column like activities_type (type_id, type_name) instead of storing directly in table string ('IN_VEHICLE', 'ON_BICYCLE', ...).
Addendum
I am not Postgresql Expert but after some playing:
SELECT
LEFT(end_user_id, strpos(end_user_id, '_')-1) AS end_user_id,
RIGHT(end_user_id, LENGTH(end_user_id) - strpos(end_user_id, '_'))::timestamp AS tms,
COALESCE(IN_VEHICLE,0) AS IN_VEHICLE,
COALESCE(ON_BICYCLE,0) AS ON_BICYCLE,
COALESCE(ON_FOOT,0) AS ON_FOOT,
COALESCE(RUNNING,0) AS RUNNING,
COALESCE(STILL,0) AS STILL,
COALESCE(TILTING,0) AS TILTING,
COALESCE("UNKNOWN",0) AS "UNKNOWN",
COALESCE(WALKING,0) AS WALKING
FROM crosstab(
'SELECT (end_user_id || ''_'' || tms) AS row_id, type, confidence
FROM activities
ORDER BY row_id, type, confidence',
'SELECT DISTINCT type FROM activities order by type'
) AS newtable (
end_user_id text,
IN_VEHICLE int,
ON_BICYCLE int,
ON_FOOT int,
RUNNING int,
STILL int,
TILTING int,
"UNKNOWN" int,
WALKING int)
ORDER BY end_user_id, tms;
Why concatenate and split end_user_id + tms?
Because crosstab(text,text) needs:
row_id <=> end_user_id + tms
category <=> type
value <=> confidence
Please note that there is no GROUP BY in this version.
Addendum2 - Final version
Based on tablefunc module doc F.37.1.4. crosstab(text, text):
This is much better because it can handle row_id, extra_col1, extra_col2, category, value). So now:
row_id <=> id
extra_col1 <=> end_user_id
extra_col2 <=> tms
...
And final query:
SELECT
end_user_id,
tms,
coalesce(max(IN_VEHICLE), 0) as IN_VEHICLE,
coalesce(max(ON_BICYCLE), 0) as ON_BICYCLE,
coalesce(max(ON_FOOT), 0) as ON_FOOT,
coalesce(max(RUNNING), 0) as RUNNING,
coalesce(max(STILL), 0) as STILL,
coalesce(max(TILTING), 0) as TILTING,
coalesce(max("UNKNOWN"), 0) as "UNKNOWN",
coalesce(max(WALKING), 0) as WALKING
FROM crosstab(
'SELECT id,end_user_id , tms, type, confidence
FROM activities',
'SELECT DISTINCT type FROM activities order by type'
) AS newtable (
id INT,
end_user_id text,
tms timestamp,
IN_VEHICLE int,
ON_BICYCLE int,
ON_FOOT int,
RUNNING int,
STILL int,
TILTING int,
"UNKNOWN" int,
WALKING int
)
GROUP BY end_user_id, tms
ORDER BY end_user_id, tms;
What would be the point of the activities_type table ?
Database normalization and you can use:
SELECT DISTINCT type FROM activities order by type
vs
SELECT type_name FROM activities_types ORDER BY type_name;
This version uses id as row_id so it still needs GROUP BY to squash multiple rows.
To sum up: conditional aggregation is most readable solution.
Upvotes: 3
Related Questions
- postgresql crosstab simple example
- Crosstab in Postgres for several fields
- crosstab in PostgreSQL
- Postgresql crosstab query with multiple "row name" columns
- PostgreSQL Crosstab
- Postgres CrossTab for query with variable number of columns
- PostgreSQL Crosstab - variable number of columns
- Postgres crosstab query
- PostgreSQL 9.3: Crosstab query
- Crosstab using PostgreSQL