当前位置： 文江博客话题详情

如何计算 TSQL 中特定值之间的差异的平均值？

发布于 2024-10-10 05:20:21 字数 596 浏览 0 评论 0原文

嘿伙计们，抱歉，这是一个有点长的问题...

我有一个包含以下列的表格：

[ChatID] [User] [LogID] [CreatedOn] [Text]

我需要找到的是平均响应时间给定的用户 ID 到另一个特定的用户 ID。因此，如果我的数据如下所示：

[1] [john] [20] [1/1/11 3:00:00] [Hello]
[1] [john] [21] [1/1/11 3:00:23] [Anyone there?]
[1] [susan] [22] [1/1/11 3:00:43] [Hello!]
[1] [susan] [23] [1/1/11 3:00:53] [What's up?]
[1] [john] [24] [1/1/11 3:01:02] [Not much]
[1] [susan] [25] [1/1/11 3:01:08] [Cool]

...那么我需要看到 Susan 的平均响应时间为 (20 + 6) / 2 =>约翰13秒，约翰平均为(9 / 1) =>苏珊还有 9 秒。

我什至不确定这可以在基于集合的逻辑中完成，但如果有人有任何想法，他们将不胜感激！

原文

Hey folks, sorry this is a bit of a longer question...

I have a table with the following columns:

[ChatID] [User] [LogID] [CreatedOn] [Text]

What I need to find is the average response time for a given user id, to another specific user id. So, if my data looks like:

[1] [john] [20] [1/1/11 3:00:00] [Hello]
[1] [john] [21] [1/1/11 3:00:23] [Anyone there?]
[1] [susan] [22] [1/1/11 3:00:43] [Hello!]
[1] [susan] [23] [1/1/11 3:00:53] [What's up?]
[1] [john] [24] [1/1/11 3:01:02] [Not much]
[1] [susan] [25] [1/1/11 3:01:08] [Cool]

...then I need to see that Susan has an average response time of (20 + 6) / 2 => 13 seconds to John, and John has an average of (9 / 1) => 9 seconds to Susan.

I'm not even sure this can be done in set-based logic, but if anyone has any ideas, they'd be much appreciated!

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（6）

天赋异禀 2024-10-17 05:20:21

我没有一台电脑来验证语法或任何东西，但我认为这应该给你一个起点：

WITH ChatWithRownum AS (
    SELECT ChatID, User, LogID, CreatedOn, ROW_NUMBER() OVER(ORDER BY ChatID, CreatedOn) AS rownum
    FROM ChatLog
)
SELECT First.ChatID, Second.User,
    AVG(DATEDIFF(seconds, First.CreatedOn, Second.CreatedOn)) AS AvgElapsedTime
FROM ChatWithRownum First
    JOIN ChatWithRownum Second ON First.ChatID = Second.ChatID
        AND First.rownum = Second.rownum - 1
WHERE First.User != Second.User
GROUP BY First.ChatID, Second.User

本质上，这个想法是将行号添加到数据中，这样你就可以将一行连接到下一行（所以你有声明后立即作出回应）。连接行后，您可以获得两个条目之间经过的时间，然后按 ChatID（我假设单独聊天之间的时间不相关）和两个用户对数据进行分组。就像我说的，这只是一个起点，因为我确信我的查询中可能存在一些额外的条件和/或错误:)

I don't have a PC to verify syntax or anything, but I think this should give you a starting place:

WITH ChatWithRownum AS (
    SELECT ChatID, User, LogID, CreatedOn, ROW_NUMBER() OVER(ORDER BY ChatID, CreatedOn) AS rownum
    FROM ChatLog
)
SELECT First.ChatID, Second.User,
    AVG(DATEDIFF(seconds, First.CreatedOn, Second.CreatedOn)) AS AvgElapsedTime
FROM ChatWithRownum First
    JOIN ChatWithRownum Second ON First.ChatID = Second.ChatID
        AND First.rownum = Second.rownum - 1
WHERE First.User != Second.User
GROUP BY First.ChatID, Second.User

Essentially, the idea is to add row numbers to the data so you can join one row to the next row (so you have a statement followed by its immediate response). Once you have the rows joined, you can get the time elapsed between the two entries, and then group the data by the ChatID (I'm assuming that times between separate chats aren't relevant) and the two users. Like I said though, this is just a starting place as I'm sure there may be some additional criteria and/or bugs in my query :)

回复 原文
<逆流佳人身旁 2024-10-17 05:20:21

在进入光标之前尝试如下简单的操作。

select ChatId, User, datediff('second', min(CreatedOn, max(CreatedOn))/count(*)
from ChatLog
group by ChatId, User

这个可以工作并且不涉及使用游标。如果我有更多时间，我什至可以消除临时表，但是嘿......它有效。

declare @operator varchar(50)
set @operator = 'john'
declare @customer varchar(50)
set @customer = 'susan'
declare @chatid int
set @chatid = 1

declare @t table (chatid int, username varchar(50), responsetime int)

insert @t (chatid, username, responsetime)
select ChatId, 
    Username,
    datediff(second, 
    CreatedOn,
    (
        select min(createdon)
        from chatlog
        where createdon > cl.createdon
        and username = @customer
              and chatid = @chatid
    ))
from ChatLog cl
where chatid = @chatid and username = @operator

insert @t (chatid, username, responsetime)
select ChatId, 
    Username, 
    datediff(second, 
    CreatedOn,
    (
        select min(createdon)
        from chatlog
        where createdon > cl.createdon
        and username = @operator
              and chatid = @chatid
    ))
from ChatLog cl
where chatid = @chatid and username = @customer

select chatid, username, avg(responsetime) as avgresponsetime 
from @t
group by chatid, username
order by username

Try something simple like the following before moving into cursors.

select ChatId, User, datediff('second', min(CreatedOn, max(CreatedOn))/count(*)
from ChatLog
group by ChatId, User

This one works and doesn't involve using cursors. If I had more time, I could probably even eliminate the temp table, but hey... it works.

declare @operator varchar(50)
set @operator = 'john'
declare @customer varchar(50)
set @customer = 'susan'
declare @chatid int
set @chatid = 1

declare @t table (chatid int, username varchar(50), responsetime int)

insert @t (chatid, username, responsetime)
select ChatId, 
    Username,
    datediff(second, 
    CreatedOn,
    (
        select min(createdon)
        from chatlog
        where createdon > cl.createdon
        and username = @customer
              and chatid = @chatid
    ))
from ChatLog cl
where chatid = @chatid and username = @operator

insert @t (chatid, username, responsetime)
select ChatId, 
    Username, 
    datediff(second, 
    CreatedOn,
    (
        select min(createdon)
        from chatlog
        where createdon > cl.createdon
        and username = @operator
              and chatid = @chatid
    ))
from ChatLog cl
where chatid = @chatid and username = @customer

select chatid, username, avg(responsetime) as avgresponsetime 
from @t
group by chatid, username
order by username
回复 原文
梦里泪两行 2024-10-17 05:20:21

似乎您需要一个光标来逐步浏览每一行并检查记录中用户的更改，并获取该时间的差异，并将其存储在某个地方（可能是临时表），并稍后聚合它。

我相信它可以在 TSQL 中完成，逻辑如下：

DECLARE delta CURSOR FOR
SELECT user, createdon from table
order by createdon --or logid

OPEN delta
fetch next from delta into @username, @time
while @@fetch_status = 0

begin

FETCH PRIOR FROM delta into @username_prior, @time_prior
IF @username_prior  @username
BEGIN
  @timedelta = @time - @time_prior
  @total = @total + @timedelta
  insert into #average (@username, @total)
END 

fetch next from delta into @username, @time
END

CLOSE delta
DEALLOCATE delta

SELECT user, AVG(time) from #average
group by user

我相信您可以弄清楚如何声明所有参数。

Seems like you need a cursor to step through each line and check for the change of user in the record, and get the difference of that time, and store it somewhere(temp table maybe), and aggregate it later.

I believe it can be done in TSQL, logic would be something like:

DECLARE delta CURSOR FOR
SELECT user, createdon from table
order by createdon --or logid

OPEN delta
fetch next from delta into @username, @time
while @@fetch_status = 0

begin

FETCH PRIOR FROM delta into @username_prior, @time_prior
IF @username_prior  @username
BEGIN
  @timedelta = @time - @time_prior
  @total = @total + @timedelta
  insert into #average (@username, @total)
END 

fetch next from delta into @username, @time
END

CLOSE delta
DEALLOCATE delta

SELECT user, AVG(time) from #average
group by user

I'm sure you can figure out how to declare all the parameters.

回复 原文
萌无敌 2024-10-17 05:20:21

这可以通过 RowNumber() 来完成和 DateDiff()

WITH TableWithOrderings AS (
    SELECT DateTime, ROW_NUMBER() OVER (ORDER BY DateTime) AS Ordering
    FROM myTable
)

WITH Intervals As (
    SELECT DATEDIFF(second, A.DateTime, B.DateTime) AS IntervalSeconds
    FROM TableWithOrderings A
        INNER JOIN TableWithOrderings B ON B.Ordering = A.Ordering + 1
)

SELECT AVG(IntervalSeconds) FROM Intervals

This can be done with RowNumber() and DateDiff()

WITH TableWithOrderings AS (
    SELECT DateTime, ROW_NUMBER() OVER (ORDER BY DateTime) AS Ordering
    FROM myTable
)

WITH Intervals As (
    SELECT DATEDIFF(second, A.DateTime, B.DateTime) AS IntervalSeconds
    FROM TableWithOrderings A
        INNER JOIN TableWithOrderings B ON B.Ordering = A.Ordering + 1
)

SELECT AVG(IntervalSeconds) FROM Intervals
回复 原文
情释 2024-10-17 05:20:21

试试这个：

create table chats
(
chat_id int not null,
user_name text not null,
log_id int not null primary key,
created_on timestamp not null,
message text not null
);


insert into chats(chat_id, user_name, log_id, created_on, message)
values(1, 'john', 20, '1/1/11 3:00:00', 'Hello'),
(1, 'john',21, '1/1/11 3:00:23', 'Anyone there?'),
(1, 'susan',22, '1/1/11 3:00:43', 'Hello!'),
(1, 'susan', 23, '1/1/11 3:00:53', 'What''s up?'),
(1, 'john', 24, '1/1/11 3:01:02', 'Not much'),
(1, 'susan', 25, '1/1/11 3:01:08', 'Cool')

示例数据：

select c.*, 'x', next.*
from chats c
left join chats next on next.log_id = c.log_id + 1 
order by c.log_id

输出：

 chat_id | user_name | log_id |     created_on      |    message    | ?column? | chat_id | user_name | log_id |     created_on      |    message    
---------+-----------+--------+---------------------+---------------+----------+---------+-----------+--------+---------------------+---------------
       1 | john      |     20 | 2011-01-01 03:00:00 | Hello         | x        |       1 | john      |     21 | 2011-01-01 03:00:23 | Anyone there?
       1 | john      |     21 | 2011-01-01 03:00:23 | Anyone there? | x        |       1 | susan     |     22 | 2011-01-01 03:00:43 | Hello!
       1 | susan     |     22 | 2011-01-01 03:00:43 | Hello!        | x        |       1 | susan     |     23 | 2011-01-01 03:00:53 | What's up?
       1 | susan     |     23 | 2011-01-01 03:00:53 | What's up?    | x        |       1 | john      |     24 | 2011-01-01 03:01:02 | Not much
       1 | john      |     24 | 2011-01-01 03:01:02 | Not much      | x        |       1 | susan     |     25 | 2011-01-01 03:01:08 | Cool
       1 | susan     |     25 | 2011-01-01 03:01:08 | Cool          | x        |         |           |        |                     |

分组：

select c.*, 'x', next.*, count(case when next.user_name is null or next.user_name <> c.user_name then 1 end) over(order by c.log_id)
from chats c
left join chats next on next.log_id + 1 = c.log_id 
order by c.log_id

输出：

 chat_id | user_name | log_id |     created_on      |    message    | ?column? | chat_id | user_name | log_id |     created_on      |    message    | count 
---------+-----------+--------+---------------------+---------------+----------+---------+-----------+--------+---------------------+---------------+-------
       1 | john      |     20 | 2011-01-01 03:00:00 | Hello         | x        |         |           |        |                     |               |     1
       1 | john      |     21 | 2011-01-01 03:00:23 | Anyone there? | x        |       1 | john      |     20 | 2011-01-01 03:00:00 | Hello         |     1
       1 | susan     |     22 | 2011-01-01 03:00:43 | Hello!        | x        |       1 | john      |     21 | 2011-01-01 03:00:23 | Anyone there? |     2
       1 | susan     |     23 | 2011-01-01 03:00:53 | What's up?    | x        |       1 | susan     |     22 | 2011-01-01 03:00:43 | Hello!        |     2
       1 | john      |     24 | 2011-01-01 03:01:02 | Not much      | x        |       1 | susan     |     23 | 2011-01-01 03:00:53 | What's up?    |     3
       1 | susan     |     25 | 2011-01-01 03:01:08 | Cool          | x        |       1 | john      |     24 | 2011-01-01 03:01:02 | Not much      |     4
(6 rows)

分组结果：

with grouped_result as
(
select c.log_id, c.user_name, count(case when next.user_name is null or next.user_name <> c.user_name then 1 end) over(order by c.log_id) as the_grouping
from chats c
left join chats next on next.log_id + 1 = c.log_id 
order by c.log_id
)
select user_name, max(log_id) as last_chat_of_each_user
from grouped_result
group by the_grouping
    ,user_name
order by last_chat_of_each_user

输出：

 user_name | last_chat_of_each_user 
-----------+------------------------
 john      |                     21
 susan     |                     23
 john      |                     24
 susan     |                     25
(4 rows)

聊天和响应：

with grouped_result as
(
select c.log_id, c.user_name, count(case when next.user_name is null or next.user_name <> c.user_name then 1 end) over(order by c.log_id) as the_grouping
from chats c
left join chats next on next.log_id + 1 = c.log_id 
order by c.log_id
),
last_chats as
(
select user_name as responded_to, max(log_id) as last_chat_of_each_user
from grouped_result
group by the_grouping
    ,responded_to
)
select lc.responded_to, lc.last_chat_of_each_user as responded_to_log_id, lc_the_chat.created_on as responded_to_timestamp, 'x',  answered_by.user_name as responded_by, answered_by.created_on as response_created_on
from last_chats lc
join chats lc_the_chat on lc_the_chat.log_id = lc.last_chat_of_each_user
join chats answered_by on answered_by.log_id = lc.last_chat_of_each_user + 1
order by lc.last_chat_of_each_user

输出：

 responded_to | responded_to_log_id | responded_to_timestamp | ?column? | responded_by | response_created_on 
--------------+---------------------+------------------------+----------+--------------+---------------------
 john         |                  21 | 2011-01-01 03:00:23    | x        | susan        | 2011-01-01 03:00:43
 susan        |                  23 | 2011-01-01 03:00:53    | x        | john         | 2011-01-01 03:01:02
 john         |                  24 | 2011-01-01 03:01:02    | x        | susan        | 2011-01-01 03:01:08
(3 rows)

聊天的响应平均时间：

with grouped_result as
(
select c.log_id, c.user_name, count(case when next.user_name is null or next.user_name <> c.user_name then 1 end) over(order by c.log_id) as the_grouping
from chats c
left join chats next on next.log_id + 1 = c.log_id 
order by c.log_id
),
last_chats as
(
select user_name as responded_to, max(log_id) as last_chat_of_each_user
from grouped_result
group by the_grouping
    ,responded_to
),
responses as
(
select lc.responded_to, lc.last_chat_of_each_user as responded_to_log_id, lc_the_chat.created_on as responded_to_timestamp,  answered_by.user_name as responded_by, answered_by.created_on as response_created_on
from last_chats lc
join chats lc_the_chat on lc_the_chat.log_id = lc.last_chat_of_each_user
join chats answered_by on answered_by.log_id = lc.last_chat_of_each_user + 1
order by lc.last_chat_of_each_user
)
select responded_by, responded_to, sum(response_created_on - responded_to_timestamp), count(*), avg(response_created_on - responded_to_timestamp) as average_response_to_person
from responses
group by responded_by, responded_to

输出：

 responded_by | responded_to |   sum    | count | average_response_to_person 
--------------+--------------+----------+-------+----------------------------
 susan        | john         | 00:00:26 |     2 | 00:00:13
 john         | susan        | 00:00:09 |     1 | 00:00:09
(2 rows)

将在 Postgresql 上开箱即用。要使其在 Sql Server 上工作，只需将 response_created_on - returned_to_timestamp 更改为相应的 Sql Server DATEDIFF 构造（我记不起 DATEDIFF 秒数）

Try this:

create table chats
(
chat_id int not null,
user_name text not null,
log_id int not null primary key,
created_on timestamp not null,
message text not null
);


insert into chats(chat_id, user_name, log_id, created_on, message)
values(1, 'john', 20, '1/1/11 3:00:00', 'Hello'),
(1, 'john',21, '1/1/11 3:00:23', 'Anyone there?'),
(1, 'susan',22, '1/1/11 3:00:43', 'Hello!'),
(1, 'susan', 23, '1/1/11 3:00:53', 'What''s up?'),
(1, 'john', 24, '1/1/11 3:01:02', 'Not much'),
(1, 'susan', 25, '1/1/11 3:01:08', 'Cool')

Sample data:

select c.*, 'x', next.*
from chats c
left join chats next on next.log_id = c.log_id + 1 
order by c.log_id

Output:

 chat_id | user_name | log_id |     created_on      |    message    | ?column? | chat_id | user_name | log_id |     created_on      |    message    
---------+-----------+--------+---------------------+---------------+----------+---------+-----------+--------+---------------------+---------------
       1 | john      |     20 | 2011-01-01 03:00:00 | Hello         | x        |       1 | john      |     21 | 2011-01-01 03:00:23 | Anyone there?
       1 | john      |     21 | 2011-01-01 03:00:23 | Anyone there? | x        |       1 | susan     |     22 | 2011-01-01 03:00:43 | Hello!
       1 | susan     |     22 | 2011-01-01 03:00:43 | Hello!        | x        |       1 | susan     |     23 | 2011-01-01 03:00:53 | What's up?
       1 | susan     |     23 | 2011-01-01 03:00:53 | What's up?    | x        |       1 | john      |     24 | 2011-01-01 03:01:02 | Not much
       1 | john      |     24 | 2011-01-01 03:01:02 | Not much      | x        |       1 | susan     |     25 | 2011-01-01 03:01:08 | Cool
       1 | susan     |     25 | 2011-01-01 03:01:08 | Cool          | x        |         |           |        |                     |

The grouping:

select c.*, 'x', next.*, count(case when next.user_name is null or next.user_name <> c.user_name then 1 end) over(order by c.log_id)
from chats c
left join chats next on next.log_id + 1 = c.log_id 
order by c.log_id

Output:

 chat_id | user_name | log_id |     created_on      |    message    | ?column? | chat_id | user_name | log_id |     created_on      |    message    | count 
---------+-----------+--------+---------------------+---------------+----------+---------+-----------+--------+---------------------+---------------+-------
       1 | john      |     20 | 2011-01-01 03:00:00 | Hello         | x        |         |           |        |                     |               |     1
       1 | john      |     21 | 2011-01-01 03:00:23 | Anyone there? | x        |       1 | john      |     20 | 2011-01-01 03:00:00 | Hello         |     1
       1 | susan     |     22 | 2011-01-01 03:00:43 | Hello!        | x        |       1 | john      |     21 | 2011-01-01 03:00:23 | Anyone there? |     2
       1 | susan     |     23 | 2011-01-01 03:00:53 | What's up?    | x        |       1 | susan     |     22 | 2011-01-01 03:00:43 | Hello!        |     2
       1 | john      |     24 | 2011-01-01 03:01:02 | Not much      | x        |       1 | susan     |     23 | 2011-01-01 03:00:53 | What's up?    |     3
       1 | susan     |     25 | 2011-01-01 03:01:08 | Cool          | x        |       1 | john      |     24 | 2011-01-01 03:01:02 | Not much      |     4
(6 rows)

The grouped result:

with grouped_result as
(
select c.log_id, c.user_name, count(case when next.user_name is null or next.user_name <> c.user_name then 1 end) over(order by c.log_id) as the_grouping
from chats c
left join chats next on next.log_id + 1 = c.log_id 
order by c.log_id
)
select user_name, max(log_id) as last_chat_of_each_user
from grouped_result
group by the_grouping
    ,user_name
order by last_chat_of_each_user

Output:

 user_name | last_chat_of_each_user 
-----------+------------------------
 john      |                     21
 susan     |                     23
 john      |                     24
 susan     |                     25
(4 rows)

Chat and responses:

with grouped_result as
(
select c.log_id, c.user_name, count(case when next.user_name is null or next.user_name <> c.user_name then 1 end) over(order by c.log_id) as the_grouping
from chats c
left join chats next on next.log_id + 1 = c.log_id 
order by c.log_id
),
last_chats as
(
select user_name as responded_to, max(log_id) as last_chat_of_each_user
from grouped_result
group by the_grouping
    ,responded_to
)
select lc.responded_to, lc.last_chat_of_each_user as responded_to_log_id, lc_the_chat.created_on as responded_to_timestamp, 'x',  answered_by.user_name as responded_by, answered_by.created_on as response_created_on
from last_chats lc
join chats lc_the_chat on lc_the_chat.log_id = lc.last_chat_of_each_user
join chats answered_by on answered_by.log_id = lc.last_chat_of_each_user + 1
order by lc.last_chat_of_each_user

Output:

 responded_to | responded_to_log_id | responded_to_timestamp | ?column? | responded_by | response_created_on 
--------------+---------------------+------------------------+----------+--------------+---------------------
 john         |                  21 | 2011-01-01 03:00:23    | x        | susan        | 2011-01-01 03:00:43
 susan        |                  23 | 2011-01-01 03:00:53    | x        | john         | 2011-01-01 03:01:02
 john         |                  24 | 2011-01-01 03:01:02    | x        | susan        | 2011-01-01 03:01:08
(3 rows)

Chat's response average time:

with grouped_result as
(
select c.log_id, c.user_name, count(case when next.user_name is null or next.user_name <> c.user_name then 1 end) over(order by c.log_id) as the_grouping
from chats c
left join chats next on next.log_id + 1 = c.log_id 
order by c.log_id
),
last_chats as
(
select user_name as responded_to, max(log_id) as last_chat_of_each_user
from grouped_result
group by the_grouping
    ,responded_to
),
responses as
(
select lc.responded_to, lc.last_chat_of_each_user as responded_to_log_id, lc_the_chat.created_on as responded_to_timestamp,  answered_by.user_name as responded_by, answered_by.created_on as response_created_on
from last_chats lc
join chats lc_the_chat on lc_the_chat.log_id = lc.last_chat_of_each_user
join chats answered_by on answered_by.log_id = lc.last_chat_of_each_user + 1
order by lc.last_chat_of_each_user
)
select responded_by, responded_to, sum(response_created_on - responded_to_timestamp), count(*), avg(response_created_on - responded_to_timestamp) as average_response_to_person
from responses
group by responded_by, responded_to

Output:

 responded_by | responded_to |   sum    | count | average_response_to_person 
--------------+--------------+----------+-------+----------------------------
 susan        | john         | 00:00:26 |     2 | 00:00:13
 john         | susan        | 00:00:09 |     1 | 00:00:09
(2 rows)

Will work out-of-the box on Postgresql. To make it work on Sql Server, just change the response_created_on - responded_to_timestamp to corresponding Sql Server DATEDIFF construct (i cannot recall off the top my head what's the DATEDIFF for seconds)

回复 原文
凯凯我们等你回来 2024-10-17 05:20:21

这将完成工作，但我不确定它将如何扩展：

select spoke, responded, count(*) responses, avg(time_diff) avg_seconds from (
select a.user_name spoke, b.user_name responded, a.created_on spoke_at, min(b.created_on) responded_at, datediff(ss, a.created_on, min(b.created_on)) time_diff
from chats a, chats b
where a.chat_id = b.chat_id
 and a.log_id < b.log_id
 and not exists (select 1 from chats c where c.chat_id = a.chat_id and c.log_id < b.log_id and c.log_id > a.log_id)
group by a.user_name, b.user_name, a.created_on
) users group by spoke, responded

 spoke     responded     responses     avg_seconds    
 --------  ------------  ------------  -------------- 
 john      john          1             23             
 susan     john          1             9              
 john      susan         2             13             
 susan     susan         1             10

选择了 4 条记录 [Fetch MetaData: 0ms] [Fetch Data: 0ms]

(chat_id, log_id) 上的索引应该没问题。

如果您想消除相同的响应，您只需要在外部 where 子句中使用 != 即可：

select spoke, responded, count(*) responses, avg(time_diff) avg_seconds from (
select a.user_name spoke, b.user_name responded, a.created_on spoke_at, min(b.created_on) responded_at, datediff(ss, a.created_on, min(b.created_on)) time_diff
from chats a, chats b
where a.chat_id = b.chat_id
 and a.log_id < b.log_id
 and not exists (select 1 from chats c where c.chat_id = a.chat_id and c.log_id < b.log_id and c.log_id > a.log_id)
group by a.user_name, b.user_name, a.created_on
) users 
where spoke != responded
group by spoke, responded

This will get the job done, but I'm not sure how it will scale:

select spoke, responded, count(*) responses, avg(time_diff) avg_seconds from (
select a.user_name spoke, b.user_name responded, a.created_on spoke_at, min(b.created_on) responded_at, datediff(ss, a.created_on, min(b.created_on)) time_diff
from chats a, chats b
where a.chat_id = b.chat_id
 and a.log_id < b.log_id
 and not exists (select 1 from chats c where c.chat_id = a.chat_id and c.log_id < b.log_id and c.log_id > a.log_id)
group by a.user_name, b.user_name, a.created_on
) users group by spoke, responded

 spoke     responded     responses     avg_seconds    
 --------  ------------  ------------  -------------- 
 john      john          1             23             
 susan     john          1             9              
 john      susan         2             13             
 susan     susan         1             10

4 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]

It should be okay with an index on (chat_id, log_id).

If you'd like to eliminate same responses, all you need is a != in the outer where clause:

select spoke, responded, count(*) responses, avg(time_diff) avg_seconds from (
select a.user_name spoke, b.user_name responded, a.created_on spoke_at, min(b.created_on) responded_at, datediff(ss, a.created_on, min(b.created_on)) time_diff
from chats a, chats b
where a.chat_id = b.chat_id
 and a.log_id < b.log_id
 and not exists (select 1 from chats c where c.chat_id = a.chat_id and c.log_id < b.log_id and c.log_id > a.log_id)
group by a.user_name, b.user_name, a.created_on
) users 
where spoke != responded
group by spoke, responded
回复 原文
~没有更多了~

关于作者

亢潮

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

lioqio

文章 0 评论 0

Single

文章 0 评论 0

禾厶谷欠

文章 0 评论 0

alipaysp_2zg8elfGgC

文章 0 评论 0

qq_N6d4X7

文章 0 评论 0

放低过去

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文