Geekly articles weekly

SQL中的Lambda函数...让我们想一想

图片

顾名思义，本文将涉及的内容。

此外，作者还将从他的角度解释为什么这样做是必要的，并告诉我们SUBJ不仅是一种时尚的技术，而且是“一项双重必要的业务-既愉快又有用”。

看到几个有才华的人如何做某事（一种编程语言，为什么不这样做），确切地知道他们正在解决什么问题以及他们为自己设定的任务，总是很有趣的。 并测试自己的创作。 不能将它与巨型委员会的巨大创造相提并论，巨型委员会将维持宇宙的和谐放在首位，并且谁知道这一点。

比较例如FORTRAN和PL / 1的命运。 现在谁会记得这个PL / 1。

从这个角度来看，例如AWK是非常成功的。 值得一说的是，以A的名字叫Alfred Aho （《 龙书》的作者之一），W的是Peter Weinberger ，他在Fortran-77上有过帮助，K的人是Brian Kernigan ，如果没有他的话。 该语言旨在处理进程之间管道中的即时文本流。

该语言是无类型的（ 这不是完全正确的 ），其语法与C非常相似，具有过滤功能，关联数组，流开始/结束事件，换行事件...

该语言也给作者留下了深刻的印象，那就是它的解释器不需要安装，在类UNIX的系统下，它总是存在，而在Windows下，仅复制可执行文件就可以了，一切正常。 但是，事实并非如此。

在此过程中，作者不得不经常使用SQL + AWK捆绑软件，这就是原因。 SQL仍然是最初用来控制数据流的声明性语言。 它以聚合函数的形式提供了非常有限的机会来处理查询执行的上下文。

例如，如何使用SQL构建二维直方图？

--   100 x 100 SELECT count(), round(x, -2) AS cx, round(y, -2) AS cy FROM samples GROUP BY cx, xy

但是，可以说，使用GROUP BY意味着要进行排序，如果您拥有数亿（甚至更多）行，这也不是一件容易的事。
UPD：他们在评论中纠正了我的观点，即这并不完全正确（或根本不正确）
SQL处理器具有在根据分组标准构造散列的过程中执行聚合功能的能力。 为此，有必要拥有足够的可用内存量以将哈希图放置在内存中。

然后，将在读取表时更新组的上下文，并且在读取结束时，我们将已经具有计算的结果。
可以将相同的技术扩展到窗口功能（下图），只是上下文会“变厚”。

如果组的数量事先未知或很大，则SQL处理器将被迫建立一个临时索引并在第二遍中遍历它。

例如，在简单的情况下，例如这里-一个简单的COUNT，一个通用选项是可能的-一个临时索引（cx，cy，count），然后只有少量的组，它们都将存储在缓存页面上的内存中。 在复杂的情况下，窗口功能会导致组的状态变得不平凡，并且不断地（取消）对其进行序列化根本不是医生命令的。
简介：SQL处理器在无法估计GROUP BY之后的组数时会诉诸排序。 但是，按计算值进行分组只是（通常）情况。

因此，您必须执行以下操作：

 psql -t -q -c 'select x, y from samples' | gawk -f mk_hist2d.awk

其中，mk_hist2d.awk在关联数组中累积统计信息，并在工作完成后显示该统计信息

 # mk_hist2d.awk { bucket[int($2*0.01), int($3*0.01)]+=$1; } END { for (i=0; i < 500; i++) for (j=0; j < 500; j++) { if ((i, j) in bucket) print i*100." "j*100." "bucket[i, j]; else print i*100." "j*100." 0"; } }

但是只有一个-完整的数据流必须从服务器发送到工作机器，这并不便宜。

是否可以通过某种方式将令人愉悦的与有用的相结合-在执行SQL查询期间累积统计信息，而无需求助于排序？ 是的，例如，使用自定义聚合函数。

自定义汇总功能


