SQL> with
2 tbl as (
3 select min(NUM) low, max(NUM) high
4 from TBL_1K_RND),
5 tbl2 as (
6 select width_bucket(NUM, low, high, 10) s, NUM
7 from TBL_1K_RND, tbl
8 )
9 select s,
10 min(NUM) lower, max(NUM) upper,
11 count(1) num,
12 round((ratio_to_report(count(1)) over())*100,1) pcnt,
13 lpad('*', round((ratio_to_report(count(1)) over())*100,0), '*') histogram
14 from tbl2 group by s order by s;
S LOWER UPPER NUM PCNT HISTOGRAM
---------- ---------- ---------- ---------- ---------- ------------------------------
1 -3.0602851 -2.5082225 7 .7 *
2 -2.4379864 -1.84617 35 3.5 ***
3 -1.8150501 -1.2060425 79 7.9 ********
4 -1.1991025 -.58413539 164 16.4 ****************
5 -.58247189 .029088646 224 22.4 **********************
6 .040683615 .65371762 242 24.2 ************************
7 .672280301 1.27225368 151 15.1 ***************
8 1.27500147 1.82004315 61 6.1 ******
9 1.90959272 2.47850573 33 3.3 ***
10 2.56404876 3.00088262 4 .4
11 3.1319198 3.1319198 1 .1
11 rows selected.
Popular Topics
Popular Topics
Wednesday, February 19, 2014
More on Calculating Histogram Using Oracle Function
Thursday, August 01, 2013
Get source code for Oracle database objects: user_source view
We can query Oracle user_source to get the source code about functions, procedures, triggers, etc. User_source contains 4 columns, i.e., name, type, line and text.
On my schema, the following query shows that there are 55 lines of source code for functions, 50 lines of source code for procedures, etc.
SQL> select type, count(*) from user_source group by type order by type;TYPE COUNT(*)
------------ ----------
FUNCTION 55
PACKAGE 31
PACKAGE BODY 469
PROCEDURE 50
The following query shows the definition of a function z_cap. Thus, user_source is a very handy way to retrieve the source code.
SQL> select line, text from user_source where name='Z_CAP';LINE TEXT
---------- --------------------------------------------------------------------------------
1 FUNCTION z_cap (
2 p_data number,
3 low number,
4 hi number
5 )
6 RETURN NUMBER IS
7 v_number NUMBER;
8 BEGIN
9 if p_data
11 else v_number:= p_data;
12 end if;
13 return v_number;
14 END z_cap;
Friday, May 25, 2012
Calculate Histogram Using Oracle Function
The following Oracle SQL script calculates the histogram. width_bucket() and ratio_to_report() are two useful functions.
Width_buck function divides the CREDIT_SCORE into 10 equal length bins based on the minimum and maximum values.
Ratio_to_report function calculate the percentage of number of records, i.e., count(*), for each bin created by with_bucket function in step2.
with
tbl as (
select min(CREDIT_SCORE) low, max(CREDIT_SCORE) high
from CELL_PHONE_SERVICE_APPS),
tbl2 as (
select width_bucket(CREDIT_SCORE, low, high, 10) s, CREDIT_SCORE
from CELL_PHONE_SERVICE_APPS, tbl
)
select s,
min(CREDIT_SCORe) lower, max(credit_score) upper,
count(1) num,
round((ratio_to_report(count(1)) over())*100,1) pcnt
from tbl2 group by s order by s;