Go Premium for a chance to win a PS4. Enter to Win

x
Solved

Query help

Posted on 2014-09-10
Medium Priority
207 Views
Dear Experts,

I have rows like this.

TABLE : samp

col1
---------
1
2
3
5
6
9
10

I need result like below.

1 3
5 6
9 10

Idea is, rows should be grouped into sets, and need min and max value in each set.

Let me know how to achieve this, need to use analytic funtions?
0
Question by:sakthikumar
• 7
• 4
• 2
• +2

LVL 5

Expert Comment

ID: 40314286
Can provide your requirement in excel file.
0

LVL 74

Expert Comment

ID: 40314289
what defines a set?
That is, why are 1,2,3 part of a set but not 1,2,3,5  ?

Just guessing your data is grouped into  sets of 4, (1-4) (5-8) (9-12) etc
If my guess is correct then try this...

SELECT MIN(col1), MAX(col1)
FROM samp
GROUP BY FLOOR(col1 / 4)
ORDER BY 1

if your grouping sets are defined by something else, then please specify
0

LVL 143

Expert Comment

ID: 40314304
I have written an article for this kind of queries: finding ranges, see here:
http://www.experts-exchange.com/Database/Miscellaneous/A_3952-ranges-gaps-overlaps-for-numbers-and-date-ranges.html

the code is written on SQL Server, but should work with the relevant "oracle" tweaks also on that DBMS
0

Author Comment

ID: 40314312
if next value is one more than the previous value, then it becomes a set.
0

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 40314343
in that case try this...

SELECT MIN(col1), MAX(col1)
FROM (SELECT col1, col1 - ROW_NUMBER() OVER (ORDER BY col1) grp FROM samp)
GROUP BY grp
ORDER BY 1;
0

LVL 38

Expert Comment

ID: 40314524
in oracle 12 there is the new pattern matching clause.
the guide : http://docs.oracle.com/database/121/DWHSG/pattern.htm#DWHSG8956

try this in an oracle 12 database

``````create table data (n number);

insert into data (n)
select level from dual connect by level < 11;

delete from data where n in (4, 7, 8);

select *
from data
match_recognize (
order by n
measures
STRT.N as start_value,
LAST(UP.n) as end_value
ONE ROW PER MATCH
PATTERN (STRT UP+)
DEFINE
UP AS UP.n = PREV(UP.n)+1
) MR
``````

output:
START_VALUE,END_VALUE
1,3
5,6
9,10
0

Author Closing Comment

ID: 40316815
Thank you, very simple, i thought this would be complex.
0

LVL 38

Expert Comment

ID: 40316825
simple ... so you don't have oracle 12 ?
0

LVL 38

Expert Comment

ID: 40316870
no i wouldn't
:)
0

LVL 38

Expert Comment

ID: 40316904
i'd use my plan (on a 12) as it's just as inefficient as your analytic rownumbering

``````Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

15:05:51 test@TESTU12>set autot trace
15:05:58 test@TESTU12>select *
15:08:09   2  from data
15:08:09   3    match_recognize (
15:08:09   4      order by n
15:08:09   5      measures
15:08:09   6        STRT.N as start_value,
15:08:09   7        LAST(UP.n) as end_value
15:08:09   8      ONE ROW PER MATCH
15:08:09   9      PATTERN (STRT UP+)
15:08:09  10      DEFINE
15:08:09  11        UP AS UP.n = PREV(UP.n)+1
15:08:09  12      ) MR;

Execution Plan
----------------------------------------------------------
Plan hash value: 2220740601

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                     |      |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  VIEW                                                |      |     1 |    26 |     3   (0)| 00:00:01 |
|   2 |   MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTOMATON|      |     1 |    13 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                                 | DATA |     1 |    13 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
7  consistent gets
0  redo size
671  bytes sent via SQL*Net to client
504  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
3  rows processed
``````

``````15:08:21 test@TESTU12>SELECT MIN(n), MAX(n)
15:09:26   2      FROM (SELECT n, n - ROW_NUMBER() OVER (ORDER BY n) grp FROM data)
15:09:26   3  GROUP BY grp
15:09:26   4  ORDER BY 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1451161479

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  SORT ORDER BY        |      |     1 |    26 |     3   (0)| 00:00:01 |
|   2 |   HASH GROUP BY       |      |     1 |    26 |     3   (0)| 00:00:01 |
|   3 |    VIEW               |      |     1 |    26 |     3   (0)| 00:00:01 |
|   4 |     WINDOW SORT       |      |     1 |    13 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| DATA |     1 |    13 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
7  consistent gets
0  redo size
663  bytes sent via SQL*Net to client
504  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
2  sorts (memory)
0  sorts (disk)
3  rows processed

15:09:32 test@TESTU12>
``````

