Re: [sqlite] Status analyze with Sqlite

2012-11-23 Thread Keith Medcalf 

Your expected result does not make any logical algorithmic sense whatsoever.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Steffen Mangold
> Sent: Friday, 23 November, 2012 02:02
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Status analyze with Sqlite
> 
> 
> 
> > ID  | TimeStamp | Status
> > 
> > 0   | 2012-07-24 22:23:00   | status1
> > 1   | 2012-07-24 22:23:05   | status1
> > 2   | 2012-07-24 22:23:10   | status2
> > 3   | 2012-07-24 22:23:16   | status2
> > 4   | 2012-07-24 22:23:21   | status2
> > 5   | 2012-07-24 22:23:26   | status2
> > 6   | 2012-07-24 22:23:32   | status2
> > 7   | 2012-07-24 22:23:37   | status3
> > 8   | 2012-07-24 22:23:42   | status3
> > 9   | 2012-07-24 22:23:47   | status3
> >
> > What I want as result is
> > ID  | Begin | End   | Status
> > ---
> 
> > 0   | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
> > 1   | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
> > 2   | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
> >
> 
> >
> > Hmmm, the ID in the result bears virtually no relation to the ID in the
> data. Is that  intentional?
> >
> > Anyhow, some of what you want could come from
> >
> > select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data
> group by Status order by Status
> >
> 
> Hi Gerry,
> 
> thank you but this won't work if the table look like this:
> 
>  ID   | TimeStamp | Status
>  
>  0| 2012-07-24 22:23:00   | status1
>  1| 2012-07-24 22:23:05   | status1
>  2| 2012-07-24 22:23:10   | status2
>  3| 2012-07-24 22:23:16   | status2
>  4| 2012-07-24 22:23:21   | status1
>  5| 2012-07-24 22:23:26   | status1
>  6| 2012-07-24 22:23:32   | status2
>  7| 2012-07-24 22:23:37   | status3
>  8| 2012-07-24 22:23:42   | status3
>  9| 2012-07-24 22:23:47   | status3
> 
> Than you get:
>  ID   | Begin | End   | Status
>  
> ---
>  0| 2012-07-24 22:23:00   | 2012-07-24 22:23:26   | status1
>  1| 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
>  2| 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
> 
> But it should be:
> ID| Begin | End   | Status
>  
> ---
>  0| 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
>  0| 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
>  1| 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
>  2| 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
> 
> You are right the ID column is not relevant. You can ignore them.
> 
> regards
> Steffen Mangold



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status analyze with Sqlite

2012-11-23 Thread Simon Slavin 

On 23 Nov 2012, at 9:01am, Steffen Mangold  wrote:

> But it should be:
> ID| Begin | End   | Status
> ---
> 0 | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
> 0 | 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
> 1 | 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
> 2 | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

You can find the 'Begin's by, for every line, looking up the previous line with 
a sub-SELECT and checking to see whether it has the same status.  Then you do 
the 'Ends' by looking up the following line.  Might have some trouble with the 
first and last lines in the table.  It would be a horribly complicated SQL 
command.

However, do you absolutely have to do this inside a SQL command ?  It's trivial 
in any programming language but puts a great load on a SQL engine.  In code you 
only have to read every row once, in an order already provided by an INDEX.  If 
you do it in SQL you also have to do at least one look-up for every row, keyed 
on a long string.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status analyze with Sqlite

2012-11-23 Thread Clemens Ladisch 
Steffen Mangold wrote:
> TimeStamp | Status
> -
> 2012-07-24 22:23:00   | status1
> 2012-07-24 22:23:05   | status1
> 2012-07-24 22:23:10   | status2
> 2012-07-24 22:23:16   | status2
> 2012-07-24 22:23:21   | status1
> 2012-07-24 22:23:26   | status1
> 2012-07-24 22:23:32   | status2
> 2012-07-24 22:23:37   | status3
> 2012-07-24 22:23:42   | status3
> 2012-07-24 22:23:47   | status3
>
> This is what I want:
>
> Begin | End   | Status
> ---
> 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
> 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
> 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
> 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

And what about the event at 22:23:32?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status analyze with Sqlite

2012-11-23 Thread Steffen Mangold 
Hi Clemens,

first thank you for your comments.

>
> It works for what you've asked.  Perhaps you should not have kept your actual 
> requirements a secret.
>

