CODEWITHSUNDEEP
sqlpostgresqlsummarize
Michael Milewski
Michael Milewski

Reputation: 156

PostgreSQL summarize features query

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?

Upvotes: 0

Views: 75

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

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;

Upvotes: 2

Related Questions