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


Apache Storm Reworked In Java
10/06/2019

Apache Storm 2.0 has been released with better performance, new features, and integration with external systems. It has also had its core features rewritten in Java.



Amazon Open Sources Python Library for AWS Glue
04/06/2019

Amazon has open-sourced a Python library known as Athena Glue Service Logs (AGSlogger) that makes it easier to parse log formats into AWS Glue for analysis and is intended for use with AWS servic [ ... ]


More News

appC

 



 

Comments




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