Subj存在于不同的系统中，在任何地方都以自己的方式做了一些事。

  1. PostgreSQL的 文档在这里 。 更多细节在这里 。
    这是计算最大帐户余额的地方 。
    这是一个计算布尔列中更多内容的示例 -true或false。

    看起来像这样-

     CREATE AGGREGATE mode(boolean) ( SFUNC = mode_bool_state, STYPE = INT[], FINALFUNC = mode_bool_final, INITCOND = '{0,0}' );

    SFUNC是一个针对流中每一行调用的函数，
    其中的第一个参数是STYPE类型。

    FINALFUNC用于完成计算并返回合计值。
    INITCOND-内部状态的初始值（ STYPE ）的初始化，作为第一个参数传递。
    鉴于函数可以用C编写（这意味着对于内部状态，您可以使用在关闭请求时自动释放的内存），这是一个非常强大的工具。 在其使用范围之外，仍然必须能够使用。
  2. MS SQL
    以前 （2000年），在请求之前，有必要创建一个ActiveX对象，以使用该对象进行聚合。
    现在 （2016年以上），这是在CLR环境中完成的。 您将必须创建一个自定义函数 ，创建并注册一个程序集 。 然后，您可以创建一个聚合 。
    计算几何平均值以及合并字符串的示例 ：带有附加参数和用于存储中间状态的用户定义类型。
  3. 甲骨文
    在Oracle中，这是使用ODCIAggregate 数据盒式磁带 （接口）完成的。
    要创建自己的聚合，您需要编写一个实现4种方法的自定义类型
    -初始化（ODCIAggregateInitialize），静态的，应创建所需类型的实例并通过参数返回
    -迭代（ODCIAggregateIterate），在每行数据上调用
    -合并（ODCIAggregateMerge），用于合并并行执行的聚合
    -完成（ODCIAggregateTerminate）-结果输出
    示例： 1，2，3，4 。
  4. DB2
    在DB2中没有明确的方法来使用定制聚合。
    但是您可以将标准函数（尽管是MAX）转换为用户定义的类型（在Java中），并使系统执行以下形式的查询

     CREATE TYPE Complex AS ( real DOUBLE, i DOUBLE ) … CREATE TABLE complexNumbers ( id INTEGER NOT NULL PRIMARY KEY, number Complex ) … SELECT sum..real, sum..i FROM ( SELECT GetAggrResult(MAX(BuildComplexSum(number))) FROM complexNumbers ) AS t(sum)

在所有这些系统中值得注意的是什么？

  • 一种或另一种方式，您将需要在数据库中创建一些对象。 是AGGREGATE还是TYPE。 至少需要适当的权利。 而且只想在他的膝盖上加上一些数字。
  • 您可能必须用另一种语言编写某些内容，无论是C，C＃还是Java。
    为了将写入的内容集成到系统中，再次需要权限。 但是我只想要...
  • 初始化困难。 假设您要读取不同篮子尺寸的直方图。 似乎更容易-在声明汇总（PostgreSQL）和整个业务时，我们将指示所需的INITCOND。 但是，对于每个篮子的大小，您将需要自己的汇总，并且再次需要权利。

    在这里，您可以求助于肮脏的技巧，并从初始化行（向前）和数据中滑动联合处理器，而不是在构造函数中构造上下文，而是在接收到第一行时构造上下文。
  • 但是，即使有上述限制，自定义聚合也可以计算任何内容。
  • 重要的是聚合可以并行化 ，至少PostgreSQL，Oracle（Enterprise Edition）可以做到。 为此，真相将必须学习如何序列化/反序列化中间状态，以及冻结从不同流接收到的中间状态。

视窗功能


窗口函数出现在SQL：2003标准中。 目前，它们受以上所有系统的支持。 本质上，窗口功能是单元工作的扩展。 而且，当然，自定义聚合函数也可以在窗口上下文中工作。

扩展名是这个。 在SQL：2003之前，聚合函数在某个窗口中工作，该窗口要么是整个结果集，要么是其一部分，与GROUP BY表达式中字段值的组合相对应。 用户现在可以自由操作该窗口。

区别在于，使用窗口计算的值将添加到单独列中的输出中，并且不需要使用聚合函数折叠整个流。 因此，在一个请求中，您可以在自己的上下文（窗口）中使用多个窗口聚合。 之前可能有几个聚合函数，但是它们都在一个窗口中工作。

