Hi all,
I'm trying to get deciles for a large file about 20-30 million recs. I'm running out of spool space on the sort part. Is there any way i could improve my sorts?
This is what i do. first, i get sums of sales per client. then, i assign deciles to the sales. finaly, i compute max, min, avg per decile and count of clients in deciles
for step 2 i tried
select rank(sales), sales -- don't like it because clients are not distributed evenly in deciles
select csum(1,sales), sales -- works
quantile(10,sales) , sales -- same problem as with rank, but cuts down my sql code in half
the problem is that all of these fail when i attempt to run it on a larger nbr of recs.
i can't really split the query becuase it will affect the deciles. i'm looking for a way to run it in smaller pieces or for a better way to sort such that i won't run out of spool. any ideas?
thx.
I'm trying to get deciles for a large file about 20-30 million recs. I'm running out of spool space on the sort part. Is there any way i could improve my sorts?
This is what i do. first, i get sums of sales per client. then, i assign deciles to the sales. finaly, i compute max, min, avg per decile and count of clients in deciles
for step 2 i tried
select rank(sales), sales -- don't like it because clients are not distributed evenly in deciles
select csum(1,sales), sales -- works
quantile(10,sales) , sales -- same problem as with rank, but cuts down my sql code in half
the problem is that all of these fail when i attempt to run it on a larger nbr of recs.
i can't really split the query becuase it will affect the deciles. i'm looking for a way to run it in smaller pieces or for a better way to sort such that i won't run out of spool. any ideas?
thx.