Requêtes imbriquées et comptage de doublons [Résolu]

[scavenger]

bonjour

ça fait 1 an que je n'ai plus fait de SQL et ça se sent... j'espère avoir mis le bon titre de post ^^

ce que je souhaite faire :

dans une base avec plusieurs shemas,
pour un schema particulier,
pour les tables de ce shema contenant une colone particuliere,
compter le nombre de doublons et afficher la colonne et le resultat du count

ça paraît super simple à la plupart d'entre vous j'imagine, mais je n'ai jamais fait de requètes imbriquées et ça fait 4h que je galère, donc voici ce que j'ai testé :

Code :

SELECT 'owner.table1' AS TABLE_NAME,count(*) FROM owner.table1 WHERE rowid NOT IN (SELECT max(rowid) FROM owner.table1 GROUP BY 'column_key');

j'obtient bien :

TABLE_NAME NB
-------------------- ----------
owner.table1 26

et je voudrais faire ça sur toutes les tables du select :

Code :

SELECT owner,table_name,column_name FROM ALL_TAB_COLUMNS WHERE owner = 'owner1' AND column_name = 'column_key;

c'est super simple comme concept : j'affiche à priori 1 colonne de résultats et une colonne de calculs sur la 1ère, je pense à un join ou qqch comme ça ?

j'ai testé ceci mais ça ne marche pas :

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
DECLARE
	Cursor listTables IS
		SELECT owner,table_name 
		FROM ALL_TAB_COLUMNS 
		WHERE owner = 'OPS$VTF' AND column_name = 'BRCH_CODE';
	Type t_CurTyp IS Ref Cursor;
	crsData		t_CurTyp;
	columnName	varchar(100);
	lv_string	varchar(100);
	l_sql		Varchar2(32676);
BEGIN
	dbms_output.enable(400000);
	FOR line IN listTables
	LOOP
		lv_string:=('Table ' || line.owner || '.' || line.table_name);
		dbms_output.put_line(lv_string);
		l_sql := 'SELECT count(*) FROM ' || line.owner || '.' || line.table_name ||
		' WHERE rowid NOT IN (SELECT max(rowid) FROM ' || line.owner || '.' || line.table_name ||
		' GROUP BY "BRCH_CODE")';
		Open crsData FOR l_sql USING line.table_name;
		LOOP
			FETCH crsData INTO columnName;
			EXIT WHEN crsData%NOTFOUND;
			lv_string:=('Table ' || line.owner || '.' || line.table_name || ' : ' || columnName || ' lignes');
			dbms_output.put_line(lv_string);
		END LOOP;
		Close crsData;
	END LOOP;
END;
/

enfin si ça marche une fois, ça plante au bout de 15 lignes puis ça refuse de refonctionner, j'ai peur de casser qqch si j'insiste :

Code :

1
2
3
4
5
6
7
8
9
10
11
TABLE OPS$VTF.BDT_C_DUE_DATE_ADV
TABLE OPS$VTF.BDT_C_DUE_DATE_ADV : 0 lignes
TABLE OPS$VTF.BDT_C_NOT_ADV
TABLE OPS$VTF.BDT_C_NOT_ADV : 0 lignes
TABLE OPS$VTF.BDT_P_ECR_PC
TABLE OPS$VTF.BDT_P_ECR_PC : 26 lignes
DECLARE
*
ERROR at line 1:
ORA-01006: bind variable does NOT exist
ORA-06512: at line 20

avec pour ligne 20:
20 Open crsData FOR l_sql USING line.table_name;


je ne sais plus quoi tester... please helppp

[McM]

A quoi sert le USING ?

Vu que tu construit ta requête complètement sans binder, tu ne devrais faire qu'un simple

Code :

Open crsData FOR l_sql

[McM]

PS : Pour les doublons, il est plus simple de faire comme suit

Code :

SELECT COUNT(*) FROM (SELECT 1 FROM table_name GROUP BY BRCH_CODE HAVING COUNT(*) > 1)

Ta requête de doublons compte visiblement le nombre de lignes en doublons (et pas le nombre de brch_code différent ayant des doublons). Ce serait plutôt

Code :

 SELECT SUM(nb) FROM (SELECT COUNT(*) - 1 nb FROM table_name GROUP BY BRCH_CODE HAVING COUNT(*) > 1)

[skuatamad]

