Skip to main content

Indicateurs délinquance

On cherche à construire une base de données qui renvoie une ligne pour chaque commune et qui nous permet d'avoir un indicateur de la délinquance sur chaque type de crimes sauf homicides tout en nous permettant de connaitre la significativité de la différence entre l'indicateur obtenu à l'échelle communale et celui qu'on obtient à l'échelle départementale ou encore l'échelle régionale. 

Choix de la base de données : 

Bases statistiques communale, départementale et régionale de la délinquance enregistrée par la police et la gendarmerie nationales. Cette base de données se structure par communes en ayant une ligne pour chaque infraction pour chaque commune avec comme liste d'infractions : 

Infraction commise Abréviation Unité de compte
Violences Physiques Intrafamiliales sur personnes de 15 ans ou plus VPI Victime
Violences Physiques Hors Cadre Familial sur personnes de 15 ans ou plus VPHCF Victime
Violences sexuelles VS Victime
Vols avec armes (armes à feu, armes blanches ou par destination) VAA Infraction
Vols violents sans arme VVSA Infraction

Vols sans violence contre des personnes

 VSVCP Victime entendue
Cambriolages de logements CL Infraction

Vols de véhicules

 VV Véhicule
Vols dans les véhicules VDV Véhicule
Vols d'accessoires sur véhicules VAV Véhicule
Destructions et dégradations volontaires DDV Infraction
Usage de stupéfiants US Mis en cause
Usage de stupéfiants dont amendes forfaitaires délictuelles (AFD) USAFD Mis en cause
Trafic de stupéfiants TS Mis en cause
Escroqueries et fraudes aux moyens de paiement EFMP Victime

Pour obtenir le choix on va d'abord définir le nom des colonnes

Identifiant Commune codgeo
Identifiant Departement coddep
Identifiant région codreg

Identifiant crime échelle communale

 i_"Abreviation de l'infraction en minuscule"_com
Identifiant crime échelle départementale i_"Abreviation de l'infraction en minuscule"_dep
Identifiant crime échelle régionale i_"Abreviation de l'infraction en minuscule"_reg
Significativité crime entre commune et département i_sign_"Abréviation de l'infraction en minuscule"_com_dep
Significativité crime entre commune et région i_sign_"Abréviation de l'infraction en minuscule"_com_reg
Significativité crime entre département et région i_sign_"Abréviation de l'infraction en minuscule"_dep_reg
Fiabilité des données sur le crime à l'échelle communale ( y'a t'il une donnée manquante sur une année, au quel cas on a pris la moyenne départementale sur cette année )  i_"Abréviation de l'infraction en minuscule"_com_fiabilite

Pour obtenir ces colonnes on va donc traiter la base de données via PGadmin dans lequel on va effectuer deux requetes, tout d'abord on va effectuer une première requette qu'on appellera i_deli_nat : 

DROP TABLE IF EXISTS i_deli_nat;

CREATE TABLE i_deli_nat AS
WITH 

-- 1. Nettoyage hyper-sécurisé de la base
base_clean AS (
    SELECT 
        codgeo_2025 AS codgeo,
        LEFT(codgeo_2025, 2) AS coddep,
        annee,
        indicateur,
        
        -- Nettoyage de insee_pop
        CASE 
            WHEN TRIM(insee_pop) IN ('', 'NA', 'N/A', 'NULL', 'error') OR insee_pop IS NULL THEN NULL 
            ELSE REPLACE(insee_pop, ',', '.')::numeric 
        END AS insee_pop,
        
        -- Nettoyage de taux_pour_mille et application de la règle
        CASE 
            WHEN TRIM(taux_pour_mille) IN ('', 'NA', 'N/A', 'NULL', 'error') OR taux_pour_mille IS NULL THEN 
                CASE 
                    WHEN TRIM(complement_info_taux) IN ('', 'NA', 'N/A', 'NULL', 'error') OR complement_info_taux IS NULL THEN NULL
                    ELSE REPLACE(complement_info_taux, ',', '.')::numeric 
                END
            ELSE REPLACE(taux_pour_mille, ',', '.')::numeric 
        END AS taux_retenu,
        
        -- Règle : Fiabilité
        CASE 
            WHEN TRIM(taux_pour_mille) IN ('', 'NA', 'N/A', 'NULL', 'error') OR taux_pour_mille IS NULL THEN false 
            ELSE true 
        END AS est_fiable,
        
        -- Préparation pour le département
        CASE 
            WHEN TRIM(complement_info_taux) IN ('', 'NA', 'N/A', 'NULL', 'error') OR complement_info_taux IS NULL THEN NULL
            ELSE REPLACE(complement_info_taux, ',', '.')::numeric 
        END AS comp_taux_num
    FROM delinquance_nat
),

-- 2. Moyenne par COMMUNE
stats_com AS (
    SELECT 
        codgeo,
        coddep,
        indicateur,
        AVG(taux_retenu) AS i_com,
        AVG(insee_pop) AS pop_com,
        BOOL_AND(est_fiable) AS i_com_fiabilite
    FROM base_clean
    GROUP BY codgeo, coddep, indicateur
),

