CODEWITHSUNDEEP
postgresql
Fomalhaut
Fomalhaut

Reputation: 9727

How do I aggregate a table by multiple values as columns provided as values in a related table?

I have a table of users like this:

Table user

id | name  | date
1  | alice | 2021-03-28
2  | bob   | 2021-03-29
...

And a table with their contacts:

Table contact

id | user_id | contact  | has_profile
1  | 1       | facebook | 1
2  | 1       | gmail    | 1
3  | 1       | yahoo    | 0
4  | 2       | facebook | 0
5  | 2       | gmail    | 1
6  | 2       | yahoo    | 1
...

I want to write a query that aggregates average rates of the contacts in different dates, so that the result should be:

date       | facebook | gmail | yahoo
2021-03-29 | 0.7      | 0.82  | 0.15
2021-03-28 | 0.75     | 0.85  | 0.18
...

I could reach it by the query:

select
    u."date",
    avg(f.has_profile) as facebook,
    avg(g.has_profile) as gmail,
    avg(y.has_profile) as yahoo
from user u
join contact f on f.user_id = u.id and f.contact = 'facebook'
join contact g on g.user_id = u.id and g.contact = 'gmail'
join contact y on y.user_id = u.id and y.contact = 'yahoo'
group by u."date"
order by u."date" desc

But the point is that this query depends on the certain contact names in the table contact, so if there are too many ones, the query is too long and complicated to modify. Is there a way to say to Postgresql to extract the names of the contacts automatically and to aggregate over them?

Upvotes: 0

Views: 41

Answers (1)

S-Man
S-Man

Reputation: 23666

(JSON aggregate as requested in the comments)

demo:db<>fiddle

SELECT
    my_date,
    jsonb_object_agg(contact, avg)         -- 3
FROM (
    SELECT
        my_date,
        contact,
        AVG(has_profile)                   -- 2
    FROM
        contact c
    JOIN users u ON c.user_id = u.id       -- 1
    GROUP BY my_date, contact
) s
GROUP BY my_date
  1. Join tables
  2. Calculate average values by date and contact
  3. Aggregate these values into a JSON object

Unrelated note: date and user are unrecommended names for database entities because they are reserved keywords. You have to handle them carefully (always use " characters). It's better to rename these properly. Moreover, date is not very descriptive. Maybe login_date or something

Upvotes: 1

Related Questions