Re: Impossible SQL query?

Comments interleaved.

vincentj_at_nycap.rr.com wrote:

  > I've got these data points that mark the beginning and end of a
> process, lets call them B (begin) and E (end.) The problem is that
> there is nothing that links the begin point to the end point, except a
> date stamp. End points always come after begin points. There could be
> more than one end point, in which case we take the first one and
> discard the rest. So we could have data like this:
>
>
> ID Key Date
> -- --- ------
> 1 B 1/1/05
> 2 B 1/2/05
> 3 E 1/3/05
> 4 B 1/3/05
> 5 E 1/4/05
> 6 E 1/4/05
> 7 E 1/5/05
> 8 B 1/6/05
> 9 B 1/7/05
> 10 E 1/7/05
>
>
> The result would look something like this:
>
>
> Begin End
> ----- ---
> B1 E3
> B2 E5
> B4 E6
> B8 E10
> B9
>
>
> Notice that E7 gets discarded because there are not enough begin points
> before it. Also notice that B9 does not have an end point. We can
> discard end points but never begin points.
>

But how do you know it shouldn't be this:

Begin	End
-----	---
B1
B2	E3
B4	E5
B8
B9	E10

This seems to meet your rules equally well.

> Does anyone think they can tackle this query? It can use any advanced
> SQL functions (subqueries, analytics, etc.) available in Oracle
> 8.1.7.4, but I don't want to use PL/SQL. The output doesn't have to
> look exactly like that, I'm just looking for the logic. Thanks, I
> really appreciate any help I can get here. Or if this is impossible
> without PL/SQL, please let me know! Thanks!
>

This sounds very similar to a problem I had trying to monitor how many users of a web app (JDBC pool connections to the database) were concurrently logged in throughout the day. The login of each user was reliably recorded in an application-maintained table, but since they could either log out explicitly (recorded) or through inactivity (not recorded), there was no way to exactly determine the period during which they were logged in (in this app, only about half of the users explicitly clicked on the "logout" button).

I solved it with a Perl script (procedural) that didn't take too long to run, but there was an unavoidable degree of uncertainty due to the necessity of estimating the logout time of those who didn't explicitly do so (or in your case, which B's are missing an E). I performed a sensitivity analysis to balance the plus and minus errors, and when compared to a similar analysis using the Webtrends product on the HTTP server logs, the results were remarkably well in agreement.

-Mark Bole Received on Tue Oct 04 2005 - 13:34:35 CDT