Re: [sqlite] Doing math in sqlite
On Thu 20 Dec 2018 4:21 PM, Jungle Boogie wrote: > Hi All, > > This is more of a how do I do this in sql question. I apologize in advance > for a simple question, but I need to learn somehow, so any pointers are > appreciated. Thank you all for the helpful replies and education on doing math with sqlite. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing math in sqlite
You can use recently supported window function (portable): SELECT * FROM ( SELECT car, lag(date) OVER w last_date, date, od_reading - (lag(od_reading) OVER w) diff FROM mileage WINDOW w AS (PARTITION BY car ORDER BY date) ) q WHERE diff IS NOT NULL; 2018/12/21 11:48, Jungle Boogie: > On Thu 20 Dec 2018 6:26 PM, Barry Smith wrote: >> >> >>> On 20 Dec 2018, at 4:21 pm, Jungle Boogie wrote: >>> >>> Hi All, >>> >>> Some sample data: >>> 2018/04/15,foo,170644 >>> 2018/04/15,bar.69625 >>> 2018/04/22,foo,170821 >>> 2018/04/22,bar,69914 >>> 2018/04/29,foo,171006 >>> 2018/04/29,bar,70123 >>> 2018/05/06,foo,171129 >>> 2018/05/06,bar,70223 >>> 2018/05/09,foo,171178 >>> 2018/05/20,foo,171304 >>> 2018/05/20,bar,70517 >>> >> >> I wouldn't call my solution elegant, but if you put a unique constraint on >> the "date" column, and want your query to return null if there is not a >> record exactly on that date: >> >> SELECT >>(SELECT od_reading FROM mileage WHERE date = '2018/05/20' AND car='bar') >>- >>(SELECT od_reading FROM mileage WHERE date='2018/05/06' AND car='bar') >> As ExactMileage >> >> You can rephrase that as: >> SELECT end.od_reading - start.od_reading FROM mileage AS start, mileage AS >> end WHERE end.date='2018/05/20' AND start.date='2018/05/06' AND end.car >> ='bar' and start.car='bar' >> >> Or even you could use CTEs to repeat yourself less: >> WITH OnlyBar AS SELECT * FROM mileage WHERE car='bar' >> SELECT end.od_reading - start.od_reading FROM OnlyBar AS start, OnlyBar AS >> end WHERE end.date='2018/05/20' AND start.date='2018/05/06 >> >> If you might query between two dates that don't necessarily have a record, >> the best you can do is give a range of what the possible driven distance may >> be: >> SELECT >>(SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/20' AND >> car='bar') >>- >>(SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/06' AND >> car='bar') >> AS MaxPossible, >>(SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/20' AND >> car='bar') >>- >>(SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/06' AND >> car='bar') >> AS MinPossible >> >> The second query finds the records before and after the dates in question >> (or uses data on exactly that date) to figure out the minimum and maximum >> possible distances that may have been driven between the dates of interest. >> >> Note that there is a more efficient form of the second query, where one >> selects just od_reading and orders by date, with a limit of 1 instead of >> using the MIN and MAX functions; if your table is going to be large then >> that is a better option (with an index on (car, date), the value can then be >> computed with an index seek instead of a table scan). I used the min/max >> version because it's simpler and easier to read >> > > Thanks all for for the helpful replies! > > All of them work, as expected, but I see I wasn't exactly clear with my second > part of the question. > > Is it possible to determine how many miles were driven the previous week with > current week's data in one query. > > For example with foo: > 2018/05/20 - 2018/05/09 = 126 > 2018/05/09 - 2018/04/29 = 172 > 2018/04/29 - 2018/04/22 = 185 > > Basically some kind of for loop through all the matches of 'foo' > >>> >>> Thanks, >>> j.b. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing math in sqlite
The following will give you information pretty much as your example: car, start date, end date, distance. SELECT m1.Car, m2.Date AS StartDate, m1.Date AS endDate, m1.od_reading - m2.od_reading AS distance FROM mileage AS m1 JOIN mileage AS m2 ON m2.rowid = (SELECT rowid FROM mileage m3 WHERE m3.Date < m1.Date AND m3.car = m1.car ORDER BY Date DESC LIMIT 1) WHERE Distance IS NOT NULL; You can add an additional "AND CAR ='foo'" if you only want data about foo. Note: this uses the rowid, which is a SQLite specific feature, as a match on the join because it's slightly more efficient. You could also have the join condition stated in more general SQL terms as: JOIN mileage AS m2 ON m2.car = m1.car AND m2.Date = (SELECT Date FROM mileage m3 WHERE m3.Date < m1.Date AND m3.car = m1.car ORDER BY Date DESC LIMIT 1) On Thu, 20 Dec 2018 at 19:48, Jungle Boogie wrote: > On Thu 20 Dec 2018 6:26 PM, Barry Smith wrote: > > > > > > > On 20 Dec 2018, at 4:21 pm, Jungle Boogie > wrote: > > > > > > Hi All, > > > > > > Some sample data: > > > 2018/04/15,foo,170644 > > > 2018/04/15,bar.69625 > > > 2018/04/22,foo,170821 > > > 2018/04/22,bar,69914 > > > 2018/04/29,foo,171006 > > > 2018/04/29,bar,70123 > > > 2018/05/06,foo,171129 > > > 2018/05/06,bar,70223 > > > 2018/05/09,foo,171178 > > > 2018/05/20,foo,171304 > > > 2018/05/20,bar,70517 > > > > > > > I wouldn't call my solution elegant, but if you put a unique constraint > on the "date" column, and want your query to return null if there is not a > record exactly on that date: > > > > SELECT > >(SELECT od_reading FROM mileage WHERE date = '2018/05/20' AND > car='bar') > >- > >(SELECT od_reading FROM mileage WHERE date='2018/05/06' AND car='bar') > > As ExactMileage > > > > You can rephrase that as: > > SELECT end.od_reading - start.od_reading FROM mileage AS start, mileage > AS end WHERE end.date='2018/05/20' AND start.date='2018/05/06' AND end.car > ='bar' and start.car='bar' > > > > Or even you could use CTEs to repeat yourself less: > > WITH OnlyBar AS SELECT * FROM mileage WHERE car='bar' > > SELECT end.od_reading - start.od_reading FROM OnlyBar AS start, OnlyBar > AS end WHERE end.date='2018/05/20' AND start.date='2018/05/06 > > > > If you might query between two dates that don't necessarily have a > record, the best you can do is give a range of what the possible driven > distance may be: > > SELECT > >(SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/20' AND > car='bar') > >- > >(SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/06' AND > car='bar') > > AS MaxPossible, > >(SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/20' AND > car='bar') > >- > >(SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/06' AND > car='bar') > > AS MinPossible > > > > The second query finds the records before and after the dates in > question (or uses data on exactly that date) to figure out the minimum and > maximum possible distances that may have been driven between the dates of > interest. > > > > Note that there is a more efficient form of the second query, where one > selects just od_reading and orders by date, with a limit of 1 instead of > using the MIN and MAX functions; if your table is going to be large then > that is a better option (with an index on (car, date), the value can then > be computed with an index seek instead of a table scan). I used the min/max > version because it's simpler and easier to read > > > > Thanks all for for the helpful replies! > > All of them work, as expected, but I see I wasn't exactly clear with my > second > part of the question. > > Is it possible to determine how many miles were driven the previous week > with > current week's data in one query. > > For example with foo: > 2018/05/20 - 2018/05/09 = 126 > 2018/05/09 - 2018/04/29 = 172 > 2018/04/29 - 2018/04/22 = 185 > > Basically some kind of for loop through all the matches of 'foo' > > > > > > > Thanks, > > > j.b. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing math in sqlite
On Thursday, 20 December, 2018 17:32, Jens Alfke wrote: >> On Dec 20, 2018, at 4:21 PM, Jungle Boogie wrote: >> select od_reading from mileage where car='foo' limit 1 >> select od_reading from mileage where car='bar' order by od_reading >> desc limit 1 >Note: the first query should use “order by od_reading”, otherwise the >order is undefined. >A clearer way to specify these is > select min(od_reading) from mileage where car=‘foo’ > select max(od_reading) from mileage where car=‘foo’ >> How do I subtract the last query from the first query to determine >> how many miles were driven? >It’s super simple: >(select od_reading from mileage where car='foo' limit 1) - (select >od_reading from mileage where car='bar' order by od_reading desc >limit 1) Or, more simply, select max(od_reading) - min(od_reading) as miles_driven from mileage where car='bar'; --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing math in sqlite
On Thu 20 Dec 2018 6:26 PM, Barry Smith wrote: > > > > On 20 Dec 2018, at 4:21 pm, Jungle Boogie wrote: > > > > Hi All, > > > > Some sample data: > > 2018/04/15,foo,170644 > > 2018/04/15,bar.69625 > > 2018/04/22,foo,170821 > > 2018/04/22,bar,69914 > > 2018/04/29,foo,171006 > > 2018/04/29,bar,70123 > > 2018/05/06,foo,171129 > > 2018/05/06,bar,70223 > > 2018/05/09,foo,171178 > > 2018/05/20,foo,171304 > > 2018/05/20,bar,70517 > > > > I wouldn't call my solution elegant, but if you put a unique constraint on > the "date" column, and want your query to return null if there is not a > record exactly on that date: > > SELECT >(SELECT od_reading FROM mileage WHERE date = '2018/05/20' AND car='bar') >- >(SELECT od_reading FROM mileage WHERE date='2018/05/06' AND car='bar') > As ExactMileage > > You can rephrase that as: > SELECT end.od_reading - start.od_reading FROM mileage AS start, mileage AS > end WHERE end.date='2018/05/20' AND start.date='2018/05/06' AND end.car > ='bar' and start.car='bar' > > Or even you could use CTEs to repeat yourself less: > WITH OnlyBar AS SELECT * FROM mileage WHERE car='bar' > SELECT end.od_reading - start.od_reading FROM OnlyBar AS start, OnlyBar AS > end WHERE end.date='2018/05/20' AND start.date='2018/05/06 > > If you might query between two dates that don't necessarily have a record, > the best you can do is give a range of what the possible driven distance may > be: > SELECT >(SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/20' AND > car='bar') >- >(SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/06' AND > car='bar') > AS MaxPossible, >(SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/20' AND > car='bar') >- >(SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/06' AND > car='bar') > AS MinPossible > > The second query finds the records before and after the dates in question (or > uses data on exactly that date) to figure out the minimum and maximum > possible distances that may have been driven between the dates of interest. > > Note that there is a more efficient form of the second query, where one > selects just od_reading and orders by date, with a limit of 1 instead of > using the MIN and MAX functions; if your table is going to be large then that > is a better option (with an index on (car, date), the value can then be > computed with an index seek instead of a table scan). I used the min/max > version because it's simpler and easier to read > Thanks all for for the helpful replies! All of them work, as expected, but I see I wasn't exactly clear with my second part of the question. Is it possible to determine how many miles were driven the previous week with current week's data in one query. For example with foo: 2018/05/20 - 2018/05/09 = 126 2018/05/09 - 2018/04/29 = 172 2018/04/29 - 2018/04/22 = 185 Basically some kind of for loop through all the matches of 'foo' > > > > Thanks, > > j.b. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing math in sqlite
> On 20 Dec 2018, at 4:21 pm, Jungle Boogie wrote: > > Hi All, > > This is more of a how do I do this in sql question. I apologize in advance > for a simple question, but I need to learn somehow, so any pointers are > appreciated. > > My very simple schema: > > CREATE TABLE mileage ( > date text, > "car" text, > "od_reading" integer > ) > > Some sample data: > 2018/04/15,foo,170644 > 2018/04/15,bar.69625 > 2018/04/22,foo,170821 > 2018/04/22,bar,69914 > 2018/04/29,foo,171006 > 2018/04/29,bar,70123 > 2018/05/06,foo,171129 > 2018/05/06,bar,70223 > 2018/05/09,foo,171178 > 2018/05/20,foo,171304 > 2018/05/20,bar,70517 > > I can select the first and last od_readings pertaining to a particular car > this > way: > select od_reading from mileage where car='foo' limit 1 > select od_reading from mileage where car='bar' order by od_reading desc limit > 1 > > How do I subtract the last query from the first query to determine how many > miles were driven? > > Next, is there an elegant way to see how many miles difference there is > between > two readings of the same car? > > For instance, how many miles difference are there between bar on 2018/05/20 > and > 2018/05/06? I wouldn't call my solution elegant, but if you put a unique constraint on the "date" column, and want your query to return null if there is not a record exactly on that date: SELECT (SELECT od_reading FROM mileage WHERE date = '2018/05/20' AND car='bar') - (SELECT od_reading FROM mileage WHERE date='2018/05/06' AND car='bar') As ExactMileage You can rephrase that as: SELECT end.od_reading - start.od_reading FROM mileage AS start, mileage AS end WHERE end.date='2018/05/20' AND start.date='2018/05/06' AND end.car ='bar' and start.car='bar' Or even you could use CTEs to repeat yourself less: WITH OnlyBar AS SELECT * FROM mileage WHERE car='bar' SELECT end.od_reading - start.od_reading FROM OnlyBar AS start, OnlyBar AS end WHERE end.date='2018/05/20' AND start.date='2018/05/06 If you might query between two dates that don't necessarily have a record, the best you can do is give a range of what the possible driven distance may be: SELECT (SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/20' AND car='bar') - (SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/06' AND car='bar') AS MaxPossible, (SELECT MAX(od_reading) FROM mileage WHERE date <= '2018/05/20' AND car='bar') - (SELECT MIN(od_reading) FROM mileage WHERE date >= '2018/05/06' AND car='bar') AS MinPossible The second query finds the records before and after the dates in question (or uses data on exactly that date) to figure out the minimum and maximum possible distances that may have been driven between the dates of interest. Note that there is a more efficient form of the second query, where one selects just od_reading and orders by date, with a limit of 1 instead of using the MIN and MAX functions; if your table is going to be large then that is a better option (with an index on (car, date), the value can then be computed with an index seek instead of a table scan). I used the min/max version because it's simpler and easier to read > > Thanks, > j.b. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing math in sqlite
For the cost of a single table scan, you may be better with: select max(foo) - min(foo) where etc. S. -- Anyone using floating point for financial computations needs their head examined. On 12/20/18 4:32 PM, Jens Alfke wrote: On Dec 20, 2018, at 4:21 PM, Jungle Boogie wrote: select od_reading from mileage where car='foo' limit 1 select od_reading from mileage where car='bar' order by od_reading desc limit 1 Note: the first query should use “order by od_reading”, otherwise the order is undefined. A clearer way to specify these is select min(od_reading) from mileage where car=‘foo’ select max(od_reading) from mileage where car=‘foo’ How do I subtract the last query from the first query to determine how many miles were driven? It’s super simple: (select od_reading from mileage where car='foo' limit 1) - (select od_reading from mileage where car='bar' order by od_reading desc limit 1) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Doing math in sqlite
> On Dec 20, 2018, at 4:21 PM, Jungle Boogie wrote: > > select od_reading from mileage where car='foo' limit 1 > select od_reading from mileage where car='bar' order by od_reading desc limit > 1 Note: the first query should use “order by od_reading”, otherwise the order is undefined. A clearer way to specify these is select min(od_reading) from mileage where car=‘foo’ select max(od_reading) from mileage where car=‘foo’ > How do I subtract the last query from the first query to determine how many > miles were driven? It’s super simple: (select od_reading from mileage where car='foo' limit 1) - (select od_reading from mileage where car='bar' order by od_reading desc limit 1) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Doing math in sqlite
Hi All, This is more of a how do I do this in sql question. I apologize in advance for a simple question, but I need to learn somehow, so any pointers are appreciated. My very simple schema: CREATE TABLE mileage ( date text, "car" text, "od_reading" integer ) Some sample data: 2018/04/15,foo,170644 2018/04/15,bar.69625 2018/04/22,foo,170821 2018/04/22,bar,69914 2018/04/29,foo,171006 2018/04/29,bar,70123 2018/05/06,foo,171129 2018/05/06,bar,70223 2018/05/09,foo,171178 2018/05/20,foo,171304 2018/05/20,bar,70517 I can select the first and last od_readings pertaining to a particular car this way: select od_reading from mileage where car='foo' limit 1 select od_reading from mileage where car='bar' order by od_reading desc limit 1 How do I subtract the last query from the first query to determine how many miles were driven? Next, is there an elegant way to see how many miles difference there is between two readings of the same car? For instance, how many miles difference are there between bar on 2018/05/20 and 2018/05/06? Thanks, j.b. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
