高级SQL选择查询(唯一行)(分组)

Question

我的数据库正在保存我公司打的电话。

我需要做一个select查询，选择所有唯一的人，并为每个人选择：

• 入站呼叫次数
• 出站呼叫数
• 电话总数
• 平均呼叫时间

我试了一下，在选择入站和出站电话时被困住了。

我将解释如何查看呼叫何时入站或出站。

当OriginationDevice被填充时，它对DestinationName的入站调用。

当DestinationDevice被填充时，它对OriginationName的出站调用。

我需要每一个唯一的DestinationName或OriginationName，列出入站、出站和总呼叫的数量，当然还有平均呼叫时间。

我已经走了很远，但我似乎无法在一个查询中获得入站和出站。

看看我的SQL Fiddle，在那里你可以玩来帮助我！

有人能帮我得到平均呼叫时间，出站呼叫，入站呼叫和每个人的总呼叫吗？

我的查询尝试：

SELECT * FROM (
   SELECT 
   IFNULL (SUM( CASE WHEN OriginationDevice != '' AND ConnectTime != '' THEN 
   DATEDIFF(ConnectTime, EndTime) ELSE null END ) /  COUNT(case when 
   OriginationDevice <> '' then 1 else null end), 0) as calltime,
   COUNT(case when OriginationDevice != '' then 1 else null end) as inbound,
   COUNT(case when DestinationDevice != '' AND OriginationDevice = '' then 1 
   else null end) as outbound,
   COUNT(*) as total,
   DestinationName

   FROM calls WHERE (YEAR(EndTime) = 2018 AND MONTH(EndTime) = 12) and 
   (OriginationDevice != '' or DestinationDevice != '')
   AND ConnectTime != ''  GROUP BY DestinationName
) as t1
WHERE total > 0  ORDER BY total DESC, calltime

这是sql表：

CREATE TABLE IF NOT EXISTS `calls` (
  `OriginationName` varchar(200) NOT NULL,
  `DestinationName` varchar(200) NOT NULL,
  `ConnectTime` DATETIME NOT NULL,
  `EndTime` DATETIME NOT NULL,
  `OriginationDevice` varchar(200) NOT NULL,
  `DestinationDevice` varchar(200) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `calls` (`OriginationName`, `DestinationName`, `ConnectTime`, `EndTime`, `OriginationDevice`, `DestinationDevice`) VALUES
  ('Person 1', 'Person 5', '2018-12-11 11:26:12', '2018-12-11 11:26:18', '243', '(call routing)'),
  ('Person 2', 'Person 3', '2018-12-11 10:16:12', '2018-12-11 10:16:54', '', '(call routing)'),
  ('Person 5', 'Person 1', '2018-12-11 10:21:12', '2018-12-11 10:22:22', '', ''),
   ('Person 2', 'Person 1', '2018-12-11 11:26:12', '2018-12-11 11:26:52', '233', ''),
    ('Person 1', 'Person 4', '2018-12-11 12:26:12', '2018-12-11 12:28:25', '456', ''),
     ('', 'Person 1', '2018-12-11 14:56:12', '2018-12-11 14:57:24', '', '(call routing)'),
  ('Person 3', '', '2018-12-11 15:26:12', '2018-12-11 15:26:37', '223', '');

我的预期结果在查询中。

Answer 1

这个人应该按你的要求去做：

 SELECT 
    Person,
    SUM(case when typology = 'outbounds' then calls_number else 0 end) as outbounds,
    SUM(case when typology = 'inbounds' then calls_number else 0 end) as inbounds,
    SUM(calls_number) as calls_number,
    case when SUM(calls) = 0 then 0 else SUM(callTime) / SUM(calls) end as avgCallTime
FROM(
SELECT 
    OriginationName as Person,
    SUM(case 
        when DestinationDevice != '' and ConnectTime != '' then (EndTime - ConnectTime) 
        else 0 end
    ) as callTime,
    SUM(case when DestinationDevice != '' then 1 else 0 end) as calls_number,
    'outbounds' as typology
FROM calls 
WHERE (YEAR(EndTime) = 2018 AND MONTH(EndTime) = 12) and ConnectTime != ''
    and OriginationName != ''
GROUP BY OriginationName
    union
SELECT 
    DestinationName,
    SUM(case 
        when OriginationDevice != '' and ConnectTime != '' then (EndTime - ConnectTime) 
        else 0 end
    ),
    SUM(case when OriginationDevice  != ''  then 1 else 0 end),
    'inbounds' as typology
FROM calls 
WHERE (YEAR(EndTime) = 2018 AND MONTH(EndTime) = 12) and ConnectTime != '' 
    and DestinationName != ''
GROUP BY DestinationName) as T
GROUP BY Person;

如果有什么问题，让我知道，这个示例工作https://sqltest.net/#386096