Comparing rows

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

Comparing rows

Matthew Halliday
Good morning all

Sorry for the long email.  I'm back to using SQLite after some years away
from it and from databases in general, so a bit rusty.  I'be been trying to
figure this out for almost a week now but can't quite get my head around it
although I think I understand the principles.

My problem is I am not sure how to calculate between rows.  I have looked
at many examples online and tried hard to adapt them into my scenario but
after a week of it its getting frustrationg.

We have a small db that monitors disk space on 40-odd servers in a
manufacturing situation.  We monitor disk growth day by day every 6 hours
so we can project forward what we will need in the coming years and how
long current space will last with some new kit coming on stream.

A vbscript (we have old legacy kit that doesn't understand Powershell) runs
a regular WMI query and simply pulls of the disk data.

I have a simple import table: id, servername, drive, capacity, used_mb,
free_mb, free_pc (%) and a date_time field.  I have a large number of csv
files to import and crunch, so I simply need to subtract the previous
Used_mb, free_mb and free_pc values from the curent ones to get the disk
data.  I want to do:

new Used_mb - old used mb
old free_mb - new free_mb
old free_pc -new free_pc

(its in Mb because its the best value for what I need.  I'll convert it
later as needed)

I understand I can do a self-join and create a virtual table but don't
really understand how it works.
 tried something like

SELECT DISTINCT a.*
FROM   (SELECT servername, date_time, drive, used_mb
        FROM   tmp_dspace_import
        GROUP BY servername, drive, date_time) a
JOIN   (SELECT servername, date_time(getdate) -1, drive, used_mb
        FROM   tmp_dspace_import
         GROUP BY servername, drive, date_time) b
ON     a.servername = b.servername AND a.used_mb > b.used_mb AND
a.date_time > b.date_time

just getting really confused now.

I have ammended the script to import direct into the db after generating
the csv file and I want to run the update there either as an UPDATE or a
trigger.

Its in a db because I have other purposes I can use the data for.   The
import table is a temp location.

Hope this helps and I'm not waffling here.
_______________________________________________
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: Comparing rows

Clemens Ladisch
Matthew Halliday wrote:
> I have a simple import table: id, servername, drive, capacity, used_mb,
> free_mb, free_pc (%) and a date_time field.

What is the format of the values in the date_time field?

Is there always a constant offset between two consecutive timestamps?


Regards,
Clemens
_______________________________________________
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: Comparing rows

Matthew Halliday
Hi Clemens - thanks for the reply.

I'm trying to keep them a regular 4  or 6 hours - I'll see what works
best.  The script runs as a scheduled task.

I used the SQLite Studio to create the table so used the DATETIME data type
for that, and although I used yyyy-mm-dd hh:mm:ss in the script it seems to
have reverted it to dd/mm/yy hh:mm:ss.   I prefer using the PortableApps
SQLite browser for other things as you can have tabbed SQL queries but it
doesn't have the DATETIME data type.  if I use "where date_time =
date('now','-1 day')"  for example, that seems to work ok.

I have mailing list emails going back a few years to when I used to use
SQLite a lot but couldn't find anything in them for this.

On Tue, Aug 22, 2017 at 9:20 AM, Clemens Ladisch <[hidden email]> wrote:

> Matthew Halliday wrote:
> > I have a simple import table: id, servername, drive, capacity, used_mb,
> > free_mb, free_pc (%) and a date_time field.
>
> What is the format of the values in the date_time field?
>
> Is there always a constant offset between two consecutive timestamps?
>
>
> Regards,
> Clemens
> _______________________________________________
> 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: Comparing rows

Chris Locke-3
> I prefer using the PortableApps SQLite browser for other things as you
can have tabbed SQL queries but it doesn't have the DATETIME data type

Date and Time *Datatype*. *SQLite* does not have a storage *class* set
aside for storing dates and/or times. Instead, the built-in Date And Time
Functions of *SQLite *are capable of storing dates and times as TEXT, REAL,
or INTEGER values: TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

DB4S (DB Browser for SQLite - the 'real' name for SQLite Browser) will
accept anything as a data type, not necessarily whats in the drop down list.

Drop me an email - I can show you how to create an application in
SharpDevelop using VB.Net if you required.  What you want could be
accomplished with 'naked' SQLite, but a proper 'script' (or application)
would give you much more control, reporting, etc, etc.

PS: DB4S is on v3.10 now, so if you're using the 'portable version', it
needs updating! ;)


