SQL 查询..使用 DISTINCT 和 SUM 对 AVG 和 MEDIAN 有一点帮助

Question

我有一个查询来获取不同用户的手机使用总持续时间...

但我需要能够计算出他们的使用情况的不同平均值..问题是某些用户共享手机，而我只能获取手机信息，所以通话持续时间会重复，这会使数据出现偏差。

所以我需要一个平均值和一个不同的值（在 pin.Number 字段上）...如果可能的话，做一个中位数也很有用......？

这是当前的查询...

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  

非常感谢您的任何指示

这是我所追求的当前数据的示例（但我在下面添加了我所追求的平均值），因为您可以看到一些用户共享同一部手机，但是他们之间共享秒数，因此不希望其影响平均值（我不希望重复 11113 秒），因此每个电话号码需要有一个不同的..

Answer 1

这是实现以下想法的解决方案：

1. 获取 每部手机总计 (SUM(Duration))。

2. 排名总持续时间值的结果集 (ROW_NUMBEROVER (ORDER BY SUM(Duration)))。

3. 再添加一列作为总行数 (COUNT(*)OVER ())。

4. 从结果集中获取平均值 (AVG(TotalDuration))。

5. 获取排名为两个值之间的平均值作为中位数

   1) N div 2 + 1,

   2) N div 2 + N mod 2,

   其中 N 是项目数，div 是整数除法运算符，mod 是模运算符。

我的测试表：

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;

查询：

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

输出：

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

注意：在 Transact-SQL 中， / 表示如果两个操作数都是整数，则进行整数除法。 T-SQL 中的模运算符为 <代码>%。

Answer 2

我希望你可以使用这个，我用临时表

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 来获得中位数和平均值

;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

在这里尝试： https://data.stackexchange.com/stackoverflow/q/108612/

Sql 要获取总持续时间，

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

请在此处尝试：https://data.stackexchange.com/stackoverflow/q/108611/