Oracle 根据以前的记录计数返回前10名记录

嗨，

我有一个订购的产品和客户表，如下所示:

rn product customer 
1 859274 A 
2 859267 A 
3 859250 A 
4 863592 B 
5 862250 B 
6 862700 B 
7 862694 B 
8 862120 B 
9 863592 C 
10 862250 C 
11 862120 D 
12 862694 C 
13 863592 E 
14 862113 D 
15 863592 F 
16 862250 F 
17 862700 E 
18 862694 E 
19 863592 G 
复制

我需要一个SQL句子，它将返回前10个产品/客户对，但是产品和客户都不能重复超过2次。

为了更清楚地说明第3行已经出来了，因为客户A之前被包括了两次。
第13行已退出 (尽管此客户首次出现)，因为产品863592已在审核记录中包含2次。

上面这个例子的答案应该是:

1 859274 A 
2 859267 A 
4 863592 B 
5 862250 B 
9 863592 C 
10 862250 C 
11 862120 D 
14 862113 D 
17 862700 E 
18 862694 E 
复制

我尝试了几个选项，比如分析函数，包括累积计数、滞后等...无法解决这个问题 :-)

谢谢

您可以使用row_number为每个客户或产品分配一个递增计数器。只需在partition by子句中传递这些即可。例如:

row_number () over ( partition by product order by rn )
复制

然后，您可以过滤掉那些rn> 2的

CREATE TABLE t (
  rn int, product int, customer varchar2(1)
)   ;

INSERT INTO t (rn, product, customer) VALUES (1, 859274, 'A');
INSERT INTO t (rn, product, customer) VALUES (2, 859267, 'A');
INSERT INTO t (rn, product, customer) VALUES (3, 859250, 'A');
INSERT INTO t (rn, product, customer) VALUES (4, 863592, 'B');
INSERT INTO t (rn, product, customer) VALUES (5, 862250, 'B');
INSERT INTO t (rn, product, customer) VALUES (6, 862700, 'B');
INSERT INTO t (rn, product, customer) VALUES (7, 862694, 'B');
INSERT INTO t (rn, product, customer) VALUES (8, 862120, 'B');
INSERT INTO t (rn, product, customer) VALUES (9, 863592, 'C');
INSERT INTO t (rn, product, customer) VALUES (10, 862250, 'C');
INSERT INTO t (rn, product, customer) VALUES (11, 862120, 'D');
INSERT INTO t (rn, product, customer) VALUES (12, 862694, 'C');
INSERT INTO t (rn, product, customer) VALUES (13, 863592, 'E');
INSERT INTO t (rn, product, customer) VALUES (14, 862113, 'D');
INSERT INTO t (rn, product, customer) VALUES (15, 863592, 'F');
INSERT INTO t (rn, product, customer) VALUES (16, 862250, 'F');
INSERT INTO t (rn, product, customer) VALUES (17, 862700, 'E');
INSERT INTO t (rn, product, customer) VALUES (18, 862694, 'E');
INSERT INTO t (rn, product, customer) VALUES (19, 863592, 'G');

with ranks as (
  select t.*,
         row_number () over ( partition by product order by rn ) prod_rn ,
         row_number () over ( partition by customer order by rn ) cust_rn
  from   t
)
  select * from ranks
  where  prod_rn <= 2
  and    cust_rn <= 2
  order  by rn;

RN   PRODUCT   CUSTOMER   PROD_RN   CUST_RN   
   1    859274 A                  1         1 
   2    859267 A                  1         2 
   4    863592 B                  1         1 
   5    862250 B                  1         2 
   9    863592 C                  2         1 
  10    862250 C                  2         2 
  11    862120 D                  2         1 
  14    862113 D                  1         2 
  17    862700 E                  2         2
复制