SQLite Forum

Create view with best result pro week
Login

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 [link] [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 [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?