--1. 构建测试表test id 范围为 0 -- 178
SQL> create table test (id) as select trunc(dbms_random.value(0,179)) from dual connect by level < 10000 ;Table created.
--2. 构建范围表range_
SQL> create table range_ ( begin_ number , end_ number , id number) ;Table created.SQL> insert into range_ values (0,27,1) ;1 row created.SQL> insert into range_ values (27,48,2) ;1 row created.SQL> insert into range_ values (48,96,3) ;1 row created.SQL> insert into range_ values (96,111,4) ;1 row created.SQL> insert into range_ values (111,178,5) ;1 row created.SQL> commit ;Commit complete.SQL> select * from range_ order by id ; BEGIN_ END_ ID
---------- ---------- ----------
0 27 1
27 48 2
48 96 3
96 111 4
111 178 5--目标 根据 范围表 range_ 的列 begin_ end_ 统计 test 表中的数据分布情况
--目标结果集举例: BEGIN_ END_ ID COUNT
---------- ---------- ---------- -------
0 27 1 2000
27 48 2 1999
48 96 3 2001
96 111 4 1888
111 178 5 2111
解决方案 »
- oracle分页查询时,这么能同时得到rowid?
- 在oracle中,怎么将一个number(integer)转换为'233.243.34.123'的字符串类型?
- 紧急招聘顶尖互联网公司DBA系列职位-TK China soft headhunting
- 关于备份的若干问题
- 大家看看这个错误应该怎样解决呢??
- 5555,oracle9i启动不起来了呀,是TNS服务的事,请高人指点,送百分,急
- 启动实例问题,救急!!
- oracle 8i 8.1.6在Intel Xeon DP 2.0GHz CPU的IBM PCSERVER 上安装问题?
- 如何把Oracle中Select出来的数据保存成mdb或文本文件(挥泪大送分)
- ORACLE CASE WHEN 出现多行的情况
- ORA-12571 求救求救
- 用了个case when,怎么总报invalid relational operator的错误
2 ; BEGIN_ END_ ID COUNT
---------- ---------- ---------- ----------
0 27 1 1545
27 48 2 1200
48 96 3 2681
96 111 4 786
111 178 5 3735
竟然迷糊了
SELECT Count(*) FROM test WHERE id=0;
t.end_val,
t.endpoint_number,
case
when endpoint_number = 1 then
(select count(*)
from &&TB_NAME
where &&COL_NAME >= begin_val
and &&COL_NAME <= end_val)
else
(select count(*)
from INVENTORIES
where &&COL_NAME > begin_val
and &&COL_NAME <= end_val)
end as count
from (SELECT lag(endpoint_value, 1, null) over(order by endpoint_number) as begin_val,
ENDPOINT_VALUE as end_val,
ENDPOINT_NUMBER
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = '&&TB_NAME'
AND COLUMN_NAME = '&&COL_NAME'
) t
where t.begin_val is not null
/这个语句是统计histogram 为 HEIGHT BALANCED 类型 的时候 , 表的数据分布情况的语句。
select r.*,
(select count(*)
from test t
where (t.id > r.begin_
and t.id <= r.end_) --不是从0开始的计数
or (t.id >= r.begin_
and t.id <= r.end_ and r.begin_=0)--从0开始的计数
) as count
from range_ r;
select t.begin_val,
t.end_val,
t.endpoint_number,
(select count(*)
from &&TB_NAME
where (&&COL_NAME > begin_val and &&COL_NAME <= end_val)
or (&&COL_NAME = begin_val and t.endpoint_number = 1)) as count
from (SELECT lag(endpoint_value, 1, null) over(order by endpoint_number) as begin_val,
ENDPOINT_VALUE as end_val,
ENDPOINT_NUMBER
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = '&&TB_NAME'
AND COLUMN_NAME = '&&COL_NAME') t
where t.begin_val is not null
还有更好的方法吗?
select t.begin_val, t.end_val, t.endpoint_number, count(*)
from (SELECT lag(endpoint_value, 1, null) over(order by endpoint_number) as begin_val,
ENDPOINT_VALUE as end_val,
ENDPOINT_NUMBER
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = '&&TB_NAME'
AND COLUMN_NAME = '&&COL_NAME') t
left join &&TB_NAME
on ((&&COL_NAME > begin_val and &&COL_NAME <= end_val) or
(&&COL_NAME = begin_val and t.endpoint_number = 1))
where t.begin_val is not null
group by t.begin_val, t.end_val, t.endpoint_number
order by t.begin_val
/这种?