When aggregating data from multiple inner queries, how to avoid the verbosity and simplify?-CodePudding

Provided you have three tables (user, comment, like)

SELECT *
FROM user u
JOIN (SELECT user_id, count(*) as likes
    FROM likes l
    WHERE l.created_on > '2021-10-01' AND l.created_on <= '2021-10-31'
    GROUP BY l.user_id
) as likes ON u.id = likes.user_id
JOIN (
    SELECT user_id, count(*) as comments
    FROM comment c
    WHERE c.created_on > '2021-10-01' AND c.created_on <= '2021-10-31'
) as comments ON u.id = comments.user_id;

Is there any way to simplify a query like this? Could the created_on filter dates be declared as variables?

CodePudding user response：

PL/pgSQL supports variables inside functions, so you could make this a function if you wanted and then query the function results. Say something like:

CREATE FUNCTION get_user_interaction_counts
(
   date_start date,
   date_end date
)
RETURNS TABLE(user_id int, like_count int, comment_count int) as $$
SELECT u.id, likes.likes, comments.comments
FROM "user" u
JOIN (SELECT user_id, count(*) as likes
    FROM likes l
    WHERE l.created_on > date_start AND l.created_on <= date_end
    GROUP BY l.user_id
) as likes ON u.id = likes.user_id
JOIN (
    SELECT user_id, count(*) as comments
    FROM comment c
    WHERE c.created_on > date_start AND c.created_on <= date_end
    GROUP BY c.user_id
) as comments ON u.id = comments.user_id

$$ LANGUAGE SQL;

You'd then query it:

select * from get_user_interaction_counts('2021-10-01', '2021-10-30')

Of course that's probably not what you're after. If you want variables when "just querying", it'll depend on the client you're using. If you're using psql, it does support variables. Other clients may or may not support them, depends.

What you can also do is to rework the query a little bit, like so, with a parameters CTE at the beginning and then use it to join the selected params:

WITH parameters AS (
  SELECT '2021-10-01'::date as date_start
       , '2021-10-30'::date as date_end
)
SELECT u.id, likes.likes, comments.comments
FROM "user" u
JOIN (SELECT user_id, count(*) as likes
    FROM likes l
    JOIN parameters par
      ON l.created_on > par.date_start AND l.created_on <= par.date_end
    GROUP BY l.user_id
) as likes ON u.id = likes.user_id
JOIN (
    SELECT user_id, count(*) as comments
    FROM comment c
    JOIN parameters par
      ON c.created_on > par.date_start AND c.created_on <= par.date_end
    GROUP BY c.user_id
) as comments ON u.id = comments.user_id

Here's a dbfiddle demo with both

PS. Your queries use inner join so will only include users who both liked and commented. If a user didn't comment, or didn't like anything, they will be excluded because no records will be returned from the joined query.