时间戳

首页 > 解决方案 > 要计算和分组的范围

php - 要计算和分组的范围

问题描述

我有一组值，例如：标题 A的值为27.32，标题 B的值为 27.33，标题 C的值为 27。在这里，我必须检查与哪个标题、值匹配，并且可以在正负3%的公差限制内组合在一起。所以在上面的例子中，B 值在 A 的 +-3% 容限范围内，因此 A 和 B 可以组合在一起。C 不会被归类为不在公差范围内。

现在，如果除了上述值之外，我还有另一个值为 27.01 的标题 D 和值为 26 的 E，那么 C 和 D 也应该组合在一起。

Data:
---------------
Title   Values
---------------
A   27.32
B   27.33
C   27
D   27.01
E   26

期待结果

---------------
Title   Values
---------------
A,B Derived value in +-3% range of actual value(either title A or B)
C,D Derived value in +-3% range of actual value(either title C or D)
E   26

上面的输出将指定对于标题 A 和 B，我可以使用派生值，对于 C 和 D 也是如此。对于标题 E，未找到匹配项，因此将使用实际值代替

无法为此推导出逻辑。虽然在 +-3% tolorence limit 中创建范围，然后在创建的 tolorence 范围内检查实际值。

标签： phpsql

解决方案

Assuming the correct values for a and b are 23.32 and 23.33 as started in the body of your question and not 27.32 and 27.33 like in the sample table (Because your desired results don't make sense otherwise), maybe something like this self join?

SELECT CASE WHEN title_b IS NOT NULL THEN title_a || ',' || title_b
       ELSE title_a
       END AS Title
     , CASE WHEN title_b IS NOT NULL THEN 'Derived value in +-3% range of actual value(either title ' || title_a || ' or ' || title_b || ')'
       ELSE (SELECT value FROM data AS d WHERE d.title = title_a)
       END AS Value
FROM (SELECT DISTINCT
         coalesce(min(l.title, r.title), l.title) AS title_a
       , max(l.title, r.title) AS title_b
      FROM data AS l
      LEFT OUTER JOIN data AS r ON l.title <> r.title
                               AND r.value BETWEEN l.value - (l.value * .03)
                                               AND l.value + (l.value * .03))
ORDER BY title_a;

Which, testing with sqlite (You didn't mention which database you're using, which is always useful to know), produces:

Title       Value                                                           
----------  ----------------------------------------------------------------
A,B         Derived value in +-3% range of actual value(either title A or B)
C,D         Derived value in +-3% range of actual value(either title C or D)
E           26

推荐阅读