使用T查询创建频率分布

Question

我有一个具有如下数据的表delay

Delay
-----
-15
139
81
-3
-17
159
8
-128
169
-28
98
-62
-51
261
-172
217
65
-91
-22
170
50
5
213
60
-23
27
-45
-23
389
244
-82
11
92
30
-42
-34
-151
150
-57
-148
313
-36
1
50
2
-111
-63

我想使用查询创建一个类似这样的表

Range            Values    Frequency
------------------------------------
On time              0       407
Late < 1 min        60       159
Late by 1-2 min    120        80
Late by 2-3 min    180        71
Late by 3-4 min    240        35
Late by 4-5 min    300        19
Late by 5-6 min    360         8
Late by 6-7 min    420         9
Late by 7-8 min    480         4
Late >8 minutes     14

我搜索给定的问题。但是，有一些答案与表中类似值的计数有关，但没有一个对数据进行分类。

请帮帮忙

Answer 1

SQL演示

with ranges as (
    SELECT 'On time' r UNION ALL
    SELECT 'Late < 1 min' UNION ALL
    SELECT 'Late by 1-2 min' UNION ALL
    SELECT 'Late by 2-3 min' UNION ALL
    SELECT 'Late by 3-4 min' UNION ALL
    SELECT 'Late by 4-5 min' UNION ALL
    SELECT 'Late by 5-6 min' UNION ALL
    SELECT 'Late by 6-7 min' UNION ALL
    SELECT 'Late by 7-8 min' UNION ALL
    SELECT 'Late >8 minutes'
), setup as (    
    SELECT CASE WHEN [Delay] = 0 THEN 'On time'
                WHEN [Delay] < 1 THEN 'Late < 1 min'
                WHEN [Delay] < 2 THEN 'Late by 1-2 min'
                WHEN [Delay] < 3 THEN 'Late by 2-3 min'
                WHEN [Delay] < 4 THEN 'Late by 3-4 min'
                WHEN [Delay] < 5 THEN 'Late by 4-5 min'
                WHEN [Delay] < 6 THEN 'Late by 5-6 min'
                WHEN [Delay] < 7 THEN 'Late by 6-7 min'            
                WHEN [Delay] < 8 THEN 'Late by 7-8 min'         
                ELSE 'Late >8 minutes'
           END as [Range],
           [Delay]
    FROM Table1       
)
SELECT t.r as Range, COALESCE(SUM(s.[Delay]),0) as Value, COUNT(s.[Delay]) as Freq
FROM ranges t
LEFT JOIN setup  s
  ON t.r = s.[Range]
GROUP BY t.r

输出

​

​

Answer 2

这个问题可以用CASE或IIF来解决，但我建议使用函数来提高代码的可读性。而且可缩放..。

用户函数- ufRange

CREATE FUNCTION dbo.ufRange (@Delay INT)
RETURNS INT
AS
BEGIN
    DECLARE @Ret INT
    IF @Delay <= 0 SET @Ret = 0         -- not late/ early
    ELSE IF @Delay > 320 SET @Ret = 9  -- more than 8 minutes late
    ELSE SET @Ret = @Delay / 60         -- late <=1 to <=8 minutes
    RETURN @Ret
END

然后将range的详细信息放在支持表LateRange中，就像在excel中一样，如果您熟悉使用vlookup实现这个功能的话

支持表- LateRange

idrange          Range                               Values
  0               On time                               0
  1               Late < 1 min                          60
  2               Late by 1-2 min                       120
  3               Late by 2-3 min                       180 
  4               Late by 3-4 min                       240
  5               Late by 4-5 min                       300
  6               Late by 5-6 min                       360
  7               Late by 6-7 min                       420
  8               Late by 7-8 min                       480
  9               Late >8 minutes 

好的。最后，假设delay保存的表名为LateList，查询如下

SELECT Range, Values, 
      (SELECT COUNT(*) FROM LateList WHERE dbo.ufRange([delay]) = idrange) Frequency
FROM LateRange 

或者，如果您愿意的话，可以使用INNER JOIN和子查询进行修改。