基于列重复的行oracle [英] Duplicate rows oracle based on Column
问题描述
我想基于Parameter(默认情况下，参数为999)列和ID复制一行.例如，在下面的示例中，我们有一个阈值999，如果ID的行包含ParamComp = 999，另一行包含ParamComp <>999，则对于具有ParamComp <>999的行，我们必须使用ParamComp = 999的ColVal.
I want to duplicate a row based on the Parameter(Parameter is 999 by default) Column and ID. For example in the below sample, we have a threshold value 999, If an ID has a row with ParamComp = 999 and another row with ParamComp <>999, then for the row with ParamComp <>999 we must create a new record with the ColVal of ParamComp = 999.
如果ID的行仅包含ParamComp = 999，则只需将其直接加载到目标即可(无需复制逻辑).
If an ID has rows with only ParamComp = 999, Just load it to the target directly (No duplication logic is needed).
如果ID的行仅包含ParamComp <> 999，则只需将其直接加载到目标即可(无需复制逻辑)
Also If an ID has rows with only ParamComp <> 999, Just load it to the target directly (No duplication logic is needed)
输入数据
id ParamComp ColVal
1 999 a
1 80 b
2 999 c
3 85 d
目标数据
id ParamComp ColVal
1 999 a
1 80 b
1 80 a
2 999 c
3 85 d
推荐答案
戈登答案的另一种选择(可能更快，也可能不会更快)是在两行虚拟表"上进行部分交叉联接，如下所示:
An alternative to Gordon's answer (which may or may not be faster) is to do a partial cross join on a two-row dummy "table", like so:
WITH your_table AS (SELECT 1 ID, 999 paramcomp, 'a' colval FROM dual UNION ALL
SELECT 1 ID, 80 paramcomp, 'b' colval FROM dual UNION ALL
SELECT 2 ID, 999 paramcomp, 'c' colval FROM dual UNION ALL
SELECT 3 ID, 85 paramcomp, 'd' colval FROM dual UNION ALL
SELECT 4 ID, 999 paramcomp, 'e' colval FROM dual UNION ALL
SELECT 4 ID, 75 paramcomp, 'f' colval FROM dual UNION ALL
SELECT 4 ID, 70 paramcomp, 'g' colval FROM dual)
-- end of mimicking your table; see SQL below:
SELECT yt.ID,
yt.paramcomp,
case WHEN dummy.id = 1 THEN yt.colval
WHEN dummy.id = 2 THEN yt.paramcomp_999_colval
END colval
FROM (SELECT ID,
paramcomp,
colval,
MAX(CASE WHEN paramcomp = 999 THEN colval END) OVER (PARTITION BY ID) paramcomp_999_colval
FROM your_table) yt
INNER JOIN (SELECT 1 ID FROM dual UNION ALL
SELECT 2 ID FROM dual) dummy ON dummy.id = 1 -- ensures every yt row is returned
OR (dummy.id = 2
AND paramcomp_999_colval IS NOT NULL
AND yt.paramcomp != 999) -- returns an extra row if the 999 paramcomp row exists but the current row isn't 999
ORDER BY yt.ID, yt.paramcomp DESC, yt.colval;
ID PARAMCOMP COLVAL
---------- ---------- ------
1 999 a
1 80 b
1 80 a
2 999 c
3 85 d
4 999 e
4 75 e
4 75 f
4 70 g
4 70 e
假设每个id仅存在一个999 paramcomp行(例如，存在对(id，paramcomp)的唯一约束).
This assumes that there is only ever one 999 paramcomp row per id (e.g. a unique constraint on (id, paramcomp) exists).
您必须对此进行测试，并检验戈登的答案，以查看哪种数据对您的数据最有效.
You'd have to test this and Gordon's answer to see which is most performant against your data.
ETA:这是Gordon答案的固定版本，您可以将其与以下内容进行比较:
ETA: here's a fixed version of Gordon's answer for you to compare with:
select id, paramcomp, colval
from your_table
union all
select id, paramcomp, paramcomp_999_colval colval
from (select yt.*, MAX(CASE WHEN paramcomp = 999 THEN colval END) OVER (PARTITION BY ID) paramcomp_999_colval
from your_table yt
) t
where paramcomp_999_colval IS NOT NULL and paramcomp <> 999
ORDER BY ID, paramcomp DESC, colval;
ETA2:有关虚拟表使用的更多解释:
ETA2: More explanation of the use of the dummy table:
如果要复制表中的所有行，则将交叉连接到具有两行的表/子查询，如下所示:
If you wanted to duplicate all rows in your table, you would do a cross join to a table/subquery that has two rows, like so:
SELECT *
FROM your_table yt
CROSS JOIN (SELECT 1 ID FROM dual UNION ALL
SELECT 2 ID FROM dual) dummy;
ID PARAMCOMP COLVAL ID
---------- ---------- ------ ----------
1 999 a 1
1 80 b 1
2 999 c 1
3 85 d 1
4 999 e 1
4 75 f 1
4 70 g 1
1 999 a 2
1 80 b 2
2 999 c 2
3 85 d 2
4 999 e 2
4 75 f 2
4 70 g 2
但是，您并不总是希望出现重复的行，因此您需要进行选择性的内部联接.我将在最初的回答中分解内部联接，以便您希望看到它的效果会好一些.
However, you don't always want the duplicate row to appear, so you need to do an inner join that's a bit selective. I'll break down the inner join in my initial answer so you can hopefully see what it's doing a bit better.
首先，这是确保返回your_table中每一行的连接部分:
First, here's the part of the join that ensures that each row in your_table is returned:
SELECT *
FROM your_table yt
INNER JOIN (SELECT 1 ID FROM dual UNION ALL
SELECT 2 ID FROM dual) dummy ON dummy.id = 1;
ID PARAMCOMP COLVAL ID
---------- ---------- ------ ----------
1 999 a 1
1 80 b 1
2 999 c 1
3 85 d 1
4 999 e 1
4 75 f 1
4 70 g 1
接下来，这是确保选择性连接的连接部分
Next, here's the part of the join that ensures the selective joining
SELECT *
FROM your_table yt
INNER JOIN (SELECT 1 ID FROM dual UNION ALL
SELECT 2 ID FROM dual) dummy ON dummy.id = 2
AND yt.paramcomp != 999;
ID PARAMCOMP COLVAL ID
---------- ---------- ------ ----------
1 80 b 2
3 85 d 2
4 75 f 2
4 70 g 2
在第二部分中，您会看到我们仍然得到id = 3行，这是我们不想要的.因此，在上面的最后一个答案中，我使用条件最大解析函数找出了paramcomp = 999行的条件，并针对所有行返回了该条件.然后，我将其添加到第二个连接条件部分，以仅返回具有999约束的行(如果它们没有值，则我们假定999行不存在).这确实假设在999行中将始终存在colval.
You can see with this second part that we still get the id = 3 row, which we don't want. So, in my final answer above, I found out what the colval of the paramcomp = 999 row was and returned that for all rows, using a conditional max analytic function. Then, I added that into the 2nd join condition part to only return rows that had a 999 colval (if they don't have a value, then we assume that the 999 row doesn't exist). This does assume that the colval will always be present for the 999 row.
这篇关于基于列重复的行oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
