用BigQuery封装复杂代码

Question

最近，我不得不从其他烧烤桌中生成一个烧烤桌。逻辑是相当复杂的，最后我编写了一个复杂的SQL语句。

在Oracle中，我会编写一个PL/SQL过程，逻辑分解为不同的部分(通常是合并语句)。在某些情况下，我会将一些代码封装到函数中。生成的过程将是一系列DML语句，易于阅读和维护。

然而，没有类似的烧烤。UDF只是暂时的，不能存储在-比如说-视图中。

问:我正在寻找方法，使我的复杂工程量SQL代码更模块化和可读性。我有办法做到这一点吗？

Answer 1

当前可用的选项是使用WITH Clause

WITH子句包含一个或多个命名子查询，其输出充当临时表，后续SELECT语句可以在任何子句或子查询中引用该表。

我仍然认为User-Defined Functions是一个很好的选择。

JS和SQL UDF可以在BigQuery中使用，并且从已知的BigQuery团队正在致力于引入permanent UDF，并将很快推出。

同时，您可以将just的主体存储为一个JS库，并使用OPTIONS部分在您的UDF中引用它。参见上述参考资料中的Including external libraries

2019年10月最新情况

现在，使用脚本编写和存储过程的能力在Beta中。

因此，您可以在一个请求中向BigQuery发送多个语句，使用变量，并使用控制流语句，如IF和WHILE等。

而且，您可以使用过程，它是可以从其他查询调用的语句块。

注:现在是贝塔。

Answer 2

BigQuery支持持久化用户定义函数.要开始，请参阅文献资料。

例如，下面是一个CREATE FUNCTION语句，它创建一个函数来计算数组的中值：

CREATE FUNCTION dataset.median(arr ANY TYPE) AS (
  (
    SELECT
      IF(
        MOD(ARRAY_LENGTH(arr), 2) = 0,
        (arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2) - 1)] + arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]) / 2,
        arr[OFFSET(DIV(ARRAY_LENGTH(arr), 2))]
      )
    FROM (SELECT ARRAY_AGG(x ORDER BY x) AS arr FROM UNNEST(arr) AS x)
  )
);

执行此语句后，可以在后续查询中引用它：

SELECT dataset.median([7, 1, 2, 10]) AS median;

您还可以在逻辑视图中引用该函数。但是，请注意，您目前需要使用项目限定对视图中函数的引用：

CREATE VIEW dataset.sampleview AS
SELECT x, `project-name`.dataset.median(array_column) AS median
FROM `project-name`.dataset.table