SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

finding discontiguous numbers

754172Apr 9 2010 — edited Apr 19 2010

Greetings,

I need to write a plsql scripts that will find discontiguous numbers. For example, say I have a table..

id int
store varchar
tracking_num int

Each store uses a different sequence for their tracking number. ii need to find and list the contiguous segments for each store, and the total number of items shipped. For example..
STORE MIN MAX
----------------------------------------------------------
STORE X 100 105
STORE X 107 110
STORE X 112 115
---------------------------------------------------------
STORE X TOTAL 14

STORE Y 901 903
STORE Y 905 907
STORE Y 908 910
---------------------------------------------------------
STORE X TOTAL 9

OK, I figure I need a cursor to be able to do this. I figure I'll loop through each store's records, increment a counter for the total. For the discontinuous I figure I'll add a new column which I'll use to group the contiguous segments together, then I can just a max and min tracking number for each store's contiguous groups.

The problem I'm having is wrapping my head around how to build a dataset with a cursor populating the new contiguous column IN MEMORY, in an line view or something, without having to create a physical table.

Anyone have any suggestions, or see any flaws with this logic?

This post has been answered by 189821 on Apr 9 2010

Jump to Answer

Centinul

It is always helpful to provide the following:

1. Oracle version (SELECT * FROM V$VERSION)
2. Sample data in the form of CREATE / INSERT statements.
3. Expected output
4. Explanation of expected output (A.K.A. "business logic")
5. Use \

 tags for #2 and #3. See FAQ (Link on top right side) for details.

Also check out this:

<a href="http://forums.oracle.com/forums/thread.jspa?threadID=1005478&tstart=0">Tabibitosan method tutorial by Aketi Jyuuzou</a>

754172

Using Oracle 10.2.0.4

I thought this was a simple enough question that I could get away without specific examples. Tha being said

create table store_tracking (
id  int,
store varchar2(20),
tracking int
)

INSERT INTO STORE_TRACKING (SELECT 1, 'STORE X', 100 FROM DUAL ); 
INSERT INTO STORE_TRACKING (SELECT 2, 'STORE X', 101 FROM DUAL ); 
INSERT INTO STORE_TRACKING (SELECT 3, 'STORE X', 102 FROM DUAL ); 
INSERT INTO STORE_TRACKING (SELECT 4, 'STORE X', 103 FROM DUAL ); 
INSERT INTO STORE_TRACKING (SELECT 5, 'STORE X', 104 FROM DUAL ); 
INSERT INTO STORE_TRACKING (SELECT 6, 'STORE X', 105 FROM DUAL ); 
INSERT INTO STORE_TRACKING (SELECT 7, 'STORE X', 107 FROM DUAL ); 
INSERT INTO STORE_TRACKING (SELECT 8, 'STORE X', 108 FROM DUAL ); 
INSERT INTO STORE_TRACKING (SELECT 9, 'STORE X', 109 FROM DUAL ); 
INSERT INTO STORE_TRACKING (SELECT 10, 'STORE X', 110 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 11, 'STORE X', 112 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 12, 'STORE X', 113 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 13, 'STORE X', 114 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 14, 'STORE X', 115 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 15, 'STORE Y', 901 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 16, 'STORE Y', 902 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 17, 'STORE Y', 903 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 18, 'STORE Y', 905 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 19, 'STORE Y', 906 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 20, 'STORE Y', 907 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 21, 'STORE Y', 908 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 22, 'STORE Y', 909 FROM DUAL); 
INSERT INTO STORE_TRACKING (SELECT 23, 'STORE Y', 910 FROM DUAL);

Expected Output is ...

STORE      MIN     MAX
-------------------------------------
STORE X   100     105
STORE X   107     110
STORE X   112     115 
------------------------------------
STORE X TOTAL 14

STORE      MIN     MAX
-------------------------------------
STORE Y   901     903
STORE Y   905     907
STORE Y   908     910
-------------------------------------
STORE Y TOTAL 9

The explaination of the output is that we need to identify the contiguous segments of tracking numbers for each store (and thereby implicitly indicating where we are missing tacking numbers), and the total number of tracking number for each store.

I hope this helps. let me know if you have additional questions.

Edited by: user7352432 on Apr 9, 2010 8:21 AM

189821

Answer

There you are:

