在SQL查询中使用case和count作为条件之一

Question

我正在尝试从另一个表中使用select语句生成新表

给定表(name: business):

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE business (
id uuid NOT NULL DEFAULT uuid_generate_v4(),
name text NOT NULL,
brand text
);

INSERT INTO business (name, brand) VALUES
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('EpicHd' , NULL),
('Kafkflef' , 'Kafk');

我想创建一个新的表，如果name的出现次数超过5，例如条件。select语句如下所示:

SELECT
uuid_generate_v4 () as uuid,
name as name,
CASE
WHEN brand IS NOT NULL
THEN brand
WHEN brand IS NULL AND count(name) > 5
THEN name
ELSE 'Other'
END as brand,
CURRENT_DATE as time_added
FROM business;

但是我得到ERROR: column "brand"必须出现在GROUP BY子句中，或者在聚合函数中使用。

当我添加这个GROUP BY品牌时，我得到了另一个错误，但是添加GROUP BY带有名称等其他列，我没有包括在这里。

有谁能帮我一下，让我走上正轨吗?谢谢你。

Answer 1

按照postgres的提示，必须使用组by:

BEGIN;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE business (
id uuid NOT NULL DEFAULT uuid_generate_v4(),
name text NOT NULL,
brand text
);

INSERT INTO business (name, brand) VALUES
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('EpicHd' , NULL),
('Kafkflef' , 'Kafk');
SELECT
uuid_generate_v4 () AS uuid,
name AS name,
CASE WHEN brand IS NOT NULL THEN
brand
WHEN brand IS NULL
AND count(name) > 5 THEN
name
ELSE
'Other'
END AS brand,
CURRENT_DATE AS time_added
FROM
business
GROUP BY name, brand;
ROLLBACK;

结果:

f6a66b5d-fd43-4deb-be14-f052ddc142b2    Auchan  Auchan  2021-07-30
fbfd60ee-5c28-407f-8c46-21716a2e98a7    EpicHd  Other   2021-07-30
275a6d4c-6859-4a6c-a917-b818cf142809    Kafkflef    Kafk    2021-07-30