-- 3. Valeurs pour le DEPARTEMENT
premiere_valeur_dep AS (
    SELECT DISTINCT ON (coddep, indicateur, annee) 
        coddep, indicateur, annee, comp_taux_num
    FROM base_clean
    WHERE comp_taux_num IS NOT NULL
    ORDER BY coddep, indicateur, annee, codgeo
),
stats_dep AS (
    SELECT 
        c.coddep,
        c.indicateur,
        COALESCE(
            AVG(p.comp_taux_num), 
            AVG(c.taux_retenu)    
        ) AS i_dep
    FROM base_clean c
    LEFT JOIN premiere_valeur_dep p 
        ON c.coddep = p.coddep AND c.indicateur = p.indicateur AND c.annee = p.annee
    GROUP BY c.coddep, c.indicateur
),

-- 4. Jointure et nettoyage population dep/reg (au cas où il y ait des 'NA' ici aussi)
stats_dep_enrichies AS (
    SELECT 
        sd.coddep,
        sd.indicateur,
        sd.i_dep,
        pd.codreg,
        CASE 
            WHEN TRIM(pd.ptot::text) IN ('', 'NA', 'N/A', 'NULL', 'error') OR pd.ptot IS NULL THEN NULL 
            ELSE REPLACE(pd.ptot::text, ',', '.')::numeric 
        END AS pop_dep,
        CASE 
            WHEN TRIM(pd.ptotreg::text) IN ('', 'NA', 'N/A', 'NULL', 'error') OR pd.ptotreg IS NULL THEN NULL 
            ELSE REPLACE(pd.ptotreg::text, ',', '.')::numeric 
        END AS pop_reg
    FROM stats_dep sd
    JOIN pop_dep pd ON sd.coddep = pd.coddep
),

-- 5. Moyenne par REGION
stats_reg AS (
    SELECT 
        codreg,
        indicateur,
        AVG(i_dep) AS i_reg
    FROM stats_dep_enrichies
    GROUP BY codreg, indicateur
)

-- 6. ASSEMBLAGE FINAL ET CALCULS DE SIGNIFICATIVITE
SELECT 
    sc.codgeo,
    sc.coddep,
    sde.codreg,
    sc.indicateur,
    sc.i_com,
    sde.i_dep,
    sr.i_reg,
    sc.i_com_fiabilite,
    
    -- Z-Score Com vs Dep
    CASE 
        WHEN (sc.i_com/1000) = (sde.i_dep/1000) THEN 'non significatif'
        WHEN ABS((sc.i_com/1000) - (sde.i_dep/1000)) / NULLIF(SQRT(
            ((sde.i_dep/1000) * (1 - (sde.i_dep/1000)) / NULLIF(sc.pop_com, 0)) + 
            ((sde.i_dep/1000) * (1 - (sde.i_dep/1000)) / NULLIF(sde.pop_dep, 0))
        ), 0) > 1.96 
        THEN 
            CASE WHEN sc.i_com > sde.i_dep THEN 'significativement plus haut' ELSE 'significativement plus bas' END
        ELSE 'non significatif'
    END AS sign_com_dep,

    -- Z-Score Com vs Reg
    CASE 
        WHEN (sc.i_com/1000) = (sr.i_reg/1000) THEN 'non significatif'
        WHEN ABS((sc.i_com/1000) - (sr.i_reg/1000)) / NULLIF(SQRT(
            ((sr.i_reg/1000) * (1 - (sr.i_reg/1000)) / NULLIF(sc.pop_com, 0)) + 
            ((sr.i_reg/1000) * (1 - (sr.i_reg/1000)) / NULLIF(sde.pop_reg, 0))
        ), 0) > 1.96 
        THEN 
            CASE WHEN sc.i_com > sr.i_reg THEN 'significativement plus haut' ELSE 'significativement plus bas' END
        ELSE 'non significatif'
    END AS sign_com_reg,

    -- Z-Score Dep vs Reg
    CASE 
        WHEN (sde.i_dep/1000) = (sr.i_reg/1000) THEN 'non significatif'
        WHEN ABS((sde.i_dep/1000) - (sr.i_reg/1000)) / NULLIF(SQRT(
            ((sr.i_reg/1000) * (1 - (sr.i_reg/1000)) / NULLIF(sde.pop_dep, 0)) + 
            ((sr.i_reg/1000) * (1 - (sr.i_reg/1000)) / NULLIF(sde.pop_reg, 0))
        ), 0) > 1.96 
        THEN 
            CASE WHEN sde.i_dep > sr.i_reg THEN 'significativement plus haut' ELSE 'significativement plus bas' END
        ELSE 'non significatif'
    END AS sign_dep_reg

FROM stats_com sc
JOIN stats_dep_enrichies sde ON sc.coddep = sde.coddep AND sc.indicateur = sde.indicateur
JOIN stats_reg sr ON sde.codreg = sr.codreg AND sc.indicateur = sr.indicateur;

