我正在研究将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解码一样是动态的，并且适合您摆在面前的任何情况，而不是知道您要解码的内容是静态的
以下是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
具有相同的输出
地址更新-“对于可重用的UDF，不必命名字段使它更接近Oracle的实现。”
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`
仍然具有与以前相同的输出
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句