如何在条件持续时在Mysql中选择行

发布于 2024-11-11 08:02:47 字数 635 浏览 2 评论 0原文

我有这样的事情：

 Name.....Value
 A...........10 
 B............9
 C............8

意思是，这些值按降序排列。我需要创建一个新表，其中包含占总值 60% 的值。所以，这可能是一个伪代码：

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 行。我知道这可以使用处理程序来完成，但我无法让它工作。因此，任何使用处理程序或其他创意的解决方案都会很棒。它还应该具有合理的时间复杂度 - 解决方案如何选择总和不超过总数 60% 的值可以工作，但速度慢得要命:(
谢谢！！！！

原文

I have something like this:

 Name.....Value
 A...........10 
 B............9
 C............8

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

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 total
works, but it's slow as hell :(
Thanks!!!!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

〗斷ホ乔殘χμё〖 2024-11-18 08:02:48

您可能需要使用 lead() 或 lag() 窗口函数，可能带有递归查询将行合并在一起。请参阅此相关问题：

合并如果剧集直接连续或重叠，则为 DATE 行

如果您使用的是 MySQL，则可以使用类似的方法来解决缺少窗口函数的问题这个：

Mysql查询问题

回复收藏 0 原文

逆光飞翔i 2024-11-18 08:02:48

我不知道SQL Server（我假设你正在使用）支持哪些分析功能；对于 Oracle，您可以使用类似以下内容：

select v.*,
  cumulative/overall percent_current,
  previous_cumulative/overall percent_previous from (
  select 
    id, 
    name, 
    value, 
    cumulative,
    lag(cumulative) over (order by id) as previous_cumulative,
    overall
  from (
    select 
      id, 
      name, 
      value, 
      sum(value) over (order by id) as cumulative,
      (select sum(value) from mytab) overall
    from mytab
    order by id) 
) v

说明：
- sum(value) over ... 计算总和的运行总计
- lag() 为您提供前一行的值
- 然后您可以将它们组合起来找到第一行，其中percent_current > 0.6且percent_previous < 0.6

I don't know which analytical functions SQL Server (which I assume you are using) supports; for Oracle, you could use something like:

select v.*,
  cumulative/overall percent_current,
  previous_cumulative/overall percent_previous from (
  select 
    id, 
    name, 
    value, 
    cumulative,
    lag(cumulative) over (order by id) as previous_cumulative,
    overall
  from (
    select 
      id, 
      name, 
      value, 
      sum(value) over (order by id) as cumulative,
      (select sum(value) from mytab) overall
    from mytab
    order by id) 
) v

Explanation:
- sum(value) over ... computes a running total for the sum
- lag() gives you the value for the previous row
- you can then combine these to find the first row where percent_current > 0.6 and percent_previous < 0.6

回复收藏 0 原文

~没有更多了~