Partager avec vous la méthode Tabibitosan

Bonjour,

Ce message est pour partager avec vous la méthode Tabibitosan, cette méthode consiste à grouper les données selon différents critères.

Exemple :

On veut créer des groupes de prêts (Loan) qui ont des numéros de prêt consécutifs (les numéros de prêt (loan_id) 1, 2,3 forment le groupe 1, le 7 forme le groupe 2, les 10 et le 11 forment le groupe 3)

Le contenu de la table

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
WITH loan AS
     (SELECT 1 loan_id, 'xxx' bn
        FROM DUAL
      UNION ALL
      SELECT 2 loan_id, NULL borrower_name
        FROM DUAL
      UNION ALL
      SELECT 3 loan_id, NULL borrower_name
        FROM DUAL
      UNION ALL
      SELECT 7 loan_id, 'ac' borrower_name
        FROM DUAL
      UNION ALL
      SELECT 10 loan_id, 'ac' borrower_name
        FROM DUAL
      UNION ALL
      SELECT 11 loan_id, NULL borrower_name
        FROM DUAL)

La requête :

Code:

1
2
3
4
5
6
7
 
 
SELECT   loan_id, bn, 'G' || DENSE_RANK () OVER (ORDER BY makegroup) grp_id
    FROM (SELECT loan_id, bn,
                 loan_id - ROW_NUMBER () OVER (ORDER BY loan_id) AS makegroup
            FROM loan)
ORDER BY loan_id;

La demonstration:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
 
12:24:38 Scott@WEBDEVP>WITH loan AS
12:24:41   2       (SELECT 1 loan_id, 'xxx' bn
12:24:41   3          FROM DUAL
12:24:41   4        UNION ALL
12:24:41   5        SELECT 2 loan_id, NULL borrower_name
12:24:41   6          FROM DUAL
12:24:41   7        UNION ALL
12:24:41   8        SELECT 3 loan_id, NULL borrower_name
12:24:41   9          FROM DUAL
12:24:41  10        UNION ALL
12:24:41  11        SELECT 7 loan_id, 'ac' borrower_name
12:24:41  12          FROM DUAL
12:24:41  13        UNION ALL
12:24:41  14        SELECT 10 loan_id, 'ac' borrower_name
12:24:41  15          FROM DUAL
12:24:41  16        UNION ALL
12:24:41  17        SELECT 11 loan_id, NULL borrower_name
12:24:41  18          FROM DUAL)
12:24:41  19  SELECT   loan_id, bn, 'G' || DENSE_RANK () OVER (ORDER BY makegroup) grp_id
12:24:41  20      FROM (SELECT loan_id, bn,
12:24:41  21                   loan_id - ROW_NUMBER () OVER (ORDER BY loan_id) AS makegroup
12:24:41  22              FROM loan)
12:24:41  23  ORDER BY loan_id;
 
   LOAN_ID BN  GRP_ID
---------- --- -----------------------------------------
         1 xxx G1
         2     G1
         3     G1
         7 ac  G2
        10 ac  G3
        11     G3
 
6 ligne(s) sélectionnée(s).
 
12:24:43 Scott@WEBDEVP>

Pour plus ample informations sur la méthode Tabibitosan, Veuillez consulter le lien suivant:

https://forums.oracle.com/forums/thr...art=0&tstart=0

Cordialement Salim.