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 ',');