Thanks,
Chris


On Tue, Aug 22, 2017 at 9:32 AM, Matthew Halliday <[hidden email]>
wrote:

> Hi Clemens - thanks for the reply.
>
> I'm trying to keep them a regular 4  or 6 hours - I'll see what works
> best.  The script runs as a scheduled task.
>
> I used the SQLite Studio to create the table so used the DATETIME data type
> for that, and although I used yyyy-mm-dd hh:mm:ss in the script it seems to
> have reverted it to dd/mm/yy hh:mm:ss.   I prefer using the PortableApps
> SQLite browser for other things as you can have tabbed SQL queries but it
> doesn't have the DATETIME data type.  if I use "where date_time =
> date('now','-1 day')"  for example, that seems to work ok.
>
> I have mailing list emails going back a few years to when I used to use
> SQLite a lot but couldn't find anything in them for this.
>
> On Tue, Aug 22, 2017 at 9:20 AM, Clemens Ladisch <[hidden email]>
> wrote:
>
> > Matthew Halliday wrote:
> > > I have a simple import table: id, servername, drive, capacity, used_mb,
> > > free_mb, free_pc (%) and a date_time field.
> >
> > What is the format of the values in the date_time field?
> >
> > Is there always a constant offset between two consecutive timestamps?
> >
> >
> > Regards,
> > Clemens
> > _______________________________________________
> > 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
>
_______________________________________________
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: Comparing rows

Matthew Halliday
Hi Chris,

I used SharpDevelop years ago, forgot abot that.  I'd like to do it via the
script or in-DB because I want a set-and-forget solution I can run on a
scheduler.  I have stacks of other jobs to do - some actually similar to
this, but if I can just leave it to run and generate a daily report of the
back of it thats all I need for now.

The script generates a csv but I've also ammended it to insert into the DB
afterwards and just added a couple of difference collumns used_mb_diff and
free_mb_diff.  I know I could do this on the fly but space isn't a problem
and people here don't understand databases that well, so the easier I can
make it the better.  It takes around 8 minutes to run. It would be nice to
run an update onto the new collumns after that but I'm struggling with it.
I can do it in Excel quite easily but prefer this approach for many
reasons..

BTW - the browser is a portableapp, not the DB.

On Tue, Aug 22, 2017 at 9:53 AM, Chris Locke <[hidden email]>
wrote:

> > I prefer using the PortableApps SQLite browser for other things as you
> can have tabbed SQL queries but it doesn't have the DATETIME data type
>
> Date and Time *Datatype*. *SQLite* does not have a storage *class* set
> aside for storing dates and/or times. Instead, the built-in Date And Time
> Functions of *SQLite *are capable of storing dates and times as TEXT, REAL,
> or INTEGER values: TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
>
> DB4S (DB Browser for SQLite - the 'real' name for SQLite Browser) will
> accept anything as a data type, not necessarily whats in the drop down
> list.
>
> Drop me an email - I can show you how to create an application in
> SharpDevelop using VB.Net if you required.  What you want could be
> accomplished with 'naked' SQLite, but a proper 'script' (or application)
> would give you much more control, reporting, etc, etc.
>
> PS: DB4S is on v3.10 now, so if you're using the 'portable version', it
> needs updating! ;)
>
>
> Thanks,
> Chris
>
>
> On Tue, Aug 22, 2017 at 9:32 AM, Matthew Halliday <[hidden email]>
> wrote:
>
> > Hi Clemens - thanks for the reply.
> >
> > I'm trying to keep them a regular 4  or 6 hours - I'll see what works
> > best.  The script runs as a scheduled task.
> >
> > I used the SQLite Studio to create the table so used the DATETIME data
> type
> > for that, and although I used yyyy-mm-dd hh:mm:ss in the script it seems
> to
> > have reverted it to dd/mm/yy hh:mm:ss.   I prefer using the PortableApps
> > SQLite browser for other things as you can have tabbed SQL queries but it
> > doesn't have the DATETIME data type.  if I use "where date_time =
> > date('now','-1 day')"  for example, that seems to work ok.
> >
> > I have mailing list emails going back a few years to when I used to use
> > SQLite a lot but couldn't find anything in them for this.
> >
> > On Tue, Aug 22, 2017 at 9:20 AM, Clemens Ladisch <[hidden email]>
> > wrote:
> >
> > > Matthew Halliday wrote:
> > > > I have a simple import table: id, servername, drive, capacity,
> used_mb,
> > > > free_mb, free_pc (%) and a date_time field.
> > >
> > > What is the format of the values in the date_time field?
> > >
> > > Is there always a constant offset between two consecutive timestamps?
> > >
> > >
> > > Regards,
> > > Clemens
> > > _______________________________________________
> > > 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
> >
> _______________________________________________
> 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: Comparing rows

