如何为PostgreSQL创建自定义窗口功能？ （移动平均示例） [英] How to create a custom windowing function for PostgreSQL? (Running Average Example)

问题描述

我真的很想更好地了解创建在PostgreSQL中的Windows上运行的UDF所涉及的内容。我做了一些关于如何创建UDF的搜索，但是还没有找到一个如何在窗口上运行的示例。

I would really like to better understand what is involved in creating a UDF that operates over windows in PostgreSQL. I did some searching about how to create UDFs in general, but haven't found an example of how to do one that operates over a window.

为此，我希望有人愿意共享有关如何编写UDF的代码（可以使用C，pl / SQL或任何过程语言）由PostgreSQL支持），用于计算窗口中数字的运行平均值。我意识到可以通过将标准平均聚合函数与开窗语法（我相信的语法之间的行）一起应用来实现此目的，我只是要求此功能，因为我认为这是一个很好的简单示例。另外，我认为如果有一个平均函数的窗口版本，那么数据库可以保持运行中的总和和观察计数，并且不会在每次迭代中求和几乎相同的行集。

To that end I am hoping that someone would be willing to share code for how to write a UDF (can be in C, pl/SQL or any of the procedural languages supported by PostgreSQL) that calculates the running average of numbers in a window. I realize there are ways to do this by applying the standard average aggregate function with the windowing syntax (rows between syntax I believe), I am simply asking for this functionality because I think it makes a good simple example. Also, I think if there was a windowing version of average function then the database could keep a running sum and observation count and wouldn't sum up almost identical sets of rows at each iteration.

推荐答案

您必须查看postgresql源代码postgresql / src / backend / utils / adt / windowfuncs.c和postgresql / src / backend / executor / nodeWindowAgg.c

You have to look to postgresql source code postgresql/src/backend/utils/adt/windowfuncs.c and postgresql/src/backend/executor/nodeWindowAgg.c

没有好的文档:(-只能在C或PL / v8中实现功能完备的窗口功能-没有其他语言的API。

There are no good documentation :( -- fully functional window function should be implemented only in C or PL/v8 - there are no API for other languages.

http：// www.pgcon.org/2009/schedule/track/Version%208.4/128.en.html 来自PostgreSQL实现作者的演示。

http://www.pgcon.org/2009/schedule/track/Version%208.4/128.en.html presentation from author of implementation in PostgreSQL.

我发现仅有一个非核心实现- http://api.pgxn.org/src/ kmeans / kmeans-1.1.0 /

I found only one non core implementation - http://api.pgxn.org/src/kmeans/kmeans-1.1.0/

< a href = http://pgxn.org/dist/plv8/1.3.0/doc/plv8.html rel = noreferrer> http://pgxn.org/dist/plv8/1.3.0/doc/ plv8.html

这篇关于如何为PostgreSQL创建自定义窗口功能？ （移动平均示例）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！