Oracle: I need to select n rows from every k rows of a table

问题
回答1

假设您使用某种逻辑对它们进行排序1 - 10000并且只想输出1-100,2001-2100,4001-4100,etc行,那么您可以使用ROWNUM伪列:

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()分析函数:

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 .... 然后从中获取一些行。

WHERE子句替换为:

WHERE rn - POWER(
             2,
             TRUNC( CAST( LOG( 2, CEIL( rn / 1000 ) ) AS NUMBER(20,4) ) )
           ) * 1000 + 1000 <= 100
回答2
with sq1 as ( select id, col1, ntile(5) over (order by id asc) as quintile
              from t23
            )
    , sq2 as (  select id, col1, quintile
                      , row_number() over ( partition by quintile order by id asc) as rn
                from sq1 )
select *
from sq2
where rn <= 200
order by quintile, rn
/
回答3

通过 row_number 使用 partition by 和 order by。 它如下所示:

row_number()over(partition by partition_column order by order_column)<=100

partition_column 将是您划分集合的条件。 order_column 将是您选择前 100 名的条件。

标签

更多相关内容:请点击查看