如何使用GROUP BY子句将查询移植到PostgreSQL？

本文介绍了如何使用GROUP BY子句将查询移植到PostgreSQL？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我将一个简单的费用数据库移植到Postgres，并使用 GROUP BY 和多个 JOIN 子句。我认为Postgres希望我使用 GROUP BY 子句中的所有表。 表定义在最后。请注意，列 account_id ， receiving_account_id 和 place 可能 NULL 和操作可以有0个标签。 CREATE 语句 CREATE VIEW细节AS SELECT op。 id， op.name， c.name， CASE --amountsign 当op.receiving_account_id不为空时，那么 CASE 当操作。 account_id IS NULL THEN'+' ELSE'=' END ELSE' - ' END || ''|| printf（％2f，op.amount）|| 'zł'AS金额， CASE - 帐户当op.receiving_account_id不为NULL时 CASE 当op.account_id为NULL时则为ac2.name ELSE ac.name || ' - > '|| ac2.name END ELSE ac.name END AS帐户， t.name AS类型 CASE - 日期当操作时间不为NULL然后op.date || ''|| op.time ELSE op.date END AS日期， p.name AS位置， GROUP_CONCAT（tag.name，'，'）AS标签 FROM操作op LEFT JOIN类别c ON op.category_id = c.id LEFT JOIN类型t ON op.type_id = t.id LEFT JOIN帐号ac ON op.account_id = ac.id LEFT JOIN帐户ac2 ON op.receiving_account_id = ac2.id LEFT JOIN位置p ON op.place_id = p.id LEFT JOIN operation_tag ot ON op.id = ot.operation_id LEFT JOIN标记ON ot.tag_id = tag.id GROUP BY IFNULL（ot.operation_id，op.id） ORDER BY日期DESC Postgres中的当前查询 我做了一些更新，当前语句为： BEGIN TRANSACTION; CREATE VIEW细节AS SELECT op.id， op.name， c.name， CASE --amountsign 当op.receiving_account_id IS NOT NULL THEN CASE 当op.account_id IS NULL THEN'+' ELSE'=' END ELSE' - ' END || ''|| op.amount || 'zł'AS金额， CASE - 帐户当op.receiving_account_id不为NULL时 CASE 当op.account_id为NULL时则为ac2.name ELSE ac.name || ' - > '|| ac2.name END ELSE ac.name END AS帐户， t.name AS类型 CASE - 日期当操作时间IS NOT NULL THEN to_char（op.date，'DD.MM.YY'）|| ''|| op.time ELSE to_char（op.date，'DD.MM.YY'） END AS日期， p.name AS位置， STRING_AGG（tag.name， '，'）AS标签 FROM操作 LEFT JOIN类别c ON op.category_id = c.id LEFT JOIN类型t ON op.type_id = t.id LEFT JOIN帐户AC ON op.account_id = ac.id LEFT JOIN帐户ac2 ON op.receiving_account_id = ac2.id LEFT JOIN地方p ON op.place_id = p.id LEFT JOIN operation_tag ot ON op.id = ot.operation_id LEFT JOIN标记ON ot.tag_id = tag.id GROUP BY COALESCE（ot.operation_id，op.id） ORDER BY date DESC; COMMIT; 这里我得到列'x'必须出现在GROUP BY子句中错误，因为我添加了列表： GROUP BY COALESCE（ot.operation_id，op.id），op .id，c.name，ac2.name，ac.name，t.name，p.name 当我添加 p.name 列时，我得到列'p.name'被多次定义错误。 How我是否修复这个问题？ 表定义 CREATE TABLE操作整数NOT NULL PRIMARY KEY，名称字符变化（64）NOT NULL， category_id整数NOT NULL， type_id整数NOT NULL，数值数字（8） ，2）NOT NULL，日期日期NOT NULL，时间没有时区的时间NOT NULL， place_id整数， account_id整数， receiving_account_id整数， CONSTRAINT categories_transactions FOREIGN KEY（category_id） REFERENCES类别（id）MATCH SIMPLE ON UPDATE NO A CTION ON DELETE NO ACTION， CONSTRAINT transactions_accounts FOREIGN KEY（account_id） REFERENCES account（id）MATCH SIMPLE ON UPDATE NO ACTION ON ACTION， CONSTRAINT transactions_accounts_second FOREIGN KEY （ID）匹配SIMPLE 参考帐户（id）MATCH SIMPLE 更新没有行动ON DELETE NO ACTION， CONSTRAINT transactions_places FOREIGN KEY（place_id） REFERENCES place（id）MATCH SIMPLE ON UPDATE NO ACTION ON ACTION， CONSTRAINT transactions_transaction_types FOREIGN KEY（type_id） REFERENCES type（id）MATCH SIMPLE ON UPDATE NO ACTION ON ACTION ） ; 解决方案像 @ Andomar已经提供了：大多数RDBMS需要按每个出现未聚集的列进行分组 - 查询中的任何其他位置（包括 SELECT 列表，而且也在 WHERE 子句中。） PGError：错误：不允许在对象的AR查询和其has_many对象的WHERE子句中聚合 SQL标准还定义 GROUP BY 子句中的表达式也应涵盖功能相关的表达式。 Postgres实现了 PK列覆盖同一张表的所有列。 $ / $ $ b 因此， op.id 涵盖了整个表格，这应该适用于您当前的查询： GROUP BY op.id，c.name，5，t.name，p.name 5 作为位置参考到 SELECT 列表，这也是Postgres允许的。重复长表达式只是符号速记： CASE 当op.receiving_account_id不为空时，那么 CASE 当op.account_id IS NULL THEN ac2.name ELSE ac.name || ' - > '|| ac2.name END ELSE ac.name END 将一列中的多个结果行连接到一个列中，另一列列组 / select-first-in-each-group-by-group / 7630564＃7630564>在每个GROUP BY组中选择第一行？ 我从你的名字中得出你在操作和标记之间有一个： ，用 operation_tag 来实现。所有其他连接似乎都不会增加行数，因此单独聚合标签会更高效 - 就像@Andomar暗示的那样，只是让逻辑正确。 这应该work： SELECT op.id ，op.name ，c.name ，CASE - Amountign 当op.receiving_account_id不为NULL时 CASE当op.account_id IS NULL THEN'+'ELSE'='END ELSE' - ' END || ''|| op.amount || 'zł'AS金额，CASE - 账户当op.receiving_account_id不为NULL时 CASE 当op.account_id为NULL时则为ac2.name ELSE ac.name || ' - >'|| ac2.name END ELSE ac.name END AS account ，t.name AS类型， to_char（op.date，'DD。 MM.YY'）|| ''|| op.time AS date - 见下面，p.name AS位置，ot.tags FROM操作op LEFT JOIN类别c ON op.category_id = c.id LEFT JOIN类型t ON op.type_id = t.id LEFT JOIN帐号ac ON op.account_id = ac.id LEFT JOIN帐号ac2 ON op.receiving_account_id = ac2 .id LEFT JOIN地点p ON op.place_id = p.id LEFT JOIN（ SELECT操作id，string_agg（t.name，'，'）AS标记 FROM operation_tag ot LEFT JOIN标记t ON t.id = ot.tag_id GROUP BY 1 ）ot ON op.id = ot.operation_id ORDER BY op.date DESC，op.time DESC ; 旁白 您可以替换： CASE - 日期当op.time IS NOT NULL THEN to_char（op.date，'DD.MM.YY'）|| ''|| op.time ELSE to_char（op.date，'DD.MM.YY'） END AS date $ b $ $ b $ b $ b concat_ws（''，to_char（op.date，'DD .MM.YY'），op.time）AS date 但是由于两列的定义都是 NOT NULL ，您可以简化为： to_char（op.date， 'DD.MM.YY'）|| ''|| op.time AS date 小心你的 ORDER BY 您至少有一个名为 date 的输入列。如果使用非限定名称，它将引用输出列 - 这正是您想要的（正如评论中所阐明的那样）。详细信息： $ b $ b 然而 ，按文本表示进行排序不会根据您的时间轴正确排序。按照上面的查询中的建议，按原始值排序。 I'm porting a simple expense database to Postgres and got stuck on a view using GROUP BY and multiple JOIN clauses. I think Postgres wants me to use all the tables in the GROUP BY clause.Table definition is at the end. Note that columns account_id, receiving_account_id and place may be NULL and an operation can have 0 tags.Original CREATE statementCREATE VIEW details AS SELECT op.id, op.name, c.name, CASE --amountsign WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN '+' ELSE '=' END ELSE '-' END || ' ' || printf("%.2f", op.amount) || ' zł' AS amount, CASE --account WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN ac2.name ELSE ac.name || ' -> ' || ac2.name END ELSE ac.name END AS account, t.name AS type, CASE --date WHEN op.time IS NOT NULL THEN op.date || ' ' || op.time ELSE op.date END AS date, p.name AS place, GROUP_CONCAT(tag.name, ', ') AS tagsFROM operation opLEFT JOIN category c ON op.category_id = c.idLEFT JOIN type t ON op.type_id = t.idLEFT JOIN account ac ON op.account_id = ac.idLEFT JOIN account ac2 ON op.receiving_account_id = ac2.idLEFT JOIN place p ON op.place_id = p.idLEFT JOIN operation_tag ot ON op.id = ot.operation_idLEFT JOIN tag ON ot.tag_id = tag.idGROUP BY IFNULL (ot.operation_id, op.id)ORDER BY date DESCCurrent query in PostgresI made some updates and my current statement is:BEGIN TRANSACTION;CREATE VIEW details AS SELECT op.id, op.name, c.name, CASE --amountsign WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN '+' ELSE '=' END ELSE '-' END || ' ' || op.amount || ' zł' AS amount, CASE --account WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN ac2.name ELSE ac.name || ' -> ' || ac2.name END ELSE ac.name END AS account, t.name AS type, CASE --date WHEN op.time IS NOT NULL THEN to_char(op.date, 'DD.MM.YY') || ' ' || op.time ELSE to_char(op.date, 'DD.MM.YY') END AS date, p.name AS place, STRING_AGG(tag.name, ', ') AS tagsFROM operation opLEFT JOIN category c ON op.category_id = c.idLEFT JOIN type t ON op.type_id = t.idLEFT JOIN account ac ON op.account_id = ac.idLEFT JOIN account ac2 ON op.receiving_account_id = ac2.idLEFT JOIN place p ON op.place_id = p.idLEFT JOIN operation_tag ot ON op.id = ot.operation_idLEFT JOIN tag ON ot.tag_id = tag.idGROUP BY COALESCE (ot.operation_id, op.id)ORDER BY date DESC;COMMIT;Here I get Column 'x' must appear in GROUP BY clause errors as I add listed ones:GROUP BY COALESCE(ot.operation_id, op.id), op.id, c.name, ac2.name, ac.name, t.name, p.nameWhen I add p.name column I get Column 'p.name' is defined more than once error. How do I fix that?Table definitionCREATE TABLE operation ( id integer NOT NULL PRIMARY KEY, name character varying(64) NOT NULL, category_id integer NOT NULL, type_id integer NOT NULL, amount numeric(8,2) NOT NULL, date date NOT NULL, "time" time without time zone NOT NULL, place_id integer, account_id integer, receiving_account_id integer, CONSTRAINT categories_transactions FOREIGN KEY (category_id) REFERENCES category (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT transactions_accounts FOREIGN KEY (account_id) REFERENCES account (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT transactions_accounts_second FOREIGN KEY (receiving_account_id) REFERENCES account (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT transactions_places FOREIGN KEY (place_id) REFERENCES place (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT transactions_transaction_types FOREIGN KEY (type_id) REFERENCES type (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION); 解决方案 Like @Andomar already provided: Most RDBMS require to group by every column that appears unaggregated - anywhere else in the query (including the SELECT list, but also in the WHERE clause etc.)PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects The SQL standard also defines that expressions in the GROUP BY clause shall also cover functionally dependent expressions. Postgres implemented that the PK column covers all columns of the same table.PostgreSQL - GROUP BY clauseSo op.id covers the whole table and this should work for your current query:GROUP BY op.id, c.name, 5, t.name, p.name5 being a positional reference to the SELECT list, which is also allowed in Postgres. It's just notational shorthand for repeating the long expression:CASE WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN ac2.name ELSE ac.name || ' -> ' || ac2.name END ELSE ac.nameENDConcatenate multiple result rows of one column into one, group by another columnSelect first row in each GROUP BY group?I derive from your names that you have a n:m relationship between operation and tag, implemented with operation_tag. All other joins don't seem to multiply rows, so it would be more efficient to aggregate tags separately - like @Andomar hinted, just get the logic right.This should work:SELECT op.id , op.name , c.name , CASE -- amountsign WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN '+' ELSE '=' END ELSE '-' END || ' ' || op.amount || ' zł' AS amount , CASE -- account WHEN op.receiving_account_id IS NOT NULL THEN CASE WHEN op.account_id IS NULL THEN ac2.name ELSE ac.name || ' -> ' || ac2.name END ELSE ac.name END AS account , t.name AS type , to_char(op.date, 'DD.MM.YY') || ' ' || op.time AS date -- see below , p.name AS place , ot.tagsFROM operation opLEFT JOIN category c ON op.category_id = c.idLEFT JOIN type t ON op.type_id = t.idLEFT JOIN account ac ON op.account_id = ac.idLEFT JOIN account ac2 ON op.receiving_account_id = ac2.idLEFT JOIN place p ON op.place_id = p.idLEFT JOIN ( SELECT operation_id, string_agg(t.name, ', ') AS tags FROM operation_tag ot LEFT JOIN tag t ON t.id = ot.tag_id GROUP BY 1 ) ot ON op.id = ot.operation_idORDER BY op.date DESC, op.time DESC;AsidesYou can replace:CASE --date WHEN op.time IS NOT NULL THEN to_char(op.date, 'DD.MM.YY') || ' ' || op.time ELSE to_char(op.date, 'DD.MM.YY')END AS datewith this shorter equivalent:concat_ws(' ', to_char(op.date, 'DD.MM.YY'), op.time) AS dateBut since both columns are defined NOT NULL, you can furher simplify to:to_char(op.date, 'DD.MM.YY') || ' ' || op.time AS dateCareful with your ORDER BY you have at least one input column also named date. If you use the unqualified name, it will refer to the output column - which is what you want (as clarified in the comment). Details:PostgreSQL: How to return rows with respect to a found row (relative results)?However, sorting by the text representation would not sort according to your timeline correctly. Sort by original values instead as suggested in my query above. 这篇关于如何使用GROUP BY子句将查询移植到PostgreSQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！