PostgreSQL summarize features query

Question

I have a table of events for a user with many feature flags

CREATE TABLE features (
  user_id bigint
  , value text
  , feature_1 boolean
  , feature_2 boolean
  , feature_3 boolean
);

populating this

INSERT INTO features
VALUES
  (1, 'value a', true, false, false)
  , (1, 'value b', false, false, true)
  , (1, 'value c', false, true, false)
  , (2, 'value X', false, true, false);

give us

select * from features;
 user_id |  value  | feature_1 | feature_2 | feature_3
---------+---------+-----------+-----------+-----------
       1 | value a | t         | f         | f
       1 | value b | f         | f         | t
       1 | value c | f         | t         | f
       2 | value X | f         | t         | f
(4 rows)

Now I want to transpose this data on features to make it look something like the following

 user_id | feature_1_value | feature_2_value | feature_3_value
---------+-----------------+-----------------+-----------------
       2 |                 | value X         |
       1 | value a         | value c         | value b
(2 rows)

and at the moment I am doing this with the following

WITH feature_1 AS (
  SELECT user_id, value FROM features where feature_1
), feature_2 AS (
  SELECT user_id, value FROM features where feature_2
), feature_3 AS (
  SELECT user_id, value FROM features where feature_3
) SELECT
  features.user_id
  , feature_1.value AS feature_1_value
  , feature_2.value AS feature_2_value
  , feature_3.value AS feature_3_value
FROM
  features
LEFT JOIN feature_1 ON features.user_id = feature_1.user_id
LEFT JOIN feature_2 ON features.user_id = feature_2.user_id
LEFT JOIN feature_3 On features.user_id = feature_3.user_id
GROUP BY features.user_id, feature_1_value, feature_2_value, feature_3_value;

this is starting to get unwieldily as more and more features get added. Is there a simpler way? is there some kind of crosstab query that can be used? some transpose function?

What if more than one feature is true on a row? If not, why not just store the feature name? — Gordon Linoff
– Gordon Linoff, Commented Jun 24, 2021 at 2:52

Tim Biegeleisen · Accepted Answer · 2021-06-24 02:21:52Z

2

You want a pivot query here:

SELECT
    user_id,
    MAX(value) FILTER (WHERE feature_1) AS feature_1_value,
    MAX(value) FILTER (WHERE feature_2) AS feature_2_value,
    MAX(value) FILTER (WHERE feature_3) AS feature_3_value
FROM features
GROUP BY
    user_id;

answered Jun 24, 2021 at 2:21

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL summarize features query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related