3 ms·
sqlite doesn't have linear regression functions, and doing the math manually is a bit awkward because "b" relies on "m". Instead of duplicating the math to cal
by Izkata 4y ago
sqlite doesn't have linear regression functions, and doing the math manually is a bit awkward because "b" relies on "m". Instead of duplicating the math to calculate "b", here's how to do it with CTEs:
CREATE TABLE vals (x, y);
INSERT INTO vals VALUES (1, 1), (2, 0.5), (3, 0.4), (4, 0.1), (5, 0);
WITH
m(v) AS (
SELECT ((COUNT(*) * (SUM(x * y))) - (SUM(x) * SUM(y))) / ((COUNT(*) * SUM(POW(x, 2))) - (POW(SUM(x), 2)))
FROM vals
),
b(v) AS (
SELECT (SUM(y) - (m.v * SUM(x))) / COUNT(*)
FROM vals JOIN m
)
SELECT
x AS real_x,
y AS real_y,
m.v AS m,
b.v AS b,
x * m.v + b.v AS interp_y
FROM vals JOIN m JOIN b;
real_x real_y m b interp_y
------ ------ ----- ---- -------------------
1 1 -0.24 1.12 0.88
2 0.5 -0.24 1.12 0.64
3 0.4 -0.24 1.12 0.4
4 0.1 -0.24 1.12 0.16
5 0 -0.24 1.12 -0.0800000000000001
If all you want is "m" and "b" the final query can just be "FROM m JOIN b" and the result will be 1 row.
- acjohnson55 4y agoI'd love to see this in blog post form! This seems like a case where it would be nice to be able to pass in a parameter `vals (x, y)`.
- Izkata 4y agoI've tried to get myself writing a few times but never succeeded, so I don't have a blog. But - I am actually doing parameters like so in the view where I copied the above from: CREATE TABLE view_confs (key, value); INSERT INTO view_confs VALUE ('start', 2), ('end', 4); Now prefix it with another one: vals_range(x, y) AS ( SELECT x, y FROM vals WHERE x >= (SELECT value FROM view_confs WHERE key = 'start') AND x < (SELECT value FROM view_confs WHERE key = 'end') ), And swap the rest of the original query to use "vals_range" instead of "vals". Just gotta remember to update view_confs with the new range whenever you want to change the view. I think it should work inside a transaction to avoid multiple threads interfering with each other, but also to be clear: This is a workaround for sqlite not having stored procedures, and not wanting to implement it in code (so I can JOIN to the view in other queries). Better to use stored procedures in other databases than this workaround.