查找返回最大总和的连续范围的组合-解网

问：

我有一个类似于以下内容的 SQL 表

-- Create the table
CREATE TABLE YourTableName (
    val FLOAT,
    p300 FLOAT,
    p100 FLOAT
);
-- Insert values into the table
INSERT INTO YourTableName (val, p300, p100)
VALUES
    (2295.91836734693400, -1.370, -2.340),
    (1538.77551020407994, -0.035, 0.135),
    (1269.68503937007615, -0.041, 0.300),
    (-1130.38277511960990, -0.160, -0.075),
    (1004.27350427350345, -0.070, 0.030),
    (-2396.37305699481525, -0.210, 0.580),
    (1632.46268656716000, -0.090, 0.290)

我需要找到列 p100 和 p300 的连续范围的组合，这些组合在列 val 中返回最大可能的结果。此示例适用于 3 列，但我的实际案例具有更多列和更多行。

我首先做了一个脚本，分别为每个属性找到最大和子数组。这奏效了。然后我继续尝试查找 p100 和 p300 之间的最大和子矩阵，但我意识到这是行不通的，因为每个子矩阵都必须是连续的，并且我只能以一种方式对矩阵进行排序。

python c# sql-server 数学统计

CREATE TABLE YourTableName (
    val FLOAT,
    p300 FLOAT,
    p100 FLOAT,
    ix int IDENTITY
);
-- Insert values into the table
INSERT INTO YourTableName (val, p300, p100)
VALUES
    (2295.91836734693400, -1.370, -2.340),
    (1538.77551020407994, -0.035, 0.135),
    (1269.68503937007615, -0.041, 0.300),
    (-1130.38277511960990, -0.160, -0.075),
    (1004.27350427350345, -0.070, 0.030),
    (-2396.37305699481525, -0.210, 0.580),
    (1632.46268656716000, -0.090, 0.290)

;WITH cte AS (
    SELECT *
    , row_number() OVER(ORDER BY p300) AS sort
    FROM    YourTableName
    )
, cte2 AS (
    SELECT  val AS sum, c.sort, c.ix, CAST(ix AS NVARCHAR(MAX)) AS path
    FROM    cte  c
    UNION ALL
    SELECT  c.val + c2.sum, c.sort, c2.ix, CONCAT(path, '_', c.ix)
    FROM    cte c
    INNER JOIN cte2 c2
        ON  c.sort = c2.sort + 1
     )
SELECT TOP 1 x.ix, x.path, max(sum)
FROM (
    SELECT c.*
    ,   y.p100
    ,   CASE WHEN lag(p100) OVER(partition BY c.path ORDER BY p100) <> ISNULL(y.prev100, -9999999999) THEN 1 ELSE 0 END AS unordered
    FROM    cte2 c
    CROSS apply STRING_SPLIT(c.path, '_') x
    INNER JOIN (
        SELECT p100
        ,   lag(p100) OVER(ORDER BY p100) AS prev100
        ,   ix
        FROM    YourTableName y
        ) y
        ON  y.ix = x.Value
    ) x
GROUP BY x.ix, x.path
HAVING max(unordered) = 0
ORDER BY MAX(sum) DESC

--5445.198
--DROP TABLE YourTableName

首先，我添加了一个标识列，用于快速识别矩阵中的行。

然后，我创建了一个递归 CTE，它循环按 p300 值排序的每一行。

每次迭代都会创建一个“路径”，它基本上是一种跟踪行如何组合成总和的方法。例如，1_2_3_5 表示路径中的行是 1、2、3、5。

然后，循环将对第 1 - 6 行、1 - 5 行、1 - 4 行、1 - 3 行、1 - 2 行、1 - 1 行、2 行 - 6 行求和......等。

最后，我通过执行string_split来解开路径，并从矩阵中获取原始行。联接内的矩阵是固定的，因此它也会获取之前的 p100 值。此值将与生成的路径表的上一个值进行比较。这是为了确保我们真正尊重值的顺序，而不是跳来跳去。

最后，我按没有任何“无序”行的总和和路径进行分组。然后很容易获得最大金额。

此代码应易于适应另一个 p 值。只需添加另一个 LAG 并确保检查连续值即可。

上一个：鞅和顺序相关 [已关闭]

下一个：计算两个数字之间的归一化分数

查找返回最大总和的连续范围的组合

Find combination of continuous ranges returning max sum

评论