3x3 clustering in a SELECT.

[jdfuhr]

Good afternoon,

I am developing a select to introduce me to the notes products from a particular load order. Until then all right, my difficulty is in need of grouping the products of these notes grouped from 3 in 3 notes, that is:

Order of charge 3

Select all

NF 1 
NF 2 
NF 3 
. 
. 
. 
NF N

nf 1

Select all

3 X BOLACHA ISABELA  
2 X LEITE CONDENÇADO 
5 X PIPOCA YOKI

Nf2

Select all

1 X BOLACHA ISABELA 

NF 3

Select all

3 X BOLACHA ISABELA  
5 X PIPOCA YOKI 
2 X BOLACHA RECHEADA TORTINHAS

nf 4

Select all

1 X BOLACHA ISABELA  
1 X PIPOCA YOKI 
1 X BOLACHA RECHEADA TORTINHAS

So on.

The desired result in my SELECT would be this:

separation of the load order 3

Select all

7 X BOLACHA ISABELA  
2 X LEITE CONDENÇADO 
10 X PIPOCA YOKI 
---------------------------------------------------------------------------- 
1 X BOLACHA ISABELA  
1 X PIPOCA YOKI 
1 X BOLACHA RECHEADA TORTINHAS

For a better understanding:
in the first block of products presented would be grouped the products from Notes 1, 2 and 3. in the second block the notes 4, 5 and 6 and so on.

Does anyone know any role where I could do this grouping?

Thanks for the attention.

[Diego_Mello]

You can do something like this, but with SQL is half limited.
You can not make a report?

Select all

 
SQL> SELECT a 
  2        ,b 
  3    FROM (WITH tt AS (SELECT 1 a, 2 b 
  4                        FROM dual UNION 
  5                      SELECT 2 a, 3 b 
  6                        FROM dual UNION 
  7                      SELECT 3 a, 4 b 
  8                        FROM dual UNION 
  9                      SELECT 4 a, 5 b 
 10                        FROM dual UNION 
 11                      SELECT 5 a, 6 b 
 12                        FROM dual UNION 
 13                      SELECT 6 a, 7 b 
 14                        FROM dual UNION 
 15                      SELECT 7 a, 8 b 
 16                        FROM dual UNION 
 17                      SELECT 8 a, 9 b 
 18                        FROM dual) 
 19           SELECT TO_CHAR(tt.a) a 
 20                 ,TO_CHAR(tt.b) b 
 21                 ,ROWNUM ordem_a 
 22                 ,1 ordem_b 
 23                 ,1 
 24             FROM tt 
 25           UNION 
 26           SELECT * 
 27             FROM (SELECT a 
 28                         ,b 
 29                         ,oa + LEVEL - 1 oa 
 30                         ,ob 
 31                         ,max_tt 
 32                     FROM (SELECT '-------------------------------------------' a 
 33                                 ,'-------------------------------------------' b 
 34                                 ,3 oa 
 35                                 ,2 ob 
 36                                 ,COUNT(*) max_tt 
 37                             FROM dual 
 38                                 ,tt) 
 39                   CONNECT BY oa + LEVEL - 1 <= max_tt) 
 40            WHERE MOD(oa, 3) = 0) 
 41            ORDER BY ordem_a 
 42                    ,ordem_b 
 43  ; 
  
A                                           B 
------------------------------------------- ------------------------------------------- 
1                                           2 
2                                           3 
3                                           4 
------------------------------------------- ------------------------------------------- 
4                                           5 
5                                           6 
6                                           7 
------------------------------------------- ------------------------------------------- 
7                                           8 
8                                           9 
  
10 rows selected 
  

[victorhugomuniz]

You can do something like this, but with SQL is half limited.
You can not make a report?

