使用group by计算查询中的列(Counting columns in a query with group by)

我有下表

id integer NOT NULL DEFAULT nextval('cl_id_seq'::regclass), from_number character varying(30), to_number character varying(30), message text, received_at timestamp with time zone, sent_at timestamp with time zone, reply_batch boolean DEFAULT false, chat_id character varying(20), insert_time timestamp with time zone DEFAULT now(), own_account boolean DEFAULT false,

我有以下查询，只有当与chat_id相关的最后一条消息 （由insert_time列检查）将列own_account设置为false时，才会返回会话的chat_id。

select chat_id from message_log where own_account = 'f' and insert_time in ( select distinct max(insert_time) from message_log group by chat_id )

上面的SQL工作得很好但是它返回会话而不检查聊天中own_account列的真实次数。我想基于own_account为真的次数添加返回会话的chat_id的功能。

我尝试过很多不同的SQL查询但是我无法成功完成这项工作。任何帮助，将不胜感激。

我希望我足够清楚。如果混淆了什么请评论。

编辑

我在这里加载了SQL Fiddle上的数据

http://sqlfiddle.com/#!15/1d7dc/2

如果运行以下查询，它将返回与聊天对话相关的消息

select * from message_log where chat_id = '1412' order by insert_time

最后一条消息不是来自own_account，结果中只有少于3条own_account消息，因此以下查询应该返回它的chat_id，但它不会

select m.chat_id from message_log m inner join (select chat_id, max(insert_time) as max_insert_time, count(case when own_account='t' then 1 else 0 end) as true_count from message_log group by chat_id) as latest on m.chat_id = latest.chat_id and m.insert_time = latest.max_insert_time where m.own_account = 'f' and latest.true_count <= 3

编辑2

我用一条记录创建了另一个sql小提琴

http://sqlfiddle.com/#!15/ad045/1

I have the following table

id integer NOT NULL DEFAULT nextval('cl_id_seq'::regclass), from_number character varying(30), to_number character varying(30), message text, received_at timestamp with time zone, sent_at timestamp with time zone, reply_batch boolean DEFAULT false, chat_id character varying(20), insert_time timestamp with time zone DEFAULT now(), own_account boolean DEFAULT false,

I have the following query which would return chat_ids of conversations only if the last message (checked by insert_time column) related to the chat_id has the column own_account to false.

select chat_id from message_log where own_account = 'f' and insert_time in ( select distinct max(insert_time) from message_log group by chat_id )

The above SQL works perfectly but it returns conversations without checking how many times own_account column is true in a chat. I would like to add the ability to return chat_ids of conversations also based on how many times own_account is true.

I have tried many different SQL queries but I can not succeed at making this work. Any help would be appreciated.

I hope I'm clear enough. if confused by anything please comment.

EDIT

I have loaded the data on SQL Fiddle here

http://sqlfiddle.com/#!15/1d7dc/2

If the following query is ran it will return the messages related to a chat conversation

select * from message_log where chat_id = '1412' order by insert_time

The last message is not from own_account and there are less than 3 own_account messages in the results, so the following query should return it's chat_id but it does not

select m.chat_id from message_log m inner join (select chat_id, max(insert_time) as max_insert_time, count(case when own_account='t' then 1 else 0 end) as true_count from message_log group by chat_id) as latest on m.chat_id = latest.chat_id and m.insert_time = latest.max_insert_time where m.own_account = 'f' and latest.true_count <= 3

EDIT 2

I have created another sql fiddle here with one record

http://sqlfiddle.com/#!15/ad045/1

最满意答案

您可以构建一个派生表来记录所有聊天的最新插入时间，然后查明该最新记录是否为own_account='f'

select m.chat_id from message_log m inner join (select chat_id, max(insert_time) as max_insert_time from message_log group by chat_id) as latest on m.chat_id = latest.chat_id and m.insert_time = latest.max_insert_time where m.own_account = 'f'

扩展它以查找最新为own_account ='f'但至少有3个own_account='t'条目的聊天

select m.chat_id from message_log m inner join (select chat_id, max(insert_time) as max_insert_time, sum(case when own_account='t' then 1 else 0 end) as true_count from message_log group by chat_id) as latest on m.chat_id = latest.chat_id and m.insert_time = latest.max_insert_time where m.own_account = 'f' and latest.true_count >= 3

SQLFiddle可从http://sqlfiddle.com/#!15/ee8c0/2获得

You can build a derived table that records the latest insert times for all chats, and then find out if that latest record is own_account='f'

select m.chat_id from message_log m inner join (select chat_id, max(insert_time) as max_insert_time from message_log group by chat_id) as latest on m.chat_id = latest.chat_id and m.insert_time = latest.max_insert_time where m.own_account = 'f'

Extending it to find the chats where the latest is own_account='f' but has at least 3 own_account='t' entries

select m.chat_id from message_log m inner join (select chat_id, max(insert_time) as max_insert_time, sum(case when own_account='t' then 1 else 0 end) as true_count from message_log group by chat_id) as latest on m.chat_id = latest.chat_id and m.insert_time = latest.max_insert_time where m.own_account = 'f' and latest.true_count >= 3

SQLFiddle available at http://sqlfiddle.com/#!15/ee8c0/2

更多推荐