如何在Oracle中将表行拆分为固定大小的块 [英] How to Split a table rows into fixed size chunk in Oracle
问题描述
我有一个超过100万行的大型表,其中一个数字列作为主键.我正在尝试使用Oracle sql查询来拆分大小,例如500.因此,我有一些存储桶,每个存储桶都有500条记录,并在该存储桶中给出了它们的最大值和最小值. 样本数据
I have a large table 1 million+ rows with a numeric column as primary key. I was trying for an Oracle sql query to get a split of size say 500. So that I have sort of buckets that have 500 records each and give their max and min value in that bucket. sample data
pk_column column1 column2 column3
1002 abcd 1234 15-apr-20
1004 efgh 3435 14-apr-20
1007 ijkl 8855 16-apr-20
....
....
....
2002 asdf 8565 17-apr-20
2005 efgh 5894 14-apr-20
所需的输出如下所示
bucket_no no_of_element min_value max_value
1 500 1002 2002
2 500 2005 3002
3 500 3003 4002
4 480 4003 4500
我可以使用NTILE或WIDTH_BUCKET提出的代码无法确定存储区大小的数量,因此元素的种类是固定的.随着元素数量的不断变化,我无法找到一种动态计算存储桶数的方法,无法在NTILE或WIDTH_BUCKET窗口函数中使用它.以分层的方式使用LEAD和LAG功能非常令人困惑.谁能建议如何解决这个问题.
The code that I could come up with using NTILE or WIDTH_BUCKET is not able to decide the number of bucket size so that no of element is kind of fixed. As the number of elements keep changing , I am unable to find a way to calculate the bucket count dynamically and use it in the NTILE or WIDTH_BUCKET window function. Use of LEAD and LAG function in a hierarchical way was a lot confusing. Could anyone suggest how to tackle this.
推荐答案
您可以按如下方式使用解析函数ROW_NUMBER
:
You can use the analytical function ROW_NUMBER
as follows:
SELECT
RN AS BUCKET_NO,
COUNT(1) AS NO_OF_ELEMENT,
MIN(PK_COLUMN) AS MIN_VALUE,
MAX(PK_COLUMN) AS MAX_VALUE
FROM
( SELECT T.*,
MOD(ROW_NUMBER() OVER(ORDER BY PK_COLUMN) - 1, 500) + 1 RN
FROM YOUR_TABL T
)
GROUP BY RN
这篇关于如何在Oracle中将表行拆分为固定大小的块的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!