sql - 有没有办法在 RDBMS 中为每个呼叫客户的 CDR（呼叫数据记录）编写 sql 查询。？

Question

这是我遇到的一个有趣的问题。我有一张桌子

create table phonebook(
    customer_id (varchar 30)
    call_start_day (date)
    call_end_day (date)
    call_start_time (varchar 30)
    call_end_time (varchar 30)
    called_number (integer)
    customer_call_location_city (varchar 30)
    customer_call_location_state (varchar 30)
    customer_call_location_zip (integer)
    called_number_city (varchar 30)
    called_number_state (varchar 30)
    )

我需要根据过去 12 个月的平均每月 call_duration 找到前 n 个客户。（仅考虑完整的月份，例如，如果当前日期是 2021-03-07，则过去 12 个月：2020-03-01 – 2021-02-28。每月通话时长 = 总和（通话时长），其中通话时长 = 时差call_end_day+ call_end_time 和 call_start_day+call_start_time)

解决方案-我想找出时间，然后找出他们的平均值。有什么建议么？

标签： sqlsql-serverdatetimeaggregate-functions

Answer 1

在 SQL Server 中，首先计算出给出的表达式

• 您要考虑的第一个日期。
• 您要考虑的最后日期后的第二天。

EOMONTH () 对此有好处。

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(),-1)) day_after_last,
       DATEADD(DAY, 1, EOMONTH(GETDATE(), -13)) first

接下来，弄清楚如何获取通话时长。您还没有告诉我们您如何表示您的call_start_time和call_end_time，因此我们无法帮助您提供详细信息。因此，我将持续时间计算表示为存储函数。由您决定如何获得持续时间。可能以秒为单位的持续时间是获取呼叫详细信息记录的最佳方式。

专业提示永远，永远不要使用 varchars 或 chars 来表示 DATE、TIME 或 DATETIME 数据。他们总是导致混乱和悲伤。

这是要走的路。

SELECT TOP (5) customer_id, 
       AVG(dbo.duration(call_start_day, call_start_time, 
                        call_end_day, call_end_time)) average
  FROM phonebook
 WHERE call_start_day >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -13))
   AND call_start_day <  DATEADD(DAY, 1, EOMONTH(GETDATE(),-1))
 GROUP BY customer_id
 ORDER BY 2 DESC;

该WHERE子句仅提取您关心的日期的记录。

该ORDER BY 2 DESC子句表示按结果集的第二列从高到低排序。子句表示显示有序结果集的TOP (5)前五行。

编辑

如果您的时间列看起来像，08:47:13那么您可以使用DATEDIFF () 计算持续时间。看起来像这样

DATEDIFF(
   second, 
   CAST(call_start_day AS DATETIME) + CAST(call_start_time AS DATETIME),
   CAST(call_end_day AS DATETIME) + CAST(call_end_time AS DATETIME))

所以你的查询看起来像这样：

SELECT TOP (5) customer_id, 
       AVG(
        DATEDIFF(
          second, 
            CAST(call_start_day AS DATETIME) + 
            CAST(call_start_time AS DATETIME),
            CAST(call_end_day AS DATETIME) + 
            CAST(call_end_time AS DATETIME))) average
  FROM phonebook
 WHERE call_start_day >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -13))
   AND call_start_day <  DATEADD(DAY, 1, EOMONTH(GETDATE(),-1))
 GROUP BY customer_id
 ORDER BY 2 DESC;