gh-79097: Add support for aggregate window functions in sqlite3 (GH-20903)

3 files changed, 79 insertions, 0 deletions

diff --git a/Doc/includes/sqlite3/sumintwindow.py b/Doc/includes/sqlite3/sumintwindow.py
new file mode 100644
index 0000000..0e915d6
--- /dev/null
+++ b/Doc/includes/sqlite3/sumintwindow.py
@@ -0,0 +1,46 @@
+# Example taken from https://www.sqlite.org/windowfunctions.html#udfwinfunc
+import sqlite3
+
+
+class WindowSumInt:
+    def __init__(self):
+        self.count = 0
+
+    def step(self, value):
+        """Adds a row to the current window."""
+        self.count += value
+
+    def value(self):
+        """Returns the current value of the aggregate."""
+        return self.count
+
+    def inverse(self, value):
+        """Removes a row from the current window."""
+        self.count -= value
+
+    def finalize(self):
+        """Returns the final value of the aggregate.
+
+        Any clean-up actions should be placed here.
+        """
+        return self.count
+
+
+con = sqlite3.connect(":memory:")
+cur = con.execute("create table test(x, y)")
+values = [
+    ("a", 4),
+    ("b", 5),
+    ("c", 3),
+    ("d", 8),
+    ("e", 1),
+]
+cur.executemany("insert into test values(?, ?)", values)
+con.create_window_function("sumint", 1, WindowSumInt)
+cur.execute("""
+    select x, sumint(y) over (
+        order by x rows between 1 preceding and 1 following
+    ) as sum_y
+    from test order by x
+""")
+print(cur.fetchall())
diff --git a/Doc/library/sqlite3.rst b/Doc/library/sqlite3.rst
index 852b684..60dfbef 100644
--- a/Doc/library/sqlite3.rst
+++ b/Doc/library/sqlite3.rst
@@ -473,6 +473,35 @@ Connection Objects
       .. literalinclude:: ../includes/sqlite3/mysumaggr.py
 
 
+   .. method:: create_window_function(name, num_params, aggregate_class, /)
+
+      Creates user-defined aggregate window function *name*.
+
+      *aggregate_class* must implement the following methods:
+
+      * ``step``: adds a row to the current window
+      * ``value``: returns the current value of the aggregate
+      * ``inverse``: removes a row from the current window
+      * ``finalize``: returns the final value of the aggregate
+
+      ``step`` and ``value`` accept *num_params* number of parameters,
+      unless *num_params* is ``-1``, in which case they may take any number of
+      arguments.  ``finalize`` and ``value`` can return any of the types
+      supported by SQLite:
+      :class:`bytes`, :class:`str`, :class:`int`, :class:`float`, and
+      :const:`None`.  Call :meth:`create_window_function` with
+      *aggregate_class* set to :const:`None` to clear window function *name*.
+
+      Aggregate window functions are supported by SQLite 3.25.0 and higher.
+      :exc:`NotSupportedError` will be raised if used with older versions.
+
+      .. versionadded:: 3.11
+
+      Example:
+
+      .. literalinclude:: ../includes/sqlite3/sumintwindow.py
+
+
    .. method:: create_collation(name, callable)
 
       Creates a collation with the specified *name* and *callable*. The callable will
diff --git a/Doc/whatsnew/3.11.rst b/Doc/whatsnew/3.11.rst
index 354e211..d803801 100644
--- a/Doc/whatsnew/3.11.rst
+++ b/Doc/whatsnew/3.11.rst
@@ -389,6 +389,10 @@ sqlite3
   serializing and deserializing databases.
   (Contributed by Erlend E. Aasland in :issue:`41930`.)
 
+* Add :meth:`~sqlite3.Connection.create_window_function` to
+  :class:`sqlite3.Connection` for creating aggregate window functions.
+  (Contributed by Erlend E. Aasland in :issue:`34916`.)
+
 
 sys
 ---