sql - Oracle-按百分比限制Row_number()

我写了一个查询，从总结果集中给出n％。我不确定这是否是一种有效的查询方式。

以下是我的查询(为便于理解，对它进行了修改)，它占总结果集的20％:

SELECT *
FROM (
    WITH RS AS (
            SELECT ID
                ,ROW_NUMBER() OVER (
                    ORDER BY ID
                    ) r_num
                ,count(1) OVER () AS n_rows
            FROM TAB1
            LEFT OUTER JOIN TAB2 ON TAB1.ID = TAB2.ID
            INNER JOIN TAB4 ON TAB4.ID = TAB4.ID
            INNER JOIN TAB3 ON TAB3.ID = TAB4.ID
            WHERE TAB2.ID_ IS NULL
                AND TAB3.C_ID = 'JP'
            )
    SELECT *
    FROM RS
    WHERE r_num <= round(n_rows * 0.2)
    ORDER BY ID
    )

您是否有任何建议以有效的方式从结果集中获取第一/最后/随机n％？

最佳答案

由于您使用的是11g(从标记看来)，因此建议使用NTILE分析功能。您可以在文档中阅读有关NTILE的更多信息。 NTILE会将有序数据集划分为多个存储桶。例如，在您的情况下，排名前10％的行将类似于:
NTILE (10) OVER (ORDER BY id)
该查询可以重写为:

WITH RS AS (
            SELECT ID
                ,NTILE (10) OVER (ORDER BY id) r_num
            FROM TAB1
            LEFT OUTER JOIN TAB2 ON TAB1.ID = TAB2.ID
            INNER JOIN TAB4 ON TAB4.ID = TAB4.ID
            INNER JOIN TAB3 ON TAB3.ID = TAB4.ID
            WHERE TAB2.ID_ IS NULL
                AND TAB3.C_ID = 'JP'
            )
SELECT id
   FROM rs
  WHERE r_num = 10
ORDER BY id

注意:由于您没有提供测试用例，因此我没有表和数据来重新创建测试用例。

那是关于先前的12c的信息，现在在12c中，您有了新的ROW LIMITING CLAUSE，只需要将其添加到子句中:
FETCH FIRST 10 PERCENT ROWS ONLY
例如 :

SELECT col
FROM   table
ORDER BY col
FETCH FIRST 10 PERCENT ROWS ONLY;

与旧的ROWNUM相比，它具有优势，而ORDERED在期望有序结果集时一直是一个问题。在上面的示例中，使用12c中的新行限制子句，您将始终获得ojit_code结果集。您可以在这里查看更多示例ROW LIMITING clause

关于sql - Oracle-按百分比限制Row_number()，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25956367/

ntile

sql - Oracle-按百分比限制Row_number()