Sorry for being unclear! :(
Hope my English is understandable (I'm from Germany).

>
> So you want to group only consecutive events with identical statuses, where 
> "consecutive" is defined on the ordering of the timestamps?
>
> Then why do you write them?  (And why are there duplicates in your last 
> example?)
>

You are right I only want consecutive events of same status. Here more a clear 
example.

This is the table:

TimeStamp   | Status
-
2012-07-24 22:23:00 | status1
2012-07-24 22:23:05 | status1
2012-07-24 22:23:10 | status2
2012-07-24 22:23:16 | status2
2012-07-24 22:23:21 | status1
2012-07-24 22:23:26 | status1
2012-07-24 22:23:32 | status2
2012-07-24 22:23:37 | status3
2012-07-24 22:23:42 | status3
2012-07-24 22:23:47 | status3


This is what I want:

Begin   | End   | Status
---
2012-07-24 22:23:00 | 2012-07-24 22:23:05   | status1
2012-07-24 22:23:10 | 2012-07-24 22:23:16   | status2
2012-07-24 22:23:21 | 2012-07-24 22:23:26   | status1
2012-07-24 22:23:37 | 2012-07-24 22:23:47   | status3

>
> Well, try this:
>
> SELECT TimeStamp AS Begin,
>(SELECT MAX(TimeStamp)
> FROM Data AS same
> WHERE same.Status = ou.Status
>   AND same.TimeStamp >= ou.TimeStamp
>   AND same.TimeStamp < (COALESCE((SELECT MIN(TimeStamp)
>   FROM Data AS next
>   WHERE next.TimeStamp > ou.TimeStamp
> AND next.Status <> ou.Status),
>  ''))
>) AS End,
>Status
> FROM Data AS ou
> WHERE Status IS NOT (SELECT Status
>  FROM (SELECT Status,
>   MAX(prev.TimeStamp)
>FROM Data AS prev
>   WHERE prev.TimeStamp < ou.TimeStamp))
>

Thank you I try this.

>
> (And it might be easier and faster to just query the events ordered by 
> timestamp, and aggregate statuses by hand in your code.)
>
Hm ok I make some test. I think you can be right that in-code aggregation is 
faster that subquerys.

Regards,
Steffen


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status analyze with Sqlite

2012-11-23 Thread Clemens Ladisch 
Steffen Mangold wrote:
> this won't work

It works for what you've asked.  Perhaps you should not have kept your
actual requirements a secret.

> if the table look like this:
>
>  ID   | TimeStamp | Status
>  
>  0| 2012-07-24 22:23:00   | status1
>  1| 2012-07-24 22:23:05   | status1
>  2| 2012-07-24 22:23:10   | status2
>  3| 2012-07-24 22:23:16   | status2
>  4| 2012-07-24 22:23:21   | status1
>  5| 2012-07-24 22:23:26   | status1
>  6| 2012-07-24 22:23:32   | status2
>  7| 2012-07-24 22:23:37   | status3
>  8| 2012-07-24 22:23:42   | status3
>  9| 2012-07-24 22:23:47   | status3
>
> Than you get:
>  ID   | Begin | End   | Status
>  
> ---
>  0| 2012-07-24 22:23:00   | 2012-07-24 22:23:26   | status1
>  1| 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
>  2| 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
>
> But it should be:
> ID| Begin | End   | Status
>  
> ---
>  0| 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
>  0| 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
>  1| 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
>  2| 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

So you want to group only consecutive events with identical statuses,
where "consecutive" is defined on the ordering of the timestamps?

> You are right the ID column is not relevant. You can ignore them.

Then why do you write them?  (And why are there duplicates in your last
example?)


Well, try this:

SELECT TimeStamp AS Begin,
   (SELECT MAX(TimeStamp)
FROM Data AS same
WHERE same.Status = ou.Status
  AND same.TimeStamp >= ou.TimeStamp
  AND same.TimeStamp < (COALESCE((SELECT MIN(TimeStamp)
  FROM Data AS next
  WHERE next.TimeStamp > ou.TimeStamp
AND next.Status <> ou.Status),
 ''))
   ) AS End,
   Status
FROM Data AS ou
WHERE Status IS NOT (SELECT Status
 FROM (SELECT Status,
  MAX(prev.TimeStamp)
   FROM Data AS prev
   WHERE prev.TimeStamp < ou.TimeStamp))

(And it might be easier and faster to just query the events ordered by
timestamp, and aggregate statuses by hand in your code.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status analyze with Sqlite

2012-11-23 Thread Steffen Mangold 


> ID| TimeStamp | Status
> 
> 0 | 2012-07-24 22:23:00   | status1
> 1 | 2012-07-24 22:23:05   | status1
> 2 | 2012-07-24 22:23:10   | status2
> 3 | 2012-07-24 22:23:16   | status2
> 4 | 2012-07-24 22:23:21   | status2
> 5 | 2012-07-24 22:23:26   | status2
> 6 | 2012-07-24 22:23:32   | status2
> 7 | 2012-07-24 22:23:37   | status3
> 8 | 2012-07-24 22:23:42   | status3
> 9 | 2012-07-24 22:23:47   | status3
>
> What I want as result is
> ID| Begin | End   | Status
> ---
> 0 | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
> 1 | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
> 2 | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
>

>
> Hmmm, the ID in the result bears virtually no relation to the ID in the data. 
> Is that  intentional?
>
> Anyhow, some of what you want could come from
> 
> select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data group 
> by Status order by Status
> 

Hi Gerry,

thank you but this won't work if the table look like this:

 ID | TimeStamp | Status
 
 0  | 2012-07-24 22:23:00   | status1
 1  | 2012-07-24 22:23:05   | status1
 2  | 2012-07-24 22:23:10   | status2
 3  | 2012-07-24 22:23:16   | status2
 4  | 2012-07-24 22:23:21   | status1
 5  | 2012-07-24 22:23:26   | status1
 6  | 2012-07-24 22:23:32   | status2
 7  | 2012-07-24 22:23:37   | status3
 8  | 2012-07-24 22:23:42   | status3
 9  | 2012-07-24 22:23:47   | status3

Than you get:
 ID | Begin | End   | Status
 ---
 0  | 2012-07-24 22:23:00   | 2012-07-24 22:23:26   | status1
 1  | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
 2  | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

But it should be:
ID  | Begin | End   | Status
 ---
 0  | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
 0  | 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
 1  | 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
 2  | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

You are right the ID column is not relevant. You can ignore them.

regards
Steffen Mangold
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status analyze with Sqlite

2012-11-22 Thread Gerry Snyder 

On 11/22/2012 4:47 PM, Steffen Mangold wrote:

HI sqlite community,

I have a problem I get stucked, maybe someone can help me. :(

My issue:

For instance if we have 10 rows with following data

ID  | TimeStamp | Status

0   | 2012-07-24 22:23:00   | status1
1   | 2012-07-24 22:23:05   | status1
2   | 2012-07-24 22:23:10   | status2
3   | 2012-07-24 22:23:16   | status2
4   | 2012-07-24 22:23:21   | status2
5   | 2012-07-24 22:23:26   | status2
6   | 2012-07-24 22:23:32   | status2
7   | 2012-07-24 22:23:37   | status3
8   | 2012-07-24 22:23:42   | status3
9   | 2012-07-24 22:23:47   | status3

What I want as result is
ID  | Begin | End   | Status
---
0   | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
1   | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
2   | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3



Hmmm, the ID in the result bears virtually no relation to the ID in the 
data. Is that intentional?


Anyhow, some of what you want could come from

select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data 
group by Status order by Status



HTH,

Gerry


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Status analyze with Sqlite

2012-11-22 Thread Steffen Mangold 
HI sqlite community,

I have a problem I get stucked, maybe someone can help me. :(

My issue:

For instance if we have 10 rows with following data

ID  | TimeStamp | Status

0   | 2012-07-24 22:23:00   | status1
1   | 2012-07-24 22:23:05   | status1
2   | 2012-07-24 22:23:10   | status2
3   | 2012-07-24 22:23:16   | status2
4   | 2012-07-24 22:23:21   | status2
5   | 2012-07-24 22:23:26   | status2
6   | 2012-07-24 22:23:32   | status2
7   | 2012-07-24 22:23:37   | status3
8   | 2012-07-24 22:23:42   | status3
9   | 2012-07-24 22:23:47   | status3

What I want as result is
ID  | Begin | End   | Status
---
0   | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
1   | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
2   | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

What I have so far is

SELECT ou. ID AS ID,
ou.Status AS Status,
ou.TimeStamp AS Begin,
(SELECT MAX(TimeStamp)
FROM Data
WHERE TimeStamp >= ou.TimeStamp 
AND 
Status = ou.Status
AND
TimeStamp < '2010-02-24 00:00:00') AS End
FROM Data ou

But don't work :(
Can you please help me?

Regards
Steffen Mangold


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users