From f6a4965a22830a74cfbcc9b0b549d71d976bf5cb Mon Sep 17 00:00:00 2001 From: Eugene Crosser Date: Mon, 21 Dec 2015 00:11:55 +0300 Subject: [PATCH] change scheme to use sum of adjustments --- DATABASE | 29 +++++++++++++++++++++++++++++ select.sql | 12 ++++++++++++ 2 files changed, 41 insertions(+) create mode 100644 select.sql diff --git a/DATABASE b/DATABASE index 4108c08..b498c2a 100644 --- a/DATABASE +++ b/DATABASE @@ -57,3 +57,32 @@ mysql: +---------------------+-------+ 6 rows in set (0.00 sec) +2015-12-20: + +After some playing with it, I think it is a mistake. One of my assumed +goals was minimize carrying state along time. This is achieved in the +`counter` table, but not in `adjustment` table. Namely, when you need to +adjust before one or more later adjustments, you'd have to update the +later adjustment records. Advantage of this approach is that you need +to select just one row from the `adjustment` table in addition to the +row of the `counter` table. Disadvantage is as stated above, departure +from the "write only" principle. + +So I am changing the design to this: + + +select timestamp, value+adj as value from + (select c.timestamp timestamp, c.value value, + (select sum(value) from adjustment a + where a.timestamp <= c.timestamp + ) adj from counter c + ) t; + +so every row in the `adjustment` table contains incremental offset, +typically simply the last recorded value of the counter (unless some +events where lost while the sensor was down). Select is potentially +more expensive; given that I do not anticipate many adjustments this +should not be a problem for a single-user setup. For "industrial +size" setup, the problem is easily solved caching the result of the +"summing" select. + diff --git a/select.sql b/select.sql new file mode 100644 index 0000000..88bcba4 --- /dev/null +++ b/select.sql @@ -0,0 +1,12 @@ +select to_seconds(timestamp) as time, value+adj as value from + (select c.timestamp timestamp, c.value value, + (select sum(value) from coldadj a + where a.timestamp <= c.timestamp + ) adj from coldcnt c order by timestamp + ) t; +select to_seconds(timestamp) as time, value+adj as value from + (select c.timestamp timestamp, c.value value, + (select sum(value) from hotadj a + where a.timestamp <= c.timestamp + ) adj from hotcnt c order by timestamp + ) t; -- 2.39.2