Hi, I'm trying to create a pareto chart using ratio_to_report and getting strange results.
DB 19.0.0.0.0
What I expect in the ratio to grow to 100% but I'm seeing is holding for the last results where the salary = 1.
I've re-created the problem with simple data
--DROP TABLE emp PURGE;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
SELECT empno, sal, SUM(ratio) over (order by ratio desc) as running_ratio,
ROUND(SUM(ratio) over (order by ratio desc)*100) as running_ratio_percent
FROM (
SELECT empno,
ename,
deptno,
sal,
RATIO_TO_REPORT(sal) OVER () AS ratio
FROM emp)
7839 5000 .210278408613003616788628143662208764404 21
7788 3000 .336445453780805786861805029859534023047 34
7566 2975 .461561106905542938851038775338548237867 46
7698 2850 .581419799814955000420556817226007233577 58
7782 2450 .684456220035326772646984607620489528135 68
7499 1600 .751745310791487930019345613592396332745 75
7844 1500 .814828833375389015055934056691058962066 81
7521 1250 .919968037681890823450248128522163344268 92
7654 1250 .919968037681890823450248128522163344268 92
7876 1100 .966229287576751619143746320127849272437 97
7369 800 .999873832954832197829926823113802674741 100
7900 1 1 100
7902 1 1 100
7934 1 1 100
What I should see is this:
(I calulated the right hand column in a spreadsheet)
EMPNO SAL RUNNING_RATIO calc in spreadsheet
7839 5000 0.2102784086 0.210278409
7788 3000 0.3364454538 0.336445454
7566 2975 0.4615611069 0.461561107
7698 2850 0.5814197998 0.581419800
7782 2450 0.68445622 0.684456220
7499 1600 0.7517453108 0.751745311
7844 1500 0.8148288334 0.814828833
7521 1250 0.9199680377 0.867398436
7654 1250 0.9199680377 0.919968038
7876 1100 0.9662292876 0.966229288
7369 800 0.999873833 0.999873833
7900 1 1 0.999915889
7902 1 1 0.999957944
7934 1 1 1.000000000
SUMof salary 23778