sql-server-2008 - 计算最长不间断语音时长

Question

我想计算两个说话者不间断讲话的最长持续时间。数据以 XML 格式存储在 MS SQL 数据库中。

到目前为止，我已经 converted the xml into table (in MSSQL) 它输出这样的结果。生成的表按时间顺序排列(时间以毫秒为单位 - 存储为 int)。

speaker  duration    time
 1      480           0
 2      100           0
 2      260         100
 2      200         360
 1      2640        480
 2      280         560
 ..     ..          ..

接下来，我想使用以下逻辑遍历结果:

• 如果speaker id相同，则继续增加duration

• 如果说话者发生变化，将当前说话者的总时间与某个全局变量进行比较(如果新的说话时间更大，则更新变量)

• 对另一位演讲者应用相同的逻辑。

我需要在 SQL 中执行此操作 - 我是编写条件 SQL 的新手。我不知道该怎么做.. :(

Answer 1

一旦您有某种方式对行进行排序(此处，我添加了 ord)，您就可以让 SQL Server 进行所需的累积:

declare @t table (speaker int not null,duration int not null,ord int not null)
insert into @t (speaker,duration,ord) values
 (1,      480,1),
 (2,      100,2),
 (2,      260,3),
 (2,      200,4),
 (1,      2640,5),
 (2,      280,6)

 ;with Merged as (
    select speaker,duration,ord,ord as last
    from @t t1
    where not exists(
          select * from @t t2
          where t1.speaker = t2.speaker and t1.ord = t2.ord + 1)
    union all
    select m.speaker,m.duration+t.duration,m.ord,t.ord
    from Merged m
    inner join @t t on m.speaker = t.speaker and m.last = t.ord - 1
), Final as (
    select speaker,duration,ord,last,
        ROW_NUMBER() OVER (PARTITION BY ord ORDER by last desc) as rn
    from Merged
)
select * from Final where rn = 1 order by duration desc

结果:

speaker     duration    ord         last        rn
----------- ----------- ----------- ----------- --------------------
1           2640        5           5           1
2           560         2           4           1
1           480         1           1           1
2           280         6           6           1

因此，扬声器 1 的单个持续时间最长，为 2640，扬声器 2 以 560 位居第二，依此类推。

上面的查询使用了两个 Common Table Expressions (CTE)。在第一个 (Merged) 中，我们递归地定义了一个。查询的第一部分查找没有紧邻同一说话者的前一行的行(因此，从逻辑上讲，这些是说话者每个完整语音部分的第一行)。

在递归部分，如果下一行属于同一个说话者，我们会添加它，并且我们会跟踪(在 last 中)最后添加的行。此递归部分根据需要运行多次以累积未中断的部分。

不幸的是，Merged 生成的集合还包括我们在构建不间断语音时采取的所有中间步骤。因此，在 Final 中，分配一个 ROW_NUMBER()，这使我们能够轻松找到作为 Merged 。因此最终查询只选择这些行。

---

如果您没有像 ord 这样方便的列，就像我在上面所做的那样(单调递增)，您可以简单地使用另一个 CTE 生成这样的列，以及您做的任何列 对行 (*) 进行唯一排序。因此，如果您可以通过名为 time 的列唯一标识行，则可以将此 CTE 放在第一个:

;WITH StrictOrdered as (
    SELECT speaker,duration,
       ROW_NUMBER() OVER (ORDER BY time) as ord
    FROM YourTable
)

然后用 StrictOrdered 替换我查询的其余部分中所有使用的 @t。

(* 您更新后的示例的时间 不符合此要求)

---

要获得每个扬声器的最高值，请替换:

select * from Final where rn = 1 order by duration desc

与:

, Highest as (
   select *,ROW_NUMBER() OVER (PARTITION BY Speaker ORDER BY duration desc) as rnDuration
   from Final where rn = 1
)
select * from Highest where rnDuration = 1