大笔画

  • 超过（）
    窗口是整个结果集。 假设查询“ 来自Samples的select count（1） ”返回169。在这种情况下，将“ select count（1）over Samples（）之上 ”运行，我们得到的列被写入169次，即169次。
  • 结束（提交依据）
    它类似于GROUP BY，对于每个值组合，都会创建一个窗口，在其中执行聚合函数。 假设在“样品”表中，一个整数列是val，数据是1到169之间的数字。
    然后查询“ 从样本中选择计数（1）超过（除以（12 + val）/ 13） ”将返回一列，其中将值13写入169次。
  • 超过（订购）
    可以与PARTITION BY结合使用，使您可以在光标期间动态更改窗口的大小，在这种情况下，窗口从组的开始延伸到当前光标位置。 结果，对于该组，结果在聚合列中不是相同的值，而是它自己的值。 方便计算累计金额。 查询结果
    “从Samples中选择（按Val排序）之上的sum（val） ”将是一列，其中第n个元素将包含从1到n的自然数之和。
  • 超过（行）
    允许您从光标位置或ORDER BY范围的开始/结束处定义窗口框架。

    例如，“ ... ROWS 1 PRECEDING ... ”表示窗口由当前行和前一行组成。 “ ...跟随1跟随和2跟随...之间的行 ”-窗口在光标之后紧接两行。

    在此模式下，CURRENT ROW指示当前光标位置。 例如，“行与当前行之间的行”表示从当前行到范围的结尾。
  • 超过（范围）
    与ROWS的不同之处在于，此处的CURRENT ROW表示作为窗口的起点，是ORDER BY范围的起点，而作为窗口的终点-ORDER BY范围的最后一行。

在不同系统上使用窗口函数的语法略有不同。

综上所述，开发人员在分析了各种SQL报表的构造后，突出显示了最常见的情况并在语法中将它们具体化了，仍然感到有些痛苦。

记录返回功能


在聚合/窗口函数的输出中，每个结果行对应于传入数据流中的特定范围的行。 在生活中，这种对应并不总是存在。

例如，需要构造协方差矩阵10X10（ 为此需要672X672）。 这可以一次完成，为此，我们使用10个数字参数执行由我们编写的聚合函数。 她的工作结果是一个10行10值记录集，每个矩阵元素引用输入流的所有行（无论有多少行）。