En associant la nouvelle "requête à la mode" et la remarque de MCM on peut tout faire en SQL :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
  WITH t AS (
SELECT owner,table_name, column_name 
  FROM ALL_TAB_COLUMNS 
 WHERE owner       = 'OPS$VTF' 
   AND column_name = 'BRCH_CODE'
)
SELECT table_name,
       nvl(to_number(extractvalue(
                   dbms_xmlgen.getXMLtype ('SELECT SUM(nb) as cnt 
                                              FROM (SELECT COUNT(*) - 1 nb 
                                                      FROM '|| owner ||'.'|| table_name ||
                                                   ' GROUP BY '||column_name||' HAVING COUNT(*) > 1)'),
                                           '/ROWSET/ROW/CNT'))
           ,0) AS doublons_par_table
  FROM T

Mais ça n'est probablement pas très performant.
Regarde les commentaires autour de cette approche sur le blog de Laurent Schneider notamment pour les limitations, comme les tables IOT.

[scavenger]

Envoyé par skuatamad

En associant la nouvelle "requête à la mode" et la remarque de MCM on peut tout faire en SQL :

Mais ça n'est probablement pas très performant.
Regarde les commentaires autour de cette approche sur le blog de Laurent Schneider notamment pour les limitations, comme les tables IOT.

heuuuu merci je vais lire ça cet après midi !!
en tous cas cette fois ça fonctionne, ça me donne un beau tableau avec les noms des tables et le "nombre" de doublons.

Enfin doublons, j'en sais trop rien. Ma requète initiale venait d'un autre forum qui expliquait que le champ rowid était fait pour cela.

avec vos exemples de requètes, j'obtient des résultats forts différents:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT 'OPS$VTF.DBT_C_BANK' AS TABLE_NAME,count(*) AS NB FROM OPS$VTF.DBT_C_BANK WHERE rowid NOT IN (SELECT max(rowid) FROM OPS$VTF.DBT_C_BANK GROUP BY 'BRCH_CODE');
 
TABLE_NAME                 NB
------------------ ----------
OPS$VTF.DBT_C_BANK     153040
 
SELECT 'OPS$VTF.DBT_C_BANK' AS TABLE_NAME,COUNT(*) FROM (SELECT 1 FROM OPS$VTF.DBT_C_BANK GROUP BY BRCH_CODE HAVING COUNT(*) > 1);
 
TABLE_NAME           COUNT(*)
------------------ ----------
OPS$VTF.DBT_C_BANK          5
 
SELECT 'OPS$VTF.DBT_C_BANK' AS TABLE_NAME,SUM(nb) FROM (SELECT COUNT(*) - 1 nb FROM DBT_C_BANK GROUP BY BRCH_CODE HAVING COUNT(*) > 1);
TABLE_NAME            SUM(NB)
------------------ ----------
OPS$VTF.DBT_C_BANK     153036

moi mon critère c'est le champ BRCH_CODE, là dessus je cherche les lignes en doublons pour un même BRCH_CODE.
Pourquoi j'ai 3 résultats différents ???
je vais devoir analyser le code de ces 3 requètes sur une table qui contient peu d'enregistrements pour comprendre...

[scavenger]

bon alors en fait...
j'ai refait ces tests sur une table avec seulement 5 enregistrements, et il m'apparaît que........ mon select et le second select de McM donnent le résultat attendu, merci McM et merci skuatamad pour votre expertise et votre temps !!

Le tableau de skuatamad est très joli merci encore ! Même si je vais avoir du mal à comprendre le code...

maintenant j'aimerai trouver de vraies lignes en doublon dans leur totalité, pas seulement sur un champ.
j'imagine que le code va être plus gros et plus ardu, ce sera un autre sujet.

résolu bravo pour votre réactivité et la qualité des réponses !!!

[skuatamad]

maintenant j'aimerai trouver de vraies lignes en doublon dans leur totalité, pas seulement sur un champ.

Pour ça il suffit de concatener les colonnes avec la virgule en séparateurs.

Si tu es en 11GR2 il y a listagg :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
  WITH t AS (
SELECT owner,table_name, listagg(column_name,',') WITHIN GROUP (ORDER BY column_id) AS liste_colonne
  FROM ALL_TAB_COLUMNS 
 WHERE owner = 'OPS$VTF' 
 GROUP BY owner,table_name
)
SELECT table_name,
       nvl(to_number(extractvalue(
                   dbms_xmlgen.getXMLtype ('SELECT SUM(nb) as cnt 
                                              FROM (SELECT COUNT(*) - 1 nb 
                                                      FROM '|| owner ||'.'|| table_name ||
                                                   ' GROUP BY '||liste_colonne||' HAVING COUNT(*) > 1)'),
                                           '/ROWSET/ROW/CNT'))
           ,0) AS doublons_par_table
  FROM T

Mais je doute que tu ais cette chance donc il faut regarder d'autres méthodes, comme celle du XML (je réécris que le WITH):

Code :

1
2
3
4
5
6
7
  WITH t AS (
SELECT owner,table_name, 
       rtrim(REPLACE(REPLACE(XMLAGG(XMLElement("x", column_name) ORDER BY column_id ASC),'<x>', ''),'</x>', ','),',') AS liste_colonne
  FROM ALL_TAB_COLUMNS 
 WHERE owner = 'OPS$VTF' 
 GROUP BY owner,table_name
)

Sinon pour toujours plus de méthode d'aggrégation des colonnes regarde :
String Aggregation Techniques
Et choisis celle que tu comprends le mieux et pour laquelle tu as les droits de création/exécution

PS : il faudra sûrement filtrer la requête dans le WITH pour exclure les PK voir les UK
Regarde pour ça ALL_CONSTRAINTS

[islamov2000]