No question at this time
DBA Top 10
1 M. Cadot 20800
2 A. Kavsek 14900
3 M. Hidayathullah ... 10800
4 B. Vroman 7900
5 P. Wisse 5300
6 T. Boles 4200
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1600
10 A. Hudspith 1500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48052
Total active users1733
Act. users last 24h10
Act. users last hour1
Registered user hits last week395
Registered user hits last month1204
Go up

SQL help on Analytic Function 'Rolling' Subtraction from a Total
Next thread: sqlloader with FIELD NAMES FIRST FILE in control file
Prev thread: sqlplus truss report

Message Score Author Date
Hi, I am using the SQL below to produce a runni...... BenBart BartBen Oct 02, 2017, 01:24
Hi, This is embarrassing, look like I get the r...... BenBart BartBen Oct 02, 2017, 01:39

Follow up by mail Click here


Subject: SQL help on Analytic Function 'Rolling' Subtraction from a Total
Author: BenBart BartBen, Philippines
Date: Oct 02, 2017, 01:24, 15 days ago
Os info: Solaris
Oracle info: Oracle8, YES it is Oracle8 :(
Message: Hi,

I am using the SQL below to produce a running total of a huge partitioned table. The SQL is from
http://www.java2s.com/Code/Oracle/Analytical-Functions/Sumoverandorderby.htm that I modified to suit what I am doing.


col running_total_csv format 999,999,999,999
select table_name, partition_name, num_rows,
sum(num_rows) over (order by table_name, partition_name desc) running_total,
sum(num_rows) over (order by table_name, partition_name desc) running_total_csv
from all_tab_partitions
where table_name = 'A_BIG_TABLE'
order by partition_name desc
;


Sample output as below:


TABLE_NAME PARTITION_NAME NUM_ROWS RUNNING_TOTAL RUNNING_TOTAL_CSV
------------------------------ ------------------------------ ---------- ------------- -----------------
A_BIG_TABLE A_BIG_TABLE_201711 0 0 0
A_BIG_TABLE A_BIG_TABLE_201710 0 0 0
A_BIG_TABLE A_BIG_TABLE_201709 18745446 18745446 18,745,446
A_BIG_TABLE A_BIG_TABLE_201708 25562440 44307886 44,307,886
A_BIG_TABLE A_BIG_TABLE_201707 26550840 70858726 70,858,726
...
...
...
A_BIG_TABLE A_BIG_TABLE_200710 12524990 2116568956 2,116,568,956
A_BIG_TABLE A_BIG_TABLE_200708 12887910 2129456866 2,129,456,866
A_BIG_TABLE A_BIG_TABLE_200707 12996710 2142453576 2,142,453,576

124 rows selected.


Now, I want to start with the total_num_rows and then subtract the next oldest partition. So the first row should contain the total sum of num_rows and the num_rows of the oldest partition then the next row should contain the row of num_rows of the second oldest partition and the total_num_rows-sum_oldest_partition and so on. Manually typed in, below is what output that I am hoping to get.


TABLE_NAME PARTITION_NAME NUM_ROWS REDUCING_TOTAL REDUCING_TOTAL_CSV
------------------------------ ------------------------------ ---------- -------------- ------------------
A_BIG_TABLE A_BIG_TABLE_200707 12996710 2142453576 2,142,453,576
A_BIG_TABLE A_BIG_TABLE_200708 12887910 2129456866 2,129,456,866
A_BIG_TABLE A_BIG_TABLE_200710 12524990 2116568956 2,116,568,956
A_BIG_TABLE A_BIG_TABLE_200711 13652520 2104043966 2,104,043,966
A_BIG_TABLE A_BIG_TABLE_200712 14628790 2090391446 2,090,391,446


Please advise how to do this. Still Googling at the moment.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: SQL help on Analytic Function 'Rolling' Subtraction from a Total
Author: BenBart BartBen, Philippines
Date: Oct 02, 2017, 01:39, 15 days ago
Message: Hi,

This is embarrassing, look like I get the result that I wanted by simply changing order by partition_name desc at the end to to order by partition_name asc.

Sorry.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here