Cette première requete permet de créer les indicateurs que l'on va calculer, elle est déjà exploitable mais chaque ligne représente une infraction par commune, on ne peut donc pas utiliser cette dernière pour obtenir la table que l'on recherche. 

Pour cela on va donc effectuer une deuxième requete : 

DROP TABLE IF EXISTS I_Deli_TX;

CREATE TABLE I_Deli_TX AS
SELECT 
    codgeo, 
    coddep, 
    codreg,

    -- 1. Violences physiques intrafamiliales (VPI)
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN i_com END) AS I_VPI_Com,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN i_dep END) AS I_VPI_Dep,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN i_reg END) AS I_VPI_Reg,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN sign_com_dep END) AS I_Sign_VPI_Com_Dep,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN sign_com_reg END) AS I_Sign_VPI_Com_Reg,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN sign_dep_reg END) AS I_Sign_VPI_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Violences physiques intrafamiliales' THEN i_com_fiabilite ELSE true END) AS I_VPI_Com_Fiabilite,

    -- 2. Violences physiques hors cadre familial (VPHCF)
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN i_com END) AS I_VPHCF_Com,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN i_dep END) AS I_VPHCF_Dep,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN i_reg END) AS I_VPHCF_Reg,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN sign_com_dep END) AS I_Sign_VPHCF_Com_Dep,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN sign_com_reg END) AS I_Sign_VPHCF_Com_Reg,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN sign_dep_reg END) AS I_Sign_VPHCF_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Violences physiques hors cadre familial' THEN i_com_fiabilite ELSE true END) AS I_VPHCF_Com_Fiabilite,

    -- 3. Violences sexuelles (VS)
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN i_com END) AS I_VS_Com,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN i_dep END) AS I_VS_Dep,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN i_reg END) AS I_VS_Reg,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN sign_com_dep END) AS I_Sign_VS_Com_Dep,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN sign_com_reg END) AS I_Sign_VS_Com_Reg,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' THEN sign_dep_reg END) AS I_Sign_VS_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Violences sexuelles' THEN i_com_fiabilite ELSE true END) AS I_VS_Com_Fiabilite,

    -- 4. Vols avec armes (VAA)
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN i_com END) AS I_VAA_Com,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN i_dep END) AS I_VAA_Dep,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN i_reg END) AS I_VAA_Reg,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN sign_com_dep END) AS I_Sign_VAA_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN sign_com_reg END) AS I_Sign_VAA_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols avec armes' THEN sign_dep_reg END) AS I_Sign_VAA_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols avec armes' THEN i_com_fiabilite ELSE true END) AS I_VAA_Com_Fiabilite,

    -- 5. Vols violents sans arme (VVSA)
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN i_com END) AS I_VVSA_Com,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN i_dep END) AS I_VVSA_Dep,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN i_reg END) AS I_VVSA_Reg,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN sign_com_dep END) AS I_Sign_VVSA_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN sign_com_reg END) AS I_Sign_VVSA_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' THEN sign_dep_reg END) AS I_Sign_VVSA_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols violents sans arme' THEN i_com_fiabilite ELSE true END) AS I_VVSA_Com_Fiabilite,

    -- 6. Vols sans violence contre des personnes (VSVCP)
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN i_com END) AS I_VSVCP_Com,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN i_dep END) AS I_VSVCP_Dep,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN i_reg END) AS I_VSVCP_Reg,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN sign_com_dep END) AS I_Sign_VSVCP_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN sign_com_reg END) AS I_Sign_VSVCP_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN sign_dep_reg END) AS I_Sign_VSVCP_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols sans violence contre des personnes' THEN i_com_fiabilite ELSE true END) AS I_VSVCP_Com_Fiabilite,

    -- 7. Escroqueries et fraudes aux moyens de paiement (EFMP)
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN i_com END) AS I_EFMP_Com,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN i_dep END) AS I_EFMP_Dep,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN i_reg END) AS I_EFMP_Reg,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN sign_com_dep END) AS I_Sign_EFMP_Com_Dep,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN sign_com_reg END) AS I_Sign_EFMP_Com_Reg,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN sign_dep_reg END) AS I_Sign_EFMP_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' THEN i_com_fiabilite ELSE true END) AS I_EFMP_Com_Fiabilite,

    -- 8. Cambriolages de logement (CL)
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN i_com END) AS I_CL_Com,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN i_dep END) AS I_CL_Dep,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN i_reg END) AS I_CL_Reg,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN sign_com_dep END) AS I_Sign_CL_Com_Dep,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN sign_com_reg END) AS I_Sign_CL_Com_Reg,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' THEN sign_dep_reg END) AS I_Sign_CL_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Cambriolages de logement' THEN i_com_fiabilite ELSE true END) AS I_CL_Com_Fiabilite,

    -- 9. Vols de véhicule (VV)
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN i_com END) AS I_VV_Com,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN i_dep END) AS I_VV_Dep,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN i_reg END) AS I_VV_Reg,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN sign_com_dep END) AS I_Sign_VV_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN sign_com_reg END) AS I_Sign_VV_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' THEN sign_dep_reg END) AS I_Sign_VV_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols de véhicule' THEN i_com_fiabilite ELSE true END) AS I_VV_Com_Fiabilite,

    -- 10. Vols dans les véhicules (VDV)
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN i_com END) AS I_VDV_Com,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN i_dep END) AS I_VDV_Dep,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN i_reg END) AS I_VDV_Reg,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN sign_com_dep END) AS I_Sign_VDV_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN sign_com_reg END) AS I_Sign_VDV_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' THEN sign_dep_reg END) AS I_Sign_VDV_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols dans les véhicules' THEN i_com_fiabilite ELSE true END) AS I_VDV_Com_Fiabilite,

    -- 11. Vols d'accessoires sur véhicules (VAV)
    -- Attention ici : on met deux apostrophes ('') pour dire à SQL qu'il s'agit du texte "d'accessoires" et non de la fin de la chaîne
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN i_com END) AS I_VAV_Com,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN i_dep END) AS I_VAV_Dep,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN i_reg END) AS I_VAV_Reg,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN sign_com_dep END) AS I_Sign_VAV_Com_Dep,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN sign_com_reg END) AS I_Sign_VAV_Com_Reg,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN sign_dep_reg END) AS I_Sign_VAV_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' THEN i_com_fiabilite ELSE true END) AS I_VAV_Com_Fiabilite,

    -- 12. Destructions et dégradations volontaires (DDV)
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN i_com END) AS I_DDV_Com,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN i_dep END) AS I_DDV_Dep,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN i_reg END) AS I_DDV_Reg,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN sign_com_dep END) AS I_Sign_DDV_Com_Dep,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN sign_com_reg END) AS I_Sign_DDV_Com_Reg,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN sign_dep_reg END) AS I_Sign_DDV_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Destructions et dégradations volontaires' THEN i_com_fiabilite ELSE true END) AS I_DDV_Com_Fiabilite,

    -- 13. Usage de stupéfiants (US)
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN i_com END) AS I_US_Com,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN i_dep END) AS I_US_Dep,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN i_reg END) AS I_US_Reg,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN sign_com_dep END) AS I_Sign_US_Com_Dep,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN sign_com_reg END) AS I_Sign_US_Com_Reg,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' THEN sign_dep_reg END) AS I_Sign_US_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Usage de stupéfiants' THEN i_com_fiabilite ELSE true END) AS I_US_Com_Fiabilite,

    -- 14. Usage de stupéfiants (AFD) (USAFD)
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN i_com END) AS I_USAFD_Com,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN i_dep END) AS I_USAFD_Dep,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN i_reg END) AS I_USAFD_Reg,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN sign_com_dep END) AS I_Sign_USAFD_Com_Dep,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN sign_com_reg END) AS I_Sign_USAFD_Com_Reg,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN sign_dep_reg END) AS I_Sign_USAFD_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' THEN i_com_fiabilite ELSE true END) AS I_USAFD_Com_Fiabilite,

    -- 15. Trafic de stupéfiants (TS)
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN i_com END) AS I_TS_Com,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN i_dep END) AS I_TS_Dep,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN i_reg END) AS I_TS_Reg,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN sign_com_dep END) AS I_Sign_TS_Com_Dep,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN sign_com_reg END) AS I_Sign_TS_Com_Reg,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN sign_dep_reg END) AS I_Sign_TS_Dep_Reg,
    BOOL_AND(CASE WHEN indicateur = 'Trafic de stupéfiants' THEN i_com_fiabilite ELSE true END) AS I_TS_Com_Fiabilite

