Postgres:避免列出所有列("必须出现在GROUP BY子句中或在聚合函数中使用")

Question

查询成功:

SELECT
COUNT(video_views.user_id) AS view_count,
video_feed_unscored.*
FROM video_feed_unscored
LEFT JOIN video_user_interaction video_views ON (video_views.video_id = video_feed_unscored.id)
WHERE video_views.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
GROUP BY
video_feed_unscored.id,
video_feed_unscored.title,
video_feed_unscored.username,
video_feed_unscored.user_id,
video_feed_unscored.video_rating_id,
video_feed_unscored.mux_asset_id,
video_feed_unscored.mux_playback_id,
video_feed_unscored.days_old,
video_feed_unscored.view_start_count,
video_feed_unscored.view_5seconds_count,
video_feed_unscored.like_count
;

，但它非常明确，我希望它简化为:

SELECT
COUNT(video_views.user_id) AS view_count,
video_feed_unscored.*
FROM video_feed_unscored
LEFT JOIN video_user_interaction video_views ON (video_views.video_id = video_feed_unscored.id)
WHERE video_views.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
GROUP BY
video_feed_unscored.id
;

但是这给出了错误column "video_feed_unscored.title" must appear in the GROUP BY clause or be used in an aggregate function。

还有其他方法可以简化查询吗?

Answer 1

可能不是simplify，但假设有很多提要，用户只看到其中的几个，下面的查询应该有更好的执行计划(值得检查)。

另外，选择列的任何范围的列不再是一个问题

SELECT
view_count,
video_feed_unscored.*
FROM video_feed_unscored
LEFT JOIN (
select 
count(*) view_count, 
b.video_id 
from video_user_interaction b
where 
b.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
group by b.video_id
) c on (c.video_id = video_feed_unscored.id)

并且总是有地方用于子查询(如果执行计划足够好)

SELECT
(SELECT 
COUNT(video_views.user_id)
FROM video_user_interaction video_views
WHERE 
video_views.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
AND video_views.video_id = video_feed_unscored.id
) AS view_count,
video_feed_unscored.*
FROM video_feed_unscored