View with grouping

[Karen Bailey]

Hi,

I am attempting to create a view that will rollup or group like, consecutive
data. I have created a view using unions, but the statement is extremely
slow.

Here is example data

Colour Table:

Colour_id hole_number depth_from depth_to colour
1 1 0 2
red
2 1 2 6
red
3 1 6 8
blue
4 1 8 10
blue
5 1 12 14
blue
6 1 14 16
red
7 2 0 5
orange
8 2 5 10
orange
9 2 10 15
red
Using this example data, we need to create groups of all consecutive data
with the same colour for the same hole.

For example, my view needs to produce the following output where depth_from
is the smallest depth_from in the group and depth_to is the largest depth_to
in the group

hole_number depth_from depth_to colour
1 0 6 red
1 6 14 blue
1 14 16 red
2 0 10 orange
2 10 15 red

The groups can contain gaps, but no overlaps. The data in each group must
be consecutive and is grouped by the colour column.

Here is my existing statement. The first select brings back all groups
containing multiple rows, the second select brings back all groups that
aren't multiples and aren't the first or last row, the third select brings
back the last row as a group if it doesn't fall within a group already, and
the last select returns the first row as a group if it doesn't fall within a
group already.
select min(colour.depth_from) as min_depth_from,

max_colour.depth_to as max_depth_to,

colour.colour,

colour.hole_number as hole

from colour

join colour as max_colour

on max_colour.hole_number = colour.hole_number

AND max_colour.colour=colour.colour

AND max_colour.depth_from > colour.depth_from

AND max_colour.depth_to > colour.depth_to

AND (select count(colour_id)

from colour as mid

where mid.depth_from < max_colour.depth_from

AND mid.depth_from > hole_colour.depth_from

AND mid.depth_to < max_colour.depth_to

AND mid.depth_to > colour.depth_to

AND mid.hole_number = colour.hole_number

AND mid.colour= colour.colour) >= 0

AND (select count(colour_id)

from colour as mid

where mid.depth_from < max_colour.depth_from

AND mid.depth_from > colour.depth_from

AND mid.depth_to < max_colour.depth_to

AND mid.depth_to > colour.depth_to

AND mid.hole_number = colour.hole_number

AND mid.colour<> colour.colour) = 0

AND max_colour.depth_to = (select max(depth_to)

from colour. as maximum_colour

where maximum_colour.depth_to >
colour.depth_to

AND maximum_colour.depth_from >
colour.depth_from

AND maximum_colour.hole_number =
colour.hole_number

AND maximum_colour.colour= colour.colour

AND (select count(colour_id)

from colour as
mid

where
mid.depth_from <= maximum_colour.depth_from

AND
mid.depth_from > colour.depth_from

AND mid.depth_to
<= colour.depth_to

AND mid.depth_to

colour.depth_to

AND
mid.hole_number = colour.hole_number

AND mid.colour<>
colour.colour) = 0)

group by colour.hole_number, colour.colour, max_colour.depth_to,
max_colour.depth_from

UNION

select min(colour.depth_from)as min_depth_from,

max(colour.depth_to) as max_depth_to,

colour.rock_type_code,

colour.hole_number as hole

from colour

join colour as next_colour

on next_colour.depth_from = colour.depth_to

and colour.hole_number = colour.hole_number

AND next_colour.colour <> colour.colour

JOIN colour as prev_colour

on prev_colour.depth_to = colour.depth_from

and prev_colour.hole_number = colour.hole_number

AND prev_colour.colour <> colour.colour

group by colour.hole_number, colour.colour, colour.depth_from,
colour.depth_to

UNION

select min(colour.depth_from)as min_depth_from,

max(colour.depth_to) as max_depth_to,

colour.colour,

colour.hole_number as hole

from colour

JOIN colour as prev_colour

ON prev_colour.depth_to = colour.depth_from

and prev_colour.hole_number = colour.hole_number

AND prev_colour.colour<> colour.colour

and colour.depth_to = (select max(colour.depth_to)

from colour

where colour.hole_number =
prev_colour.hole_number)

group by colour.hole_number, colour.colour, colour.depth_from,
colour.depth_to

UNION

select min(colour.depth_from)as min_depth_from,

max(colour.depth_to) as max_depth_to,

colour.colour,

colour.hole_number as hole

from colour

JOIN colour as next_colour

ON next_colour.depth_from = colour.depth_to

and next_colour.hole_number = colour.hole_number

AND next_colour.colour<> colour.colour

and colour.depth_from = (select min(colour.depth_from)

from colour

where colour.hole_number =
next_colour.hole_number)

group by colour.hole_number, colour.colour, colour.depth_from,
colour.depth_to



Any help to speed this statement up or to re-write this statement so that it
is quicker would be appreciated.
Thanks,

Karen

[David Portas]

It helps if you post DDL with questions like this and include your sample
data as INSERT statements.

Here's what I assume your DDL looks like:

