SQL-Abfrage .. ein wenig Hilfe mit AVG und MEDIAN mit DISTINCT und SUM

Question

Ich habe eine Abfrage, um die Gesamtdauer der Telefonnutzung für verschiedene Benutzer zu erhalten...

Aber ich muss in der Lage sein, eindeutige Durchschnittswerte für ihre Nutzung zu ermitteln. Das Problem ist, dass bestimmte Benutzer Telefone gemeinsam nutzen und ich nur Telefoninformationen abrufen kann, sodass die Anrufdauer wiederholt wird und dies die Daten verzerren würde.

Ich benötige also einen Durchschnittswert und einen eindeutigen Wert (für das Feld pin.Number)... es wäre auch nützlich, einen Median zu berechnen, wenn das möglich ist...?

Dies ist die aktuelle Abfrage...

SELECT TOP 40 SUM(Duration) AS TotalDuration, c.Caller, oin.Name, oin.Email, pin.Number, oin.PRN 
FROM Calls as c 
INNER JOIN Phones as pin On c.caller = pin.id 
INNER JOIN officers as oin On pin.id = oin.fk_phones 
WHERE Duration <> 0 AND Placed BETWEEN '01/07/2011 00:00:00' AND '20/08/2011 23:59:59' 
GROUP BY c.Caller, oin.Name, pin.Number, oin.Email, oin.PRN 
ORDER BY TotalDuration DESC  

Vielen Dank für alle Hinweise

Hier ist ein Beispiel für die aktuellen Daten, die ich bin nach (aber ich habe die Durchschnitte unten hinzugefügt, die ist, was ich bin nach), wie Sie sehen können einige Benutzer teilen das gleiche Telefon, aber die Anzahl der Sekunden ist zwischen ihnen geteilt, so wollen nicht, dass der Durchschnitt beeinflussen (ich will nicht 11113 Sekunden wiederholt), so dass es eine unterschiedliche auf jeder Telefonnummer sein muss.

Answer 1

Hier ist eine Lösung, die die folgende Idee umsetzt:

1. Siehe Summen pro Telefon ( SUM(Duration) ).

2. Rang die Ergebnismenge durch die Gesamtdauerwerte ( ROW_NUMBEROVER (ORDER BY SUM(Duration)) ).

3. Fügen Sie eine weitere Spalte für die Gesamtzahl der Zeilen ein ( COUNT(*)OVER () ).

4. Ermitteln Sie aus der resultierenden Menge den Durchschnitt ( AVG(TotalDuration) ).

5. Ermitteln Sie den Median als Durchschnitt zwischen zwei Werten, deren Rangfolge

   1) N div 2 + 1 ,

   2) N div 2 + N mod 2 ,

   donde N ist die Anzahl der Artikel, div ist der ganzzahlige Divisionsoperator, und mod ist der Modulo-Operator.

Mein Testtisch:

DECLARE @Calls TABLE (Caller int, Duration int);
INSERT INTO @Calls (Caller, Duration)
SELECT 3, 123 UNION ALL
SELECT 1,  23 UNION ALL
SELECT 2,  15 UNION ALL
SELECT 1, 943 UNION ALL
SELECT 3, 326 UNION ALL
SELECT 3,  74 UNION ALL
SELECT 9,  49 UNION ALL
SELECT 5,  66 UNION ALL
SELECT 4,  56 UNION ALL
SELECT 4, 208 UNION ALL
SELECT 4, 112 UNION ALL
SELECT 5, 521 UNION ALL
SELECT 6, 197 UNION ALL
SELECT 8,  23 UNION ALL
SELECT 7,  22 UNION ALL
SELECT 1,  24 UNION ALL
SELECT 0,  45;

Die Anfrage:

WITH totals AS (
  SELECT
    Caller,
    TotalDuration = SUM(Duration),
    rn = ROW_NUMBER() OVER (ORDER BY SUM(Duration)),
    N = COUNT(*) OVER ()
  FROM @Calls
  GROUP BY Caller
)
SELECT
  Average = AVG(TotalDuration),
  Median = AVG(CASE WHEN rn IN (N / 2 + 1, N / 2 + N % 2) THEN TotalDuration END)
FROM totals

Das Ergebnis:

Average     Median
----------- -----------
282         123

Hinweis: In Transact-SQL, / steht für Ganzzahldivision, wenn beide Operanden ganzzahlig sind. Der Modulo-Operator in T-SQL lautet % .

Answer 2

Ich hoffe, Sie können das verwenden, ich habe es mit temporären Tabellen gemacht

declare @calls table (number char(4), duration int)
declare @officers table(number char(4), name varchar(10))

insert @calls values (3321,1)
insert @calls values (3321,1)
insert @calls values (3321,1)
insert @calls values (3321,42309)

insert @calls values (1235,34555)
insert @calls values (2979,31133)
insert @calls values (2324,24442)
insert @calls values (2345,11113)
insert @calls values (3422,9922)
insert @calls values (3214,8333)

insert @officers values(3321, 'Peter')
insert @officers values(1235, 'Stewie')
insert @officers values(2979, 'Lois')
insert @officers values(2324, 'Brian')
insert @officers values(2345, 'Chris')
insert @officers values(2345, 'Peter')
insert @officers values(3422, 'Frank')
insert @officers values(3214, 'John')
insert @officers values(3214, 'Mark')

Sql zum Ermitteln von Median und Durchschnitt

;with a as 
(
select sum(duration) total_duration, number from @calls group by number
)
select avg(a.total_duration) avg_duration, c.total_duration median_duration from a
cross join (
select top 1 total_duration from (
select top 50 percent total_duration from a order by total_duration desc) b order by
total_duration) c
group by c.total_duration

Versuchen Sie es hier: https://data.stackexchange.com/stackoverflow/q/108612/

Sql Um die Gesamtzeiträume zu erhalten

select o.name, c.total_duration, c.number from @officers o join
(select sum(duration) total_duration, number from @calls group by number) c
on o.number = c.number
order by total_duration desc

Versuchen Sie es hier: https://data.stackexchange.com/stackoverflow/q/108611/