SQLite: Subtract 1st Row from Last Row

Question

I have a large table data that has an ascending counter the column counter for each row. Assuming that my program is working correctly, counter should increase by 20 units per row.

Therefore, for each table the query Select (Max(counter)-Min(counter))/(Max(ROWID)-1) should return 20 if the program works correctly. If the program drops any rows, the query would return > 20.

As I know that Max and Min are in rows 0 and N, is there a better way to run this search without requiring the processor to search the entire table for the max value?

Answer 1

What you could do is maintain a table with a single row that keeps a record of the lowest and highest values.

You could maintain this table using TRIGGER's.

There would be a small overhead per insert,update,delete due to the triggering but scan for the max/min is not undertaken rather min/max values are calculated based upon the row being inserted/updated/deleted note that deletion takes your should increase by 20 units per row. as being a rule that can be applied.

For example, consider, the following demonstration. Noting that it includes an additional (unnecessary) trigger that allows the triggering to be monitored:-

DROP TABLE IF EXISTS maintable;
DROP TABLE IF EXISTS counterlowhigh;
DROP TABLE IF EXISTS trigger_monitor; /*<<<<<<<<<< ONLY EXISTS TO MONITOR THE TRIGGERING - OTEHREWISE NOT NEEDED */

CREATE TABLE IF NOT EXISTS maintable (id INTEGER PRIMARY KEY, counter INTEGER, othercolumns TEXT);
CREATE TABLE IF NOT EXISTS counterlowhigh (id INTEGER PRIMARY KEY, low INTEGER, high INTEGER);
INSERT INTO counterlowhigh VALUES(1,0,0);
/* ONLY EXISTS TO MONITOR THE TRIGGERING - OTEHREWISE NOT NEEDED */
CREATE TABLE IF NOT EXISTS trigger_monitor (id INTEGER PRIMARY KEY, timestamp text DEFAULT CURRENT_TIMESTAMP, maintable_id INTEGER, operation TEXT);
CREATE TRIGGER IF NOT EXISTS maintain_counterlowhigh_afterinsert AFTER INSERT ON maintable 
    BEGIN 
        UPDATE counterlowhigh SET low = min(low,new.counter), high = max(high,new.counter) WHERE id = 1;
        /* ONLY EXISTS TO MONITOR THE TRIGGERING - OTEHREWISE NOT NEEDED */
        INSERT INTO trigger_monitor (maintable_id,operation) 
            VALUES (
            new.id,
            'AFTER INSERT counter was '||new.counter
            ||' High='||(SELECT high FROM counterlowhigh WHERE id=1)
            ||' Low='||(SELECT low FROM counterlowhigh WHERE id= 1)
            )
        ;
    END
;
CREATE TRIGGER IF NOT EXISTS maintain_counterlowhigh_afterupdate AFTER UPDATE ON maintable
    BEGIN
        UPDATE counterlowhigh SET low = min(low,new.counter), high = max(high,new.counter) WHERE id = 1;
        /* ONLY EXISTS TO MONITOR THE TRIGGERING - OTEHREWISE NOT NEEDED */
        INSERT INTO trigger_monitor (maintable_id,operation) 
            VALUES (
            new.id,
            'AFTER UPDATE counter was '||new.counter
            ||' High='||(SELECT high FROM counterlowhigh WHERE id=1)
            ||' Low='||(SELECT low FROM counterlowhigh WHERE id= 1)
            )
        ;
    END
;
CREATE TRIGGER IF NOT EXISTS maintain_counterlowhig_afterdelete AFTER DELETE ON maintable
    BEGIN
        UPDATE counterlowhigh SET low = old.counter + 20 WHERE low = old.counter;
        UPDATE counterlowhigh SET high = old.counter - 20 WHERE high = old.counter;
        /* ONLY EXISTS TO MONITOR THE TRIGGERING - OTEHREWISE NOT NEEDED */
        INSERT INTO trigger_monitor (maintable_id,operation) 
            VALUES (
            old.id,
            'AFTER DELETE counter was '||old.counter
            ||' High='||(SELECT high FROM counterlowhigh WHERE id=1)
            ||' Low='||(SELECT low FROM counterlowhigh WHERE id= 1)
            )
        ;
    END
;

/* Test insertion  */
WITH 
    cte(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM cte LIMIT 10000)
INSERT INTO maintable (counter,othercolumns) SELECT i * 20,'other data '||i FROM cte
;
/* Show counterhighlow data after insertions */
SELECT * FROM counterlowhigh;
/* Test some deletions */
DELETE FROM maintable WHERE id = 1;
DELETE FROM maintable WHERE id IN (1 /*<<<<< already deleted by previous delete */,53,73,10000,10001) OR id > 10000 /*<<<< should not do anything */;
/* Show counterhighlow data after insertions */
SELECT * FROM counterlowhigh;

So initially the counterhighlow table has low and high as 0. After the 10000 insertions counterhighlow is :-

After the deletions (the lowest and highest rows being included in the deletions) then counterhighlow is :-

The trigger monitoring table includes 10004 rows, the first 10000 for the inserts e.g :-

....

The last 4 rows, the deletions being :-