主页 / 问题 / 1211063
Accepted
arelive
Asked:2021-11-28 21:42:26 +0000 UTC2021-11-28 21:42:26 +0000 UTC

PostgreSQL：列必须出现在 GROUP BY 子句中或在聚合函数中使用

  • 772

有四张桌子。合二为一 - 有关图像作为文件的数据：

CREATE TABLE public.images (
    id bigserial NOT NULL PRIMARY KEY,
    hash character(50) NOT NULL,
    type public.image_type NOT NULL,
    uploader_type public.entity_type NOT NULL,
    uploader_id bigint NOT NULL,
    uploader_ip inet NOT NULL
);

在其他 - 用户相册中：

CREATE TABLE public.albums (
    id bigserial NOT NULL PRIMARY KEY,
    access public.access DEFAULT 'private'::public.access NOT NULL,
    name text NOT NULL,
    description text DEFAULT ''::text,
    owner_type public.entity_type NOT NULL,
    owner_id bigint NOT NULL,
    poster_image_id bigint,
    comments public.access DEFAULT 'public'::public.access NOT NULL,
    anonymous_comments_only boolean DEFAULT false NOT NULL
);

第三个连接第一个和第二个。也就是说，如果 images 是关于图像文件的信息，那么下表表示哪些相册有哪些图像：

CREATE TABLE public.album_images (
    id bigserial NOT NULL PRIMARY KEY,
    album_id bigint NOT NULL,
    image_id bigint NOT NULL,
    sha1 character(40) NOT NULL,
    owner_type public.entity_type NOT NULL,
    owner_id bigint NOT NULL,
    description text NOT NULL,
    last_comment_number bigint DEFAULT 0 NOT NULL,
    saved timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

这里 sha1 与图像中的散列不同。理论上，需要通过图像和描述过滤掉重复项。

而第四张表是图片的好恶，表示为“评分”（评分布尔字段似乎是喜欢或不喜欢）：

CREATE TABLE public.media_ratings (
    id bigserial NOT NULL PRIMARY KEY,
    media_type public.media_type NOT NULL,
    media_id bigint NOT NULL,
    user_id bigint NOT NULL,
    rating boolean NOT NULL,
    datetime timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

我正在进行查询以确定相册中最喜欢的三张图片。即我选择相册的所有者、相册本身和图片信息，按album_images.id分组，然后按好恶差排序，将结果限制为三行。像这样：

SELECT a.owner_type, a.owner_id, a.name, i.hash, i.type
FROM albums a
JOIN album_images ai ON a.id = ai.album_id
JOIN images i ON i.id = ai.image_id
JOIN media_ratings mr ON mr.media_id = ai.id
GROUP BY ai.id
ORDER BY ( COUNT(mr.rating = true) - COUNT(mr.rating = false) ) DESC
LIMIT 3;

我得到的是一个错误：column "a.owner_type" must appear in the GROUP BY clause or be used in an aggregate function. 如果您将 a.owner_type 添加到 GROUP BY，它将开始按顺序对所有其他可选字段发誓，直到我将它们全部分组。但是我只需要按相册中的图像进行分组，因为我担心如果我填写 GROUP BY 中的所有字段，所需的结果会中断。stackoverflow 上有类似的问题，建议添加 DISTINCT。添加，不犁。该怎么办？

sql

2 个回答

  1. Best Answer

    此处无需附表media_ratings。从中，只需要喜欢和不喜欢的数量的汇总差异：

    SELECT a.owner_type, a.owner_id, a.name, i.hash, i.type
FROM albums a
JOIN album_images ai ON a.id = ai.album_id
JOIN images i ON i.id = ai.image_id
ORDER BY (
  SELECT COUNT(mr.rating) filter (where mr.rating = true) 
       - COUNT(mr.rating) filter (where mr.rating = true)
  from media_ratings mr where mr.media_id = ai.id
) DESC
LIMIT 3;
    • 1

  2. 我自己找到了另一个解决方案。我们不是在 ORDER BY 中选择，而是在 media_requests 中选择，而是将从内部请求接收到的图像 ID 列表附加到主表，已经根据我们的需要进行了排序，然后再次指定外部排序。像这样：

    
SELECT a.owner_type, a.owner_id, a.name, i.hash, i.type
FROM albums a
JOIN album_images ai ON ai.album_id = a.id
JOIN images i ON i.id = ai.image_id
JOIN (
    SELECT ai.id, ( COUNT(mr.rating = true) - COUNT(mr.rating = false) ) AS diff
    FROM album_images ai
    JOIN media_ratings mr ON mr.media_id = ai.id
    WHERE mr.media_type = 'image'
    GROUP BY ai.id
    ORDER BY diff DESC
    LIMIT 3
) t ON t.id = ai.id
ORDER BY t.diff DESC;

    这种方法可能工作得更慢，但它的优点是如果 media_requests 表为空，结果也将为空，其中的空评级位置不会被来自 album_images 的随机行填充。

    • 0

相关问题