我们可以说-因此，例如在PostgreSQl中， 您可以从函数返回二维数组（例如：“ ARRAY [[1,2]，[3,4]”）。 或者只是将矩阵序列化为一行。

很好，但并非总是可以在这种方法可接受的框架内保持结果的大小。

抒情离题
例如，我们的任务是概括几何。

几何形状的大小对我们来说是未知的，也可能是来自数千万个点的欧亚大陆的海岸线。 反之亦然，这是一个非常粗糙的几何图形，您需要使用样条曲线对其进行平滑处理。 我想将参数传递给聚合并获取数据流，而不是向量或字符串。

您当然可以说问题牵强，没有人解决，DBMS中的几何以一种特殊的方式存储，有专门的程序来处理几何，...

实际上，将几何图形逐点存储在常规表中非常方便，这仅是因为仅通过移动一个点就无需重写整个Blob。 在DBMS中的空间数据泄漏到各处之前，例如在ArcSDE中 。

一旦几何体Blob的平均大小超过页面大小，直接使用点就变得更加有利可图。 如果有机会以点数进行操作，那么历史之轮也许会再次转向。

协方差矩阵仍然不是输入流和输出流之间去同步的一个很好的例子，因为在最后同时获得了整个结果。 假设您要处理/压缩源数据流。 同时

  • 有很多数据，它们位于没有索引的“堆”中，实际上它们只是“快速”写入磁盘
  • 您需要将它们归类为不同的类别，这些类别相对较少
  • 在类别中，在时间间隔内取平均值，仅存储平均值，测量次数和方差
  • 所有这些都需要快速完成

有哪些选择？

  1. 在SQL中，需要按时间间隔/类别进行排序，这与最后一点相矛盾。
  2. 如果数据已经按时间排序（实际上并不能保证），并且有可能将此事实传达给SQL处理器，则可以使用窗口函数和一次传递。
  3. 编写一个单独的应用程序来完成所有这些工作。 在PL / SQL中，或更可能的情况是，在C / C ++中，由于有大量数据。
  4. 返回记录的函数。 也许他们可以帮助我们。

有关A.4的更多详细信息。 为此有两种机制-临时表和管道功能。

  1. 传送带功能。
    这种机制出现在Oracle中（从9i，2001开始），并且允许返回记录集的函数不累积数据，而是根据需要进行计算（类似于通过管道连接的两个进程的stdout和stdin的同步）。
    即 流水线函数的结果可能在退出此函数之前开始进行处理。 为此，在函数的定义中就可以说

      FUNCTION f_trans(p refcur_t) RETURN outrecset PIPELINED IS …

    并在正文中记录结果行

     LOOP … out_rec.var_char1 := in_rec.email; out_rec.var_char2 := in_rec.phone_number; PIPE ROW(out_rec); … END LOOP;

    结果，我们有

     SELECT * FROM TABLE( refcur_pkg.f_trans( CURSOR(SELECT * FROM employees WHERE department_id = 60)));

    有管道功能时，根本不需要自定义聚合。

    真棒，甲骨文！

    不久之前（2014年），管道功能也出现在DB2中（IBM i 7.1 TR9，i 7.2 TR1）。
  2. 临时表。
    首先，似乎MS SQL和PostgreSQL都不能从聚合函数返回游标。

    好吧，让我们类似于流水线函数，将游标作为参数，进行处理，将其添加到临时表中，然后将游标返回给它。

    但是，在MS SQL中，无法通过参数将游标传递到存储过程，只能在过程中创建游标并通过输出返回参数。 PostgreSQL也可以这样说 。

    好吧，好的，只需打开游标，减去它，处理值，计算结果，将其添加到临时表中并呈现游标即可。

    或更简单地说，我们将查询结果添加到一个临时表中，对其进行处理，然后将结果通过游标返回到另一个临时表中。

    我能说什么 首先，也是最重要的是，通过游标读取数据要比流中的处理慢。 其次，为什么要使用SQL处理器，让我们用光标读取表，用手创建临时表，在循环中编写连接逻辑……就像在C / C ++中插入汇编程序一样，有时您可以自己处理，但最好不要滥用它。

因此，考虑到函数返回记录集的问题后，我们得出以下结论：

  • 自定义聚合不会真正帮助我们。
  • 无论如何，您都需要在数据库中创建一些对象。 无论是功能表还是临时表。 至少需要适当的权利。 并且只想处理一些数字。
  • 但是，即使有上述限制，有时它也不是很优雅，但是使用此方法可以解决问题。

还有什么


实际上，如果我们已经有解决问题的机会，那么作者还需要什么？
实际上，图灵机还可以计算任何东西，只是速度不是很快且不太方便。

我们制定如下要求：

  1. 它必须是一个可与其他运算符（选择，投影等）同等使用的关系运算符
  2. 它必须是将一个数据流转换为另一个数据流的运算符
  3. 输入和输出流之间没有同步
  4. 运算符声明定义输出流的结构
  5. 操作员具有动态初始化的能力（以函数的形式，更确切地说是其主体，直接在操作员的定义中指定）
  6. 以及函数形式的析构函数（...）
  7. 以及每次从输入流中收到新行时调用的函数（...）
  8. 操作员具有执行上下文-工作所需的用户定义的变量和/或集合集
  9. 要运行此运算符，您不需要创建数据库对象，不需要其他权限
  10. 工作所需的所有内容都用一种语言在一个地方定义

曾几何时，作者创建了这样的运算符，该运算符扩展了TTM /教程D的已实现子集的自制处理器。 现在，针对SQL提出了相同的想法。

值得警告的是，此处SQL结束并且即兴开始。 语法保留了原来的语法，最后，语法糖可以是任何东西，它不会改变本质。

因此， 咀嚼操作员包括

  1. 包含输出字段及其类型列表的标题。
    每个输出（和输入）字段都是一个局部变量。
    例如： “ chew {“ var1” float，“ var2” integer}”表示输出流中将有两列-浮点数和整数
  2. 实体-事件的回调列表，此刻-流的开始，流的结束，行。 按照语法，函数接近PL / SQL。 预定义的函数__interrupt （）是PIPE的类似物，它从对应于输出列的变量中获取值并将其放置在输出流中。 如果输出流的缓冲区溢出，则处理程序的工作将暂停，并且流的接收方的工作将开始。
    例如：“ hook”初始化“ {var1：= 0; var2：= -1; }“

显示示例的最简单方法。

  • 聚合函数SUM的类似物。

     --  'select sum(val) from samples' -- select * from samples chew {“sum(val)” float} --    hook “init” { “sum(val)” := 0; --      } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; end if; } hook “finit” { call __interrupt(); --  PIPE }

    它看起来很笨重，但这只是一个例子，
    无需编写C程序来添加几个数字。
  • 求和+ AVG

     --  'select sum(val), avg(val) from samples' -- select * from samples chew { “sum(val)” float, “avg(val)” float --       } hook “init” { “sum(val)” := 0; “avg(val)” := 0; var num integer; num := 0; --    ,       } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then “avg(val)” := “sum(val)” / num; end if; call __interrupt(); }

    在这里，我们提请注意以下事实：求和仅发生一次。
  • SUM + GROUP BY

     --  'select sum(val) from samples group by type' -- select * from --     ( samples val, type from samples order by type ) chew { “sum(val)” float } hook “init” { “sum(val)” := 0; var gtype integer; gtype := NULL; var num integer; --   num := 0; } hook “row” { if (gtype <> “type”) then __interrupt(); “gtype” := type; "sum(val)" := 0; num := 0; end if; if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then call __interrupt(); end if; }
  • ROW_NUMBER（）个（）个以上

     -- select row_number() over() as num, * from samples -- select * from samples chew { “num” integer, * --        --   '* except val1, ...valX',   TTM } hook “init” { num := 0; } hook “row” { num := num + 1; call __interrupt(); }

