Skip to main content

Indicateur crise de confiance

Code pour obtenir la proportion d'établissements non conventionnels sur la base Sirene dans une commune spécifique

CREATE TABLE sirene_la_ciotat_sante AS 
WITH base_data AS (
    -- On isole d'abord les établissements actifs, sièges et de santé à La Ciotat
    SELECT * FROM read_csv_auto('C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Brut\StockEtablissement_utf8.csv', ALL_VARCHAR=TRUE)
    WHERE codeCommuneEtablissement = '13028' 
      AND etatAdministratifEtablissement = 'A'
      AND etablissementSiege = 'true'
      AND activitePrincipaleEtablissement IN (
          '86.90E', '86.90D', '86.23Z', 
          '86.22C', '86.22B', '86.22A', '86.21Z', '86.90F'
      )
)
SELECT 
    *,
    -- Calcul de l'indicateur : (Nombre de 86.90F / Nombre total de la sélection) * 100
    (COUNT(*) FILTER (WHERE activitePrincipaleEtablissement = '86.90F') OVER () * 100.0 / COUNT(*) OVER ()) as part_non_conv
FROM base_data;

-- Exportation vers un CSV propre pour QGIS
COPY sirene_la_ciotat_sante TO 'C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Brut\Sirene_Sante_La_Ciotat_Clean.csv' (HEADER, DELIMITER ',');

Code pour obtenir la proportion d'établissements non conventionnels sur la base Sirene dans un département : 

-- 1. Création de la table pour tout le département 13
CREATE TABLE sirene_13_sante_complet AS 
WITH base_data AS (
    -- On extrait les données brutes avec les filtres de base
    SELECT * FROM read_csv_auto('C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Brut\StockEtablissement_utf8.csv', ALL_VARCHAR=TRUE)
    WHERE codeCommuneEtablissement LIKE '13%' 
      AND etatAdministratifEtablissement = 'A'
      AND etablissementSiege = 'true'
      AND activitePrincipaleEtablissement IN (
          '86.90E', '86.90D', '86.23Z', 
          '86.22C', '86.22B', '86.22A', '86.21Z', '86.90F'
      )
)
SELECT 
    *,
    -- Calcul de l'indicateur arrondi à 2 décimales
    ROUND(
        (COUNT(*) FILTER (WHERE activitePrincipaleEtablissement = '86.90F') OVER () * 100.0 / COUNT(*) OVER ()), 
        2
    ) as part_non_conv
FROM base_data;

-- 2. Exportation du fichier départemental
COPY sirene_13_sante_complet TO 'C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Brut\Sirene_Sante_13_Final.csv' (HEADER, DELIMITER ',');

Modification rougeole et grippe : 

-- 1. TRAITEMENT DE LA BASE GRIPPE
-- On crée la table en mémoire avec la moyenne du 13
CREATE TABLE Vacc_Grippe_13 AS
SELECT 
    *,
    -- Calcul de la moyenne sur la colonne 'Vaccines' pour le 13 uniquement
    ROUND(AVG(Vaccines) OVER (), 2) as moy_vacc_grippe_13
FROM read_csv_auto('C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Region\Vacc_Grippe.csv')
-- Conversion de Code en texte pour permettre le filtrage LIKE '13%'
WHERE CAST(Code AS VARCHAR) LIKE '13%';

-- Exportation vers le fichier CSV réel
COPY Vacc_Grippe_13 TO 'C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Region\Vacc_Grippe_13_Clean.csv' (HEADER, DELIMITER ',');


-- 2. TRAITEMENT DE LA BASE ROUGEOLE
-- On crée la table en mémoire avec la moyenne du 13
CREATE TABLE Vacc_Rougeole_13 AS
SELECT 
    *,
    -- Calcul de la moyenne sur la colonne 'Vaccination' pour le 13 uniquement
    ROUND(AVG(Vaccination) OVER (), 2) as moy_vacc_rougeole_13
FROM read_csv_auto('C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Region\Vacc_Rougeole.csv')
-- Conversion de Code en texte pour permettre le filtrage LIKE '13%'
WHERE CAST(Code AS VARCHAR) LIKE '13%';

-- Exportation vers le fichier CSV réel
COPY Vacc_Rougeole_13 TO 'C:\Users\eliob\OneDrive\Documents\NEXTE\Stage\1ere semaine\Lundi\PACA\Data Region\Vacc_Rougeole_13_Clean.csv' (HEADER, DELIMITER ',');

Creation Indicateurs Commune : 

-- 1. CREATION DE LA TABLE
CREATE TABLE Table_Indicateurs_Final AS 
SELECT 
    r.Code,
    -- Calcul avec les noms de colonnes exacts détectés par DuckDB
    ROUND(
        (0.5 * CAST(r.Vaccination AS FLOAT) + 
         0.25 * CAST(g."Vaccinés" AS FLOAT) + 
         0.25 * AVG(CAST(s.part_8690Z AS FLOAT))), 
    2) AS I_T1_02_Com
FROM 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') r
JOIN 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') g 
    ON CAST(r.Code AS VARCHAR) = CAST(g.Code AS VARCHAR)
JOIN 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_La_Ciotat_Clean.csv') s
    ON CAST(r.Code AS VARCHAR) = CAST(s.codeCommuneEtablissement AS VARCHAR)
