如何在Databricks中实现SSMS同款MaxOf5Values函数？

我在SSMS中定义了一个MaxOf5Values函数，代码如下：

CREATE FUNCTION [dbo].[MaxOf5Values] (@D1 [int],@D2 [int],@D3 [int],@D4 [int],@D5 [int]) RETURNS int
AS
BEGIN
    DECLARE @Result int

    SET @Result = COALESCE(@D1, @D2, @D3, @D4, @D5)

    IF @D2 IS NOT NULL AND @D2 > @Result SET @Result = @D2
    IF @D3 IS NOT NULL AND @D3 > @Result SET @Result = @D3
    IF @D4 IS NOT NULL AND @D4 > @Result SET @Result = @D4
    IF @D5 IS NOT NULL AND @D5 > @Result SET @Result = @D5

    RETURN @Result
END

现在需要在Databricks中实现相同功能，但语法适配遇到问题。我尝试的代码如下，却在SET语句处报错：

CREATE TEMPORARY FUNCTION test(D1  INT, D2 int) RETURNS INT RETURN SET Result = D1 IF D2 IS NOT NULL AND D2 > Result SET Result = D2 Result SELECT test(1,25)

解决方案 #

Databricks SQL语法和T-SQL差异较大，不能直接照搬写法，以下两种方式可实现相同功能：

方法一：利用内置函数快速实现 #

Databricks SQL内置的GREATEST函数可直接返回多个参数的最大值，且自动忽略NULL值，和原函数逻辑完全一致：

CREATE TEMPORARY FUNCTION MaxOf5Values(D1 INT, D2 INT, D3 INT, D4 INT, D5 INT)
RETURNS INT
RETURN GREATEST(D1, D2, D3, D4, D5);

测试调用：

SELECT MaxOf5Values(1, 25, NULL, 10, 5); -- 返回25

方法二：逐次判断实现（完全对应原函数逻辑） #

如果要严格匹配原函数的逐次比较逻辑，需使用Databricks SQL的流程控制语法，注意变量声明、IF语句的格式要求：

CREATE TEMPORARY FUNCTION MaxOf5Values(D1 INT, D2 INT, D3 INT, D4 INT, D5 INT)
RETURNS INT
BEGIN
  DECLARE Result INT;
  SET Result = COALESCE(D1, D2, D3, D4, D5);
  
  IF D2 IS NOT NULL AND D2 > Result THEN
    SET Result = D2;
  END IF;
  
  IF D3 IS NOT NULL AND D3 > Result THEN
    SET Result = D3;
  END IF;
  
  IF D4 IS NOT NULL AND D4 > Result THEN
    SET Result = D4;
  END IF;
  
  IF D5 IS NOT NULL AND D5 > Result THEN
    SET Result = D5;
  END IF;
  
  RETURN Result;
END;

测试调用：

SELECT MaxOf5Values(3, NULL, 15, 7, 20); -- 返回20

错误原因说明 #

你尝试的代码存在以下问题：

• 多步骤逻辑的临时函数必须用BEGIN...END包裹，不能直接将所有语句写在RETURN后
• Databricks SQL的IF语句需要用THEN...END IF闭合，语法和T-SQL不同
• 函数定义和调用的SELECT语句必须分开书写

内容的提问来源于stack exchange，提问作者Greencolor