Create view with best result pro week
(1) By Cecil (CecilWesterhof) on 2020-12-05 15:10:11 [link] [source]
I am going to use a table like the following: CREATE TABLE results ( resultID INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT NOT NULL, result INTEGER NOT NULL, CONSTRAINT date CHECK(date = date(strftime('%s', date), 'unixepoch')), CONSTRAINT result CHECK(TYPEOF(result) = 'integer') ) I would like to see the best result pro week. Is it possible to create a VIEW that looks like: CREATE TABLE bestResults ( year INTEGER NOT NULL, week INTEGER NOT NULL, best INTEGER NOT NULL, PRIMARY KEY(year, week) ) Or would I be better of filling a real table once a week with the best value from the last week?
(2) By anonymous on 2020-12-05 16:45:22 in reply to 1 [link] [source]
date is a reserved word (inadvisable as a column name).
Try:
SELECT year,
week,
max("result") AS best
FROM (
SELECT strftime('%W', "date") AS week,
strftime('%Y', "date") AS year,
"result"
FROM results
) a
GROUP BY year,
week;
(3.1) By Keith Medcalf (kmedcalf) on 2020-12-05 16:52:35 edited from 3.0 in reply to 1 [source]
Or would I be better of filling a real table once a week with the best value from the last week?
Define "best" and "week".
(4) By Cecil (CecilWesterhof) on 2020-12-05 17:43:18 in reply to 3.1 [link] [source]
Last week (there is only data on workdays) was 2020-11-30 - 2020-12-04. Best is the lowest value. (It is a ranking.) The last week I had the values: 202, 203, 60, 61, 27, 102, 24, 29. So best for week 49 would be 24.
(5) By Keith Medcalf (kmedcalf) on 2020-12-05 18:20:06 in reply to 3.1 [link] [source]
Assuming that "week" is per ISO Year/Week, then you could do the following:
CREATE TABLE results (
resultID INTEGER PRIMARY KEY AUTOINCREMENT,
date TEXT NOT NULL,
ISOStamp TEXT as printf('%s-%02d', strftime('%Y', cast(julianday(date)-1721061.5 as integer)/7*7+1721061.5), (strftime('%j', cast(julianday(date)-1721061.5 as integer)/7*7+1721064.5)+6)/7) stored,
result INTEGER NOT NULL,
CONSTRAINT date CHECK(date = date(strftime('%s', date), 'unixepoch')),
CONSTRAINT result CHECK(TYPEOF(result) = 'integer')
);
Then you merely need to query for what you want grouped by ISOStamp. Example:
select date, max(result) from results group by isostamp;
Your will need an index on ISOStamp of course.
You can change the ISOWeek definition by changing the values of the constants (the ISO definition starts a week on a Monday and the first week of a year is the week in which the first Thursday falls in the new year).
(6) By Cecil (CecilWesterhof) on 2020-12-05 19:46:16 in reply to 5 [link] [source]
Looks very promising. Would it not be better to use integer? There is something wrong with the syntax. I get: Error: near "as": syntax error I am using SQLite 3.27.2.
(7) By Keith Medcalf (kmedcalf) on 2020-12-06 02:27:01 in reply to 6 [link] [source]
Probably to old a version of SQLite3 or maybe it is missing an extra set of brackets around the expression.
However, it appears that has a little difficulty with getting the correct ISO year. Presuming that you do not need to actually know a meaningful year number, just a valid arbitrary week number to group by, you could use the following (assuming that a week starts on Monday).
CREATE TABLE results (
resultID INTEGER PRIMARY KEY AUTOINCREMENT,
date TEXT NOT NULL,
Weekno INTEGER as (cast(julianday(date)-1721061.5 as integer)/7) stored,
result INTEGER NOT NULL,
CONSTRAINT date CHECK(date = date(strftime('%s', date), 'unixepoch')),
CONSTRAINT result CHECK(TYPEOF(result) = 'integer')
);
or for old versions of SQLite3 that do not support computed solumns then you can compute the Weekno each time you need it, though it would be far more efficient to store only when the data is stored or the date changed.
create view results_week
as
select *,
cast(julianday(date)-1721061.5 as integer)/7 as WeekNo
from results;
and then do the same base select just from the view this time.
select date, max(result) from results_week group by Weekno;
The actual weekno field is the number of weeks (base 0) since '0000-01-03', which is a meaningless arbitrary number. But it is unique for each week.
(8.1) By Keith Medcalf (kmedcalf) on 2020-12-06 05:37:14 edited from 8.0 in reply to 5 [link] [source]
The problems are typo's. It should be:
CREATE TABLE results (
resultID INTEGER PRIMARY KEY AUTOINCREMENT,
date TEXT NOT NULL,
ISOStamp TEXT as (printf('%s-%02d', strftime('%Y', cast(julianday(date)-1721061.5 as integer)/7*7+1721064.5), (strftime('%j', cast(julianday(date)-1721061.5 as integer)/7*7+1721064.5)+6)/7)) stored,
result INTEGER NOT NULL,
CONSTRAINT date CHECK(date = date(strftime('%s', date), 'unixepoch')),
CONSTRAINT result CHECK(TYPEOF(result) = 'integer')
);
The Year part of the ISO Date should be the year part of the Thursday of the week (note the changed second constant julian day number) and the computed as expression must be surrounded in brackets.
(9) By Cecil (CecilWesterhof) on 2020-12-06 13:38:21 in reply to 8.1 [link] [source]
Still goes wrong. Maybe my version of SQLite is to old. :'-( The CREATE VIEW in another of your replies seems to work.
(10) By Cecil (CecilWesterhof) on 2020-12-06 13:43:50 in reply to 7 [link] [source]
Computed columns is added in 3.31. Mine is to old. I can use a view, or triggers. I will start with the view and after that look into triggers.
(11) By Cecil (CecilWesterhof) on 2020-12-06 14:28:15 in reply to 7 [link] [source]
For the moment I have as view: CREATE VIEW weekResults AS SELECT * , STRFTIME('%W', dateStr) as weekNr FROM results ; And use the following select: SELECT SUBSTR(dateStr, 1, 4) AS year , weekNR , MIN(result) AS bestResult FROM weekResults GROUP BY year , weekNr ; At the moment I do not use weekResults. Would it be more efficient to merge the select into the view, or is that not a big win?
(12) By Cecil (CecilWesterhof) on 2020-12-06 16:10:02 in reply to 1 [link] [source]
I solved it with triggers. The week numbers are for weeks starting on Monday instead of Sunday, but I can live with that. I created the following table: CREATE TABLE results ( resultID INTEGER PRIMARY KEY AUTOINCREMENT, dateStr TEXT NOT NULL, weekNr INTEGER, -- NOT NULL result INTEGER NOT NULL, CONSTRAINT date CHECK(dateStr = date(strftime('%s', dateStr), 'unixepoch')), -- CONSTRAINT weekNr CHECK(TYPEOF(weekNr) = 'integer') CONSTRAINT result CHECK(TYPEOF(result) = 'integer') ) There are two things I do not like, but that is for a new thread. And I created the following triggers: CREATE TRIGGER resultsInsert AFTER INSERT ON results BEGIN UPDATE results SET weekNr = STRFTIME('%W', results.dateStr) WHERE rowId = NEW.RowId; END CREATE TRIGGER resultsUpdateDateStr AFTER UPDATE OF dateStr, weekNr ON results BEGIN UPDATE results SET weekNr = STRFTIME('%W', results.dateStr) WHERE rowId = RowId; END Weeknr should not be updated manually, but in case someone does I added it. Better save as sorry. ;-) This does what I want. I do not have much data, but when there would be: could I create a index on the year part of dateStr with weekNr?