问题描述:

I'm attempting to create group_ids based on a set of item_ids. The only indication that the item_ids are part of a single group is the fact that item_ids are sequential. For example, based on the first two columns below, the output I want is the third:

item item_id group_id

ABC 282 2

ABC 283 2

ABC 284 2

ABC 285 2

ABC 051 3

ABC 052 3

ABC 189 4

ABC 231 5

ABC 232 5

ABC 233 5

ABC 234 5

ABC 247 6

ABC 248 6

ABC 249 6

ABC 250 6

ABC 091 7

ABC 092 7

The group_id doesn't necessarily have to be sequential itself, it only has to be unique. I attempted this with the following code:

create sequence seq

start with 1

minvalue 1

increment by 1

cache 20;

select seq.nextval from dual; --to initialize the sequence

select

item,

item_id,

case when diff = 1 then seq.currval else seq.nextval end group_id

from

(

select

item,

item_id,

(id - lag(id, 1, 0) over (order by 1) diff

from

(

select

item,

item_id

from

table

)

);

But get the following output:

item item_id group_id

ABC 282 2

ABC 283 3

ABC 284 4

ABC 285 5

ABC 051 6

ABC 052 7

ABC 189 8

ABC 231 9

ABC 232 10

ABC 233 11

ABC 234 12

ABC 247 13

ABC 248 14

ABC 249 15

ABC 250 16

ABC 091 17

ABC 092 18

When looking for the cause of the problem, I found an excellent explanation by user ShannonSeverance that details why my solution won't work. However, it didn't provide any suggestions on how to move forward.

Does anyone have any ideas?

网友答案:

You have a problem, because SQL tables are inherently unordered. The following "should" logically work, although it won't in practice:

select ii.*, (item_id - rownum) as grp_id
from item_ids ii;

A sequence of item_ids in order minus the row number is constant. You can use that for a group, at least for a given item. To handle multiple items, concatenate the values together:

select ii.*, item||'-'||(item_id - rownum) as grp_id
from item_ids ii;

To really make this work, you need to add an order by -- this guarantees the ordering of the results from the select. This might work, assuming that there are "holes" between the groups:

select ii.*, item||'-'||(item_id - rownum) as grp_id
from item_ids ii
order by item, item_id;

Otherwise, you need some other column to determine the proper ordering for the items.

相关阅读:
Top