如何使用GROUP BY子句将查询移植到PostgreSQL？ [英] How do I port query with GROUP BY clause to 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 子句中。）
- < a href =https://stackoverflow.com/questions/8684486/pgerror-error-aggregates-not-allowed-in-where-clause-on-a-ar-query-of-an-objec/8684512#8684512 > 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 < b> 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 statement
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 || ' ' || 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 tags
FROM operation op
LEFT JOIN category c ON op.category_id = c.id
LEFT JOIN type t ON op.type_id = t.id
LEFT JOIN account ac ON op.account_id = ac.id
LEFT JOIN account ac2 ON op.receiving_account_id = ac2.id
LEFT JOIN place p ON op.place_id = p.id
LEFT JOIN operation_tag ot ON op.id = ot.operation_id
LEFT JOIN tag ON ot.tag_id = tag.id
GROUP BY IFNULL (ot.operation_id, op.id)
ORDER BY date DESC
Current query in Postgres
I 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 tags
FROM operation op
LEFT JOIN category c ON op.category_id = c.id
LEFT JOIN type t ON op.type_id = t.id
LEFT JOIN account ac ON op.account_id = ac.id
LEFT JOIN account ac2 ON op.receiving_account_id = ac2.id
LEFT JOIN place p ON op.place_id = p.id
LEFT JOIN operation_tag ot ON op.id = ot.operation_id
LEFT JOIN tag ON ot.tag_id = tag.id
GROUP 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.name
When I add p.name column I get Column 'p.name' is defined more than once error. How do I fix that?
Table definition
CREATE 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.)
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.
So op.id covers the whole table and this should work for your current query:
GROUP BY op.id, c.name, 5, t.name, p.name
5 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.name
END
- Concatenate multiple result rows of one column into one, group by another column
- Select 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.tags
FROM operation op
LEFT JOIN category c ON op.category_id = c.id
LEFT JOIN type t ON op.type_id = t.id
LEFT JOIN account ac ON op.account_id = ac.id
LEFT JOIN account ac2 ON op.receiving_account_id = ac2.id
LEFT JOIN place p ON op.place_id = p.id
LEFT 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_id
ORDER BY op.date DESC, op.time DESC;
Asides
You 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 date
with this shorter equivalent:
concat_ws(' ', to_char(op.date, 'DD.MM.YY'), op.time) AS date
But since both columns are defined NOT NULL, you can furher simplify to:
to_char(op.date, 'DD.MM.YY') || ' ' || op.time AS date
Careful 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:
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？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
