hd_keywords series {generate_series}

Overview

The generate_series(START,END,STEP) [table-valued function] is a [loadable extension] included in the SQLite source tree, and compiled into the [command-line shell]. The generate_series() table has a single result column named "value" holding integer values and a number of rows determined by the parameters START, END, and STEP. The first row of the table has a value of START. Subsequent rows increase by STEP up to END.

Omitted parameters take on default values. STEP defaults to 1. END defaults to 9223372036854775807. START defaults to 0.

Equivalent Recursive Common Table Expression

The generate_series table can be simulated using a [recursive common table expression]. If the three parameters are $start, $end, and $step, then the equivalent common table expression is: WITH RECURSIVE generate_series(value) AS ( SELECT $start UNION ALL SELECT value+$step FROM generate_series WHERE value+$step<=$end ) ...

The common table expression works without having to load an extension. On the other hand, the extension is easier to program and faster.

Usage Examples

Generate all multiples of 5 less than or equal to 100: SELECT value FROM generate_series(5,100,5);

Generate the 20 random integer values: SELECT random() FROM generate_series LIMIT 20;

Find the name of every customer whose account number is an even multiple of 100 between 10000 and 20000. SELECT customer.name FROM customer, generate_series(10000,20000,100) WHERE customer.id=value; /* or */ SELECT name FROM customer WHERE id IN (SELECT value FROM generate_series(10000,20000,200));