Clemens Ladisch
In reply to this post by Matthew Halliday
Matthew Halliday wrote:
> I used the SQLite Studio to create the table so used the DATETIME data type
> for that, and although I used yyyy-mm-dd hh:mm:ss in the script

That is correct.

> it seems to have reverted it to dd/mm/yy hh:mm:ss.

That would not be usable.
Check the actual format with the sqlite3 command-line shell.


Assuming the timestamp format is usable, you can compute the differences
with a statement like this:

-- add three columns; then:
UPDATE MyTable
SET (diff_used, diff_free, diff_free_pc)
  = (SELECT MyTable.used_mb - ifnull(prev.used_mb, 0),
            MyTable.free_mb - ifnull(prev.free_mb, 0),
            MyTable.free_pc - ifnull(prev.free_pc, 0)
     FROM MyTable AS prev
     WHERE prev.date_time < MyTable.date_time
     ORDER BY date_time DESC
     LIMIT 1);

This is much faster if there is an index on date_time.


Regards,
Clemens
_______________________________________________
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: Comparing rows

Matthew Halliday
Hi Clemens, thanks for that.

Unfortunately is comlains after "SET diff_used," and I get "near ",":
syntax error: "

If I seperate it out into stand-alone statemeonts like this:

UPDATE tmp_dspace_import
SET diff_used = (SELECT tmp_dspace_import.used_mb - ifnull(prev.used_mb, 0)
FROM tmp_dspace_import AS prev  WHERE prev.date_time <
tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
    diff_free = (SELECT tmp_dspace_import.free_mb - ifnull(prev.free_mb, 0)
FROM tmp_dspace_import AS prev  WHERE prev.date_time <
tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
    diff_free_pc = (SELECT tmp_dspace_import.free_pc - ifnull(prev.free_pc,
0) FROM tmp_dspace_import AS prev  WHERE prev.date_time <
tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1);

then it comes back "no error:" but doesn't update anything.  Not sure if
the abokve is ok - looks ok to me.  The only question in my mind is that it
won't diferentiate between servers and drives.SOme only have a C drive but
most hace a D and E, some have an F.  All local drives btw, not mapped.

On Tue, Aug 22, 2017 at 10:32 AM, Clemens Ladisch <[hidden email]>
wrote:

> Matthew Halliday wrote:
> > I used the SQLite Studio to create the table so used the DATETIME data
> type
> > for that, and although I used yyyy-mm-dd hh:mm:ss in the script
>
> That is correct.
>
> > it seems to have reverted it to dd/mm/yy hh:mm:ss.
>
> That would not be usable.
> Check the actual format with the sqlite3 command-line shell.
>
>
> Assuming the timestamp format is usable, you can compute the differences
> with a statement like this:
>
> -- add three columns; then:
> UPDATE MyTable
> SET (diff_used, diff_free, diff_free_pc)
>   = (SELECT MyTable.used_mb - ifnull(prev.used_mb, 0),
>             MyTable.free_mb - ifnull(prev.free_mb, 0),
>             MyTable.free_pc - ifnull(prev.free_pc, 0)
>      FROM MyTable AS prev
>      WHERE prev.date_time < MyTable.date_time
>      ORDER BY date_time DESC
>      LIMIT 1);
>
> This is much faster if there is an index on date_time.
>
>
> Regards,
> Clemens
> _______________________________________________
> 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: Comparing rows

Matthew Halliday
However this does appear to have worked!  Seems a bit long-winded to me but
it worked.  I think - going to compare to the same thing in Excel and just
check the data before I do a happy-dance.

UPDATE tmp_dspace_import
SET diff_used = (SELECT tmp_dspace_import.used_mb - ifnull(prev.used_mb, 0)
FROM tmp_dspace_import AS prev  WHERE tmp_dspace_import.servername =
prev.servername AND tmp_dspace_import.drive = prev.drive AND prev.date_time
< tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
    diff_free = (SELECT tmp_dspace_import.free_mb - ifnull(prev.free_mb, 0)
FROM tmp_dspace_import AS prev  WHERE tmp_dspace_import.servername =
prev.servername AND tmp_dspace_import.drive = prev.drive AND prev.date_time
< tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
    diff_free_pc = (SELECT tmp_dspace_import.free_pc - ifnull(prev.free_pc,
0) FROM tmp_dspace_import AS prev  WHERE tmp_dspace_import.servername =
prev.servername AND tmp_dspace_import.drive = prev.drive AND prev.date_time
< tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1);



On Tue, Aug 22, 2017 at 11:35 AM, Matthew Halliday <[hidden email]>
wrote:

> Hi Clemens, thanks for that.
>
> Unfortunately is comlains after "SET diff_used," and I get "near ",":
> syntax error: "
>
> If I seperate it out into stand-alone statemeonts like this:
>
> UPDATE tmp_dspace_import
> SET diff_used = (SELECT tmp_dspace_import.used_mb - ifnull(prev.used_mb,
> 0) FROM tmp_dspace_import AS prev  WHERE prev.date_time <
> tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
>     diff_free = (SELECT tmp_dspace_import.free_mb - ifnull(prev.free_mb,
> 0) FROM tmp_dspace_import AS prev  WHERE prev.date_time <
> tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
>     diff_free_pc = (SELECT tmp_dspace_import.free_pc -
> ifnull(prev.free_pc, 0) FROM tmp_dspace_import AS prev  WHERE
> prev.date_time < tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT
> 1);
>
> then it comes back "no error:" but doesn't update anything.  Not sure if
> the abokve is ok - looks ok to me.  The only question in my mind is that it
> won't diferentiate between servers and drives.SOme only have a C drive but
> most hace a D and E, some have an F.  All local drives btw, not mapped.
>
> On Tue, Aug 22, 2017 at 10:32 AM, Clemens Ladisch <[hidden email]>
> wrote:
>
>> Matthew Halliday wrote:
>> > I used the SQLite Studio to create the table so used the DATETIME data
>> type
>> > for that, and although I used yyyy-mm-dd hh:mm:ss in the script
>>
>> That is correct.
>>
>> > it seems to have reverted it to dd/mm/yy hh:mm:ss.
>>
>> That would not be usable.
>> Check the actual format with the sqlite3 command-line shell.
>>
>>
>> Assuming the timestamp format is usable, you can compute the differences
>> with a statement like this:
>>
>> -- add three columns; then:
>> UPDATE MyTable
>> SET (diff_used, diff_free, diff_free_pc)
>>   = (SELECT MyTable.used_mb - ifnull(prev.used_mb, 0),
>>             MyTable.free_mb - ifnull(prev.free_mb, 0),
>>             MyTable.free_pc - ifnull(prev.free_pc, 0)
>>      FROM MyTable AS prev
>>      WHERE prev.date_time < MyTable.date_time
>>      ORDER BY date_time DESC
>>      LIMIT 1);
>>
>> This is much faster if there is an index on date_time.
>>
>>
>> Regards,
>> Clemens
>> _______________________________________________
>> 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: Comparing rows

Clemens Ladisch
In reply to this post by Matthew Halliday
Matthew Halliday wrote:
> Unfortunately is comlains after "SET diff_used," and I get "near ",":
> syntax error: "

Then your SQLite is too old; row values require 3.15 or later.

> it won't diferentiate between servers and drives.

Oops!

> However this does appear to have worked!  Seems a bit long-winded to me but
> it worked.
>
> UPDATE tmp_dspace_import
> SET diff_used = (SELECT tmp_dspace_import.used_mb - ifnull(prev.used_mb, 0)
> FROM tmp_dspace_import AS prev  WHERE tmp_dspace_import.servername =
> prev.servername AND tmp_dspace_import.drive = prev.drive AND prev.date_time
> < tmp_dspace_import.date_time ORDER BY date_time DESC LIMIT 1),
> [...]

With the three-column subquery, it wouldn't be long winded.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users