使用Pl / PgSQL计算新的Postgres列

在上一篇有关使用Postgres进行统计的博客文章中，我介绍了有关如何处理PostgreSQL中计算列的一些决策。我选择在同一张表中添加额外的列，然后将计算出的值插入这些新列中。今天的帖子将介绍如何使用Pl / pgSQL实现此解决方案。

我敢打赌，您的第一个问题将是：为什么选择在Pl / pgSQL中执行此操作，而不仅仅是一些快速的编辑器工作？

我实际上是从手工制作所有数据定义语言（DDL）和SQL语句开始的，然后迅速改变了主意。提醒您，以下是我们的火灾数据集中所有的说明性列：

Hasfire是我们试图预测的变量（发生火灾），其他所有列都是潜在的解释变量。

我不仅需要创建9个新表列，而且还要创建9个不同的INSERT公式列。所有这些都是在列名或公式中输入错字的绝佳机会。它也很多复制和粘贴。

计算Z分数是许多数据科学工作流程中的标准步骤。通过编写Pl / pgSQL函数，我可以使用该函数在任何新的分析中简单地重复计算列。其他数据科学家也可以使用它。

我需要学习更多的Pl / pgSQL，这似乎是一个有趣的问题。该功能将必须运行一些DDL和一些SQL。

如上所述，我希望该函数具有通用性，以便可以在其他地方重用它。我还决定最好将原始列名保留在计算出的列名中。最后，我想一次处理所有列，而不是一次处理单个列的函数。

考虑到这些限制，我想出了一个可以实现所有3个目标的功能。要查看整个功能，它在github上，但是对于博客文章，我将其分为几部分。

创建或替换功能final.initial_center_standardize（schema_name TEXT，table_name TEXT，前缀TEXT，column_names TEXT []，pkey TEXT）将TEXT返回为

该语句的开头只是创建新函数的普通SQL。注意，我实际上是在架构名称“ final”内部创建此函数。前两个参数获取要在其中添加列的表的模式和表名。

下一个参数将是新计算出的列名称的前缀。下一个参数是文本值数组（PostgreSQL中的数据类型）。该数组包含我们想要Z分数的所有列的名称。最后，对于该函数中使用的查询之一，我们需要主键列的名称。

我只是返回一个文本字段，因为任何一项工作都不会返回任何数据值，但是我想向调用方显示调用是否有效。为了获得成功和“错误”，我通常会返回“完成”或“完成”，并附上一些解释错误原因的文字。

需要明确的是，对于那些对Pl / pgSQL编程不熟悉的人，我一开始并没有提出这个签名。当我构建其余函数时，一些所需的参数才变得显而易见。

完成签名后，我们首先声明一些稍后在函数中需要的变量。 Pl / pgSQL要求您预先声明在块中使用的任何变量。

下一步是创建一个foreach循环，使我们可以访问传入的列名数组中的每个单独元素。

像普通的循环和迭代器模式一样，此代码将提取数组中的第一个元素，并在循环体中为该元素命名为“ col”。它将继续遍历数组元素，直到数组中没有更多元素为止。这对我们的工作而言是完美的，每个专栏都将获得相同的待遇。

接下来，我们的代码承担创建新列以保存Z分数的任务。首先，我们创建新的列名并将其存储在变量中：

我们采用传入的前缀并将其（在SQL中为＆＃39; ||＆＃39;）连接到原始列名。我喜欢在每列之前使用相同的前缀，因为这样可以更清楚地知道哪些列是原始列，哪些具有Z分数。

现在，我们执行实际的SQL以在同一表中创建新列：

通常，Pl / pgSQL将为SQL语句缓存查询计划。在我们的例子中，每次运行循环时，此语句都会更改，从而使查询动态化。对于动态查询，可以使用EXECUTE告诉PostgreSQL查询计划者不要缓存查询。

为了确保执行SQL安全变量替换，我们使用FORMAT命令。 SQL语句中的“％I”表示我们需要将此值像SQL标识符一样对待（即，在大写的表名前后加上“，或在字符串值中转义为”）。这与quote_ident命令的行为相同。

然后，默认情况下，参数以它们在FORMAT命令末尾出现的顺序替换为字符串。因此，如果：

在本例的特定情况下，我们可以避免将每一列都设为NUMERIC，因为Z分数始终是一个十进制数字，而与输入的数字类型无关。这样，我们在表中创建了新列。

我们有一个新列，但其中没有数据。现在解决这个问题。这是我用来计算新列的Z得分（为便于阅读而设置）的语句：

执行格式（＆＃39;带有摘要AS（选择AVG（％1 $ I）AS平均，STDDEV（％1 $ I）AS stddev从％3 $ I.％4 $ I），＆＃39; ||＆ ＃39; final_select AS（选择％5 $ I，（％1 $ I-avg）/ stddev AS以％3 $ I.％4 $ I CROSS JOIN摘要为中心）＆＃39; ||更新％3 $ I。 ％4 $ I SET％2 $ I = final_select.centered from final_select WHERE final_select。％5 $ I =％3 $ I.％4 $ I.％5 $ I＆＃39;，col，new_col_name，schema_name，table_name， pkey）;

再次，我们使用EXECUTE FORMAT，这样计划者就不会缓存查询，并且在每次循环迭代时都传入新变量。实际的SQL有两个CTE计算Z分数，然后有一个UPDATE语句将Z分数插入到正确的表和列中。

因为我们正在SQL中重用参数，所以必须对变量替换使用稍微不同的语法（以橙色突出显示）。现在我们有了像％1 $ I这样的变量，而不仅仅是％I。这种新的表示法是从传递给FORMAT的所有参数中选取第一个参数。如果我们以前面的方式编写了查询，那么我们将在函数调用中多次传递相同的参数。这种对参数格式的编号引用更加简洁。

第一个CTE（摘要）计算循环中当前列的平均值和标准偏差。我们将这些值用于第二个CTE（final_select）。通过在查询中使用CROSS JOIN，我们可以在Z分数计算中为数据集中的每一行使用avg和stddev。最终查询只是使用Z分数更新新的列表，其中final_select中的pkey与表的pkey相匹配。

对于迭代循环中的每个项目，将继续创建新列并填充值。我们使用END LOOP语句终止循环。然后，我们将“ done”返回给使用该函数的SQL调用。同样，我喜欢返回此字符串，因为它使函数用户可以知道函数是否真正完成或花费了很长时间。您可以根据需要将其更改为返回NULL。

有了，我很酷的小功能。我知道乍看起来可能看起来很复杂，但是现在我们已经完成了，您可以看到只有2条SQL行可以完成所有工作。考虑一下此副本可以节省多少并粘贴！

另一个很大的好处是现在我可以将来在任何数据库中重用此功能。我可以通过使用架构权限来控制对其的访问。事实上，我可以将此功能添加为模板的一部分。这样，在我们假设的数据科学PostgreSQL集群中创建的每个数据库都可以使用此功能。我可以授权数据科学家进行此调用，并快速计算Z分数，而无需了解所有Pl / pgSQL。

最后，可以轻松地修改此示例，以使用Z分数的新列创建一个新表。也可以快速修改它以使用辅助表，其中存储每个预测变量的平均值和标准偏差。这是我在上一篇文章中解释的可能的解决方案之一，可能会改善整体性能。

希望您在这篇文章中学到了新的东西。也许您已经完成了一些Pl / pgSQL来更改表。您有什么样的用例？您发现的有趣的曲折是什么？我希望听到您的想法，请将其发送到Crunchy Data Twitter帐户。函数编写愉快！