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.

sqlite

 

More Information

SQLite Site

Related Articles

SQLite Adds Zipfile Support

SQLite 3.20 Improves Query Planner

SQLite 3.17 Adds SHA1 Extensions 

SQLite 3.16 Adds Pragma Functions  

SQLite 3.15 Adds Row Value Support

Portable Version Of DB Browser For SQLite

SQLite 3.9 

SQLite Updated

 

 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Banner


100 Episodes of 5mins of Postgres
08/03/2024

The popular PostgreSQL explainer series is celebrating its 100th release and beyond. Let's take a look at what it makes it so special.



Generative AI Training For All On Coursera
04/03/2024

Generative AI is on the loose, getting into business and commerce as well as into art, poetry and coding. Already useful, it  will become ever more useful as long as we use it properly. Coursera  [ ... ]


More News

raspberry pi books

 

Comments




or email your comment to: comments@i-programmer.info