Oracle SQL : How to SELECT N records for each "group"/ "cluster"

Question

我有一张 table big_table ，有 400 万条记录，它们通过名为“ process_type_cod ”的列聚集在 40 个组中。此列可能采用的值列表在第二个表中。我们叫它 small_table .

因此，我们有 big_table 和一个名为 process_type_cod 的 NOT NULL FK，它指向 small_table(假设两个表上的列名称相同)。

我想要来自 big_table 的 N 条记录(即 10)，每条记录 small_table 的。

IE。
big_table 中的 10 条记录与 small_table 的第一条记录相关
联盟
big_table 中的 10 个不同记录与小表的第二个记录相关，依此类推。

是否可以使用单个 SQL 函数获取？

Answer 1

我推荐一个分析函数，例如 rank() 或 row_number()。您可以使用硬编码的联合来做到这一点，但分析功能会为您完成所有艰苦的工作。

select *
from 
(
    select
      bt.col_a,
      bt.col_b,
      bt.process_type_cod,
      row_number() over ( partition by process_type_cod order by col_a nulls last ) rank
    from small_table st
    inner join big_table bt
      on st.process_type_cod = bt.process_type_cod
)
where rank < 11
;

您甚至可能不需要该连接，因为 big_table 具有您关心的所有类型。在这种情况下，只需将“from 子句”更改为使用 big_table 并删除连接。

它的作用是执行查询，然后使用分区语句中的“order by”运算符对记录进行排序。对于给定的组(这里我们按 col_a 分组)，一个数字行号(即 1、2、3、4、5、n+1...)被连续应用于每个记录。在外部 where 子句中，只过滤数字小于 N 的记录。