Best way to calculate Max/Min of N columns in SQL

问题:

Ok, firstly I've seen this thread. But none of the solutions are very satisfactory. The nominated answer looks like NULLs would break it, and the highest-rated answer looks nasty to maintain. So I was wondering about something like the following :

CREATE FUNCTION GetMaxDates
(
    @dte1 datetime,
    @dte2 datetime,
    @dte3 datetime,
    @dte4 datetime,
    @dte5 datetime
)
RETURNS datetime
AS
BEGIN
    RETURN (SELECT Max(TheDate)
        FROM
        (
            SELECT @dte1 AS TheDate
            UNION ALL
            SELECT @dte2 AS TheDate
            UNION ALL
            SELECT @dte3 AS TheDate
            UNION ALL
            SELECT @dte4 AS TheDate
            UNION ALL
            SELECT @dte5 AS TheDate) AS Dates
        )
END
GO

Main problems I see are that if there are only 3 fields to compare, you'd still have to specify NULL for the other 2, and if you wanted to extend it to six comparisons it would break existing use. If it was a parameterized stored procedure you could specify a default for each parameter, and adding new parameters wouldn't break existing references. The same method could also obviously be extended to other datatypes or stuff like Min or Avg. Is there some major drawback to this that I'm not spotting? Note that this function works whether some, all or none of the values passed to it are nulls or duplicates.

回答1:

I would pass the Dates in XML (you could use varchar/etc, and convert to the xml datatype too):

DECLARE @output DateTime
DECLARE @test XML
    SET @test = '<VALUES><VALUE>1</VALUE><VALUE>2</VALUE></VALUES>'

DECLARE @docHandle int 
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc 

SET @output = SELECT MAX(TheDate)
                FROM (SELECT t.value('./VALUE[1]','DateTime') AS 'TheDate'
                        FROM OPENXML(@docHandle, '//VALUES', 1) t)

EXEC sp_xml_removedocument @docHandle

RETURN @output

That would address the issue of handling as many possibilities, and I wouldn't bother putting nulls in the xml.

I'd use a separate parameter to specify the datetype rather than customize the xml & supporting code every time, but you might need to use dynamic SQL for it to work.

回答2:

You can solve null issue with ISNULL function:

SELECT ISNULL(@dte1,0) AS TheDate
UNION ALL
SELECT ISNULL(@dte2,0) AS TheDate
UNION ALL
SELECT ISNULL(@dte3,0) AS TheDate
UNION ALL
SELECT ISNULL(@dte4,0) AS TheDate
UNION ALL
SELECT ISNULL(@dte5,0) AS TheDate) AS Dates

But it will only work with MAX functions.

Here is another suggestion: http://www.sommarskog.se/arrays-in-sql-2005.html

They suggest comma delimited values in a form of string.

The function takes as many parameters as you wish and looks like this:

CREATE FUNCTION GetMaxDate
(
 @p_dates VARCHAR(MAX)
)
RETURNS DATETIME
AS
BEGIN
DECLARE @pos INT, @nextpos INT, @date_tmp DATETIME, @max_date DATETIME, @valuelen INT

SELECT @pos = 0, @nextpos = 1
SELECT @max_date = CONVERT(DATETIME,0)


WHILE @nextpos > 0
BEGIN
   SELECT @nextpos = charindex(',', @p_dates, @pos + 1)
   SELECT @valuelen = CASE WHEN @nextpos > 0
      THEN @nextpos
      ELSE len(@p_dates) + 1
      END - @pos - 1
   SELECT @date_tmp = CONVERT(DATETIME, substring(@p_dates, @pos + 1, @valuelen))

    IF @date_tmp > @max_date
SET @max_date = @date_tmp

SELECT @pos = @nextpos
END

RETURN @max_date
END

And calling:

DECLARE @dt1 DATETIME
DECLARE @dt2 DATETIME
DECLARE @dt3 DATETIME
DECLARE @dt_string VARCHAR(MAX)

SET @dt1 = DATEADD(HOUR,3,GETDATE())
SET @dt2 = DATEADD(HOUR,-3,GETDATE())
SET @dt3 = DATEADD(HOUR,5,GETDATE())

SET @dt_string = CONVERT(VARCHAR(50),@dt1,21)+','+CONVERT(VARCHAR(50),@dt2,21)+','+CONVERT(VARCHAR(50),@dt3,21)
SELECT dbo.GetMaxDate(@dt_string)

回答3:

Why not just:

SELECT Max(TheDate)        
FROM        
(
    SELECT @dte1 AS TheDate WHERE @dte1 IS NOT NULL
    UNION ALL
    SELECT @dte2 AS TheDate WHERE @dte2 IS NOT NULL
    UNION ALL
    SELECT @dte3 AS TheDate WHERE @dte3 IS NOT NULL
    UNION ALL
    SELECT @dte4 AS TheDate WHERE @dte4 IS NOT NULL
    UNION ALL
    SELECT @dte5 AS TheDate WHERE @dte5 IS NOT NULL) AS Dates        

That shoud take care of the null problem without introducing any new values

回答4:

A better option is to restructure the data to support column based min/max/avg as this is what SQL is best at.

In SQL Server 2005 you can use the UNPIVOT operator to perform the transformation.

Not always appropriate for every problem, but can make things easier if you can use it.

See:
http://msdn.microsoft.com/en-us/library/ms177410.aspx http://blogs.msdn.com/craigfr/archive/2007/07/17/the-unpivot-operator.aspx

回答5:

If you have to do it over one row only, it doesn't matter how you will do it (everything would be fast enough).

For selecting Min/Max/Avg value of several columns PER ROW, solution with UNPIVOT should be much faster than UDF

回答6:

an other possibility is to create a custom table type, like this:

CREATE TYPE [Maps].[TblListInt] AS TABLE(   [ID] [INT] NOT NULL )

then,

CREATE FUNCTION dbo.GetMax(@ids maps.TblListInt READONLY) RETURNS INT
BEGIN
RETURN (select max(id) from @ids)
END

Of course, you can swap "int" with your required type.