SQLite Forum

How to create the views
Login

How to create the views

(1) By Cecil (CecilWesterhof) on 2021-02-08 15:24:36 [link] [source]

Background Information

Like a lot of other people I did get some extra kilos during the lockdown. And I already had to many.

I set some quite rigorous goals to get rid of those.

Today I was thinking: SQLite can help me.

The Tables I Created

CREATE  TABLE monthValues (
    month           INTEGER PRIMARY KEY,
    striveWeight    REAL    NOT NULL,
    realWeight      REAL
);
-- striveWeight is -1 in the first month, because that is when I started.
-- realWeight is only filled when the month has started yet.


CREATE  TABLE weekValues (
    week    INTEGER PRIMARY KEY,
    weight  REAL    NOT NULL
);

The Views I Would Like

I would like a view on monthValues that shows how much of I am. That would be:

    realWeight - striveWeight

but there is a catch: I do not want a value when striveWeight = -1, or when realWeight = NULL.

Can this be done?

I also would like a view that shows how many kilos I lost in a week. That would be:

    weight_from_previous_week - weight_from_this_week

Can this be done?

(2.2) By Larry Brasfield (larrybr) on 2021-02-08 15:57:55 edited from 2.1 in reply to 1 [source]

Not being snide here, but:

It can be done if a SELECT query will produce the result you wish to see. Once you know view creation syntax, your question would be, "How do I write a SELECT query to do X?"

... I do not want a value when striveWeight = -1, or when realWeight = NULL. Can this be done?

You need to put expressions: striveWeight <> -1 AND realWeight NOTNULL into a WHERE clause in your view's underlying query. See Expression Syntax.

Your weekly loss query can be accomplished by doing a same-table join and limiting results (via WHERE) to pairs where the left datetime and right datetime differ by a week (exactly or approximately, your choice.)

(Appended:) Your schema as shown does not permit week by week resolution. So you will want to fix that before writing that join.

(3.1) By Ryan Smith (cuz) on 2021-02-08 16:08:05 edited from 3.0 in reply to 1 [link] [source]

Problem 1 - This is a most fundamental SQL skill, unless you mean something different to this solution:

  SELECT * FROM monthValues WHERE striveWeight > 0 AND realWeight IS NOT NULL

Problem 2 - More fun:

  SELECT curWeek.week, curWeek.weight, prevWeek.weight, 
         curWeek.weight-prevWeek.weight AS weightLossHopeToGodItIsNegative
    FROM weekValues AS curWeek
    JOIN weekValues AS prevWeek ON prevWeek.week = curWeek.Week - 1
   WHERE curWeek.weight IS NOT NULL

Not tested, but should do it. :)

Note the JOIN clause here already filters for weeks that do not have previous weeks, so t his WHERE clause only has to check for nulls.

Edit: Initially mangled the query from a subquery - fixed now.

(4.1) By Ryan Smith (cuz) on 2021-02-08 16:21:23 edited from 4.0 in reply to 3.1 [link] [source]

After reading Larry's post and he helpfully pointed out that your schema doesn't contain time-resolved weeks, I need to add that in my solution I made the very large leap of faith assuming that your "week" value will simply start on the first week (1) and grow from there into infinity as the years roll by.

If (and only if) this is the case, the query will work.

Other than that, I have to add my voice to the ideal that a proper week timestamp will be more useful, in which case you can also get away with having only one table (the week one) and the month view can simply be a query (because we can work out if a row falls in a next month from its datetime-timestamp).

Edit: And also, you probably want to measure progress in constant intervals, like 4-weeks rather than months, since months have the nasty side effect of not being equal time-spans and so if your weightloss is 100% constant, the month view will still show sawtooth progress changes because Kg/Month will fluctuate even if Kg/minute is constant.

(Yes I work with data where this matters, for weightloss, you probably do not care :D)

(5) By Cecil (CecilWesterhof) on 2021-02-08 18:23:44 in reply to 4.1 [link] [source]

I should have thought a little longer before posting. :'-(

This are the views I have created:

CREATE VIEW monthValuesExt AS
SELECT *
,      striveWeight - realWeight AS extraLoss
FROM   monthValues
WHERE  striveWeight > 0
   AND realWeight IS NOT NULL
;
CREATE VIEW weekValuesExt AS
SELECT curWeek.*
,      prevWeek.weight                            AS oldWeight
,      ROUND(prevWeek.weight - curWeek.weight, 1) AS lost
FROM   weekValues AS curWeek
JOIN   weekValues AS prevWeek ON prevWeek.week = curWeek.Week - 1
;

I 'need' two tables. I measure at the first day (Sunday) of every week and the first day of the month. (It looks like the hour administration I had at several companies.)

For me it is probably not necessary, but I look into if the second few can be updated. (Previous week max of lower as current and dived by the number of weeks between them. But that is an exercise for when I am bored.)

I should be finished in half a year, so just week number (1 -27) should be good enough.