SQL>SELECT   STORE, TO_CHAR(MIN(tracking), '99999') AS "Min", TO_CHAR(MAX(tracking), '99999') AS "Max"
  2      FROM (SELECT STORE, tracking, SUM(is_first) OVER(PARTITION BY STORE ORDER BY tracking) AS tracking_group
  3              FROM (SELECT STORE, tracking,
  4                           DECODE(LAG(tracking) OVER(PARTITION BY STORE ORDER BY tracking) - tracking,
  5                                  -1, 0,
  6                                  1) AS is_first,
  7                           DECODE(LEAD(tracking) OVER(PARTITION BY STORE ORDER BY tracking) - tracking,
  8                                  1, 0,
  9                                  1) AS is_last
 10                      FROM store_tracking))
 11  GROUP BY STORE, tracking_group
 12  UNION ALL
 13  SELECT   STORE, 'Total', TO_CHAR(COUNT(*))
 14      FROM store_tracking
 15  GROUP BY STORE
 16  ORDER BY 1, 2;

STORE                Min    Max
-------------------- ------ ----------------------------------------
STORE X                 100    105
STORE X                 107    110
STORE X                 112    115
STORE X              Total  14
STORE Y                 901    903
STORE Y                 905    910
STORE Y              Total  9

Urs

Edited by: metzguar on 09.04.2010 08:51
Added Totals

Marked as Answer by 754172 · Sep 27 2020

754172

WOW.

I have to say, I've been an SQL developer for about 7 years, and I could have solved this, but it wouldn't have been half as nifty as this is.

Very, VERY well done.

Aketi Jyuuzou

I like Tabibitosan method B-)
I like "group by rollup B-)

select store,
case grouping(dis) when 1 then 'Total'
     else to_char(min(tracking)) end as "Min",
case grouping(dis) when 1 then count(*)
     else max(tracking) end as "Max"
from (select store,tracking,
       tracking
      -Row_Number() over(partition by store order by ID) as dis
      from STORE_TRACKING)
group by store,rollup(dis);

STORE    Min    Max
-------  -----  ---
STORE X  100    105
STORE X  107    110
STORE X  112    115
STORE X  Total   14
STORE Y  901    903
STORE Y  905    910
STORE Y  Total    9

Prazy

Another way... but I know its too late ;)

select store,to_char(min(tracking) over (partition by row_num order by tracking rows unbounded preceding)) min_val,
to_char(max(tracking) over (partition by row_num order by tracking rows between unbounded preceding and unbounded following)) max_val 
from
(
	select store,tracking,
	tracking-row_number() over (partition by store order by id) row_num
	from store_tracking
)
union
select store,'Total',
to_char(sum(1) over (partition by store order by id rows between unbounded preceding and unbounded following))
from store_tracking
order by store
/
STORE                MIN_VAL                                  MAX_VAL
-------------------- ---------------------------------------- ---------------------
STORE X              100                                      105
STORE X              107                                      110
STORE X              112                                      115
STORE X              Total                                    14
STORE Y              901                                      903
STORE Y              905                                      910
STORE Y              Total                                    9

7 rows selected.

Elapsed: 00:00:00.03

Regards,
Prazy

Solomon Yakobson

Aketi Jyuuzou wrote:
I like Tabibitosan method B-)
I like "group by rollup B-)

Overkill:

{code}
select store,
case grouping(tracking - id)
when 1 then 'Total'
else to_char(min(tracking))
end as "Min",
case grouping(tracking - id)
when 1 then count(*)
else max(tracking)
end as "Max"
from store_tracking
group by store,rollup(tracking - id)
/

STORE Min Max
-------------------- ---------------------------------------- ----------
STORE X 100 105
STORE X 107 110
STORE X 112 115
STORE X Total 14
STORE Y 901 903
STORE Y 905 910
STORE Y Total 9

7 rows selected.

SQL>
{code}

SY.

764456

One more variation with connect by:

SQL> select store, to_char(min(m)) MIN, to_char(max(tracking)) MAX
  2  from
  3  (select store, connect_by_root(tracking) m, tracking, connect_by_isleaf is_last
  4    from store_tracking
  5  connect by prior tracking=tracking-1)
  6  where is_last=1
  7  group by store,tracking
  8  union all
  9  select   store, 'Total', to_char(count(*))
 10  FROM store_tracking
 11  group by store
 12  order by 1,2
 13  /

STORE                MIN        MAX
-------------------- ---------- ----------------------------------------
STORE X              100        105
STORE X              107        110
STORE X              112        115
STORE X              Total      14
STORE Y              901        903
STORE Y              905        909
STORE Y              Total      8

7 rows selected.

Aketi Jyuuzou

Hi "Solomon Yakobson"

I think your solution is very nice :-)

I did not realize that in this datas,there is no data which id increments more than 1.

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on May 17 2010

Added on Apr 9 2010

9 comments

1,725 views

SQL & PL/SQL

finding discontiguous numbers

Comments

Post Details