Skip to content

Latest commit

 

History

History
58 lines (40 loc) · 2.1 KB

stats.md

File metadata and controls

58 lines (40 loc) · 2.1 KB
Raw

stats: Mathematical Statistics in SQLite

Common statistical functions. Adapted from extension-functions.c by Liam Healy, percentile.c and series.c by D. Richard Hipp.

Aggregate Functions

  • median(x) — median (50th percentile),
  • percentile_25(x) — 25th percentile,
  • percentile_75(x) — 75th percentile,
  • percentile_90(x) — 90th percentile,
  • percentile_95(x) — 95th percentile,
  • percentile_99(x) — 99th percentile,
  • percentile(x, perc) — custom percentile (perc between 0 and 100),
  • stddev(x) or stddev_samp(x) — sample standard deviation,
  • stddev_pop(x) — population standard deviation,
  • variance(x) or var_samp(x) — sample variance,
  • var_pop(x) — population variance.

generate_series(start[, stop[, step]])

This table-valued function generates a sequence of integer values starting with start, ending with stop (inclusive) with an optional step.

Generate all integers from 1 to 99:

select * from generate_series(1, 99);

Generate all multiples of 5 less than or equal to 100:

select * from generate_series(5, 100, 5);

Generate 20 random integer values:

select random() from generate_series(1, 20);

The generate_series() table has a single result column named value holding integer values, and a number of rows determined by the parameters start, stop, and step. The first row of the table has a value of start. Subsequent rows increase by step up to stop.

stop defaults to 9223372036854775807. step defaults to 1.

Installation and Usage

SQLite command-line interface:

sqlite> .load ./stats
sqlite> select median(value) from generate_series(1, 99);

See How to Install an Extension for usage with IDE, Python, etc.

⬇️ Download • ✨ Explore • 🚀 Follow