| SQLite Adds Support For Window Functions |
| Written by Kay Ewbank |
| Thursday, 20 September 2018 |
|
The developers of SQLite have released a new version with support for Window functions and an improved query optimizer. SQLite is an in-process library that implements a self-contained, serverless, transactional SQL database engine. The entire SQL database with multiple tables, indexes, triggers, and views, is contained in a single disk file. The new support for Window functions makes it possible to write SQL statements where the input values are taken from a "window" of one or more rows in the results set of a SELECT statement, so you can write statements such as:
SELECT x, y, row_number() OVER win1, rank() OVER win2
FROM t0
WINDOW win1 AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
win2 AS (PARTITION BY y ORDER BY x)
ORDER BY x;
Window functions are distinguished from ordinary SQL functions by the presence of an OVER clause. In addition to the Window function support, this release has an enhanced Alter Table command that lets you rename columns within a table using ALTER TABLE table RENAME COLUMN oldname TO newname. The table rename feature has been modified so that it also updates references to the renamed table in triggers and views. The query optimizer is another element that has been improved, particularly when used with aggregate queries. The optimizer no longer loads columns that aren't necessary in an aggregate query where the columns aren't within an aggregate function and aren't not part of the GROUP BY clause. Other improvements to the query optimizer improve the performance in queries that use an IN operator where the column being used for the search isn't the left-most column in a multi-column index; and the optimizer now uses the transitive property to try to propagate constant values within the WHERE clause. For example, convert "a=99 AND b=a" into "a=99 AND b=99". The other interesting improvement is the addition of a Geopoly module. This is an alternative interface to the R-Tree extension that uses the GeoJSON notation to describe two-dimensional polygons. Geopoly includes functions for detecting when one polygon is contained within or overlaps with another. It can be used to compute the area enclosed by a polygon, or do carry out linear transformations of polygons, or to render polygons as SVG. |
