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

Question

我写了一个查询，给出了总结果集中的 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%，您有什么建议吗？

Answer 1

由于您使用的是 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，您只需将其添加到子句中:

仅获取前 10% 的行

例如:

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

它提供了优于旧的 ROWNUM 的优势，旧的 ROWNUM 在需要有序结果集时一直是个问题。使用 12c 中新的行限制子句，在我上面的示例中，您将始终获得 ORDERED 结果集。您可以在此处查看更多示例 ROW LIMITING clause