Select all

 
SQL> SELECT a 
  2        ,b 
  3    FROM (WITH tt AS (SELECT 1 a, 2 b 
  4                        FROM dual UNION 
  5                      SELECT 2 a, 3 b 
  6                        FROM dual UNION 
  7                      SELECT 3 a, 4 b 
  8                        FROM dual UNION 
  9                      SELECT 4 a, 5 b 
 10                        FROM dual UNION 
 11                      SELECT 5 a, 6 b 
 12                        FROM dual UNION 
 13                      SELECT 6 a, 7 b 
 14                        FROM dual UNION 
 15                      SELECT 7 a, 8 b 
 16                        FROM dual UNION 
 17                      SELECT 8 a, 9 b 
 18                        FROM dual) 
 19           SELECT TO_CHAR(tt.a) a 
 20                 ,TO_CHAR(tt.b) b 
 21                 ,ROWNUM ordem_a 
 22                 ,1 ordem_b 
 23                 ,1 
 24             FROM tt 
 25           UNION 
 26           SELECT * 
 27             FROM (SELECT a 
 28                         ,b 
 29                         ,oa + LEVEL - 1 oa 
 30                         ,ob 
 31                         ,max_tt 
 32                     FROM (SELECT '-------------------------------------------' a 
 33                                 ,'-------------------------------------------' b 
 34                                 ,3 oa 
 35                                 ,2 ob 
 36                                 ,COUNT(*) max_tt 
 37                             FROM dual 
 38                                 ,tt) 
 39                   CONNECT BY oa + LEVEL - 1 <= max_tt) 
 40            WHERE MOD(oa, 3) = 0) 
 41            ORDER BY ordem_a 
 42                    ,ordem_b 
 43  ; 
  
A                                           B 
------------------------------------------- ------------------------------------------- 
1                                           2 
2                                           3 
3                                           4 
------------------------------------------- ------------------------------------------- 
4                                           5 
5                                           6 
6                                           7 
------------------------------------------- ------------------------------------------- 
7                                           8 
8                                           9 
  
10 rows selected 
  

/ quote]

: -O

[jdfuhr]

Thank you Victor,

I am trying to understand the functioning and testing.

As soon as you have a position you step.

Performing your code here seems to work.

Thanks

[jdfuhr]

Thank you Diego, (sorry to have the name before)

I am trying to understand the functioning and testing.

As soon as you have a position you step.

Performing your code here seems to work.

Thanks

[rogenaro]

Reading the question, I imagined that the doubt is in how to perform the grouping of items 3 in 3 notes, which is an interesting problem of solving:

Select all

 
create table nota 
( nf number 
); 
/ 
create table item 
( nf number 
, produto varchar2(10) 
, quantidade number 
); 
/ 
 
insert into nota select 10*level from dual connect by level <= 10; 
 
insert into item  select 10*(mod(level,10)+1),  'produto '||trunc(dbms_random.value(1,4)), trunc(dbms_random.value(1,8)  ) from dual connect by level <= 35; 
 

The main step is to perform the data grouping.
You can accomplish this by ordering the results, and then subtract from the line number the rest of the division of the previous line number by the size of your partition (3 in the case):

Select all

 
select n.* 
     , rn -  mod( rn-1, 3 ) grupo 
from 
( 
  select n.* 
       -- basta mudar o order by para informar o critério de ordenação das 3 notas 
       -- (data de emissão, filial, etc) 
       , row_number() 
         over(  order by n.nf 
             ) rn 
  from   nota n 
) n 
; 
 
 
NF	RN	GRUPO 
10	1	 1 
20	2	 1 
30	3	 1 
40	4	 4 
50	5	 4 
60	6	 4 
70	7	 7 
80	8	 7 
90	9	 7 
100 10    10 

With the data already grouped, simply perform the Join with the table of items, and perform the Group by by the group column:

Select all

 
select grupo, produto, sum(quantidade) quantidade 
from 
( 
  select n.* 
       , rn -  mod( rn-1, 3 ) grupo 
  from 
  ( 
    select n.* 
         -- basta mudar o order by para informar o critério de ordenação das 3 notas 
         -- (data de emissão, filial, etc) 
         , row_number() 
           over(  order by n.nf 
               ) rn 
    from   nota n 
  ) n 
) n 
, item i 
where n.nf = i.nf 
group by grupo, produto 
order by grupo, produto 
; 
 
GRUPO	    PRODUTO  	QUANTIDADE 
1	        produto 1	        15 
1	        produto 2	        17 
1	        produto 3	        19 
4	        produto 1	        33 
4	        produto 2	        17 
4	        produto 3	        13 
7	        produto 1	        18 
7	        produto 2	        3 
7	        produto 3	        14 
10	       produto 1	        5 
10	       produto 2	        6 
10	       produto 3	        3