On Thu, 07 Dec 2017 19:46:21 +0100, [email protected] wrote:
> Hi all,
>
> I have a DB i.e. like this:
>
> table values
> bc temp
> 35 123
> 35 124
> 35 123
> 20 123
> 12 123
> 12 123
> 16 123
> 35 123
> 35 123
> 35 123
> 35 123
> 35 123
>
> The value in temp (or all the other columns) is not of interest.
>
> Now I a looking for the basic concept how to count the number of rows where
> bc=bc_of_last_row after the last different bc.
>
> SELECT COUNT(bc) FROM values WHERE bc=35;
>
> gives me the number of all rows with bc=35, so here 8.
> But I am looking for 5, the number of rows after the last change of bc.
I recently had a similar but different requirement:
Store a series of CPU temperatures over time, but if the
temperature stays the same, just increment a count.
Again, not the same as your problem, but my solution might
inspire you with yours.
CREATE TABLE cputemp (
epoch INTEGER PRIMARY KEY NOT NULL -- "epochfr"
, epochto INTEGER DEFAULT NULL
, ctemp INTEGER NOT NULL
);
CREATE TRIGGER before_ins_cputemp BEFORE INSERT ON cputemp
FOR EACH ROW
WHEN NEW.ctemp == (SELECT ctemp FROM cputemp WHERE epoch ==
(SELECT max(epoch) FROM cputemp))
BEGIN
UPDATE cputemp set epochto = NEW.epoch
WHERE epoch == (SELECT max(epoch) FROM cputemp);
SELECT RAISE(IGNORE); -- do not insert a new row
END;
-- Optional trigger: set epochto in new rows,
-- could be done in other ways.
CREATE TRIGGER after_ins_cputemp AFTER INSERT ON cputemp
FOR EACH ROW
WHEN NEW.epochto IS NULL
BEGIN
UPDATE cputemp SET epochto = NEW.epoch
WHERE epoch == NEW.epoch;
END;
The INSERT looks like (awk code):
printf "INSERT INTO cputemp (epoch,ctemp) " \
"VALUES ( 0 + strftime('%%s','now'),%s);\n",cputemp
This can also be done in other ways of course.
Hope this helps.
--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
