找出两个人之间发生的通话总时长

Question

我想通过SQL查询两个人之间的通话时长。比如"Joe"打电话给"Mak"并通话了5分钟，"Mak"打电话给"Joe"并通话了15分钟，则总通话时长必须为20分钟。

我尝试使用自连接，但是对于这种通话会出现重复。

SELECT t1.cal1, t2.cal2,sum(t1.dur) as Total 
from tele t1
INNER JOIN   tele t2
ON (t1.cal1 = t2.cal1 AND t1.cal2 = t2.cal2)
GROUP BY T1.CAL1, T2.CAL2;

cal1       cal2    duration (in Mins)
A           B        5
A           B       10
B           A       10
B           A       25
A           D        1
D           C        2
B           C        4
C           B        9
C           B        7
C           A        2
D           C        5
D           B       10

- Vin

---

我的代码答案是： B A 70 C A 2 A B 30 C B 32 D B 10 B C 4 D C 14 A D 1 - Vin

请参阅为什么我需要提供一个MCVE来展示看似非常简单的SQL查询？，以提供示例数据和所需的预期结果。 - Raymond Nijland

Answer 1

更简单的方法是使用CASE，以便获取col1和col2的唯一组合，然后按每个组合进行分组：

SELECT 
  case when cal1 < cal2 then cal1 else cal2 end col1, 
  case when cal1 < cal2 then cal2 else cal1 end col2, 
  SUM(duration) duration
FROM tele 
GROUP BY col1, col2

查看演示。
结果：

> col1 | col2 | duration
> :--- | :--- | -------:
> A    | B    |       50
> A    | C    |        2
> A    | D    |        1
> B    | C    |       20
> B    | D    |       10
> C    | D    |        7

如果您使用SQLServer，您需要这样做：

SELECT 
  case when cal1 < cal2 then cal1 else cal2 end col1, 
  case when cal1 < cal2 then cal2 else cal1 end col2, 
  SUM(duration) duration
FROM tele 
GROUP BY 
  case when cal1 < cal2 then cal1 else cal2 end, 
  case when cal1 < cal2 then cal2 else cal1 end

或者用其他语句替代CASE语句

- forpas

---

只是一个小问题，如果在“GROUP BY”子句中引用在“SELECT”子句中定义的列，是否符合ANSI标准？ - Steve

需要进行一些小的更正，因为在上面的查询中，我们得到了A B 35和A B 15，以及B C 16和B C 4的重复项。我的意思是，您对B A更改为A B和C B更改为B C时产生了重复。 - Vin

@Vin 不需要。请查看演示：https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=1123bceff93ca51a821c4135c2f4fc3f - forpas

@forpas：我正在SQL中运行相同的查询。不确定如何，但我得到了8行结果。 - Vin

完美的@forpas 选择 case when cal1 < cal2 then cal1 else cal2 end col1, case when cal1 < cal2 then cal2 else cal1 end col2, SUM(duration) duration 从tele中 分组 case when cal1 < cal2 then cal1 else cal2 end, case when cal1 < cal2 then cal2 else cal1 end这个有效！ 非常感谢 :) - Vin

如果这个有效，您可以考虑接受这个答案。 - forpas

Answer 2

你需要在子查询中动态设置列，并再次对其结果进行分组以实现所需的结果：

SELECT 
  col1, col2, SUM(duration) 
FROM 
  (
    SELECT 
      IF(cal1 < cal2, cal1, cal2) AS col1, 
      IF(cal1 < cal2, cal2, cal1) AS col2, 
      SUM(duration) AS duration
    FROM 
      tel 
    GROUP BY 
      cal1, 
      cal2
  ) AS m 
GROUP BY 
  col1, 
  col2

- Miroslav Glamuzina

---

标记为 SQL 的问题的答案应使用 ISO/IEC 标准 SQL。IF() 不是有效的 ANSI/ISO SQL 标准。这似乎是 MySQL/MariaDB 语法。 - Raymond Nijland

是的，Raymond，你说得对。 @Miroslav - 这段代码不会起作用。如果你找到了正确的代码，请告诉我。 - Vin

内部的 GROUP BY 和 SUM 似乎是不必要的 - 所有聚合都可以由外部查询完成。 - Steve

"内部的GROUP BY和SUM似乎是不必要的 - 所有聚合可以由外部查询完成。"确实，@Steve一个好的优化器也会看到这一点，并且重新编写SQL而不使用子查询。" - Raymond Nijland

Answer 3

如果A到B的呼叫等同于B到A的呼叫，那么创建一个包含双方名称的组合列可能会有所帮助 - 例如AB - 其中组合中名称的顺序由字符串的排序顺序确定。

例如：CASE WHEN A < B THEN A + B ELSE B + A END AS party_names。

这将确保从A到B和从B到A的所有呼叫都会产生一个名为AB的party_names。

然后，您可以按该列进行分组并选择，并对持续时间进行求和。

- Steve

---

标记为SQL的问题的答案应使用ISO/IEC标准SQL。IIF()不是有效的ANSI/ISO SQL标准。 - Raymond Nijland

Raymond说得很有道理。可以用一个CASE语句来替代它。希望原帖作者能够理解这种方法的思路。 - Steve

你需要知道的是，SQL Server（我假设）IIF(A < B, A + B, B + A)会被重写为内部的CASE WHEN A < B THEN A + B ELSE B + A END，这是ANSI/ISO SQL标准。 - Raymond Nijland

确实。我已经编辑了我的帖子以反映这一点并纠正错误。回过头来说，我认为Miroslav的方法在这种情况下更优秀（尽管我注意到他同样在ANSI语法上犯了错误！）。 - Steve

Answer 4

大多数数据库都支持least()和greatest()函数：

select least(t.cal1, t2.cal2), greatest(t.cal1, t2.cal2),
       sum(t1.dur) as Total 
from tele t1 
group by least(t.cal1, t2.cal2), greatest(t.cal1, t2.cal2);

对于那些不支持的数据库，你可以使用case表达式替代。

- Gordon Linoff