Oracle如何每10％对数据进行分区和获取记录 [英] Oracle how to partition data and get records at every 10%

问题描述

我有一张巨大的桌子，上面有数十亿条这样的记录:

I have a giant table that has billions of records like this:

ID   |  H  |  N   |  Q  | other
-----+-----+------+-----+--------
AAAA |  0  |  7   |  Y  | ...
BBBB |  1  |  5   |  Y  | ...
CCCC |  0  |  11  |  N  | ...
DDDD |  3  |  123 |  N  | ...
EEEE |  6  |  4   |  Y  | ...

这四列是索引的一部分.我想做的是构造一个查询，该查询为我提供第一行，然后依次为10％，20％，30％，40％...，以便该查询始终为我提供10行，而不管我有多大该表是(只要#行> = 10).

These four columns are part of an index. What I want to do is construct a query that gives me the 1st row, followed by the row at 10%, 20%, 30%, 40%, ... so that the query will always give me 10 rows regardless of how big the table is (as long as # rows >= 10).

使用SQL甚至可以做到吗?如果是这样，我该怎么办?它具有什么样的性能特征?

Is this even possible with SQL? If so, how would I do it? What kind of performance characteristics does it have?

推荐答案

一个选项是

SELECT id,
       h,
       n,
       q
  FROM (
    SELECT id, 
           h, 
           n, 
           q,
           row_number() over (partition by decile order by id, n) rn
      FROM (
        SELECT id, 
               h, 
               n, 
               q,
               ntile(10) over (order by id, n) decile
          FROM your_table
            )
        )
   WHERE rn = 1

使用PERCENTILE_DISC或CUME_DIST可能是一种更有效的方法，该方法目前对我不起作用.但这应该可行.

There is probably a more efficient approach using PERCENTILE_DISC or CUME_DIST that isn't clicking for me at the moment. But this should work.

这篇关于Oracle如何每10％对数据进行分区和获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！