是否有可能提供一个示例，说明该方法所获得的结果是通常用常规方法无法获得的？ 我们有他们。

有时会发生数据几乎被排序的情况。 它们甚至可能已经完全排序，但是不确定。

假设在上面的示例（数据流压缩）中，数据来自不同的来源，并且由于各种原因，可以将它们进行少量混合。即来自一个带有时间戳T1的源的行可以位于来自另一个带有时间戳T2的源的行之后，而T1 <T2。

即使我们保证T1和T2之间的差异永远不会超过某个（稀疏的）常数，我们也不能在这里进行排序（以传统方式）。

然而，使用所提出的方法，仅在输入已经接收到时间戳超过该间隔的右边界的时间戳至少超过给定常数的行之后，才可能缓冲输入流并处理当前时间间隔的数据。

这里有一个非常重要的观点。

只有我们知道数据几乎已排序。

只有我们知道该常数的值。

该常数仅对于该问题是特征，并且可能仅对于该实验是特征。
而且，我们会根据自己的职责使用此技巧来避免排序。

我们对任务的标准知识并不以告诉SQL处理器的标准方式存在，并且很难想象。

并且使用lambda函数提供了一种通用的方式来强制SQL处理器完全在需要的地方执行我们需要的操作。

结论


拟议的设计看起来很难实现。

无论如何，要使用有效的PL / SQL。

这个想法本身是简单而直观的，不会在语言中添加新的实体。

这是一个单元，如有必要，它将替换聚合和窗口函数GROUP BY。

一种机制，使您无需传统SQL处理器就无法进行排序。

但最重要的是，它是一种机制，使您可以自由地以最必要的方式处理数据。

PS：感谢Dorofei Proleskovsky参与本文的准备。

Source: https://habr.com/ru/post/zh-CN435120/

More articles:


All Articles