Блог

У меня есть таблица с образцами данных; table

 email_id | sender_id  | received_id |email_cnt
 1      | 101        |  102        | t1
 2      | 101        |  103        |  t2
 3      | 102        |  103        |  t3
 4      | 103        |  104        |  t4
  

Вот email_id уникальный идентификатор. Цель состоит в том, чтобы найти cnt электронной почты для каждого пользователя. Как мы можем видеть, всего email_cnt t1 t2 t3 t4. , и у нас есть 4 уникальных пользователя. Следовательно, в конце концов, мы должны быть в состоянии получить (t1 t2 t3 t4)/4 в качестве ответа.

Моя идея состоит в том, чтобы сначала определить вызывающего абонента, который также является получателем.

 SELECT tab1.receiver_id,
tab1.email_cnt AS received,
 tab2.sender_id,
 tab2.email_cnt AS made
FROM table tab1
JOIN table tab2
ON tab1.receiver_id=tab2.caller_id;
  

Это дает следующий результат;

 receiver_id | received     |sender_id | made
   102      |     t1        |   102   | t3
   103      |    t2         |   103   | t4
   103      |    t3         |  103    | t4
  

Проблема в том, что значение t4 повторяется из-за объединения здесь. Позже это может привести к чрезмерному подсчету email_cnt. Могу ли я любезно получить информацию о том, как решить эту проблему? спасибо

Это кажется немного сложным. Но вы можете сделать:

 select max(total_duration) * 1.0 / count(distinct v.id)
from (select t.*, sum(call_duration) over () as total_duration
      from t
     ) t cross join lateral
     (values (caller_id), (receiver_id)) v(id);
  

Вот скрипка db<> .

Это может сработать, но вам может потребоваться выполнить sum(Made) / count(distinct caller_ID), чтобы получить среднее значение для вызывающего абонента

здесь было бы полезно левое соединение и использование следующих агрегатов sum amp; count

 SELECT tab1.receiver_id,
       --tab1.call_duration AS received,
       sum(tab2.call_duration) / count(distinct tab2.caller_id) as Made
FROM table tab1
left JOIN table tab2
ON tab1.receiver_id=tab2.caller_id
group by
      tab1.receiver_id,
      tab1.call_duration;
  

это также приведет к появлению дополнительной строки, но длительность комментария составит 1 результат для каждого клиента

Возможно, я неправильно истолковываю, но кажется, что и получатель, и вызывающий абонент являются клиентами, и вы хотели бы, чтобы клиент вызывал индивидуальную длину вызова независимо от того, является ли он получателем или вызывающим абонентом?

Я предлагаю использовать объединение, которое удалит повторяющиеся вызовы, т.Е. Будет работать с уникальными вызовами для каждого клиента, а затем вы сможете усреднить продолжительность

 WITH CTE AS (  
SELECT 
call_id,
receiver_id as customer_id, 
call_duration

FROM table 

UNION 

SELECT 
call_id, 
caller_id as customer_id, 
call_duration

FROM table
) 

SELECT 
AVG(call_duration) AS AVG_CallDuration, 
COUNT(call_id) AS TotalCalls, 
customer_id 

FROM CTE

GROUP BY customer_id
  

Чтобы вычислить среднее время, затраченное на одного клиента, вы можете

 select sum(call_duration) / count(distinct receiver_id)
from yourtable;
  

Если я неправильно понял проблему, пожалуйста, дайте мне знать.

Редактировать

Оказывается, что вызывающий и получатель оба являются клиентами. Итак

 select sum(call_duration) / (count(distinct receiver_id)   count(distinct caller_id) - (select count(distinct mt1.caller_id) from mytable mt1 join mytable mt2 on mt1.caller_id = mt2.receiver_id))
from mytable;