我使用的是Maria DB 10.1.16,我正在寻找几个列中最常出现的集合大小2。
示例数据库:
ID | Col 1 | Col 2 | Col 3 | Result ID | EXPECTED RESULT 1 1 0 2 1 "Col 1/ Col 3" 2 1 0 0 No Results 3 1 1 3 3 "Col 1/ Col 2" 3 "Col 1/ Col 3" 3 "Col 2/ Col 3"通过这个新专栏,我只需要Count和GroupBy来接收哪一组会发生的频率。
然而,我不太确定转移它的优雅方式。 基线解决方案将检查所有可能集的存在的每一行:
SELECT *,"Col 1/ Col 2" AS FreqSet FROM `Table` WHERE Col 1 >0 AND Col 2 >0 UNION SELECT *,"Col 1/ Col 3" AS FreqSet FROM `Table` WHERE Col 1 >0 AND Col 3 >0 UNION SELECT *,"Col 2/ Col 3" AS FreqSet FROM `Table` WHERE Col 2 >0 AND Col 3 >0 Order By ID然而,因为我想这样做总共8列,结果将是28个SELECT语句来涵盖所有组合。 所以我希望有更好的解决方案^^。
附注我还有另一个视图,其中包含所有条目> 0,如下所示:
ID ColTotal 1 Col1 1 Col3 2 Col1 3 Col1 3 Col2 3 Col3也许某人有更好的队列来观看该视图。 我希望我已经覆盖了一切?
提前感谢您的任何提示! 问候Hasorko
I'm using Maria DB 10.1.16 and I am looking for the most often occuring set size 2 of several columns.
Example Database:
ID | Col 1 | Col 2 | Col 3 | Result ID | EXPECTED RESULT 1 1 0 2 1 "Col 1/ Col 3" 2 1 0 0 No Results 3 1 1 3 3 "Col 1/ Col 2" 3 "Col 1/ Col 3" 3 "Col 2/ Col 3"With this new column I'd just Count and GroupBy to receive how often which set would occur.
I am not quite sure about an elegant way to transfer this however. The baseline solution would be to check every row of the existence of all possible sets:
SELECT *,"Col 1/ Col 2" AS FreqSet FROM `Table` WHERE Col 1 >0 AND Col 2 >0 UNION SELECT *,"Col 1/ Col 3" AS FreqSet FROM `Table` WHERE Col 1 >0 AND Col 3 >0 UNION SELECT *,"Col 2/ Col 3" AS FreqSet FROM `Table` WHERE Col 2 >0 AND Col 3 >0 Order By IDHowever as I want to do this for a total of 8 columns the result would be 28 SELECT statements to cover all combinations. So I am hoping for a better solution ^^.
Side note I also have another view containing all entries >0 in one column like this:
ID ColTotal 1 Col1 1 Col3 2 Col1 3 Col1 3 Col2 3 Col3Maybe someone has a better queue for that view. I hope I have covered everything?
Thanks for any tips in advance! Greetings Hasorko
最满意答案
你的看法更有用。 这只是一个join :
select distinct v1.id, concat(v1.col, '/', v2.col) from v v1 join v v2 on v1.id = v2.id and v1.col < v2.col;Your view is much more useful. This is just a join:
select distinct v1.id, concat(v1.col, '/', v2.col) from v v1 join v v2 on v1.id = v2.id and v1.col < v2.col;更多推荐
发布评论