postgresql - 如何使用 2 个不同的聚合进行此查询

Question

我有 2 个表（对话和消息）：

CREATE TABLE public.convs (
    conv_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    user_id1 int4 NOT NULL,
    user_id2 int4 NOT NULL,
    created timestamp NOT NULL DEFAULT now(),
    CONSTRAINT conv_pkey PRIMARY KEY (conv_id)
);

和

CREATE TABLE public.conv_messages (
    conv_message_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    conv_id int4 NOT NULL,
    message text NOT NULL,
    message_type int2 NOT NULL DEFAULT 1,
    created timestamp NOT NULL DEFAULT now(),
    unread bool NOT NULL DEFAULT true,
    from_user_id int4 NOT NULL,
    CONSTRAINT convmessages_pkey PRIMARY KEY (conv_message_id)
);

并想要一个具有未读计数的最近对话列表：

select distinct on (c.conv_id) c.conv_id 
   , m.message
   , m.message_type
   , count(m.unread or null) over(partition by c.conv_id) as unreads
   , m.from_user_id
   , u.user_name
FROM convs c
   left JOIN conv_messages m using (conv_id)
   join users u on m.from_user_id = u.user_id
where 6000 IN (user_id1, user_id2)  
group by c.conv_id, m.conv_message_id, u.user_id
having m.from_user_id <> 6000
order by c.conv_id desc, m.conv_message_id desc
limit 10;

这是一些测试数据的结果：

conv_id|last_message|message_type|unreads|from_user_id|user_name|
-------|------------|------------|-------|------------|---------|
  20738|reply msg 3 |           1|      2|        6005|Hillegom |
  20737|last msg    |           1|      2|        6004|Oudewater|
  20736|reply msg 3 |           1|      2|        6003|Rockanje |
  20735|reply msg 3 |           1|      2|        6002|Heerlen  |
  20734|reply msg 3 |           1|      2|        6001|Bergen   |

我在这里遇到的唯一问题是该HAVING子句仅用于获取用户 6000 的未读计数，但我也想要 last_message。由于有子句，我无法到达最后一条消息，但如果我删除该子句，我将无法获得正确的计数。我如何以有效的方式解决这个问题？我尝试对计数使用子查询，但性能影响非常高。

编辑 - 得到这些结果的数据：

对话：

conv_id|user_id1|user_id2|created                   |
-------|--------|--------|--------------------------|
  20734|    6000|    6001|2019-04-21 16:10:43.931866|
  20735|    6000|    6002|2019-04-21 16:10:58.068944|
  20736|    6000|    6003|2019-04-21 16:11:07.986172|
  20737|    6000|    6004|2019-04-21 16:11:21.571927|
  20738|    6000|    6005|   2019-04-21 16:11:32.344|

留言：

conv_message_id|conv_id|message    |message_type|created                   |unread|from_user_id|
---------------|-------|-----------|------------|--------------------------|------|------------|
          72836|  20734|last msg   |           1|2019-04-21 16:10:43.931866|true  |        6000|
          72835|  20734|reply msg 3|           1|2019-04-21 16:10:43.931866|true  |        6001|
          72834|  20734|reply msg 2|           1|2019-04-21 16:10:43.931866|true  |        6001|
          72833|  20734|msg 1      |           1|2019-04-21 16:10:43.931866|true  |        6000|
          72840|  20735|last msg   |           1|2019-04-21 16:10:58.068944|true  |        6000|
          72839|  20735|reply msg 3|           1|2019-04-21 16:10:58.068944|true  |        6002|
          72838|  20735|reply msg 2|           1|2019-04-21 16:10:58.068944|true  |        6002|
          72837|  20735|msg 1      |           1|2019-04-21 16:10:58.068944|true  |        6000|
          72844|  20736|last msg   |           1|2019-04-21 16:11:07.986172|true  |        6000|
          72843|  20736|reply msg 3|           1|2019-04-21 16:11:07.986172|true  |        6003|
          72842|  20736|reply msg 2|           1|2019-04-21 16:11:07.986172|true  |        6003|
          72841|  20736|msg 1      |           1|2019-04-21 16:11:07.986172|true  |        6000|
          72848|  20737|last msg   |           1|2019-04-21 16:11:21.571927|true  |        6004|
          72847|  20737|reply msg 3|           1|2019-04-21 16:11:21.571927|true  |        6000|
          72846|  20737|reply msg 2|           1|2019-04-21 16:11:21.571927|true  |        6000|
          72845|  20737|msg 1      |           1|2019-04-21 16:11:21.571927|true  |        6004|
          72852|  20738|last msg   |           1|   2019-04-21 16:11:32.344|true  |        6000|
          72851|  20738|reply msg 3|           1|   2019-04-21 16:11:32.344|true  |        6005|
          72850|  20738|reply msg 2|           1|   2019-04-21 16:11:32.344|true  |        6000|
          72849|  20738|msg 1      |           1|   2019-04-21 16:11:32.344|true  |        6005|

我想要的真的很简单，对话 id、last_message 和未读消息。在上面的数据中，文本“last msg”实际上是此对话的最后插入记录。

标签： postgresql