仅当高于 0 时才计算每列的 min 和 max 之间的差异 [英] Calculate difference between min and max for each column only if higher then 0

问题描述

我需要根据更新"列中的值计算赔率之间的差异，此时我将更新值为最小值的赔率从更新值为最大值的赔率中减去.它工作完美，但我刚刚意识到在某些列中有时恰好为 0，我想知道是否可以根据更新的列和仅高于 0 的值来选择最小值.

I need to calculate the difference between odds based on the value in the 'updated' column at the moment I take odds where the updated value is a min and minus it from odds where the updated value is max. It works perfect but I've just realized that in some columns happens to be 0 sometimes and I was wondering if it's possible to select the minimum still based on the updated column and only values where higher than 0.

这就是桌子的样子

<头>

fixture_id	H_odds	D_odds	A_odds	ev_tstamp	更新
120000	1.40	1.50	1.30	132000	12
120000	1.10	1.10	1.10	132000	11
120000	1.20	0	1.60	132000	10

这就是我想要回来的

<头>

fixture_id	H_odds	D_odds	A_odds	ev_tstamp	更新	dif_h	dif_d	dif_a
120000	1.40	1.50	1.30	132000	12	0.2	0.4	-0.3

这就是我现在要回来的

<头>

fixture_id	H_odds	D_odds	A_odds	ev_tstamp	更新	dif_h	dif_d	dif_a
120000	1.40	1.50	1.30	132000	12	0.2	1.5	-0.3

我正在使用的代码

select
   t_max.*,
   (t_max.H_odds - t_min.H_odds) as dif_h,
   (t_max.D_odds - t_min.D_odds) as dif_d,
   (t_max.A_odds - t_min.A_odds) as dif_a
from
(
   select
      fixture_id,
      min(updated) min_updated,
      max(updated) max_updated
  from
      test
  group by
      fixture_id
) as t1
join test as t_min on (t_min.fixture_id = t1.fixture_id and t_min.updated = t1.min_updated)
join test as t_max on (t_max.fixture_id = t1.fixture_id and t_max.updated = t1.max_updated)

推荐答案

我只是稍微修改了代码以计算仅针对特定赔率组 (avg) 的差异，因此看起来如下所示.但是它只工作了一次，处理时间超过 15 秒，其他时候我尝试过它由于超时错误而不起作用.只是为了在我的结构中澄清市场列是您示例中的结果"列.

I just modify the code little bit to calculate the difference only for specific group of odds (avg) so it look like bellow. It worked just once though, it took over 15 seconds to process and the other times I tried it didn't work due to time out error. Just to clarify in my structure the market column is the 'outcome' column from your example.

explain SELECT a.*
    , a.odds - b.odds delta
FROM 
 ( SELECT x.*
     FROM average_odds x
     JOIN
        ( SELECT fix_id
               , market
               , MAX(updated) min_updated
            FROM average_odds x where odds_type=avg
           GROUP BY fix_id
               , market
        ) y
       ON y.fix_id = x.fix_id
      AND y.market = x.market
      AND y.min_updated = x.updated
 ) a
JOIN
( SELECT x.*
     FROM average_odds x
     JOIN
        ( SELECT fix_id
               , market
               , MIN(updated) min_updated
            FROM average_odds x where odds_type=avg
           GROUP BY fix_id
               , market
        ) y
       ON y.fix_id = x.fix_id
      AND y.market = x.market
      AND y.min_updated = x.updated
) b
 ON b.fix_id = a.fix_id
AND b.market = a.market  
ORDER BY `delta` ASC

这是解释表

<头>

ID	S TYPE	表格..	parti	输入	pos_keys	KEY	key len	ref	行	过滤	额外
1	PRIMARY	衍生3>	null	全部	null	null	null	null	17466	100.00	使用临时的；使用文件排序
1	PRIMARY	x	null	ref	修复、修复、市场、更新	修复	4	y.fix_id	596	0.11	使用 where
1	PRIMARY	x	null	ref	修复、修复、市场、更新	修复	4	y.fix_id	596	2.27	使用 where
1	PRIMARY	衍生5>	null	ref	auto_key0>	auto_key0>	31	y.fix_id,y.market,bobi.x.updated	10	100.00	使用索引
5	派生	x	null	ref	boki	boki	4	const	17466	100.00	使用索引条件；使用临时；使用文件...
3	派生	x	null	ref	boki	boki	4	const	17466	100.00	使用索引条件；使用临时；使用文件...

这篇关于仅当高于 0 时才计算每列的 min 和 max 之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！