SQL Server NLMIN Function

Updated 2024-03-06 21:27:21.387000

Description

Use the table-valued function NLMIN to find the minimum of a function using the Nelder-Mead method.

Syntax

SELECT * FROM [westclintech].[wct].[NLMIN] (
   <@Func, nvarchar(max),>
  ,<@VarNames, nvarchar(max),>
  ,<@StartVals, nvarchar(max),>
  ,<@ReqMin, float,>
  ,<@StepVals, nvarchar(max),>
  ,<@konvge, int,>
  ,<@kcount, int,>)

Arguments

@Func

The function to be evaluated, as a string. The function must be in the form of a SELECT statement.

@VarNames

The names of the variables.

@StartVals

The starting point for the minimization.

@ReqMin

The required minimum value.

@StepVals

Step size for each variable.

@konvge

The convergence check is carried out every KONVGE iterations.

@kcount

The maximum number of function evaluations.

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "89891e7c-2f60-4110-930b-f575cade7dc2", "colName": "VarName", "colDatatype": "nvarchar(max)", "colDesc": "The variable name"}, {"id": "1b63574b-d69b-4f25-a3a3-4ce2e4fd1ada", "colName": "val", "colDatatype": "float", "colDesc": "The variable value"}]}

Remarks

The returned table includes the variable names and values which are estimated to minimize the function.

If @Func is not a valid SELECT statement then NULL is returned.

The returned table includes the variable YNEWLO which was the minimum value calculated.

The retuned table includes ICOUNT, the number of function evaluations.

The returned table include NUMRES, the number of restarts.

The retuned table include IFAULT, an error indicator

• 0 - no errors detected

• 1 - @ReqMin, @StartVals or @konvge has an illegal value

• 2 - Iteration was terminated because @kcount was exceeded without convergence

Examples

Example #1

Calculate the minimum of

f(x_1,x_2,x_3,x_4) = (x_1+10x_2)^2+5(x_3-x_4)^2+(x_2-2x_3)^4+10(x_1-x_4)^4

Our initial estimate is (3,-1,0,1).

DECLARE @func as varchar(max)
    = N'
SELECT
    POWER(@x1+10*@x2,2)
    +5*POWER(@x3-@x4,2)
    +POWER(@x2 - 2*@x3,4)
    +10*POWER(@x1 - @x4, 4)';
DECLARE @varnames as varchar(max) = N'@x1,@x2,@x3,@x4';
DECLARE @startvals as varchar(max) = N'3.0,-1.0,0.0,1.0';
DECLARE @reqmin as float = 1e-08;
DECLARE @stepvals as varchar(max) = N'1.0,1.0,1.0,1.0';
DECLARE @konvge as int = 10;
DECLARE @kcount as int = 500;
SELECT VarName,
       val
FROM wct.NLMIN(@func, @varnames, @startvals, @reqmin, @stepvals, @konvge, @kcount)
          ;

This produces the following result.

{"columns":[{"field":"VarName"},{"field":"val","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"VarName":"@x1","val":"0.0212558085561325"},{"VarName":"@x2","val":"-0.00220881419302346"},{"VarName":"@x3","val":"-0.00634404182661196"},{"VarName":"@x4","val":"-0.00624836005808909"},{"VarName":"ynewlo","val":"6.47322238295937E-06"},{"VarName":"icount","val":"281"},{"VarName":"numres","val":"4"},{"VarName":"ifault","val":"0"}]}