oracle 有记录限制的循环赛

muk1a3rh 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(113)

我必须为以下需求构建一个配置单元SQL查询
我有一个客户表。我需要除以6（即假设表包含600个记录，每个月100个记录长达6个月），每个月有一个括号，以针对客户的总表记录。假设括号限制是4，那么我需要从5个独特的帐户中挑选5个独特的电子邮件ID。例如，10个唯一帐户中有10个唯一的电子邮件ID
注：我使用mod操作来分发6个月的记录。
| 账户|电子邮件|Mod，6|
| --|--|--|
| ACC 1|电子邮箱：acc 1| 1 |
| ACC2|电子邮件1@acc2| 1 |
| ACC2|电子邮件2@acc2| 2 |
| ACC2|电子邮件3@acc2| 3 |
| ACC2|邮箱4@acc2| 4 |
| ACC2|电子邮件5@acc2| 5 |
| ACC2|电子邮件6@acc2| 6 |
| ACC2|电子邮件7@acc2| 1 |
| ACC3|邮箱1@acc3| 1 |
| ACC3|电子邮件2@acc3| 2 |
| ACC3|电子邮件3@acc3| 3 |
| ACC4|电子邮箱：acc 4| 1 |
| ACC5|电子邮件1@acc5| 1 |
| ACC5|电子邮件2@acc5| 2 |
预期输出-ACC 4为4（不需要低于输出ACC 5，因为记录计数已达到括号范围-4）
| 账户|电子邮件|mod，6|
| --|--|--|
| ACC 1|电子邮箱：acc 1| 1 |
| ACC2|电子邮件1@acc2| 1 |
| ACC3|邮箱1@acc3| 1 |
| ACC4|电子邮箱：acc 4| 1 |
如果是8（我必须选择所有的唯一帐户第一，然后其他序列，以达到括号范围）
预期输出
| 账户|电子邮件|mod，6|
| --|--|--|
| ACC 1|电子邮箱：acc 1| 1 |
| ACC2|电子邮件1@acc2| 1 |
| ACC3|邮箱1@acc3| 1 |
| ACC4|电子邮箱：acc 4| 1 |
| ACC5|电子邮件1@acc5| 1 |
| ACC2|电子邮件7@acc2| 1 |
| ACC2|电子邮件2@acc2| 2 |
| ACC3|电子邮件2@acc3| 2 |
如果为10
| 账户|电子邮件|mod，6|
| --|--|--|
| ACC 1|电子邮箱：acc 1| 1 |
| ACC2|电子邮件1@acc2| 1 |
| ACC3|邮箱1@acc3| 1 |
| ACC4|电子邮箱：acc 4| 1 |
| ACC5|电子邮件1@acc5| 1 |
| ACC2|电子邮件7@acc2| 1 |
| ACC2|电子邮件2@acc2| 2 |
| ACC3|电子邮件2@acc3| 2 |
| ACC5|电子邮件2@acc5| 2 |
| ACC2|电子邮件3@acc2| 3 |
我试着在下面查询。但它首先获取所有1条记录。我不知道如何获取唯一的帐户记录与mod_seq_value 1第一，然后开始其余的记录从mod seq-1。

select * from (
select *, Row_number() over(order by mod_num_seq,acc_count) as rnk
select account,email,
count(*) over(partition by account) as acc_count
,case 
when mod(row_number() over(partition by account),6)=0 then 6
else mod(row_number() over(partition by account),6)=0 
end as mod_num_seq
from 
customer
)a
)b where rnk<={:bracket}

oracle

来源：https://stackoverflow.com/questions/77004118/round-robin-with-record-limitation

1条答案

按热度按时间

roejwanj1#

不知道为什么输出中的第6行和第7行是email7@acc2, email2@acc2而不是email7@acc2, email2@acc3。

with customer (account, email, x) as
(
select 'acc1','email@acc1', 1 from dual
union all select 'acc2','email1@acc2',  1 from dual
union all select 'acc2','email2@acc2',  2 from dual
union all select 'acc2','email3@acc2',  3 from dual
union all select 'acc2','email4@acc2',  4 from dual
union all select 'acc2','email5@acc2',  5 from dual
union all select 'acc2','email6@acc2',  6 from dual
union all select 'acc2','email7@acc2',  1 from dual
union all select 'acc3','email1@acc3',  1 from dual
union all select 'acc3','email2@acc3',  2 from dual
union all select 'acc3','email3@acc3',  3 from dual
union all select 'acc4','email@acc4',   1 from dual
union all select 'acc5','email1@acc5',  1 from dual
union all select 'acc5','email2@acc5',  2 from dual
)
, t as 
(
select c.*, mod(row_number() over (partition by account order by email) - 1, 6) + 1 rn
from customer c
)
select t.*, row_number() over (partition by account order by rn, email) pick_up_order
from t
order by pick_up_order, account;

测试结果：

ACCOUNT EMAIL                X         RN PICK_UP_ORDER
------- ----------- ---------- ---------- -------------
acc1    email@acc1           1          1             1
acc2    email1@acc2          1          1             1
acc3    email1@acc3          1          1             1
acc4    email@acc4           1          1             1
acc5    email1@acc5          1          1             1
acc2    email7@acc2          1          1             2
acc3    email2@acc3          2          2             2
acc5    email2@acc5          2          2             2
acc2    email2@acc2          2          2             3
acc3    email3@acc3          3          3             3
acc2    email3@acc2          3          3             4
acc2    email4@acc2          4          4             5
acc2    email5@acc2          5          5             6
acc2    email6@acc2          6          6             7

14 rows selected.

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 有记录限制的循环赛

1条答案

相关问题

热门标签

最新问答