How to flatten a PostgreSQL result

Question

I have experiments, features, and feature_values. Features have values in different experiments. So I have something like:

Experiments:
experiment_id, experiment_name

Features:
feature_id, feature_name

Feature_values:
experiment_id, feature_id, value

Lets say, I have three experiments (exp1, exp2, exp3) and three features (feat1, feat2, feat3). I would like to have a SQL-result that looks like:

feature_name | exp1 | exp2 | exp3
-------------+------+------+-----
feat1        | 100  | 150  | 110
feat2        | 200  | 250  | 210
feat3        | 300  | 350  | 310

How can I do this? Furthermore, It might be possible that one feature does not have a value in one experiment.

feature_name | exp1 | exp2 | exp3
-------------+------+------+-----
feat1        | 100  | 150  | 110
feat2        | 200  |      | 210
feat3        |      | 350  | 310

The SQL-Query should be with good performance. In the future there might tens of millions entries in the feature_values table. Or is there a better way to handle the data?

@7alhashmi: Yes, e.g. the 100 comes from the feature_values table — Bjoernsen, Aug 27 '13 at 11:16

score 5 · Answer 1 · answered Aug 27 '13 at 11:28

5

This is a common request. It's called a pivot or crosstab query. PostgreSQL doesn't have any nice built-in syntax for it, but you can use the crosstab function from the tablefunc module to do what you want.

For more information search Stack Overflow for [postgresql] [pivot] or [postgresql] [crosstab].

Some relational database systems offer a nice way to do this with a built-in query, but as yet PostgreSQL does not.

answered Aug 27 '13 at 11:28

Craig Ringer

307,061
76
688
778

+1 for link to crosstab, have to read about it. I suppose it's just function doing n joins? – Roman Pekar Aug 27 '13 at 11:30
This sounds very promising. Thank you. – Bjoernsen Aug 27 '13 at 12:33
@RomanPekar Pretty much; it's a tool to dynamically build the desired SQL. It's rather limited by the fact that PostgreSQL can't handle result sets of unknown type properly, though. – Craig Ringer Aug 27 '13 at 23:54

Roman Pekar · Accepted Answer · 2013-08-27T11:44:18.070

I'm supposing here that feature_id, experiment_id is unique key for Feature_values.

Standard SQL way to do this is to make n joins

select
    F.feature_name,
    FV1.value as exp1,
    FV2.value as exp2,
    FV3.value as exp3
from Features as F
    left outer join Feature_values as FV1 on FV1.feature_id = F.feature_id and FV1.experiment_id = 1
    left outer join Feature_values as FV2 on FV2.feature_id = F.feature_id and FV2.experiment_id = 2
    left outer join Feature_values as FV3 on FV3.feature_id = F.feature_id and FV3.experiment_id = 3

Or pivot data like this (aggregate max is not actually aggregating anything):

select
    F.feature_name,
    max(case when E.experiment_name = 'exp1' then FV.value end) as exp1,
    max(case when E.experiment_name = 'exp2' then FV.value end) as exp2,
    max(case when E.experiment_name = 'exp3' then FV.value end) as exp3
from Features as F
    left outer join Feature_values as FV on FV.feature_id = F.feature_id
    left outer join Experiments as E on E.experiment_id = FV.experiment_id
group by F.feature_name
order by F.feature_name

sql fiddle demo

you can also consider using json (in 9.3 version) or hstore to get all experiment values into one column.

I can not use hstore, because the selected experiments and features change based on the users request. It is also possible to filter for values > e.g. 300. But your answer is great :-) — Bjoernsen, Aug 27 '13 at 11:57
if you want to filter out values - just add condition into join with Feature_Values in the second solution, like `left outer join Feature_values as FV on FV.feature_id = F.feature_id and FV.value > 300` — Roman Pekar, Aug 27 '13 at 12:01

score 2 · Answer 3 · answered Aug 27 '13 at 11:20

What you're attempting is a bit difficult since you are trying to present a set of tables as a single table and obviously, this involves some transformation and some assumptions.

Assuming that you know in advance that there are only 3 experiments and only three features, you could do something like the following

SELECT
    feature_id,
    SUM(CASE WHEN experiment_id = 1 THEN value ELSE 0 END) AS Exp1Total,
    SUM(CASE WHEN experiment_id = 2 THEN value ELSE 0 END) AS Exp2Total,
    SUM(CASE WHEN experiment_id = 3 THEN value ELSE 0 END) AS Exp3Total,
FROM
    Feature_values
GROUP BY
    feature_id
ORDER BY
    feature_id

In this case, your table will contain the ID of the experiments and the features rather than their names. To get their names, you will need to join on the Features table and also, on the Experiments table. I've omitted this for clarity since I think the most difficult part is the case logic.

Thank you. One thing, if the value is missing, it is not 0. 0 would also be a value. I changed it to `null`. — Bjoernsen, Aug 27 '13 at 12:00
You're welcome but using null might cause you a problem since SUM(1, null) == null. — PhilDin, Aug 27 '13 at 14:12

How to flatten a PostgreSQL result

3 Answers3

Linked