Oracle SQL Create PDF from Data

Question

Therefore, I am trying to create a probability density function from data in an Oracle SQL table through an SQL query. Therefore, consider the table below:

Name | Spend -------------- Anne | 110 Phil | 40 Sue | 99 Jeff | 190 Stan | 80 Joe | 90 Ben | 100 Lee | 85 

Now, if I want to create a PDF from this data, I need to calculate the number of times that each client spends using certain quanta (from 0 to 50 or from 50 to 100). A sample graph would look something like this (excuse my poor ascii art):

 5| 4| * 3| * 2| * * 1|* * * * |_ _ _ _ 5 1 1 2 0 0 5 0 0 0 0 

So the axis:

• X-Axis: Are Buckets
• Y-Axis: number of customers

I am currently using the Oracle SQL CASE function to determine if costs fall into a bucket and then summarize the number of clients that do this. However, this happens forever, as there are several million entries .

Any idea on how to do this efficiently?

Thanks!

Answer 1

You can try using WIDTH_BUCKET .

 select bucket , count(name) from (select name, spend, WIDTH_BUCKET(spend, 0, 200, 4) bucket from mytable ) group by bucket order by bucket; 

Here I divided the range from 0 to 200 into 4 buckets. And the function assigns a bucket number to each value. You can group this bucket and count how many rectors fall into each bucket.

Demo is here .

You can even display the actual bucket range.

 select bucket, cast(min_value + ((bucket-1) * (max_value-min_value)/buckets) as varchar2(10)) ||'-' ||cast(min_value + ((bucket) * (max_value-min_value)/buckets) as varchar2(10)), count(name) c from (select name, spend, WIDTH_BUCKET(spend, min_value, max_value, buckets) bucket from mytable) group by bucket order by bucket; 

An example is here .

Answer 2

 SELECT COUNT(*) y_axis, X_AXIS FROM (SELECT COUNT(*)y_axis, CASE WHEN spend <= 50 THEN 50 WHEN spend < 100 AND spend > 50 THEN 100 WHEN spend < 150 AND spend >= 100 THEN 150 WHEN spend < 200 AND spend >= 150 THEN 200 END x_axis FROM your_table GROUP BY spend ) GROUP BY X_AXIS; y_axis x_axis ----------------- 4 100 1 50 1 200 2 150