Solved

Query help

Posted on 2014-09-10
15
197 Views
Last Modified: 2014-09-12
Dear Experts,

Please help me in constructing query.

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
Comment
Question by:sakthikumar
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 5

Expert Comment

by:Omid Omarkhail
ID: 40314286
Can provide your requirement in excel file.
0
 
LVL 73

Expert Comment

by:sdstuber
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
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

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

Accepted Solution

by:
sdstuber earned 500 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 36

Expert Comment

by:Geert Gruwez
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

Open in new window


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

Author Closing Comment

by:sakthikumar
ID: 40316815
Thank you, very simple, i thought this would be complex.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 40316825
simple ... so you don't have oracle 12 ?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 40316870
no i wouldn't
:)
0
 
LVL 36

Expert Comment

by:Geert Gruwez
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  physical reads
          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

Open in new window


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  physical reads
          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>

Open in new window


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

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

Expert Comment

by:Geert Gruwez
ID: 40316906
you'll have to face it ... a split is in order ...
0
 
LVL 73

Expert Comment

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

Since you already had a reply ....

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 73

Expert Comment

by:sdstuber
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;

Open in new window


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>

Open in new window

0
 
LVL 36

Expert Comment

by:Geert Gruwez
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

Open in new window

0
 
LVL 36

Expert Comment

by:Geert Gruwez
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;

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now