查询所有用户一天内平均通话时长

Question

一个人每天使用手机多次，通话时间也不同。我正在用表格来跟踪通话时间：

Calls [callID, memberID, startTime, duration]

我需要一条查询语句返回每个用户每天的平均通话时长。每天的意思是，如果一个用户使用电话3次，第一次5分钟，第二次10分钟，最后一次7分钟，则计算方式为：5 + 10 + 7 / 3 = ...。

注意： 1. 每个人不会每天都用手机，因此我们必须获取每个人的最新一天的平均值，并使用它来得到总的平均通话时长。 2. 我们不想重复计算某个人的通话时长，因此仅有每个用户的一行将用于计算每日平均通话时长。

一些澄清： 我需要一个总体的每日平均值，基于每个用户每天的平均值，使用用户最新一天的数据（由于我们在查询中仅计算给定用户一次），因此这意味着我们将使用不同的天数平均值，因为人们可能不会每天或在同一天使用电话。

- Anonymous Cow

---

我不确定我是否理解了您的笔记。整体平均值如何取决于“最新日期”？对于几个不同的人，您想要的结果会是什么样子？ - Tomalak

我们不希望在同一查询中重复计算人数，因此每个用户只有一个平均值会计入全局平均值。 - Anonymous Cow

我想要一个每天的总体平均值，基于每个用户每天的平均值，使用用户最近几天的数字（因为我们在查询中仅计算给定用户一次），这意味着我们将使用不同的天数平均值，因为人们可能不会每天都使用手机。 - Anonymous Cow

你是不是只是把我的SQL代码复制到你的答案里，然后用"SELECT AVG"包装了一下？哈哈 - Tomalak

那如果一通电话从午夜前4分钟开始，到午夜过后6分钟结束呢？应该如何计算？ - Jonathan Leffler

Answer 1

你需要将DATETIME转换成可以按天分组的格式，这样会产生“年/月/日”的结果。

SELECT
  memberId,
  CONVERT(VARCHAR, startTime, 102) Day,
  AVG(Duration) AvgDuration
FROM
  Calls
WHERE
  CONVERT(VARCHAR, startTime, 102) = 
  (
    SELECT 
      CONVERT(VARCHAR, MAX(startTime), 102) 
    FROM 
      Calls i WHERE i.memberId = Calls.memberId
  )
GROUP BY
   memberId,
   CONVERT(VARCHAR, startTime, 102)

使用 LEFT(CONVERT(VARCHAR, startTime, 120), 10) 来产生 "yyyy-mm-dd"。

对于这类查询，拥有一个专门的“仅日期”列会很有帮助，可以避免整个转换过程，并作为副作用使查询更易读。

- Tomalak

---

它正在返回每个成员的多行数据，我只需要最新的一行（仅一行，且为最新的一行）。 - Anonymous Cow

然后，您只需要按日期字段（CONVERT(VARCHAR, startTime, 102)）进行分组。 - matt b

但这样一来，“每个成员”的平均值也会消失。 - Tomalak

您的查询中存在语法错误，因为没有名为“member”的表，并且它没有得到用户要求的总体平均值。 - Mitchel Sellers

“我们必须获得每个人最新一天的平均值。”我想发帖者能够简单地在周围放置SELECT AVG。你是对的，也没有叫做“member”的表，干得好。;-) - Tomalak

Tomalak，你可能需要修改你的条目来纠正脚本错误。 - Mitchel Sellers

Answer 2

以下查询将为您获取所需的最终结果。

SELECT AVG(rt.UserDuration) AS AveragePerDay
FROM
(
    SELECT
        c1.MemberId,
        AVG(c1.Duration) AS "UserDuration"
    FROM Calls c1
    WHERE CONVERT(VARCHAR, c1.StartTime, 102) =
        (SELECT CONVERT(VARCHAR, MAX(c2.StartTime), 102)
            FROM Calls c2
            WHERE c2.MemberId = c1.MemberId)
    GROUP By MemberId
) AS rt

这个程序的实现方式是，首先创建一个表格，为最近一天的每个成员创建一个记录，并且记录他们的通话平均时长。然后简单地对所有值求平均，得到最终的"平均通话时长"。如果您想查看特定用户，可以运行内部选择部分来获取成员列表。

- Mitchel Sellers

---

在运行内部查询（在from子句中）时返回：除非它位于包含在HAVING子句或选择列表中的子查询中，并且被聚合的列是外部引用，否则聚合项不得出现在WHERE子句中。 - Anonymous Cow

我刚刚测试了一下，你可以从“SELECT c1.MemberId”到“Group BY MemberId”语句中运行所有内容。 - Mitchel Sellers

希望这是你所需要的！ - Mitchel Sellers

Answer 3

select average(duration) from calls group by date(startTime);

- matt b

---

在SQL Server上，date()不是一个函数。 - Tomalak

该问题最初被标记为mysql。 - matt b