Folks,
One response to my post on Stack Overflow, but no solution, so I'll post
it here. (Recurse.sql).
Studied documentation without being able to de-optimize SET from single
Scalar evaluation.
Regards, Brian Burleigh
-------------- next part --------------
// In which we investigate recursive update
.mode list
SELECT 'Create and populate tables of detail and to hold aggregate';
DROP TABLE IF EXISTS temp.detail;
DROP TABLE IF EXISTS temp.average;
CREATE TEMP TABLE detail (rowid INTEGER PRIMARY Key AUTOINCREMENT,
grp INTEGER,atom FLOAT);
INSERT INTO detail (grp,atom) VALUES (2,2);
INSERT INTO detail (grp,atom) VALUES (2,4);
INSERT INTO detail (grp,atom) VALUES (1,3);
INSERT INTO detail (grp,atom) VALUES (1,5);
INSERT INTO detail (grp,atom) VALUES (3,1);
INSERT INTO detail (grp,atom) VALUES (3,3);
SELECT * FROM detail;
CREATE TEMP TABLE average (rowid INTEGER PRIMARY KEY AUTOINCREMENT, avrg FLOAT);
INSERT INTO average (avrg) VALUES (1.0);
INSERT INTO average (avrg) VALUES (1.0);
INSERT INTO average (avrg) VALUES (1.0);
.mode column average
.mode column detail
.header on
.width 7
SELECT avrg as Average FROM average;
SELECT 'SELECT produces three rows';
with recursive loop(bid) as (Values(1) union all
select bid + 1 from loop LIMIT 3)
SELECT AVG(atom) AS Average from detail
where grp IN loop GROUP BY grp;
SELECT 'Within SET clause, SELECT produces one scalar result';
with recursive loop(bid) as (Values(1) union all
select bid + 1 from loop LIMIT 3)
UPDATE average SET avrg =
(SELECT AVG(atom) from detail
WHERE grp IN loop GROUP BY grp HAVING grp IN loop)
where rowid IN loop;
SELECT 'Single SET result populates all target rows';
SELECT avrg AS Average from average;
-- End of the example code
-- But Insert works beautifully
DROP TABLE average;
CREATE TEMP TABLE average (rowid INTEGER PRIMARY KEY AUTOINCREMENT, avrg FLOAT);
INSERT INTO average (avrg)
with recursive loop(grp) as (Values(1) union all
select grp + 1 from loop LIMIT 3)
SELECT AVG(atom) from detail NATURAL JOIN loop
GROUP BY grp;
SELECT avrg AS Average FROM average;
-- Nested recursion attempt Recursive aggregate queries not supported
WITH RECURSIVE loop(grp,avg) AS (SELECT 0,1 UNION ALL
SELECT loop.grp+1,AVG(atom) FROM detail,loop
GROUP BY loop.grp HAVING detail.grp = loop.grp)
UPDATE average SET avrg = loop.avg
where rowid IN loop.grp;
SELECT avrg AS Average FROM average;
with recursive loop(grp) as (Values(1) union all
select grp + 1 from loop LIMIT 3)
UPDATE average SET avrg =
(SELECT AVG(atom) from detail NATURAL JOIN loop
GROUP BY loop.grp HAVING detail.grp IN loop)
where rowid IN loop;
SELECT avrg AS Average FROM average;
