主页 / dba / 问题 / 222442
Accepted
laurent
Asked: 2018-11-14 07:42:39 +0800 CST

在 group by 子句中强制执行已知的函数依赖

  • 772

让我们考虑一下我有下表：

    CREATE TABLE users(
        id serial PRIMARY KEY,
        age integer
    )

    CREATE TABLE products(
        id serial PRIMARY KEY,
        sku character varying(255),
        user_id integer REFERENCES users
    )

所以基本上一个产品属于一个用户，一个用户可以拥有很多产品。

我希望能够让用户使用他们的产品在数据库级别进行聚合（也许是错误的，但我认为这比在我的应用程序层运行一些聚合代码更容易也可能更有效：Postgres 带有很好的聚合函数，为什么不呢使用它们 ！）

所以我会运行类似

SELECT "users".*, json_agg("products".*) as "products" 
  FROM "users" LEFT JOIN "products" ON "users"."id" = "products"."user_id" 
  GROUP BY "users"."id"

很好，我的驱动程序可以解析 JSON 结构，而我在应用层端无事可做。

如果我想引入某种分页，问题就来了：

WITH "users" AS (SELECT * FROM "users" ORDER BY "id" LIMIT 20)
SELECT "users".*, json_agg("products".*) as "products" 
FROM "users" LEFT JOIN "products" ON "users"."id" = "products"."user_id" 
GROUP BY "users"."id"

我有已知错误

错误：列“users.age”必须出现在 GROUP BY 子句中或用于聚合函数

由于“用户”。“id”不被视为我的子查询创建的临时表的主键

我可以通过在 group by 子句中添加每个用户列来修复它。但是我发现它很麻烦并且很遗憾，因为我确信“用户”。“id”将以独特的方式定义我的子查询的项目。

所以，我想知道是否有办法告诉数据库引擎“users”。“id”是我的子查询的某种主键？

如果没有，您是否看到更好的方法？

编辑：这个问题非常相似（尽管 3 岁）

谢谢

postgresql group-by

1 个回答

  1. Best Answer
    create table users (user_id int, user_name text);

insert into users values
(1, 'user1'),(2, 'user2'),(3, 'user3'),(4, 'user4'),(5, 'user5');

create table products (user_id int, id int, name text, att1 text);

insert into products values
(2, 1, 'prod1', 'att1'),
(2, 2, 'prod2', 'att1'),
(2, 3, 'prod3', 'att1'),
(2, 4, 'prod4', 'att1'),
(3, 5, 'prod5', 'att1'),
(3, 6, 'prod6', 'att1');

    恕我直言，您的第一个查询应该抛出相同的错误消息。

    select
    users.*,
    json_agg(products.*) as products
from
    users
left join products on
    users.user_id = products.user_id;

    错误：列“users.user_id”必须出现在 GROUP BY 子句中或用于聚合函数 LINE 2: users.*,
    ^

    但是您可以移动json_agg到子查询而不是连接两个表，然后您可以轻松地限制返回的行数。

    select 
    users.*,
    (select 
        json_agg(products.*)
     from
        products
     where
        user_id = users.user_id)  as products
from
    users
limit 3;

+---------+-----------+------------------------------------------------------+
| user_id | user_name | products                                             |
+---------+-----------+------------------------------------------------------+
| 1       | user1     |                                                      |
+---------+-----------+------------------------------------------------------+
| 2       | user2     | [{"user_id":2,"id":1,"name":"prod1","att1":"att1"},  |
|         |           |  {"user_id":2,"id":2,"name":"prod2","att1":"att1"},  |
|         |           |  {"user_id":2,"id":3,"name":"prod3","att1":"att1"},  |
|         |           |  {"user_id":2,"id":4,"name":"prod4","att1":"att1"}]  |
+---------+-----------+------------------------------------------------------+
| 3       | user3     | [{"user_id":3,"id":5,"name":"prod5","att1":"att1"},  |
|         |           |  {"user_id":3,"id":6,"name":"prod6","att1":"att1"}]  |
+---------+-----------+------------------------------------------------------+

    db<>在这里摆弄

    • 0

相关问题