Oracle:我需要从表的每k行中选择n行 [英] Oracle: I need to select n rows from every k rows of a table
问题描述
例如: 我的表有10000行.首先,我将其分为5组2000(k)行.然后,从每组2000行中,我将仅选择前100(n)行. 通过这种方法,我尝试使用特定模式扫描表的某些行.
For example: My table has 10000 rows. First I will divide it in 5 sets of 2000(k) rows. Then from each set of 2000 rows I will select only top 100(n) rows. With this approach I am trying to scan some rows of table with a specific pattern.
推荐答案
假设您使用某种逻辑对它们1 - 10000
进行排序,并且只想输出1-100,2001-2100,4001-4100,etc
行,则可以使用ROWNUM
伪列:
Assuming you are ordering them 1 - 10000
using some logic and want to output only rows 1-100,2001-2100,4001-4100,etc
then you can use the ROWNUM
pseudocolumn:
SELECT *
FROM (
SELECT t.*,
ROWNUM AS rn -- Secondly, assign a row number to the ordered rows
FROM (
SELECT *
FROM your_table
ORDER BY your_condition -- First, order the data
) t
)
WHERE MOD( rn - 1, 2000 ) < 100; -- Finally, filter the top 100 per 2000.
或者您可以使用ROW_NUMBER()
分析功能:
Or you could use the ROW_NUMBER()
analytic function:
SELECT *
FROM (
SELECT t.*,
ROW_NUMBER() OVER ( ORDER BY your_condition ) AS rn
FROM your_table
)
WHERE MOD( rn - 1, 2000 ) < 100;
是否有可能以指数方式增加样本数据集.像1k,2k,4k,8k ....然后从中获取一些行.
Is it possible to increase the set of sample data exponentially. Like 1k, 2k, 4k,8k....and then fetch some rows from these.
将WHERE
子句替换为:
WHERE rn - POWER(
2,
TRUNC( CAST( LOG( 2, CEIL( rn / 1000 ) ) AS NUMBER(20,4) ) )
) * 1000 + 1000 <= 100
这篇关于Oracle:我需要从表的每k行中选择n行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!