问题描述
我有这样的东西:
Name.....Value
A...........10
B............9
C............8
意思是,值按降序排列.我需要创建一个新表,该表将包含占总值60%的值.因此,这可能是伪代码:
Meaning, the values are in descending order. I need to create a new table that will contain the values that make up 60% of the total values. So, this could be a pseudocode:
set Total = sum(value)
set counter = 0
foreach line from table OriginalTable do:
counter = counter + value
if counter > 0.6*Total then break
else insert line into FinalTable
end
如您所见,我在这里解析sql行.我知道可以使用处理程序来完成此操作,但是我无法使其正常工作.因此,任何使用处理程序或其他创意的解决方案都是不错的选择.还应该在合理的时间复杂度内-解决方案可以,但是慢到地狱:(
谢谢!!!!
As you can see, I'm parsing the sql lines here. I know this can be done using handlers, but I can't get it to work. So, any solution using handlers or something else creative will be great.It should also be in a reasonable time complexity - the solution how to select values that sum up to 60% of the totalworks, but it's slow as hell :(
Thanks!!!!
推荐答案
您可能需要使用lead()
或lag()
窗口功能,可能带有递归查询将行合并在一起.请参阅以下相关问题:
You'll likely need to use the lead()
or lag()
window function, possibly with a recursive query to merge the rows together. See this related question:
如果您使用的是MySQL,则可以使用以下方法来解决缺少窗口功能的问题:
And in case you're using MySQL, you can work around the lack of window functions by using something like this:
这篇关于如何在条件持续的情况下在Mysql中选择行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!