加入和分组时避免无用的子查询或聚合

Denys Séguret 发表于 Dev

丹尼斯·塞古雷

我在聊天数据库中有两个表room和message：

CREATE TABLE room (
    id serial primary key,
    name varchar(50) UNIQUE NOT NULL,
    private boolean NOT NULL default false,
    description text NOT NULL
);

CREATE TABLE message (
    id bigserial primary key,
    room integer references room(id),
    author integer references player(id),
    created integer NOT NULL,
);

假设我想获得一个房间，其中包含来自用户的消息数量和最近消息的日期：

 id | number | last_created | description |      name        | private 
----+--------+--------------+-------------+------------------+---------
  2 |   1149 |   1391703964 |             | Dragons & co     | t
  8 |    136 |   1391699600 |             | Javascript       | f
 10 |     71 |   1391684998 |             | WBT              | t
  1 |     86 |   1391682712 |             | Miaou            | f
  3 |    423 |   1391681764 |             | Code & Baguettes | f
  ...

我看到两个解决方案：

1）在消息上进行选择/分组，并使用子查询获取房间列：

select m.room as id, count(*) number, max(created) last_created,
(select name from room where room.id=m.room),
(select description from room where room.id=m.room),
(select private from room where room.id=m.room)
from message m where author=$1 group by room order by last_created desc limit 10

这使得3个几乎相同的子查询。这看起来很脏。我可以将其反转以仅对消息列执行2次查询，但效果不会更好。

2）在两个表上选择并对所有列使用聚合函数：

select room.id, count(*) number, max(created) last_created,
max(name) as name, max(description) as description, bool_or(private) as private
from message, room
where message.room=room.id and author=$1
group by room.id order by last_created desc limit 10

所有这些聚合函数看起来都是混乱且无用的。

这里有干净的解决方案吗？

对我来说，这似乎是一个普遍的问题。从理论上讲，这些聚合函数是无用的，因为通过构造，所有联接的行都是同一行。我想知道是否有一个通用的解决方案。

约翰·吉布

尝试在子查询中执行分组：

select m.id, m.number, m.last_created, r.name, r.description, r.private
from (
    select m.room as id, count(*) number, max(created) last_created
    from message m 
    where author=$1 
    group by room 
) m
 join room r
   on r.id = m.id
order by m.last_created desc limit 10

编辑：另一个选项（可能具有类似的性能）是将聚合移动到视图中，例如：

create view MessagesByRoom
as 
select m.author, m.room, count(*) number, max(created) last_created,
from message m 
group by author, room

然后像这样使用它：

select m.room, m.number, m.last_created, r.name, r.description, r.private
from MessagesByRoom m
 join room r
   on r.id = m.room
where m.author = $1
order by m.last_created desc limit 10

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。