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

x
?
Solved

Query help

Posted on 2014-09-10
15
Medium Priority
?
207 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 74

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 143

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

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

Accepted Solution

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

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
 
LVL 38

Expert Comment

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

Expert Comment

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

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 38

Expert Comment

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

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 74

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 38

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 38

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

876 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