FROM i_deli_nat
GROUP BY codgeo, coddep, codreg
ORDER BY codgeo;

Cette requête permet d'avoir une table où chaque ligne représente une commune et chaque colonne représente une des colonnes définies préalablement. 

NE PAS OUVRIR SANS FILTRE

Pour pouvoir exploiter cette base de données, il faut suivre un protocole, une fois que vous avez ajouté cette table à QGIS vous remarquerez qu'elle n'a pas de géométrie, c'est normal. Pour pouvoir ouvrir la table attributaire il vous faudra filtrer la table avec une requête du type 

codgeo = 'Insee_com_de_votre_commune' 

Ou alors 

coddep = 'Numéro_de_département_de_votre_commune'

Vous pourrez ensuite joindre cette table à une couche avec géométrie correspondant.

Une mise à jour existe mais est difficilement exploitable due au poids de la table  cette dernière rajoute des colonnes elle se trouve dans la colonne i_deli_tx_complete

Identifiant Commune codgeo TEXT
Identifiant Département coddep TEXT
Identifiant région codreg TEXT

Taux pour mille en fonction du crime échelle communale

 i_"Abréviation de l'infraction en minuscule"_com Numeric
Taux pour mille en fonction du crime échelle départementale i_"Abréviation de l'infraction en minuscule"_dep Numeric
Taux pour mille en fonction du crime échelle régionale i_"Abréviation de l'infraction en minuscule"_reg Numeric
Significativité crime entre commune et département i_sign_"Abréviation de l'infraction en minuscule"_com_dep Numeric
Significativité crime entre commune et région i_sign_"Abréviation de l'infraction en minuscule"_com_reg Numeric
Significativité crime entre département et région i_sign_"Abréviation de l'infraction en minuscule"_dep_reg Numeric
Fiabilité des données sur le crime à l'échelle communale ( y'a t'il une donnée manquante sur une année, au quel cas on a pris la moyenne départementale sur cette année )  i_"Abréviation de l'infraction en minuscule"_com_fiabilite Boolean
Taux pour mille en fonction du crime  et de l'année (2016 ou 2024 ) échelle communale  i_"Abréviation de l'infraction en minuscule"_com_"Année sélectionnée" Numeric
Taux pour mille en fonction du crime  et de l'année (2016 ou 2024 ) échelle départementale i_"Abréviation de l'infraction en minuscule"_dep_"Année sélectionnée" Numeric
Taux pour mille en fonction du crime  et de l'année (2016 ou 2024 ) échelle régionale i_"Abréviation de l'infraction en minuscule"_reg_"Année sélectionnée" Numeric
Évolution de taux pour mille en fonction du crime entre 2016 et 2024 (2024/2016) i_"Abréviation de l'infraction en minuscule"_ratio_24_16 Numeric

