如何展平PostgreSQL结果 [英] How to flatten a PostgreSQL result

问题描述

我有实验，功能和feature_values.要素在不同的实验中具有价值.所以我有类似的东西:

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

可以说，我有三个实验(exp1，exp2，exp3)和三个功能(feat1，feat2，feat3).我想要一个看起来像这样的SQL结果:

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

SQL查询应该具有良好的性能.将来，feature_values表中可能有数千万个条目.还是有更好的方法来处理数据?

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?

推荐答案

我在这里假设 feature_id，experiment_id 是 Feature_values 的唯一键.

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

执行此操作的标准SQL方法是进行n次连接

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

或者像这样的数据透视(聚合 max 实际上没有聚合任何东西):

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小提琴演示

您还可以考虑使用 json (在9.3版中)或 hstore 将所有实验值汇总到一栏中.

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

这篇关于如何展平PostgreSQL结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！