WHERE 
    CAST(r.Code AS VARCHAR) = '13028'
GROUP BY r.Code, r.Vaccination, g."Vaccinés";

-- 2. EXPORTATION
COPY Table_Indicateurs_Final TO 'C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Indicateurs.csv' (HEADER, DELIMITER ',');

Creation Indicateurs Dept :

 

-- 1. CREATION DE LA TABLE FINALE COMBINEE
CREATE TABLE Table_Indicateurs_Complet AS 
WITH stats_dept AS (
    -- On calcule la valeur départementale (Moyenne des moyennes)
    SELECT 
        AVG(CAST(r.moy_vacc_rougeole_13 AS FLOAT)) * 0.5 + 
        AVG(CAST(g.moy_vacc_grippe_13 AS FLOAT)) * 0.25 + 
        AVG(CAST(s.part_non_conv AS FLOAT)) * 0.25 AS val_dep
    FROM 
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') r,
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') g,
        -- MISE À JOUR DU CHEMIN ICI :
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_13_Final.csv') s
)
SELECT 
    r.Code,
    -- Colonne 1 : Indicateur de La Ciotat
    ROUND(
        (0.5 * CAST(r.Vaccination AS FLOAT) + 
         0.25 * CAST(g."Vaccinés" AS FLOAT) + 
         0.25 * AVG(CAST(sc.part_8690Z AS FLOAT))), 
    2) AS I_T1_02_Com,
    -- Colonne 2 : Indicateur du Département
    ROUND((SELECT val_dep FROM stats_dept), 2) AS I_T1_02_Dep
FROM 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') r
JOIN 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') g 
    ON CAST(r.Code AS VARCHAR) = CAST(g.Code AS VARCHAR)
JOIN 
    read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_La_Ciotat_Clean.csv') sc
    ON CAST(r.Code AS VARCHAR) = CAST(sc.codeCommuneEtablissement AS VARCHAR)
WHERE 
    CAST(r.Code AS VARCHAR) = '13028'
GROUP BY r.Code, r.Vaccination, g."Vaccinés";

-- 2. EXPORTATION FINALE
COPY Table_Indicateurs_Complet TO 'C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Indicateurs.csv' (HEADER, DELIMITER ',');

Creation colonne Significative : 

-- 1. CRÉATION DE LA TABLE COMPLÈTE AVEC INDICATEURS ET TEST
CREATE TABLE Table_Indicateurs_Final_Test AS 
WITH base_calculs AS (
    -- On récupère les valeurs de la commune et du département
    SELECT 
        r.Code,
        -- p_com (La Ciotat)
        ROUND((0.5 * CAST(r.Vaccination AS FLOAT) + 0.25 * CAST(g."Vaccinés" AS FLOAT) + 0.25 * AVG(CAST(sc.part_8690Z AS FLOAT))), 2) AS p_com,
        -- p_dep (Moyenne du 13 calculée dynamiquement)
        (SELECT ROUND(
            AVG(CAST(rv.moy_vacc_rougeole_13 AS FLOAT)) * 0.5 + 
            AVG(CAST(gv.moy_vacc_grippe_13 AS FLOAT)) * 0.25 + 
            AVG(CAST(sv.part_non_conv AS FLOAT)) * 0.25, 2
        ) FROM 
            read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') rv,
            read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') gv,
            read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_13_Final.csv') sv
        ) AS p_dep,
        -- n : Population de La Ciotat (pour la précision du test)
        35000 AS n
    FROM 
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Rougeole_13_Clean.csv') r
    JOIN 
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Vacc_Grippe_13_Clean.csv') g ON CAST(r.Code AS VARCHAR) = CAST(g.Code AS VARCHAR)
    JOIN 
        read_csv_auto('C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Sirene_Sante_La_Ciotat_Clean.csv') sc ON CAST(r.Code AS VARCHAR) = CAST(sc.codeCommuneEtablissement AS VARCHAR)
    WHERE CAST(r.Code AS VARCHAR) = '13028'
    GROUP BY r.Code, r.Vaccination, g."Vaccinés"
)
SELECT 
    Code,
    p_com AS I_T1_02_Com,
    p_dep AS I_T1_02_Dep,
    -- Calcul de la marge d'erreur (Z=1.96 pour alpha=5%)
    ROUND(1.96 * SQRT((p_dep/100 * (1 - p_dep/100)) / n) * 100, 3) AS marge_erreur,
    -- Diagnostic final
    CASE 
        WHEN p_com < (p_dep - (1.96 * SQRT((p_dep/100 * (1 - p_dep/100)) / n) * 100)) THEN 'Significativement PLUS BAS'
        WHEN p_com > (p_dep + (1.96 * SQRT((p_dep/100 * (1 - p_dep/100)) / n) * 100)) THEN 'Significativement PLUS HAUT'
        ELSE 'Conforme à la moyenne'
    END AS Interpretation
FROM base_calculs;

-- 2. EXPORTATION
COPY Table_Indicateurs_Final_Test TO 'C:/Users/eliob/OneDrive/Documents/NEXTE/Stage/1ere semaine/Lundi/Indicateur de crise de confiance/Indicateurs.csv' (HEADER, DELIMITER ',');

 

No comments to display

Back to top