Oracle SQL从数据创建PDF [英] Oracle SQL Create PDF from Data

问题描述

因此，我试图通过SQL查询从Oracle SQL表中的数据创建概率密度函数.因此，请考虑下表:

So I am trying to create a Probability Density Function from data in an Oracle SQL table through a SQL query. So consider the below table:

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

现在，如果我想根据该数据创建PDF，我需要计算每个客户在特定数量(0到50或50到100之间)花费的次数.一个示例图看起来像这样(原谅我可怜的ascii艺术):

Now if I want to create a PDF from that data I need to count the number of times each customer spends with in a certain quanta (between 0 and 50 or between 50 and 100). An example graph would look something like this (forgive my poor ascii art):

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

所以轴是:

• X轴:是铲斗吗
• Y轴:是客户数量

我目前正在使用Oracle SQL CASE函数来确定支出是否在存储桶之内，然后求和该客户的总数.但是，这要花很多时间，因为有几条上百万条记录.

I am currently using the Oracle SQL CASE function to determine whether the spend falls within the bucket and then summing the number of customers that do. However this is taking forever as it there are a couple of million records.

关于如何有效执行此操作的任何想法?

Any idea on how to do this effectively?

谢谢！

推荐答案

您可以尝试使用

You can try using WIDTH_BUCKET function.

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

在这里，我将范围0到200分为4个存储桶.并且该函数为每个值分配一个存储桶编号.您可以按此存储桶分组，并计算每个存储桶中有多少记录.

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

演示此处.

您甚至可以显示实际的铲斗范围.

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;

示例此处.

这篇关于Oracle SQL从数据创建PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！