CREATE TABLE Colour (colour_id INTEGER NOT NULL UNIQUE, hole_number INTEGER
NOT NULL, depth_from INTEGER NOT NULL, depth_to INTEGER NOT NULL, colour
CHAR(10) NOT NULL, CHECK(depth_from<depth_to), PRIMARY KEY
(depth_from,colour), UNIQUE (depth_to, colour))

INSERT INTO Colour VALUES (1,1,0,2,'red')
INSERT INTO Colour VALUES (2,1,2,6,'red')
INSERT INTO Colour VALUES (3,1,6,8,'blue')
INSERT INTO Colour VALUES (4,1,8,10,'blue')
INSERT INTO Colour VALUES (5,1,12,14,'blue')
INSERT INTO Colour VALUES (6,1,14,16,'red')
INSERT INTO Colour VALUES (7,2,0,5,'orange')
INSERT INTO Colour VALUES (8,2,5,10,'orange')
INSERT INTO Colour VALUES (9,2,10,15,'red')

And here's a query to produce the result you want:

SELECT A.hole_number,
MIN(A.depth_from) AS depth_from,
MAX(A.depth_to) AS depth_to, A.colour
FROM Colour AS A
JOIN
(SELECT C1.colour_id, MIN(C2.depth_to) AS next_depth
FROM Colour AS C1
LEFT JOIN Colour AS C2
ON C1.hole_number = C2.hole_number
AND C1.colour <> C2.colour
AND (C1.depth_from < C2.depth_from
OR (C1.depth_from = C2.depth_from)
AND C1.depth_to <= C2.depth_to)
GROUP BY C1.colour_id) AS B
ON A.colour_id = B.colour_id
GROUP BY A.hole_number, A.colour, B.next_depth

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

See also this identical post in reply to Dave Pylatuk. (Familiar?! :-))

http://tinyurl.com/thm5

--
David Portas
------------
Please reply only to the newsgroup
--

[Karen Bailey]

Hi David,

Yes, that looks very familiar. Thanks alot. I was looking for those posts.

The problem is, the examples given do not correctly group none consecutive
colours when they are seperated by a different colour.

For Example:
Data:
Hole Depth From Depth To Colour
hole1 0 5 red
hole1 5 10 red
hole1 10 15 blue
hole1 15 20 red

Using the examples given, there would only be 1 group for the colour red.

Example Result
Hole Depth From Depth To Colour
hole1 0 20 red
hole1 10 15 blue

We need the view to correctly seperate the groups and return the following
result.

Good Result
Hole Depth From Depth To Colour
hole1 0 10 red
hole1 10 15 blue
hole1 15 20 red

My sql statement i provided in my previous post correctly groups the data,
but it is extremely slow and needs to be quicker to be usefull.

Thanks for your time,

Karen
"David Portas" <RE****************************@acm.org> wrote in message
news:SM********************@giganews.com...

See also this identical post in reply to Dave Pylatuk. (Familiar?! :-))

http://tinyurl.com/thm5

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

I don't think you tried the query I posted earlier:

SELECT A.hole_number,
MIN(A.depth_from) AS depth_from,
MAX(A.depth_to) AS depth_to, A.colour
FROM Colour AS A
JOIN
(SELECT C1.colour_id, MIN(C2.depth_to) AS next_depth
FROM Colour AS C1
LEFT JOIN Colour AS C2
ON C1.hole_number = C2.hole_number
AND C1.colour <> C2.colour
AND (C1.depth_from < C2.depth_from
OR (C1.depth_from = C2.depth_from)
AND C1.depth_to <= C2.depth_to)
GROUP BY C1.colour_id) AS B
ON A.colour_id = B.colour_id
GROUP BY A.hole_number, A.colour, B.next_depth

Here's the result I get:

hole_number depth_from depth_to colour
----------- ----------- ----------- ----------
1 0 10 red
1 10 15 blue
1 15 20 red

(3 row(s) affected)

Which is what you say you wanted.

--
David Portas
------------
Please reply only to the newsgroup
--

[Karen Bailey]

Your correct Dave. Sorry about that.

Works Great.

Thank you very much.

Karen

"David Portas" <RE****************************@acm.org> wrote in message
news:oO********************@giganews.com...

I don't think you tried the query I posted earlier:

SELECT A.hole_number,
MIN(A.depth_from) AS depth_from,
MAX(A.depth_to) AS depth_to, A.colour
FROM Colour AS A
JOIN
(SELECT C1.colour_id, MIN(C2.depth_to) AS next_depth
FROM Colour AS C1
LEFT JOIN Colour AS C2
ON C1.hole_number = C2.hole_number
AND C1.colour <> C2.colour
AND (C1.depth_from < C2.depth_from
OR (C1.depth_from = C2.depth_from)
AND C1.depth_to <= C2.depth_to)
GROUP BY C1.colour_id) AS B
ON A.colour_id = B.colour_id
GROUP BY A.hole_number, A.colour, B.next_depth

Here's the result I get:

hole_number depth_from depth_to colour
----------- ----------- ----------- ----------
1 0 10 red
1 10 15 blue
1 15 20 red

(3 row(s) affected)

Which is what you say you wanted.

--
David Portas
------------
Please reply only to the newsgroup
--