有没有一种方法可以在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个月的平均每月通话时间找到排名前n的客户。(仅考虑完整的月份，例如，如果当前日期为2021-03-07，则最后12个月：2020-03-01–2021-02-28。每月通话持续时间=总和(通话持续时间(，其中通话持续时间=call_end_day+call_end_time和call_start_day+ccall_start_time的时差(

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

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;