google-bigquery - 如何在 BigQuery 中实现通用 Oracle DECODE 函数？

Question

我正在考虑将 Oracle DECODE 函数实现为 UDF。

下面是外部功能 https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm

以下是 Oracle 中解码的外部功能和语法:

Oracle:

DECODE( <expr> , <search1> , <result1> [ , <search2> , <result2> ... ] [ , <default> ] )

SELECT product_id,
       DECODE (warehouse_id, 1, 'Southlake', 
                             2, 'San Francisco', 
                             3, 'New Jersey', 
                             4, 'Seattle',
                                'Non domestic') 
       "Location of inventory" FROM inventories;

首先，对于 BigQuery UDF SQL 或 JavaScript，对于 BigQuery UDF，当您定义 UDF 函数时，您需要知道您正在接受和输入的参数数量。当你定义SQL UDF函数时，你也可以接受任何类型的数组，但我不确定它是否可以工作以及SQL UDF是否可以用数组执行我们想要的操作。似乎基于 Javascript UDF 文档，所有参数都已命名、键入并预先已知。

有没有办法使用 BigQuery UDF 来完成此任务，它必须像 Oracle 解码一样是动态的，并且适合您放在它前面的任何场景，而不是静态地知道您正在解码的内容

Answer 1

以下适用于 BigQuery 标准 SQL

CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS ((
  IFNULL((SELECT result FROM UNNEST(map) WHERE search = expr), `default`)
));  

您可以使用下面的示例了解它是如何工作的

#standardSQL
CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS ((
  IFNULL((SELECT result FROM UNNEST(map) WHERE search = expr), `default`)
));
WITH `project.dataset.inventories` AS (
  SELECT 1 product_id, 4 warehouse_id UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 5
)
SELECT product_id, warehouse_id,
  DECODE(warehouse_id, 
    [STRUCT<search INT64, result STRING>
      (1,'Southlake'),
      (2,'San Francisco'),
      (3,'New Jersey'),
      (4,'Seattle')
    ], 'Non domestic') AS `Location_of_inventory`
FROM `project.dataset.inventories`

结果

Row product_id  warehouse_id    Location_of_inventory    
1   1           4               Seattle  
2   2           2               San Francisco    
3   3           5               Non domestic       

另一个使用示例是:

#standardSQL
CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS ((
  IFNULL((SELECT result FROM UNNEST(map) WHERE search = expr), `default`)
));
WITH `project.dataset.inventories` AS (
  SELECT 1 product_id, 4 warehouse_id UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 5
), map AS (
  SELECT 1 search, 'Southlake' result UNION ALL                               
  SELECT 2, 'San Francisco' UNION ALL                               
  SELECT 3, 'New Jersey' UNION ALL                               
  SELECT 4, 'Seattle'                           
)

SELECT product_id, warehouse_id,
  DECODE(warehouse_id, kv, 'Non domestic') AS `Location_of_inventory`
FROM `project.dataset.inventories`,
(SELECT ARRAY_AGG(STRUCT(search, result)) AS kv FROM map) arr

具有相同的输出

Update to address - "for a reusable UDF, not having to name the fields makes it closer to Oracle's implementation."

CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS (
  IFNULL((
    SELECT result FROM (
      SELECT NULL AS search, NULL AS result UNION ALL SELECT * FROM UNNEST(map) 
    )
    WHERE search = expr
  ), `default`)
);

现在 - 之前的示例可以在不使用显式命名字段的情况下使用，如下例所示

#standardSQL
CREATE TEMP FUNCTION DECODE(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS (
  IFNULL((
    SELECT result FROM (
      SELECT NULL AS search, NULL AS result UNION ALL SELECT * FROM UNNEST(map) 
    )
    WHERE search = expr
  ), `default`)
);
WITH `project.dataset.inventories` AS (
  SELECT 1 product_id, 4 warehouse_id UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 5
)
SELECT product_id, warehouse_id,
  DECODE(warehouse_id, 
    [ (1,'Southlake'),
      (2,'San Francisco'),
      (3,'New Jersey'),
      (4,'Seattle')
    ], 'Non domestic') AS `Location_of_inventory`
FROM `project.dataset.inventories`

仍然具有与之前相同的输出