Pour cela on effectue deux nouvelles requetes la première permet d'obtenir toutes les données pour les années 2016 et 2024 elle se trouve dans la base de données i_deli_tx_evol :

DROP TABLE IF EXISTS I_Deli_TX_Evol;

CREATE TABLE I_Deli_TX_Evol AS
WITH base_clean AS (
    SELECT 
        codgeo_2025 AS codgeo,
        LEFT(codgeo_2025, 2) AS coddep,
        annee,
        indicateur,
        CASE WHEN TRIM(taux_pour_mille) IN ('', 'NA', 'N/A', 'NULL', 'error') OR taux_pour_mille IS NULL THEN 
                 CASE WHEN TRIM(complement_info_taux) IN ('', 'NA', 'N/A', 'NULL', 'error') OR complement_info_taux IS NULL THEN NULL
                      ELSE REPLACE(complement_info_taux, ',', '.')::numeric END
             ELSE REPLACE(taux_pour_mille, ',', '.')::numeric END AS taux_retenu,
        CASE WHEN TRIM(complement_info_taux) IN ('', 'NA', 'N/A', 'NULL', 'error') OR complement_info_taux IS NULL THEN NULL
             ELSE REPLACE(complement_info_taux, ',', '.')::numeric END AS comp_taux_num
    -- /!\ VÉRIFIEZ BIEN QUE C'EST LE NOM DE VOTRE TABLE BRUTE ICI :
    FROM delinquance_nat 
    WHERE annee IN ('2016', '2024')
),
stats_com_yr AS (
    SELECT codgeo, coddep, indicateur, annee, AVG(taux_retenu) AS i_com_yr
    FROM base_clean GROUP BY codgeo, coddep, indicateur, annee
),
premiere_valeur_dep_yr AS (
    SELECT DISTINCT ON (coddep, indicateur, annee) coddep, indicateur, annee, comp_taux_num
    FROM base_clean WHERE comp_taux_num IS NOT NULL ORDER BY coddep, indicateur, annee, codgeo
),
stats_dep_yr AS (
    SELECT c.coddep, c.indicateur, c.annee, COALESCE(AVG(p.comp_taux_num), AVG(c.taux_retenu)) AS i_dep_yr
    FROM base_clean c
    LEFT JOIN premiere_valeur_dep_yr p ON c.coddep = p.coddep AND c.indicateur = p.indicateur AND c.annee = p.annee
    GROUP BY c.coddep, c.indicateur, c.annee
),
stats_dep_yr_enrichies AS (
    SELECT sd.coddep, sd.indicateur, sd.annee, sd.i_dep_yr, pd.codreg
    FROM stats_dep_yr sd JOIN pop_dep pd ON sd.coddep = pd.coddep
),
stats_reg_yr AS (
    SELECT codreg, indicateur, annee, AVG(i_dep_yr) AS i_reg_yr
    FROM stats_dep_yr_enrichies GROUP BY codreg, indicateur, annee
),
deli_inter_yr AS (
    SELECT sc.codgeo, sc.indicateur, sc.annee, sc.i_com_yr, sde.i_dep_yr, sr.i_reg_yr
    FROM stats_com_yr sc
    JOIN stats_dep_yr_enrichies sde ON sc.coddep = sde.coddep AND sc.indicateur = sde.indicateur AND sc.annee = sde.annee
    JOIN stats_reg_yr sr ON sde.codreg = sr.codreg AND sc.indicateur = sr.indicateur AND sc.annee = sr.annee
)

