教你 SQL如何 从优秀到卓越（五）：用户定义函数

方石剑
122 阅读7分钟

UDFs能为你做什么？

简介

我知道我说过这是一个由4部分组成的系列，但谁不喜欢奖励内容呢？这一期的内容是关于用户定义函数（UDFs）的。

请使用下面的链接查看本系列的前几期内容。

用户定义的函数

UDF（用户定义函数）是一种在SQL中创建定制函数的方法。

例如，如果我想找到一个数字的立方体，我可以在查询中做以下工作。

--bigquery syntax

但如果我想创建一个可以代替POWER的任何数字的立方体的函数，我可以创建一个叫做cubed的函数**。**

--bigquery syntax

然后，每当我想对某些东西进行立方运算时，我就可以调用我们的新函数。

SELECT

-- 27.0 , 10.648000000000003

重要的是，现在任何有查询权限的人都可以使用这个新函数。

UDFs 101

UDFs的实现在你使用的每个数据库中都是不同的（有趣！），所以在使用它们之前一定要检查你自己的数据库文档。但是它们有几个共同点。

  • UDFs可以接受输入参数（比如cubed中的 "x"）。
  • 必须输出一个值（任何类型的）。
  • 通常不能引用其他UDFs（但再次检查你的数据库
  • 可以是本地的，只存在于你当前的查询中
  • 或者是共享的，存在于一个模式或一个数据库中
  • 通常可以使用SQL以外的其他语言来创建（如Python、JavaScript、Java），但请再次检查你的数据库的细节。

在他们最好的时候

UDFs的功能让我们看到了SQL离 "正确 "的编码语言有多远（链接到反SQL）。

图片由作者提供。(信不信由你，这是受这个宝石的启发)

Python或R最受欢迎的特点之一是能够通过使用函数将代码模块化以便重复使用。像pypi、tidyverse和npm这样的平台通过让用户将这些函数分享到他们的团队之外和整个用户社区，使其更进一步。

今天，大多数SQL被写成孤立的脚本，但UDFs促进了脚本之间的逻辑共享，模仿了其他 "真正的 "编程语言的模块系统。

更重要的是，UDFs给了我们一个框架， 使SQL为我们和我们周围的人更好地工作。我们可以使用UDFs来。

为不经常使用SQL的人简化普通操作

比方说，销售团队经常每季度查看一次数据，但却很难获得正确格式的数据。为了帮助他们，我可以做一个函数，把任何日期_（2020-01-02_）变成一个季度_（Q1-2020_），这样他们就不会再为繁琐的日期格式化函数而烦恼，可以继续做他们的分析。

做一些在SQL中不可能做到的事情

由于你可以使用其他语言来创建UDFs，你有能力为SQL注入它所没有的功能。例如，你可以使用JavaScript NLP库来创建一个函数，找到两个词之间的Levenshtein距离（链接），让你更容易对文本列进行模糊连接，这在SQL中是很难做到的。

在他们最糟糕的情况下

但我们并不是都生活在UDF的乌托邦中，这是有原因的。尽管UDF_很_好，但它们的缺点常常让我们怀疑它们是否值得麻烦。

特别是。

UDF是孤立的 🏝

UDFs通常需要管理员权限来创建和维护，这意味着只有某些用户能够看到它们是如何定义的。但这些同样的功能可以供任何用户使用，这意味着许多人将盲目地使用他们看不到的功能。

它们的定制性质也意味着对任何在这些功能中挣扎的人没有帮助。我的销售团队无法使用谷歌来查看为什么他们在我创建的季度函数上遇到错误，因为我是唯一知道它如何工作的人。

UDFs是很难理解的🤔

此外，如果我使用SQL以外的东西（例如JavaScript）建立了一个UDF，我_真的_不能和很多人分享。如果它突然停止工作，而我又不在身边，我没有信心我的数据团队能解决它。正如一位聪明的叔叔曾经说过的，"巨大的权力带来巨大的责任"。

这两个缺点结合起来，威胁到我们使用SQL的一个基本原因：它是一种低级别的查询语言。这意味着虽然它很啰嗦，但它是相当标准化的。我不必担心人们使用带有未知函数和方法的奇怪的库；每个人都在使用相同的标准SQL函数和语法。

那么，这就是我们的权衡。我们如何使用UDFs来帮助我们和我们周围的人更好地使用SQL，同时又不破坏它作为一种普遍理解的语言的优势？

我最喜欢的UDFs

鉴于这种权衡，我只喜欢将UDFs用于两件事。

  1. 超级简单的事情，不需要真正的解释（例如：中位数
  2. 超高级的东西，只有少数人使用，我可以确保他们了解其工作原理。

⚠️我建议不要将UDFs用于任何类似于业务逻辑的东西（例如，一个以某种方式计算收入的函数）。对于这样的事情，最好是能够向用户展示你所做的事情并解释原因，因为这是对结果的理解的基础。

我最喜欢的UDFs

中位数

资料来源。 Bigquery-utils

BigQuery（和其他公司）没有MEDIAN的本地函数，而是依靠PERCENTILE_CONT窗口函数。这很好，但我发现（1）很烦人，（2）很难向入门级用户解释，因为它需要了解窗口函数。

这个片段是为BigQuery编写的，计算一列的中间值。

CREATE

用中位数UDF制作盒状图和晶须图。(链接)

工作日

资料来源。 SQL片段

这绝对属于 "在SQL中做的简单但令人讨厌的事情 "类别。一些方言有办法获得实际的星期名称，但在许多情况下，我不得不一次又一次地做这个可爱的case语句。所以现在它可以成为一个叫做weekday的函数。

CREATE

有了这个UDF，你可以更容易地进行星期的分析。(链接)

Levenshtein距离

资料来源：Towards Data Science。 Felipe Hoffa

Levenshtein距离测量两个字符串之间的 "距离"，即把一个字符串改成另一个字符串所需要的变化，所以它可以成为一种有用的方法来寻找那些除了拼写错误或其他轻微差异之外的匹配词。

从技术上讲，这在SQL中是可以做到的，不需要使用JavaScript库（见这里的证明），但这要容易得多......

CREATE OR REPLACE FUNCTION fhoffa.x.levenshtein(a string, b string)

使用Levenshtein距离来考虑用户的拼写错误。(链接)

UDFs的未来作用

最近，我一直在想UDFs在SQL的发展中会扮演什么角色。我们对能够轻松重用SQL的渴望越来越强烈，现在是我从分析师那里听到的最频繁的请求之一。

SQL如何才能继续发展？图片由作者提供。

不难想象，UDF会成为推动SQL走出局部极值，进入一个新时代的解决方案的一部分--在这个时代，我们的SQL用户可以共同构建强大的代码模块，并可以被分发和享用。

为了推动这一进程，我们建立了这个SQL片段集。在这里，SQL用户可以通过创建、分享和改进你公司数据库之外的UDFs，并进入更大的社区，开始努力实现开源的解决方案。

在哪里可以找到更多的UDFs 👀

知道更多的UDFs来源吗？请在评论中告诉我!

让你的SQL从优秀到卓越。第5部分最初发表在Medium上的Towards Data Science，在那里人们通过强调和回应这个故事来继续对话。

avatar
文章
阅读
粉丝