Doing math in sqlite

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Doing math in sqlite

Jungle Boogie
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Doing math in sqlite

Jens Alfke-2


> On Dec 20, 2018, at 4:21 PM, Jungle Boogie <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Doing math in sqlite

Shevek
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Doing math in sqlite

Barry Smith
In reply to this post by Jungle Boogie


> On 20 Dec 2018, at 4:21 pm, Jungle Boogie <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Doing math in sqlite

Jungle Boogie
On Thu 20 Dec 2018  6:26 PM, Barry Smith wrote:

>
>
> > On 20 Dec 2018, at 4:21 pm, Jungle Boogie <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Doing math in sqlite

Keith Medcalf
In reply to this post by Jens Alfke-2

On Thursday, 20 December, 2018 17:32, Jens Alfke <[hidden email]> wrote:

>> On Dec 20, 2018, at 4:21 PM, Jungle Boogie <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Doing math in sqlite

Barry Smith
In reply to this post by Jungle Boogie
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 <[hidden email]> wrote:

> On Thu 20 Dec 2018  6:26 PM, Barry Smith wrote:
> >
> >
> > > On 20 Dec 2018, at 4:21 pm, Jungle Boogie <[hidden email]>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Doing math in sqlite

Dingyuan Wang
In reply to this post by Jungle Boogie
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Doing math in sqlite

Jungle Boogie
In reply to this post by Jungle Boogie
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users