-- LE PIVOT DES 105 COLONNES
SELECT 
    codgeo,

    -- VPI (Violences physiques intrafamiliales)
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2016' THEN i_com_yr END) AS I_VPI_Com_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2016' THEN i_dep_yr END) AS I_VPI_Dep_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2016' THEN i_reg_yr END) AS I_VPI_Reg_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2024' THEN i_com_yr END) AS I_VPI_Com_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2024' THEN i_dep_yr END) AS I_VPI_Dep_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2024' THEN i_reg_yr END) AS I_VPI_Reg_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Violences physiques intrafamiliales' AND annee = '2016' THEN i_com_yr END), 0) AS I_VPI_Ratio_24_16,

    -- VPHCF (Violences physiques hors cadre familial)
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2016' THEN i_com_yr END) AS I_VPHCF_Com_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2016' THEN i_dep_yr END) AS I_VPHCF_Dep_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2016' THEN i_reg_yr END) AS I_VPHCF_Reg_2016,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2024' THEN i_com_yr END) AS I_VPHCF_Com_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2024' THEN i_dep_yr END) AS I_VPHCF_Dep_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2024' THEN i_reg_yr END) AS I_VPHCF_Reg_2024,
    MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Violences physiques hors cadre familial' AND annee = '2016' THEN i_com_yr END), 0) AS I_VPHCF_Ratio_24_16,

    -- VS (Violences sexuelles)
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2016' THEN i_com_yr END) AS I_VS_Com_2016,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2016' THEN i_dep_yr END) AS I_VS_Dep_2016,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2016' THEN i_reg_yr END) AS I_VS_Reg_2016,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2024' THEN i_com_yr END) AS I_VS_Com_2024,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2024' THEN i_dep_yr END) AS I_VS_Dep_2024,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2024' THEN i_reg_yr END) AS I_VS_Reg_2024,
    MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Violences sexuelles' AND annee = '2016' THEN i_com_yr END), 0) AS I_VS_Ratio_24_16,

    -- VAA (Vols avec armes)
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2016' THEN i_com_yr END) AS I_VAA_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2016' THEN i_dep_yr END) AS I_VAA_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2016' THEN i_reg_yr END) AS I_VAA_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2024' THEN i_com_yr END) AS I_VAA_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2024' THEN i_dep_yr END) AS I_VAA_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2024' THEN i_reg_yr END) AS I_VAA_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols avec armes' AND annee = '2016' THEN i_com_yr END), 0) AS I_VAA_Ratio_24_16,

    -- VVSA (Vols violents sans arme)
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2016' THEN i_com_yr END) AS I_VVSA_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2016' THEN i_dep_yr END) AS I_VVSA_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2016' THEN i_reg_yr END) AS I_VVSA_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2024' THEN i_com_yr END) AS I_VVSA_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2024' THEN i_dep_yr END) AS I_VVSA_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2024' THEN i_reg_yr END) AS I_VVSA_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols violents sans arme' AND annee = '2016' THEN i_com_yr END), 0) AS I_VVSA_Ratio_24_16,

    -- VSVCP (Vols sans violence contre des personnes)
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2016' THEN i_com_yr END) AS I_VSVCP_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2016' THEN i_dep_yr END) AS I_VSVCP_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2016' THEN i_reg_yr END) AS I_VSVCP_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2024' THEN i_com_yr END) AS I_VSVCP_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2024' THEN i_dep_yr END) AS I_VSVCP_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2024' THEN i_reg_yr END) AS I_VSVCP_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols sans violence contre des personnes' AND annee = '2016' THEN i_com_yr END), 0) AS I_VSVCP_Ratio_24_16,

    -- CL (Cambriolages de logement)
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2016' THEN i_com_yr END) AS I_CL_Com_2016,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2016' THEN i_dep_yr END) AS I_CL_Dep_2016,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2016' THEN i_reg_yr END) AS I_CL_Reg_2016,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2024' THEN i_com_yr END) AS I_CL_Com_2024,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2024' THEN i_dep_yr END) AS I_CL_Dep_2024,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2024' THEN i_reg_yr END) AS I_CL_Reg_2024,
    MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Cambriolages de logement' AND annee = '2016' THEN i_com_yr END), 0) AS I_CL_Ratio_24_16,

    -- VV (Vols de véhicule)
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2016' THEN i_com_yr END) AS I_VV_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2016' THEN i_dep_yr END) AS I_VV_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2016' THEN i_reg_yr END) AS I_VV_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2024' THEN i_com_yr END) AS I_VV_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2024' THEN i_dep_yr END) AS I_VV_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2024' THEN i_reg_yr END) AS I_VV_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols de véhicule' AND annee = '2016' THEN i_com_yr END), 0) AS I_VV_Ratio_24_16,

    -- VDV (Vols dans les véhicules)
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2016' THEN i_com_yr END) AS I_VDV_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2016' THEN i_dep_yr END) AS I_VDV_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2016' THEN i_reg_yr END) AS I_VDV_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2024' THEN i_com_yr END) AS I_VDV_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2024' THEN i_dep_yr END) AS I_VDV_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2024' THEN i_reg_yr END) AS I_VDV_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols dans les véhicules' AND annee = '2016' THEN i_com_yr END), 0) AS I_VDV_Ratio_24_16,

    -- VAV (Vols d'accessoires sur véhicules)
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2016' THEN i_com_yr END) AS I_VAV_Com_2016,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2016' THEN i_dep_yr END) AS I_VAV_Dep_2016,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2016' THEN i_reg_yr END) AS I_VAV_Reg_2016,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2024' THEN i_com_yr END) AS I_VAV_Com_2024,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2024' THEN i_dep_yr END) AS I_VAV_Dep_2024,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2024' THEN i_reg_yr END) AS I_VAV_Reg_2024,
    MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Vols d''accessoires sur véhicules' AND annee = '2016' THEN i_com_yr END), 0) AS I_VAV_Ratio_24_16,

    -- DDV (Destructions et dégradations volontaires)
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2016' THEN i_com_yr END) AS I_DDV_Com_2016,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2016' THEN i_dep_yr END) AS I_DDV_Dep_2016,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2016' THEN i_reg_yr END) AS I_DDV_Reg_2016,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2024' THEN i_com_yr END) AS I_DDV_Com_2024,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2024' THEN i_dep_yr END) AS I_DDV_Dep_2024,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2024' THEN i_reg_yr END) AS I_DDV_Reg_2024,
    MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Destructions et dégradations volontaires' AND annee = '2016' THEN i_com_yr END), 0) AS I_DDV_Ratio_24_16,

    -- US (Usage de stupéfiants)
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2016' THEN i_com_yr END) AS I_US_Com_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2016' THEN i_dep_yr END) AS I_US_Dep_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2016' THEN i_reg_yr END) AS I_US_Reg_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2024' THEN i_com_yr END) AS I_US_Com_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2024' THEN i_dep_yr END) AS I_US_Dep_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2024' THEN i_reg_yr END) AS I_US_Reg_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Usage de stupéfiants' AND annee = '2016' THEN i_com_yr END), 0) AS I_US_Ratio_24_16,

    -- USAFD (Usage de stupéfiants (AFD))
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2016' THEN i_com_yr END) AS I_USAFD_Com_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2016' THEN i_dep_yr END) AS I_USAFD_Dep_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2016' THEN i_reg_yr END) AS I_USAFD_Reg_2016,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2024' THEN i_com_yr END) AS I_USAFD_Com_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2024' THEN i_dep_yr END) AS I_USAFD_Dep_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2024' THEN i_reg_yr END) AS I_USAFD_Reg_2024,
    MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Usage de stupéfiants (AFD)' AND annee = '2016' THEN i_com_yr END), 0) AS I_USAFD_Ratio_24_16,

    -- TS (Trafic de stupéfiants)
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2016' THEN i_com_yr END) AS I_TS_Com_2016,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2016' THEN i_dep_yr END) AS I_TS_Dep_2016,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2016' THEN i_reg_yr END) AS I_TS_Reg_2016,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2024' THEN i_com_yr END) AS I_TS_Com_2024,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2024' THEN i_dep_yr END) AS I_TS_Dep_2024,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2024' THEN i_reg_yr END) AS I_TS_Reg_2024,
    MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Trafic de stupéfiants' AND annee = '2016' THEN i_com_yr END), 0) AS I_TS_Ratio_24_16,

    -- EFMP (Escroqueries et fraudes aux moyens de paiement)
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2016' THEN i_com_yr END) AS I_EFMP_Com_2016,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2016' THEN i_dep_yr END) AS I_EFMP_Dep_2016,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2016' THEN i_reg_yr END) AS I_EFMP_Reg_2016,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2024' THEN i_com_yr END) AS I_EFMP_Com_2024,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2024' THEN i_dep_yr END) AS I_EFMP_Dep_2024,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2024' THEN i_reg_yr END) AS I_EFMP_Reg_2024,
    MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2024' THEN i_com_yr END) / NULLIF(MAX(CASE WHEN indicateur = 'Escroqueries et fraudes aux moyens de paiement' AND annee = '2016' THEN i_com_yr END), 0) AS I_EFMP_Ratio_24_16

