从SQL Server表创建范围区间用于直方图

Question

我在SQL Server中有以下的表：

-----------------------------
ID       Age          Gender
1          30           F
2          35           M
3          32           M
4          18           F
5          21           F

我需要做的是执行一个查询，将记录按照给定的范围分组并计算出现次数。结果需要在直方图（条形图）中显示。我尝试了类似以下的查询：

SELECT 
count(CASE WHEN Age>= 10 AND Age < 20 THEN 1 END) AS '10 - 20',
count(CASE WHEN Age>= 21 AND Age < 30 THEN 1 END) AS '21 - 30',
count(CASE WHEN Age>= 31 AND Age < 35 THEN 1 END) AS '31 - 35',
count(CASE WHEN Age>= 36 AND Age < 40 THEN 1 END) AS '36 - 40',
FROM (SELECT Age FROM Attendees) AS AgeGroups

目前，这个方法可以解决问题，但是没有考虑性别列。它将生成一个单独的行，统计每个年龄组的频率：

10-20     21-30     31-35     36-40
  0         22        21        13

如果考虑性别，应该显示两条记录，分别对应每个性别。我需要看到类似于以下内容：

Gender    10-20     21-30     31-35     36-40
  M         0         12        9         6
  F         0         10        12        7

我应该如何处理这个问题？

- user10901

---

我在这里直接演示了生成直方图的SQL代码：http://stackoverflow.com/questions/16268441/generate-histogram-in-sql-server - Pieter Geerkens

Answer 1

只需在您的SELECT中添加Gender列，然后执行GROUP BY。

SELECT 
Gender,
count(CASE WHEN Age>= 10 AND Age < 20 THEN 1 END) AS [10 - 20],
count(CASE WHEN Age>= 21 AND Age < 30 THEN 1 END) AS [21 - 30],
count(CASE WHEN Age>= 31 AND Age < 35 THEN 1 END) AS [31 - 35],
count(CASE WHEN Age>= 36 AND Age < 40 THEN 1 END) AS [36 - 40]
FROM Attendees AS AgeGroups
GROUP BY Gender

- Lamak

---

我认为你需要使用sum()函数而不是count()函数。否则，每个范围都将等于Attendees表中记录的总数。(顺便说一句 - 我也犯过这个错误多次。) - Todd Meinershagen

@ToddMeinershagen 我猜他的表格每行都有一个唯一的参与者，就像他的示例一样，这种情况下 COUNT 和 SUM 的作用是相同的，因为 SUM 对于每一行都不会得到除 1 以外的值。 - AS91

Answer 2

我最近遇到了类似的问题，需要查看多个变量而不仅仅是一个，我的解决方案是使用临时表。

CREATE TABLE #bin (
    startRange int, 
    endRange int,
    agelabel varchar(10)
);
GO

INSERT INTO #bin (startRange, endRange, mylabel) VALUES (10, 20, '10-20')
INSERT INTO #bin (startRange, endRange, mylabel) VALUES (21, 30, '21-30')
INSERT INTO #bin (startRange, endRange, mylabel) VALUES (31, 35, '31-35')
INSERT INTO #bin (startRange, endRange, mylabel) VALUES (36, 40, '36-40')
GO

SELECT 
    b.agelabel as ageBracket, 
    a.Gender, 
    count(a.Gender) as total
FROM 
    Attendees a
INNER JOIN 
    #bin b on (a.Age >= b.startRange and a.Age <= b.EndRange)
GROUP BY
    b.agelabel, a.Gender

DROP TABLE #bin 
GO

或者，也许更好的解决方案是：

With table1 as
(
SELECT 
    CASE 
        WHEN Age >= 10 and Age <= 20 then '10-20'
        WHEN Age > 20 and Age <= 30 then '21-30'
        WHEN Age > 30 and Age <= 35 then '31-35'
        WHEN Age > 35 and Age <= 40 then '36-40'
        ELSE 'NA'
    End as ageBracket,
    Gender
FROM
    Attendees
)

SELECT
    ageBracket,
    Gender,
    Count(Gender),
FROM
    table1
GROUP BY
    ageBracket, Gender

结果将会是：

AgeBracket Gender Total
10-20 M 0
10-20 F 0
21-30 M 12
21-30 F 10
31-35 M  9
31-35 F 12
36-40 M  6
36-40 F  7

你可以使用第一个选择语句收集你所需的所有数据，同时使用第二个查询执行任何必要的计算。

我认为这些解决方案可能对你的问题有些过度，但是由于这是我找到的唯一有关分箱的问题，希望它对其他人有用！

- David Mendes

Answer 3

上面的示例排除了值20、30、35和40。有多种方法来解决这个问题，这里介绍其中一种：

SELECT 
Gender,
count(CASE WHEN Age> 9 AND Age <= 20 THEN 1 END) AS [10 - 20],
count(CASE WHEN Age> 20 AND Age <= 30 THEN 1 END) AS [21 - 30],
count(CASE WHEN Age> 30 AND Age <= 35 THEN 1 END) AS [31 - 35],
count(CASE WHEN Age> 35 AND Age <= 40 THEN 1 END) AS [36 - 40]
FROM Attendees AS AgeGroups
GROUP BY Gender

- lonniect