当聚合来自多个内部查询的数据时，如何避免冗长和简化?

Question

假设您有三个表(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;

有什么方法可以简化这样的查询吗?created_on过滤器日期可以声明为变量吗?

Answer 1

PL/pgSQL支持函数内的变量，因此如果需要，可以将其作为函数，然后查询函数结果。比如:

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;

你会查询它:

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

当然这可能不是你想要的。如果在"只是查询"时需要变量，则取决于所使用的客户机。如果您使用的是psql，它确实支持变量。其他客户端可能支持也可能不支持，这取决于。

您还可以稍微修改一下查询，像这样，在开头使用参数CTE，然后使用它来连接选定的参数:

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

下面是一个使用

的dbfiddle演示p。您的查询使用内连接，因此将只包括喜欢和评论的用户。如果用户没有评论，或者不喜欢任何内容，他们将被排除，因为没有记录将从连接查询返回。