FROM deli_inter_yr
GROUP BY codgeo;
Cette base de données est lisible mais NE PAS OUVRIR SANS FILTRAGE PRÉALABLE les bases de données de ce fichier sont trop grosses elle feront planter SYSTÉMATIQUEMENT Qgis.
Filtrer de la même manière que au préalable.

Ensuite on effectue une dernière manipulation pour obtenir une base de données conjointe  qui s'appelle i_deli_tx_complete

DROP TABLE IF EXISTS I_Deli_TX_Complete;

CREATE TABLE I_Deli_TX_Complete AS
SELECT 
    t1.*, 
    -- On rajoute toutes les colonnes de l'annexe sauf "codgeo" pour éviter les doublons
    t2.I_VPI_Com_2016, t2.I_VPI_Dep_2016, t2.I_VPI_Reg_2016, t2.I_VPI_Com_2024, t2.I_VPI_Dep_2024, t2.I_VPI_Reg_2024, t2.I_VPI_Ratio_24_16,
    t2.I_VPHCF_Com_2016, t2.I_VPHCF_Dep_2016, t2.I_VPHCF_Reg_2016, t2.I_VPHCF_Com_2024, t2.I_VPHCF_Dep_2024, t2.I_VPHCF_Reg_2024, t2.I_VPHCF_Ratio_24_16,
    t2.I_VS_Com_2016, t2.I_VS_Dep_2016, t2.I_VS_Reg_2016, t2.I_VS_Com_2024, t2.I_VS_Dep_2024, t2.I_VS_Reg_2024, t2.I_VS_Ratio_24_16,
    t2.I_VAA_Com_2016, t2.I_VAA_Dep_2016, t2.I_VAA_Reg_2016, t2.I_VAA_Com_2024, t2.I_VAA_Dep_2024, t2.I_VAA_Reg_2024, t2.I_VAA_Ratio_24_16,
    t2.I_VVSA_Com_2016, t2.I_VVSA_Dep_2016, t2.I_VVSA_Reg_2016, t2.I_VVSA_Com_2024, t2.I_VVSA_Dep_2024, t2.I_VVSA_Reg_2024, t2.I_VVSA_Ratio_24_16,
    t2.I_VSVCP_Com_2016, t2.I_VSVCP_Dep_2016, t2.I_VSVCP_Reg_2016, t2.I_VSVCP_Com_2024, t2.I_VSVCP_Dep_2024, t2.I_VSVCP_Reg_2024, t2.I_VSVCP_Ratio_24_16,
    t2.I_CL_Com_2016, t2.I_CL_Dep_2016, t2.I_CL_Reg_2016, t2.I_CL_Com_2024, t2.I_CL_Dep_2024, t2.I_CL_Reg_2024, t2.I_CL_Ratio_24_16,
    t2.I_VV_Com_2016, t2.I_VV_Dep_2016, t2.I_VV_Reg_2016, t2.I_VV_Com_2024, t2.I_VV_Dep_2024, t2.I_VV_Reg_2024, t2.I_VV_Ratio_24_16,
    t2.I_VDV_Com_2016, t2.I_VDV_Dep_2016, t2.I_VDV_Reg_2016, t2.I_VDV_Com_2024, t2.I_VDV_Dep_2024, t2.I_VDV_Reg_2024, t2.I_VDV_Ratio_24_16,
    t2.I_VAV_Com_2016, t2.I_VAV_Dep_2016, t2.I_VAV_Reg_2016, t2.I_VAV_Com_2024, t2.I_VAV_Dep_2024, t2.I_VAV_Reg_2024, t2.I_VAV_Ratio_24_16,
    t2.I_DDV_Com_2016, t2.I_DDV_Dep_2016, t2.I_DDV_Reg_2016, t2.I_DDV_Com_2024, t2.I_DDV_Dep_2024, t2.I_DDV_Reg_2024, t2.I_DDV_Ratio_24_16,
    t2.I_US_Com_2016, t2.I_US_Dep_2016, t2.I_US_Reg_2016, t2.I_US_Com_2024, t2.I_US_Dep_2024, t2.I_US_Reg_2024, t2.I_US_Ratio_24_16,
    t2.I_USAFD_Com_2016, t2.I_USAFD_Dep_2016, t2.I_USAFD_Reg_2016, t2.I_USAFD_Com_2024, t2.I_USAFD_Dep_2024, t2.I_USAFD_Reg_2024, t2.I_USAFD_Ratio_24_16,
    t2.I_TS_Com_2016, t2.I_TS_Dep_2016, t2.I_TS_Reg_2016, t2.I_TS_Com_2024, t2.I_TS_Dep_2024, t2.I_TS_Reg_2024, t2.I_TS_Ratio_24_16,
    t2.I_EFMP_Com_2016, t2.I_EFMP_Dep_2016, t2.I_EFMP_Reg_2016, t2.I_EFMP_Com_2024, t2.I_EFMP_Dep_2024, t2.I_EFMP_Reg_2024, t2.I_EFMP_Ratio_24_16
FROM I_Deli_TX t1
LEFT JOIN I_Deli_TX_Evol t2 ON t1.codgeo = t2.codgeo;

NE JAMAIS OUVRIR CA PLANTE AUTOMATIQUEMENT. 

La base de données fait 250 colonnes pour 34000 lignes elle est absolument impossible à ouvrir. De manière générale elle n'est ouvrable et gérable que sur une seule commune pour ouvrir la table attributaire. 

 

No comments to display

Back to top