如何在Oracle Select SQL中的N行类似类型之后创建批号?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (28)

我有以下数据,我有治疗号码和他们各自的客户。我希望每个处理都有3个客户,并希望关联批次ID。

例如,对于T0001 3,客户将拥有唯一的批次ID。剩下的2将有另一个批次ID。类似地,对于T0002,将存在另一个批次ID,其将仅具有这两个客户。意味着每个批次每个处理编号最多有3个客户。

当前表

Treatment Number    Customer ID     
T00001              C01             
T00001              C02                 
T00001              C03             
T00001              C04                 
T00001              C05             
T00002              C06             
T00002              C07             
T00004              C09             

要求的结果

Treatment Number    Customer ID     Batch ID
T00001              C01             1
T00001              C02             1   
T00001              C03             1
T00001              C04             2   
T00001              C05             2
T00002              C06             3
T00002              C07             3
T00004              C09             4
提问于
用户回答回答于

我只是算术运算row_number()在每个处理中分配批次ID:

select t.*,
       floor( (row_number() over (partition by treatment order by customer) - 1) / 3) as batch_id
from t;

然后,我会用dense_rank()来全局分配:

select t.*, dense_rank() over (order by treatment, batch_id_within) as batch_id
from (select t.*,
             floor( (row_number() over (partition by treatment order by customer) - 1) / 3) as batch_id_within
      from t
     ) t
用户回答回答于

例:

with cust2 as (
select 
TREATMENT_NUBMBER, CUSTOMER_ID,
row_number() over (partition by TREATMENT_NUBMBER order by CUSTOMER_ID)-1 rn
from cust),
cust3 as(
select TREATMENT_NUBMBER, CUSTOMER_ID,RN,
case when mod(rn,3) = 0 then 1 end BATCH_START
from cust2)
select TREATMENT_NUBMBER, CUSTOMER_ID, BATCH_START,
sum(BATCH_START) over (order by TREATMENT_NUBMBER, CUSTOMER_ID) BATCH_ID
from cust3
order by TREATMENT_NUBMBER, CUSTOMER_ID;

TREATM CUS BATCH_START   BATCH_ID
------ --- ----------- ----------
T00001 C01           1          1
T00001 C02                      1
T00001 C03                      1
T00001 C04           1          2
T00001 C04                      2
T00002 C04           1          3
T00002 C05                      3

扫码关注云+社区

领取腾讯云代金券