选择最佳输出 [英] Selecting best output
问题描述
此问题与我的最新问题此处.
This question relate to my latest question here .
注意:这个问题在Oracle中，但是解决方案也可能在MySQL中，因此您可以选择自己喜欢的一个.
Note: This question is in Oracle, but the solution may also be in MySQL , so you can choose the one you prefer.
我想出一个查询来生成模型之间的层次结构依存关系，结果是这样的:
I've come up with a query the generate the hierarchy dependecies between my models, which result in an output like this:
FIRST_MODEL SECOND_MODEL THIRD_MODEL FOURTH_MODEL FIFTH_MODEL SIXTH_MODEL SEVENTH_MODEL EIGHTH_MODEL NINTH_MODEL
----------- ------------ ----------- ------------ ----------- ----------- ------------- ------------ -----------
test1 test
test2 test
test2 test6
test3 test
test3 test2
test3 test2 test
test3 test2 test6
test4 test3
test4 test3 test
test4 test3 test2
test4 test3 test2 test
test4 test3 test2 test6
.....
ETC ..，这意味着FIRST_MODEL等待SECOND_MODEL，依此类推.
ETC.., which means FIRST_MODEL waits for SECOND_MODEL and so on..
问题是我必须从此处选择最完成的输出，这基本上意味着仅叶"和仅填充了最多数据的叶"，例如数据将在稍后提供.这是一个虚拟的示例:
The problem is that I have to select the most completed output from here, which basically means only the "leaves" and only the "leaves" that are filled in with the most data, an example on the data will be provided in a bit. Here is a dummy example :
First | Second | Third | Forth | Fifth ....
1 2 null null null
1 2 6 null null
1 2 5 null null
1 2 5 7 null
1 3 null null null
1 4 6 null
结果应该是
First | Second | Third | Forth | Fifth ....
1 2 6 null null
1 2 5 7 null
1 4 6 null null
其他所有东西都被排除在外，因为它们是所选输出的一部分.
Every thing else got excluded because they are a part of the selected output.
因此使用ROW_NUMBER()或类似内容进行的查询将不起作用，
so a query with ROW_NUMBER() or something will not work,
SELECT t.*,
ROW_NUMBER() OVER(PARTITION BY ????? ORDER BY ???
FROM <Another Query>
我不知道如何按一组未知的列进行分区.
I don't know how to partition by an unknown set of columns.
E.G.这里的输出应该是:
E.G. The output here should be :
test4 test3 test2 test
test4 test3 test2 test6
由于其他所有原因，这只是这两个结果的一部分.我真的很固执，看不到我可以使用哪个窗口函数，因为每个组每次都更改，层次结构级别未知并且可以更改.
Because everything else, is just a part of those two result . I'm really stuck on this , I don't see which window function can I use becuase each group changes each time, the levels of hierarchy are unknown and can change.
我宁愿避免使用动态SQL，我知道可以使用过程来完成它，并且我也知道如何构建它，但是我有一个避免使用它的要求.
I prefer to avoid dynamic SQL, I know it can be done using a Procedure and I also know how to built one, but I got a requirement to avoid it.
我还将提供用于创建数据的查询，该数据是在Oracle中使用分层查询构建的，也许有人会想从一开始就只获取最完整的行:
I'll also provide the query that creates the data which is built in Oracle with Hierarchical queries , maybe someone will have an idea about taking only the most completed row from the start:
SELECT distinct REGEXP_SUBSTR( tests, '[^|]+', 1, 1 ) AS first_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 2 ) AS second_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 3 ) AS third_model,
......
FROM (
SELECT SYS_CONNECT_BY_PATH( wait_4_model_name, '|' ) || '|' || grand_model AS tests
FROM (SELECT * FROM Tab_Name)
CONNECT BY NOCYCLE PRIOR grand_model = wait_4_model_name
)
有关数据的更多信息，您可以在上面的链接中输入我的最后一个问题.
For more info about the data you can enter my last question in the link above.
谢谢.
推荐答案
Oracle安装程序:
CREATE TABLE table_name ( GRAND_MODEL, WAIT_4_MODEL_NAME ) AS
SELECT 'test', 'test1' FROM DUAL UNION ALL
SELECT 'test', 'test2' FROM DUAL UNION ALL
SELECT 'test', 'test3' FROM DUAL UNION ALL
SELECT 'test2', 'test3' FROM DUAL UNION ALL
SELECT 'test3', 'test4' FROM DUAL UNION ALL
SELECT 'test4', 'test5' FROM DUAL;
查询:
SELECT REGEXP_SUBSTR( tests, '[^|]+', 1, 1 ) AS first_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 2 ) AS second_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 3 ) AS third_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 4 ) AS fourth_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 5 ) AS fifth_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 6 ) AS sixth_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 7 ) AS seventh_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 8 ) AS eighth_model,
REGEXP_SUBSTR( tests, '[^|]+', 1, 9 ) AS ninth_model
FROM (
SELECT SYS_CONNECT_BY_PATH( wait_4_model_name, '|' ) || '|' || grand_model AS tests
FROM table_name
WHERE CONNECT_BY_ISLEAF = 1
START WITH wait_4_model_name NOT IN ( SELECT grand_model FROM table_name )
CONNECT BY PRIOR grand_model = wait_4_model_name
);
输出:
FIRST_MODEL SECOND_MODEL THIRD_MODEL FOURTH_MODEL FIFTH_MODEL SIXTH_MODEL SEVENTH_MODEL EIGHTH_MODEL NINTH_MODEL
----------- ------------ ----------- ------------ ----------- ----------- ------------- ------------ -----------
test1 test
test5 test4 test3 test
test5 test4 test3 test2 test
这篇关于选择最佳输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
