如果每个uid在昨天都只发起了一个对话的话，那就是：

with firstsent as (select uid,min(ts) 'senttime' from tbl_msgs where direction=0 group by uid),

firstrespond as (select csid,uid,min(ts) 'respondtime' from tbl_msgs where direction=1 group by csid,uid),

tmp as (select csid,respondtime-senttime 'timediff' from firstsent p1 left join firstrespond p2 on p1.uid=p2.uid)

select csid,avg(timediff) 'averagetime' from tmp group by csid;