Différences

Ci-dessous, les différences entre deux révisions de la page.

--- database:requetes-sql-utiles [2024/05/24 19:48] – jpmilcent
+++ database:requetes-sql-utiles [2024/05/25 07:44] (Version actuelle) – [Déterminer les groupes d'identifiant contigu] jpmilcent
@@ Ligne 184: / Ligne 184: @@
 Requête SQL permettant de déterminer les groupes de suites d'identifiants non contigü et le nombre d'id compris dedans :
 <code sql>
-SELECT grp, "min", "max", COUNT(id_data)
+SELECT
+	grp,
+	"min",
+	"max",
+	COUNT(id_data) AS downloaded,
+	td.nbr AS to_download
 FROM (
-                SELECT
+		SELECT
-                        grp,
+			grp,
-                        MIN(id) AS "min",
+			MIN(id) AS "min",
-                        MAX(id) AS "max"
+			MAX(id) AS "max"
-                FROM (
+		FROM (
-                        SELECT
+			SELECT
-                                id,
+				id,
-                        SUM(rst) OVER (ORDER BY id) AS grp
+				SUM(rst) OVER (ORDER BY id) AS grp
-                        FROM (
+			FROM (
-                                SELECT
+				SELECT
-                                        id_synthese AS id,
+					id_synthese AS id,
-                                        CASE WHEN COALESCE(LAG(id_synthese + 10000) OVER (ORDER BY id_synthese), 0) < id_synthese THEN 1 END AS rst
+					CASE WHEN COALESCE(LAG(id_synthese + 10000) OVER (ORDER BY id_synthese), 0) < id_synthese THEN 1 END AS rst
-                                FROM gn2pg_flavia.id_synthese_pole_invertebres AS ispi
+				FROM gn2pg_flavia.id_synthese_pole_invertebres AS ispi
-                                        LEFT JOIN gn2pg_flavia.data_json AS dj
+					LEFT JOIN gn2pg_flavia.data_json AS dj
-                                                ON ispi.id_synthese = dj.id_data
+						ON ispi.id_synthese = dj.id_data
-                                WHERE dj.id_data IS NULL
+				WHERE dj.id_data IS NULL
-                                ORDER BY ispi.id_synthese ASC
+				ORDER BY ispi.id_synthese ASC
-                        ) t
+			) AS t
-                ) t
+		) AS t
-                GROUP BY grp
+		GROUP BY grp
-                ORDER BY 1
+		ORDER BY 1
-        ) AS d
+	) AS d
-        LEFT JOIN gn2pg_flavia.data_json AS dj
+	LEFT JOIN gn2pg_flavia.data_json AS dj
-                ON dj.id_data > d.min AND dj.id_data < d.max
+		ON dj.id_data > d.min AND dj.id_data < d.max,
-GROUP BY d.grp, d."min", d."max"
+	LATERAL (
+		SELECT COUNT(id_synthese) AS nbr
+		FROM gn2pg_flavia.id_synthese_pole_invertebres
+		WHERE id_synthese > d.min AND id_synthese < d.max
+	) AS td
+WHERE td.nbr > 0
+GROUP BY d.grp, d."min", d."max", td.nbr
 ORDER BY d.grp;
 </code>
@@ Ligne 216: / Ligne 227: @@
 Résultats :
 <code>
-|grp|min       |max       |count| Done|
+|grp|min       |max       |downloaded|to_download|
-|---|----------|----------|-----|-----|
+|---|----------|----------|----------|-----------|
-|1  |3 215 985  |3 339 850  |0    | X   |
+|1  |5 839 897  |6 467 981  |3 255     |581 087    |
-|2  |5 839 897  |6 467 981  |0    |
+|2  |9 404 094  |9 576 583  |0         |172 488    |
-|3  |9 404 094  |9 576 583  |0    |
+|3  |15 444 377 |15 455 826 |2 454     |2 773      |
-|4  |15 444 377 |15 455 826 |2 454 |
+|4  |15 609 091 |15 609 795 |703       |703        |
-|5  |15 542 006 |15 542 006 |0    |
+|5  |16 335 991 |16 336 391 |1         |52         |
-|6  |15 609 091 |15 609 795 |703  |
+|6  |16 640 640 |16 641 280 |290       |639        |
-|7  |15 843 950 |15 843 951 |0    |
-|8  |16 335 991 |16 336 391 |1    |
-|9  |16 640 640 |16 641 280 |290  |
 </code>