Postgres按订单聚合客Courier对话：查询方向及字段实现求助

表结构 #

CREATE TABLE public.courier (
sender_app_type character varying(255),
customer_id integer,
from_id integer,
to_id integer,
chat_started_by_message boolean,
order_id integer,
order_stage character varying(255),
courier_id integer,
message_sent_time timestamp with time zone
);

示例数据 #

INSERT INTO public.courier(
    sender_app_type, customer_id, from_id, to_id, chat_started_by_message, order_id, order_stage, courier_id, message_sent_time)
VALUES  ('IOS',99,99,21,FALSE,555,'UP',21,timestamp '9/8/22 8:02'),
        ('Courier IOS',99,21,99,FALSE,555,'ARR',21,timestamp '9/8/22 8:01'),
        ('IOS',99,99,21,FALSE,555,'UP',21,timestamp '9/8/22 8:00'),
        ('Android',122,87,122,TRUE,38,'ADDRESS_DELIVERY',87,timestamp '9/8/22 7:55'),
        ('Android',43,43,75,FALSE,875,'UP',75,timestamp '7/8/22 14:55'),
        ('Android',43,75,43,FALSE,875,'ARR',75,timestamp '7/8/22 14:53'),
        ('Android',43,43,75,FALSE,875,'UP',75,timestamp '7/8/22 14:51'),
        ('Android',43,75,43,TRUE,875,'ADD',75,timestamp '7/8/22 14:50'),
        ('IOS',23,23,21,FALSE,134,'UP',21,timestamp '7/8/22 10:02'),
        ('IOS',23,21,23,FALSE,134,'ARr',21,timestamp '7/8/22 10:01'),
        ('my IOS',23,23,21,FALSE,134,'UP',21,timestamp '7/8/22 10:00');

需求字段 #

需要聚合生成customer_courier_conversations表，包含以下字段：

• first_courier_message：Courier发送的首条消息时间戳
• first_customer_message：客户发送的首条消息时间戳
• num_messages_courier：Courier发送的消息总数
• num_messages_customer：客户发送的消息总数
• first_message_by：对话首条消息的发送方（Courier或客户）
• conversation_started_at：对话首条消息的时间戳

当前查询语句 #

SELECT  ccc.order_id, 
    ord.city_code,
    string_agg(ccc.message_sent_time::character varying, ',' order by ccc.courier_id desc) as first_courier_message,
    string_agg(ccc.message_sent_time::character varying, ',' order by ccc.customer_id asc) as first_customer_message,
    count(ccc.courier_id) as num_messages_courier,  
    count(ccc.customer_id) as num_messages_customer,
    string_agg(ccc.sender_app_type,' ' order by )
FROM courier ccc
INNER JOIN "Orders" ord
  ON ccc.order_id = ord.order_id
group by ccc.order_id, ord.city_code;

问题解答 #

当前查询方向是否正确？ #

当前方向存在明显问题，无法满足需求：

1. first_courier_message和first_customer_message用string_agg会拼接所有时间戳，但需求是首条消息，不是全部；
2. count(ccc.courier_id)和count(ccc.customer_id)会统计所有非空记录，无法区分Courier和客户发送的消息；
3. 最后一行string_agg(ccc.sender_app_type,' ' order by )存在语法错误，缺少排序字段；

正确的实现方式 #

通过标记消息发送方、给消息排序，再结合聚合函数实现需求：

WITH message_with_sender AS (
    SELECT 
        ccc.order_id,
        ord.city_code,
        ccc.message_sent_time,
        -- 根据sender_app_type标记发送方
        CASE 
            WHEN ccc.sender_app_type LIKE '%Courier%' THEN 'Courier'
            ELSE '客户'
        END AS sender,
        -- 给每个订单下的消息按时间升序排名，排名1的是首条消息
        ROW_NUMBER() OVER (PARTITION BY ccc.order_id ORDER BY ccc.message_sent_time ASC) AS msg_rank
    FROM courier ccc
    INNER JOIN "Orders" ord ON ccc.order_id = ord.order_id
)
SELECT 
    order_id,
    city_code,
    -- 获取Courier的首条消息时间
    MIN(CASE WHEN sender = 'Courier' THEN message_sent_time END) AS first_courier_message,
    -- 获取客户的首条消息时间
    MIN(CASE WHEN sender = '客户' THEN message_sent_time END) AS first_customer_message,
    -- 统计Courier发送的消息总数
    COUNT(CASE WHEN sender = 'Courier' THEN 1 END) AS num_messages_courier,
    -- 统计客户发送的消息总数
    COUNT(CASE WHEN sender = '客户' THEN 1 END) AS num_messages_customer,
    -- 获取对话首条消息的发送方
    MAX(CASE WHEN msg_rank = 1 THEN sender END) AS first_message_by,
    -- 获取对话首条消息的时间戳
    MAX(CASE WHEN msg_rank = 1 THEN message_sent_time END) AS conversation_started_at
FROM message_with_sender
GROUP BY order_id, city_code;

关键逻辑说明 #

1. CTE预处理：
   • 用CASE判断每条消息的发送方（识别sender_app_type中包含'Courier'的为Courier）；
   • 用ROW_NUMBER()给每个订单下的消息按时间排序，标记出首条消息；
2. 聚合计算：
   • MIN(CASE ...)筛选对应发送方的最早时间戳，即首条消息；
   • COUNT(CASE ...)统计对应发送方的消息数量；
   • MAX(CASE WHEN msg_rank=1 ...)提取首条消息的发送方和时间戳（每个订单仅一条首条消息，MAX/MAX效果一致）；

内容的提问来源于stack exchange，提问作者Julio