your code requires more steps to complete
> so why would i use your query ?

or you didn't try out 12c yet ?
0

LVL 38

Expert Comment

ID: 40316906
you'll have to face it ... a split is in order ...
0

LVL 74

Expert Comment

ID: 40316951
I deleted my comment, but apparently not fast enough.

Yes - of course I tried it on 12c.  I wouldn't post a comparison comment if I didn't already have results.

I didn't care about the plans, I measured the actual execution statistics.

The pattern match did fewer sort operations, but despite that the analytic still consumed slightly -but consistently less cpu.  For very small data sets, both versions round down to 0 so I had to ramp it up.

That's why I originally posted a comment that the analytics were probably a better choice; but later I deleted it because - while my results were definitive and consistent - they weren't dramatic enough to convince me that they would always be so.

With other data, other cpu,memory,io it's possible the pattern matching might be better.  I just couldn't prove it.  I only had my results to work with and despite supporting my statement I decided it wasn't strong enough to keep the post.

Sorry for the confusion.

A split seems like an odd choice to me.  The individual posts don't contribute to a solution.  Only one answer is going to be used.  So it's not split-worthy. It's an either-or.  If the asker went with pattern matching then you get everything, if they go with analytics then I do.  If there was a third option taken - then that one.  Had the question been "what are some various options" then yes a split would seem reasonable; but again only if the asker could use all of the suggestions.

If it turned out the asker was still running oracle 7.3 then neither of our answers would qualify even though they are both very reasonable for modern systems.

If you want to RA for a split, I won't object, I just don't think it applies here.
0

LVL 74

Expert Comment

ID: 40316983
on further review - I think I would object.

The comparitive performance is irrelevant

The pattern match query produces (what I think are) incorrect results

``````DROP TABLE data PURGE;

CREATE TABLE data (n NUMBER);

INSERT INTO data(n)
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL < 11;

DELETE FROM data
WHERE n IN (2,
3,
5,
7);

COMMIT;

SELECT *
FROM data MATCH_RECOGNIZE(
ORDER BY n
MEASURES strt.n AS start_value, LAST(UP.n) AS end_value
ONE ROW PER MATCH
PATTERN (strt UP+)
DEFINE UP AS UP.n = prev(UP.n) + 1
) mr;

SELECT MIN(n), MAX(n)
FROM (SELECT n, n - ROW_NUMBER() OVER (ORDER BY n) grp FROM data)
GROUP BY grp
ORDER BY 1;
``````

my results from the above...

``````Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> DROP TABLE data PURGE;

Table dropped.

SQL>
SQL> CREATE TABLE data (n NUMBER);

Table created.

SQL>
SQL> INSERT INTO data(n)
2          SELECT LEVEL
3            FROM DUAL
4      CONNECT BY LEVEL < 11;

10 rows created.

SQL>
SQL> DELETE FROM data
2        WHERE n IN (2,
3                    3,
4                    5,
5                    7);

4 rows deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT *
2    FROM data MATCH_RECOGNIZE(
3                  ORDER BY n
4                  MEASURES strt.n AS start_value, LAST(UP.n) AS end_value
5                  ONE ROW PER MATCH
6                  PATTERN (strt UP+)
7                  DEFINE UP AS UP.n = prev(UP.n) + 1
8              ) mr;

START_VALUE  END_VALUE
----------- ----------
8         10

SQL>
SQL>   SELECT MIN(n), MAX(n)
2      FROM (SELECT n, n - ROW_NUMBER() OVER (ORDER BY n) grp FROM data)
3  GROUP BY grp
4  ORDER BY 1;

MIN(N)     MAX(N)
---------- ----------
1          1
4          4
6          6
8         10

SQL>
``````
0

LVL 38

Expert Comment

ID: 40318702
i figured it was a little more complex than a simple min and max

this also gives the correct results ... i don't think it will perform very well
but it works on 11 too

``````with cte as (
select distinct prior n as pn, n, sys_connect_by_path(to_char(n), ',') p, level alevel
from data
where level > 1
and connect_by_isleaf = 1
connect by n = prior n +1
),
cte2 as (
select pn, n, p, alevel, max(alevel) over (partition by pn, n order by pn) maxlevel
from cte)
select
substr(p, 2, instr(p, ',', 2, 1)-2) start_num,
substr(p,  instr(p, ',', -1)+1) end_num
from cte2
where alevel = maxlevel
``````
0

LVL 38

Expert Comment

ID: 40318719
on a second observation ( a range has a different start and end value)

``````SELECT MIN(n), MAX(n)
FROM (SELECT n, n - ROW_NUMBER() OVER (ORDER BY n) grp FROM data)
GROUP BY grp
having min(n) <> max(n)
ORDER BY 1;
``````
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logicalâ€¦