如果我有一个这样的表：
pkey age
---- ---
1 8
2 5
3 12
4 12
5 22
select age,count(*) n from tbl group by age;
age n
--- -
5 1
8 1
12 2
22 1
我可以使用什么查询来按年龄范围分组？
age n
----- -
1-10 2
11-20 2
20+ 1
我在使用10gR2，但我也对任何11g特有的方法感兴趣。
SELECT CASE
WHEN age <= 10 THEN '1-10'
WHEN age <= 20 THEN '11-20'
ELSE '21+'
END AS age,
COUNT(*) AS n
FROM age
GROUP BY CASE
WHEN age <= 10 THEN '1-10'
WHEN age <= 20 THEN '11-20'
ELSE '21+'
END
尝试：
select to_char(floor(age/10) * 10) || '-'
|| to_char(ceil(age/10) * 10 - 1)) as age,
count(*) as n from tbl group by floor(age/10);
ceil(age/10) 在GROUP BY表达式中缺失。但是这种方法的方向更好，正如@NitinMidha所写的那样，所以我投票支持这个答案。 - WintermuteSELECT age, count(*)
FROM tbl
GROUP BY age
FLOOR(age/10)
"FLOOR返回小于或等于n的最大整数" http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions067.htm#SQLRF00643
然后我们用该表达式替换原始的SQL中的age：
SELECT FLOOR(age/10), count(*)
FROM tbl
GROUP BY FLOOR(age/10)
这样是可以的，但我们还不能看到范围。相反，我们只能看到计算出来的底部值，它们是0、1、2 ... n。
要获取实际的下限，我们需要再次将其乘以10，这样我们就得到了0、10、20 ... n：
FLOOR(age/10) * 10
我们还需要每个范围的上限，即下限加10再减1。
FLOOR(age/10) * 10 + 10 - 1
最后，我们将它们连接成一个字符串，像这样：
TO_CHAR(FLOOR(age/10) * 10) || '-' || TO_CHAR(FLOOR(age/10) * 10 + 10 - 1)
这将创建'0-9'，'10-19'，'20-29'等。
现在我们的SQL代码如下：
SELECT
TO_CHAR(FLOOR(age/10) * 10) || ' - ' || TO_CHAR(FLOOR(age/10) * 10 + 10 - 1),
COUNT(*)
FROM tbl
GROUP BY FLOOR(age/10)
最后，应用排序和良好的列别名：
SELECT
TO_CHAR(FLOOR(age/10) * 10) || ' - ' || TO_CHAR(FLOOR(age/10) * 10 + 10 - 1) AS range,
COUNT(*) AS frequency
FROM tbl
GROUP BY FLOOR(age/10)
ORDER BY FLOOR(age/10)
然而，在更复杂的场景中，这些范围可能不会被分组为大小为10的恒定块，而需要进行动态聚类。
Oracle包括更高级的直方图函数，详见http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_histo.htm#TGSQL366
感谢@MatthewFlaschen提供的方法; 我只解释了细节。
SELECT DISTINCT descr
, COUNT(*) OVER (PARTITION BY descr) n
FROM age_table INNER JOIN (
select '1-10' descr, 1 rng_start, 10 rng_stop from dual
union (
select '11-20', 11, 20 from dual
) union (
select '20+', 21, null from dual
)) ON age BETWEEN nvl(rng_start, age) AND nvl(rng_stop, age)
ORDER BY descr;
我需要按照每小时出现的交易次数对数据进行分组。我通过从时间戳中提取小时来实现这一点：
select extract(hour from transaction_time) as hour
,count(*)
from table
where transaction_date='01-jan-2000'
group by
extract(hour from transaction_time)
order by
extract(hour from transaction_time) asc
;
HOUR COUNT(*)
---- --------
1 9199
2 9167
3 9997
4 7218
正如您所看到的，这提供了一种方便易行的方法来按小时分组记录数量。
create table age_range(
age_range_id tinyint unsigned not null primary key,
name varchar(255) not null);
insert into age_range values
(1, '18-24'),(2, '25-34'),(3, '35-44'),(4, '45-54'),(5, '55-64');
// 再次抱歉使用DML，但是您应该明白我的意思
select
count(*) as counter, p.age_range_id, ar.name
from
person p
inner join age_range ar on p.age_range_id = ar.age_range_id
group by
p.age_range_id, ar.name order by counter desc;
如果您愿意，可以完善这个想法 - 在年龄范围表中添加 from_age 和 to_age 列等等 - 但我会把这个留给您。
希望这有所帮助 :)
NTILE分析函数：WITH tiles AS (
SELECT t.age,
NTILE(3) OVER (ORDER BY t.age) AS tile
FROM TABLE t)
SELECT MIN(t.age) AS min_age,
MAX(t.age) AS max_age,
COUNT(t.tile) As n
FROM tiles t
GROUP BY t.tile
SELECT CASE t.age
WHEN BETWEEN 1 AND 10 THEN '1-10'
WHEN BETWEEN 11 AND 20 THEN '11-20'
ELSE '21+'
END AS age,
COUNT(*) AS n
FROM TABLE t
GROUP BY CASE t.age
WHEN BETWEEN 1 AND 10 THEN '1-10'
WHEN BETWEEN 11 AND 20 THEN '11-20'
ELSE '21+'
END
SELECT count (1), '1-10' where age between 1 and 10
union all
SELECT count (1), '11-20' where age between 11 and 20
union all
select count (1), '21+' where age >20
from age
我需要按天计算样本数量。受@Clarkey的启发，我使用TO_CHAR将采样时间戳中的日期提取到ISO-8601日期格式中，并在GROUP BY和ORDER BY子句中使用它。(受其启发，我也在这里发布它，以便其他人可以使用。)
SELECT
TO_CHAR(X.TS_TIMESTAMP, 'YYYY-MM-DD') AS TS_DAY,
COUNT(*)
FROM
TABLE X
GROUP BY
TO_CHAR(X.TS_TIMESTAMP, 'YYYY-MM-DD')
ORDER BY
TO_CHAR(X.TS_TIMESTAMP, 'YYYY-MM-DD') ASC
/
我的方法：
select range, count(1) from (
select case
when age < 5 then '0-4'
when age < 10 then '5-9'
when age < 15 then '10-14'
when age < 20 then '15-20'
when age < 30 then '21-30'
when age < 40 then '31-40'
when age < 50 then '41-50'
else '51+'
end
as range from
(select round(extract(day from feedback_update_time - feedback_time), 1) as age
from txn_history
) ) group by range