Oracle SQL对带标识符的左右对序号 [英] Oracle SQL Pair Right Left Sequential Numbers with Identifiers
问题描述
我正在尝试将表格中的左，右部分配对.该表包含唯一零件ID"，标识符"和左/右指示器"列.我收到了类似的问题
I am trying to pair left right parts in a table. The table contains columns Unique Part IDs, Identifiers, and Left/Right Indicator. I received an answer for a similar question assuming the left part always come before the right but I have found that this is not the case.
当前识别一对的唯一方法是，如果它们具有相同的标识符，并且匹配部分是前面的一部分，还是后面的一部分，如果右手部分与左后继部分的标识符相同，则为正确的对，但是如果左手部分与右后继部分具有相同的标识符，则该对是正确的.例如(当前ID，标识符为VARCHAR2)
Currently the only way to identify a pair is if they have the same identifier and the matching part is one part before or one part after, if a right hand part has the same identifier as the left hand subsequent part then that is the correct pair but if the left hand part has the same identifier as the right subsequent part then that is the correct pair. For example (Currently ID, Identifier, are VARCHAR2)
(ID 4将为左，ID 5将为右).但是，每个左右对之间有许多唯一的ID，但没有一对.我正在尝试编写查询以查找表中的所有左对和右对.
(ID 4 will be Left and ID 5 will be Right). However, there are many Unique IDs between each Left Right Pair that do not have a pair. I'm trying to write a query to find all Left and Right pairs within the table.
例如(当前ID和LR均为VARCHAR2)
For example (currently Both ID and LR are VARCHAR2)
ID LR Identifier
1 L B15A
2 R A15C
3 L A15C
4 R A15C
5 L A15C
6 R D5A2
9 R D5A2
10 L E5A6
11 R E5A6
12 L E5A6
13 R E5A6
14 R H9S5
17 L EE5A
18 R EE5A
我需要查询返回
ID LR Identifier
2 R A15C
3 L A15C
4 R A15C
5 L A15C
10 L E5A6
11 R E5A6
12 L E5A6
13 R E5A6
17 L EE5A
18 R EE5A
上一个问题的链接是在这里.问题是，如果我先从左搜索到右(反之亦然)，我会错误地匹配对.因此，查询必须找到链中的第一个标识符，然后对后续部分(如果存在)进行配对，而不管它是右手还是左手.例如，一个不正确的示例是:
The link to the previous question is Here. The problem is if I search by Left before Right (or vice versa) I will incorrectly match pairs. Therefore the query must find the first identifier in the chain and then pair the subsequent part if it exists regardless of if it is a right or left hand For example an incorrect example is:
ID LR Identifier
3 L A15C
4 R A15C
10 L E5A6
11 R E5A6
12 L E5A6
13 R E5A6
17 L EE5A
18 R EE5A
任何帮助将不胜感激！预先谢谢你.
Any help is greatly appreciated! Thank you in advance.
推荐答案
在这里，这种解决方案更通用，即使不一定必须彼此紧挨着找到这对. (如果实际上是必需的，如果零件的ID不连续，则零件不能配对，则可以将该条件添加到查询中.)
Here is a solution that works more generally, even if the pairs are not necessarily found right next to each other. (If that is in fact REQUIRED, if parts cannot be paired if their ID's are not consecutive, that condition can be added to the query.)
with
test_data ( id, lr, identifier ) as (
select '001', 'L', 'B15A' from dual union all
select '002', 'R', 'A15C' from dual union all
select '003', 'L', 'A15C' from dual union all
select '004', 'R', 'A15C' from dual union all
select '005', 'L', 'A15C' from dual union all
select '006', 'R', 'D5A2' from dual union all
select '009', 'R', 'D5A2' from dual union all
select '010', 'L', 'E5A6' from dual union all
select '011', 'R', 'E5A6' from dual union all
select '012', 'L', 'E5A6' from dual union all
select '013', 'R', 'E5A6' from dual union all
select '014', 'R', 'H9S5' from dual union all
select '017', 'L', 'EE5A' from dual union all
select '018', 'R', 'EE5A' from dual
)
-- end of test data, the solution (SQL query) begins below this line
select id, lr, identifier
from ( select id, lr, identifier,
row_number() over (partition by identifier, lr order by id) as rn,
least( count(case when lr = 'L' then 1 end) over (partition by identifier),
count(case when lr = 'R' then 1 end) over (partition by identifier)
) as least_count
from test_data
)
where rn <= least_count
order by id -- ORDER BY is optional
;
输出:
ID LR IDENTIFIER
--- -- ----------
002 R A15C
003 L A15C
004 R A15C
005 L A15C
010 L E5A6
011 R E5A6
012 L E5A6
013 R E5A6
017 L EE5A
018 R EE5A
10 rows selected
说明:在内部查询中，我在初始数据中又添加了两列.一个rn对每个标识符分别计数(从1开始并以1递增)，分别对'L'和'R'进行计数.这将用于形成对.并且，ct给出每个标识符的'L'和'R'总计数中的最少者.在外部查询中，我只过滤掉所有行，其中rn > ct-那些在初始表中没有对的行.剩下的就是那双.
Explanation: In the inner query, I add two more columns to the initial data. One, rn, counts separately (starting from 1 and incrementing by 1) for each identifier, separately for 'L' and for 'R'. This will be used to form the pairs. And, ct gives the least of the total counts for 'L' and 'R' for each identifier. In the outer query, I just filter out all rows where rn > ct - those are the rows without a pair in the initial table. What's left are the pairs.
ADDED :附加条件是必须由连续"行构成一对(如id列所衡量)，这成为一个更有趣的问题.这是一个空白问题(标识具有相同特征的连续行的组)，但有一个转折:LR值在组内必须交替，而不是恒定的.我认为不能在此应用非常有效的塔比妥聚糖"方法.更通用的分组开始"方法确实有效.这就是我在这里使用的.请注意，如果该组的计数为奇数，最后我将忽略该组中的最后一行. (我们可能会发现形成一对或两对或三对的两行，四行或六行连续的行，但是具有交替的LR的行数不是奇数).还要注意，如果两行具有相同的标识符AND LR，则第二行将始终开始一个NEW组，因此，如果实际上它是一对的一部分(带有AFTER行)，则此解决方案将正确捕获该行.
ADDED: With the additional condition that a pair must be formed from "consecutive" rows (as measured by the id column), this becomes a more interesting question. It's a gaps-and-islands problem (identify groups of consecutive rows with the same characteristic), but with a twist: the LR value must be alternating within the group, rather than constant. The very efficient "tabibitosan" method can't be applied here (I think); the "start of group" method, which is more general, does work. This is what I used here. Note that in the end I leave out the very last row in a group, if the count for the group is an odd number. (We may find two, or four, or six consecutive rows that form one or two or three pairs, but not an odd number of rows with alternating LR). Note also that if two rows have the same identifier AND LR, the second row will always start a NEW group, so if it is in fact part of a pair (with the row AFTER it), that will be caught correctly by this solution.
将此与我单独发布的针对Oracle 12及更高版本的MATCH_RECOGNIZE解决方案进行比较-并感谢它更简单！
Compare this to the MATCH_RECOGNIZE solution for Oracle 12 and above I posted separately - and appreciate how much simpler it is!
with
prep ( id, lr, identifier, flag ) as (
select id, lr, identifier,
case when identifier = lag(identifier) over (order by id)
and lr != lag(lr) over (order by id)
then null else 1 end
from test_data -- replace "test_data" with actual table name
),
with_groups ( id, lr, identifier, gp ) as (
select id, lr, identifier,
sum(flag) over (order by id)
from prep
),
with_rn ( id, lr, identifier, rn, ct ) as (
select id, lr, identifier,
row_number() over (partition by identifier, gp order by id),
count(*) over (partition by identifier, gp)
from with_groups
)
select id, lr, identifier
from with_rn
where rn < ct or mod(rn, 2) = 0
order by id -- ORDER BY is optional
;
这篇关于Oracle SQL对带标识符的左右对序号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
