oracle sql select/为一个数据集中的不同组设置不同的行数限制

Question

我有一个有4列的表: customer，product_id，score，tag在标签列'new‘和'old’中有2个值

对于每个客户都有来自“新”和“旧”标签(数量不同)的product_id，我们根据分数和标签对product_id进行排名，我们对每个客户的产品数量有总限制。叫它'n_prod‘

我希望为每个客户选择(2/3 *n_prod) product_id从‘旧’标签和(1/3*n_prod)产品为‘新’标签，例如，如果我们需要选择6个产品，希望有4个从‘旧’标签(根据分数排名前4)，从‘新’标签有2个(基于分数排名前2)

我可以创建一个名为'rn‘的列，使用以下命令根据每个客户和标签对product_id进行排名

select customer, product_id, score, tag
      , row_number()over(partition by customer,tag, order by score desc) as rn 
from table

不同组别限制个数不同:试过但不起作用：

with tep as
(select customer, product_id, score, tag
      , row_number()over(partition by customer,tag, order by score desc) as rn 
from table)

select tep.*
from tep
where ( case 
         when tag='new' then rn<= round(n_prod*0.33,0)
         else then rn<= round(n_prod*0.66,0)
         end
);

我能把“where”和“case when”或“if else”组合起来吗？

重申预期结果:我希望从“旧”标签中选择(2/3 *n_prod) product_id，并为每个客户的“新”标签选择(1/3*n_prod)产品。例如，如果我们需要选择6个产品，希望从“旧”标签中选择4个(根据分数排名前4)，从“新”标签中选择2个(基于分数排名前2)。

Answer 1

没有数据很难确定，但我认为您只需要在where子句中使用布尔逻辑：

...
select tep.*
from tep
where (tag = 'new' and rn <= round(n_prod*0.33))
   or (tag = 'old' and rn <= round(n_prod*0.66));

快速演示，在另一个CTE中使用一些虚构的数据，并将n_prod作为绑定变量：

var n_prod number;
exec :n_prod := 6;

with your_table (customer, product_id, score, tag) as (
            select 1234, 2345, level, 'old' from dual connect by level <= 10
  union all select 1234, 2345, level, 'new' from dual connect by level <= 10
),
tep as
(select customer, product_id, score, tag
      , row_number()over(partition by customer,tag order by score desc) as rn 
from your_table)
select tep.*
from tep
where (tag = 'new' and rn <= round(:n_prod*0.33))
or (tag = 'old' and rn <= round(:n_prod*0.66));

  CUSTOMER PRODUCT_ID      SCORE TAG         RN
---------- ---------- ---------- --- ----------
      1234       2345         10 new          1
      1234       2345          9 new          2
      1234       2345         10 old          1
      1234       2345          9 old          2
      1234       2345          8 old          3
      1234       2345          7 old          4

顺便说一句，您可能需要增加用于较大n_prod值的乘法器中的位数。对于0.33和0.66，返回的总行数在以下情况下开始出错...呃..。28.(round(28*.33)是9；round(28*.66)是18；所以总数是27而不是28。使用0.333和0.666似乎在253年之前是安全的；使用0.3333和0.6666直到2503；等等。