什么是PostgreSQL中的查询等价 [英] what is the query equivalence in postgresql

问题描述

在postgresql上的等效查询是什么？

what is the equivalence query on postgresql?

构建数据库和表

CREATE DATABASE IF NOT EXISTS lemonade;

use lemonade;

CREATE TABLE users (
    id int PRIMARY KEY NOT NULL auto_increment,
    name varchar(50) NOT NULL,
    email varchar(50) NOT NULL
);

CREATE TABLE memories (
    id int PRIMARY KEY NOT NULL auto_increment,
    content varchar(50) NOT NULL, 
    userID int,
    FOREIGN KEY (userID) REFERENCES users(id)
);

INSERT INTO users (name, email) VALUES ("Ruan", "[e-mail]");
INSERT INTO users (name, email) VALUES ("Pereira", "[e-mail]");

INSERT INTO memories (content, userID) VALUES ("memoria 1", 1);
INSERT INTO memories (content, userID) VALUES ("memoria 2", 1);
INSERT INTO memories (content, userID) VALUES ("memoria 3", 2);
INSERT INTO memories (content, userID) VALUES ("memoria 4", 2);

在mysql上查询：

select ANY_VALUE(m.id), ANY_VALUE(m.content), m.userID, ANY_VALUE(u.id), ANY_VALUE(u.name), ANY_VALUE(u.email) from memories m inner join users u on m.userID = u.id group by userID;

结果：
包含查询结果的图片

在PostgreSQL上查询：

query on postgresql:

？

结果：预期结果等于上图

result: expect result equal of up image

推荐答案

MySQL中的 ANY_VALUE 函数在查询进行 GROUP BY 聚合时使用，但是选择的列在 GROUP BY 子句中未提及，也未出现在聚合函数内。在您的查询上下文中，这意味着只能选择 userID 列或聚合函数内的另一列，例如 MAX 或 SUM 。从技术上讲，您还可以从 users 表中选择其他列，假设它们在功能上取决于 userId 。顾名思义， ANY_VALUE 告诉MySQL从每组记录中返回该列的任何值。

The ANY_VALUE function in MySQL is used when a query is doing a GROUP BY aggregation, but columns are being selected which are not mentioned in the GROUP BY clause nor appear inside aggregate functions. In the context of your query, this means that the only columns which can be selected are the userID or another column inside an aggregate function like MAX or SUM. Technically you can also select other columns from the users table as well, assuming they are functionally dependent on the userId. As the name implies, ANY_VALUE is telling MySQL to return any value for that column from each group of records.

据我所知/期望，您不能保证从 ANY_VALUE 中获得的值是确定性的，因此从逻辑上等效于从每个列中为该列选择一个随机值组记录。假设您不关心要返回的值，在Postgres中，您可以任意选择每个用户的最早内存：

As far as I know/expect, the value you get from ANY_VALUE is not guaranteed to be deterministic, and so would be logically equivalent to selecting a random value for that column from each group of records. Assuming you do not care which values you get back, in Postgres you could arbitrarily just select the earliest memory for each user:

SELECT
    memory_id, content, id, name, email
FROM
(
    SELECT m.id AS memory_id, m.content, u.id, u.name, u.email,
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY m.id) rn
    FROM memories m
    INNER JOIN users u
        ON m.userID = u.id
) t
WHERE rn = 1;

我认为通常应该避免使用 ANY_VALUE 在MySQL中，除非绝对没有其他选择。更好的长期解决方案是清理MySQL查询并使其符合ANSI。然后，直接将其移植到另一个数据库就可以了。

I think that in general you should avoid using ANY_VALUE in MySQL unless you absolutely have no other choice. A better long term solution would be to clean up the MySQL query and make it ANSI compliant. Then it would be straightforward how to port it to another database.

这篇关于什么是PostgreSQL中的查询等价的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！