根据值将数据放入类别(桶)
根据值将数据放入类别(桶)
提问于 2013-10-23 01:12:14
如何根据值分配类别？
例如，我有一个表，其值为1-200。如何为每个记录指定一个类别，如1-5、6-10、11-15等。
我可以使用下面的方法来完成，但这似乎是一个糟糕的解决方案。
对不起，这可能是非常基本的，但我不知道它是什么，谷歌桶(在我们的公司称为)没有任何结果。
谢谢
SELECT DISTINCT CountOfSA,
CASE
WHEN CountOfSA BETWEEN 1 AND 5 THEN
'1-5'
WHEN CountOfSA BETWEEN 6 AND 10 THEN
'6-10'
WHEN CountOfSA BETWEEN 11 AND 15 THEN
'11-15'
WHEN CountOfSA BETWEEN 16 AND 20 THEN
'16-20'
WHEN CountOfSA BETWEEN 21 AND 25 THEN
'21-25'
WHEN CountOfSA BETWEEN 26 AND 30 THEN
'26-30'
END
AS diff
FROM NR_CF_212回答 3
Stack Overflow用户
回答已采纳
发布于 2013-10-23 05:15:50
看看桶，桶函数。这将范围划分为大小相等的间隔，并为每个间隔分配一个桶号。
with x as (
select CountOfSA,
width_bucket(CountOfSA, 1, 200, 40) bucket_
from NR_CF_212
)
select CountOfSA,
cast(1 + (bucket_ - 1)*5 as varchar2(4)) ||
'-' ||
cast( bucket_*5 as varchar2(4)) diff
from x
order by CountOfSA;演示这里。
Stack Overflow用户
发布于 2013-10-23 01:39:38
我想您可以使用连接在一起的一些计算值来动态创建桶名：
select countofsa
, ((countofsa - 1)/5) * 5 + 1
, ((countofsa - 1)/5 + 1) * 5
, ((countofsa - 1)/5) * 5 + 1 || '-' || ((countofsa - 1)/5 + 1) * 5 AS diff
from nr_cf_212一些产出：
countofsa | ?column? | ?column? | diff
-----------+----------+----------+-------
1 | 1 | 5 | 1-5
2 | 1 | 5 | 1-5
3 | 1 | 5 | 1-5
4 | 1 | 5 | 1-5
5 | 1 | 5 | 1-5
6 | 6 | 10 | 6-10
7 | 6 | 10 | 6-10
8 | 6 | 10 | 6-10
9 | 6 | 10 | 6-10
10 | 6 | 10 | 6-10
11 | 11 | 15 | 11-15
(11 rows)从注释中更新，Oracle示例，动态计算范围：
create table nr_cf_212(countofsa number);
insert into nr_cf_212 values(1);
insert into nr_cf_212 values(2);
insert into nr_cf_212 values(3);
insert into nr_cf_212 values(4);
insert into nr_cf_212 values(5);
insert into nr_cf_212 values(6);
insert into nr_cf_212 values(7);
insert into nr_cf_212 values(9);
insert into nr_cf_212 values(10);
insert into nr_cf_212 values(11);
select countofsa
, TRUNC((countofsa - 1)/5) * 5 + 1
, (TRUNC((countofsa - 1)/5) + 1) * 5
, TRUNC((countofsa - 1)/5) * 5 + 1 || '-' || (TRUNC((countofsa - 1)/5) + 1) * 5 AS diff
from nr_cf_212;
| COUNTOFSA | TRUNC((COUNTOFSA-1)/5)*5+1 | (TRUNC((COUNTOFSA-1)/5)+1)*5 | DIFF |
|-----------|----------------------------|------------------------------|-------|
| 1 | 1 | 5 | 1-5 |
| 2 | 1 | 5 | 1-5 |
| 3 | 1 | 5 | 1-5 |
| 4 | 1 | 5 | 1-5 |
| 5 | 1 | 5 | 1-5 |
| 6 | 6 | 10 | 6-10 |
| 7 | 6 | 10 | 6-10 |
| 9 | 6 | 10 | 6-10 |
| 10 | 6 | 10 | 6-10 |
| 11 | 11 | 15 | 11-15 |我用sqlfiddle (http://sqlfiddle.com/#!4/b922e/4)试了一下。
我把它分成几个部分来显示"from“列，" to”列，然后显示范围。如果你把你的数字除以5，看看商数和剩余数，你会看到一个模式：
1/5 = 0 remainder 1
2/5 = 0 remainder 2
3/5 = 0 remainder 3
4/5 = 0 remainder 4
5/5 = 1 remainder 0
6/5 = 1 remainder 1
7/5 = 1 remainder 2
8/5 = 1 remainder 3
9/5 = 1 remainder 4
10/5 = 2 remainder 0
11/5 = 2 remainder 1这个数字的范围是从商的5倍到商数的5倍加上剩余的-almost。实际上，所有的东西都被1抵消了。所以取你的数字，减去1，然后进行除法。
Stack Overflow用户
发布于 2013-10-23 13:50:25
我会将范围值和描述放在一个单独的表中，特别是如果您计划将其用于将来的查询、视图等。此外，还可以根据需要更改范围或描述。例如：
create table sales_ranges
(
low_val number not null,
high_val number not null,
range_desc varchar2(100) not null
)
cache;
insert into sales_ranges values (0,1000,'$0-$1k');
insert into sales_ranges values (1001,10000,'$1k-$10k');
insert into sales_ranges values (10001,100000,'$10k-$100k');
insert into sales_ranges values (100001,1000000,'$100k-$1mm');
insert into sales_ranges values (1000001,10000000,'$1mm-$10mm');
insert into sales_ranges values (10000001,100000000,'$10mm-$100mm');
commit;
create table sales
(
id number,
total_sales number
);
insert into sales(id, total_sales)
-- some random values for testing
select level, trunc(dbms_random.value(1,10000000))
from dual
connect by level <= 100;
commit;
select id, total_sales, range_desc
from sales s
left outer join sales_ranges sr
on (s.total_sales between sr.low_val and sr.high_val)
order by s.id
;输出(仅前3行)：
ID TOTAL_SALES RANGE_DESC
1 5122380 $1mm-$10mm
2 347726 $100k-$1mm
3 6564700 $1mm-$10mm页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19531059
复制相关文章
相似问题
腾讯云开发者
