Hi, all.

Sorry if this question is allready answered, but I'd searched and can't find something like that.

The statements below creates the structures to represent the folowing:


ORD ITENS
=== =================
257 01,04,05,19,28,34
499 01,12,19,28,44,59
667 02,04,05,16,23,34
810 01,04,05,10,19,28

We need to know the combinations of products that happens more frequently. In this little amount of data we have:
Products 01, 19, 28 are present in 3 orders (257,499,810)
Products 01, 04, 05 are present in 2 orders (257,810)
Products 04, 05 are present in 3 orders(257,667,810)

In this way: first the combinations with more products toghether, so in this exemple, the second line groups 3 products and is showed before the third line that groups 2 products even third line has more occurences. Is expected that combinations with less products occurs more times than more complex combinations.

There is no max limit for the number of combinations. In this exemple they are 3 but could be 4,5,...,n.

It's not interesting to show combinations with a few occurrences. In the example, less than 2 occurrences.

There is a smartest way to do that than compare each product combination of one order with the other orders?

Thanks,
Vinicius Pacheco


CREATE TABLE SAMPLE_ORDERS (ORDER_ID NUMBER) ;

ALTER TABLE SAMPLE_ORDERS ADD CONSTRAINT
PK_SAMPLE_ORDERS PRIMARY KEY (ORDER_ID);

CREATE TABLE SAMPLE_ORD_ITENS (ORDER_ID NUMBER, ITEM_ID NUMBER) ;

ALTER TABLE SAMPLE_ORD_ITENS ADD CONSTRAINT
PK_SAMPLE_ORD_ITENS PRIMARY KEY (ORDER_ID, ITEM_ID);

INSERT INTO SAMPLE_ORDERS (ORDER_ID)
VALUES (257);

INSERT INTO SAMPLE_ORDERS (ORDER_ID)
VALUES (499);

INSERT INTO SAMPLE_ORDERS (ORDER_ID)
VALUES (667);

INSERT INTO SAMPLE_ORDERS (ORDER_ID)
VALUES (810);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (257, 1);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (257, 4);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (257, 5);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (257, 19);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (257, 28);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (257, 34);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (499, 1);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (499, 12);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (499, 19);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (499, 28);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (499, 44);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (499, 59);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (667, 2);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (667, 4);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (667, 5);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (667, 16);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (667, 23);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (667, 34);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (810, 1);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (810, 4);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (810, 5);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (810, 10);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (810, 19);

INSERT INTO SAMPLE_ORD_ITENS (ORDER_ID, ITEM_ID)
VALUES (810, 28);