user-defined-functions - 将列表/数组作为参数/返回类型传递给 Redshift 中的 UDF 并将其返回

Question

我有一堆指标消耗一列的整个浮点值列表(想想我正在做一些离群值分析的一系列订单值，因此需要整个值数组)。

我可以将整个列表作为参数传递吗？如果我完全在 python 中执行此操作，那将是太多的数据处理。想法？

# Redshift UDF - the red part is invalid signature & needs a fill 
create function Median_absolute_deviation(y <Pass a list, but how? >,threshold float) 

--INPUTS: 
--a list of order values, -- a threshold
 RETURNS <return a list, but how? > 

STABLE 
AS $
    import numpy as np

    m = np.median(y)
    abs_dev = np.abs(y - m)
    left_mad = np.median(abs_dev[y<=m])
    right_mad = np.median(abs_dev[y>=m])
    y_mad = np.zeros(len(y))
    y_mad[y < m] = left_mad
    y_mad[y > m] = right_mad
    modified_z_score = 0.6745 * abs_dev / y_mad
    modified_z_score[y == m] = 0
    return modified_z_score > threshold

$LANGUAGE plpythonu

1. 我可以从另一个函数传递 m = np.median(y)(在 DB 上使用 select 语句)- 但再次计算 abs_dev & left_mad & right_mad 需要整个系列。

2. 我可以在这里使用 anyelement 数据类型吗？ AWS 引用:http://docs.aws.amazon.com/redshift/latest/dg/udf-data-types.html

这是我试过的。此外，如果标志为“0”，我想返回该列的值 - 但我想我可以在第二次传递时这样做吗？

create or replace function Median_absolute_deviation(y anyelement ,thresh int) 
--INPUTS: 
--a list of order values, -- a threshold
-- I tried both float &  anyelement return type, but same error
RETURNS float 

 --OUTPUT: 
 -- returns the value of order amount if not outlier, else returns 0

STABLE 
AS $$
    import numpy as np

    m = np.median(y)
    abs_dev = np.abs(y - m)
    left_mad = np.median(abs_dev[y<=m])
    right_mad = np.median(abs_dev[y>=m])
    y_mad = np.zeros(len(y))
    y_mad[y < m] = left_mad
    y_mad[y > m] = right_mad
    modified_z_score = 0.6745 * abs_dev / y_mad
    modified_z_score[y == m] = 0
    flag= 1 if (modified_z_score > thresh ) else 0

    return flag

$$LANGUAGE plpythonu
select Median_absolute_deviation(price,3)  from my_table where price >0 limit 5; 
An error occurred when executing the SQL command:
select Median_absolute_deviation(price,3)  from my_table where price >0 limit 5

ERROR: IndexError: invalid index to scalar variable.. Please look at svl_udf_log for more information
  Detail: 
  -----------------------------------------------
  error:  IndexError: invalid index to scalar variable.. Please look at svl_udf_log for more information
  code:      10000
  context:   UDF
  query:     47544645
  location:  udf_client.cpp:298
  process:   query6_41 [pid=24744]
  -----------------------------------------------

Execution time: 0.73s

1 statement failed.

我的最终目标是使用这些通过 UDF 进行的计算(最终目标)来填充画面 View - 所以我需要一些可以与画面交互并使用函数即时进行计算的东西。 建议？

Answer 1

Redshift 目前仅支持标量 UDF，这意味着您基本上不能将列表作为参数传递。

也就是说，您可以发挥创意，将其作为一串用特殊字符分隔的数字传递，然后将其重新转换为您的 udf 中的列表，例如:list = [1, 2, 3.5] 可以传递为string_list = "1|2|3.5"

为此，您需要预先确定数字的精度和列表的最大大小，以便定义适当长度的 varchar。这不是最佳做法，但会奏效。