Requête SQL pour la création d’un indicateur

Sur cette page vous trouverez quelques conseils pour écrire des requêtes SQL simples, ainsi que des exemples issus d’indicateurs utilisés à l’ANCT.

1. Structure d’une requête SQL

Les requêtes SQL se construisent à l’aide de mot clés. Les 4 mots clés principaux à comprendre sont SELECT ... FROM, WHERE et ORDER BY.

Prenons un exemple pour expliciter leur utilisation :

SELECT lib_com
FROM commune
WHERE typecom = 'COM'
ORDER BY lib_com

Nous souhaitons ici obtenir la liste des villes par ordre alphabétique en ne prenant que les communes de type COM, en effet dans le COG les arrondissements de Paris, Lyon et Marseille sont listés dans la table commune mais sont de types ARM.

Si vous souhaitez aller plus loin avec SQL, vous pouvez consulter ce site : https://sql.sh/.

2. Exemples utilisés dans des indicateurs de l’ANCT

Nous allons détailler plusieurs exemples qui permettent de couvrir un large spectre d’indicateurs utilisés par les programmes de l’ANCT. Ces différents exemples utilisent les 4 mots clés principaux vus précédemment et parfois d’autres qui permettent de faire des opérations plus précises.

Afficher un nombre d’occurences avec ou sans contrainte

Afin d’afficher un nombre d’occurences, on utilise le nouveau mot clé COUNT qui permet de compter le nombre de lignes dans une table.

Nombre de fabriques de territoire

SELECT COUNT(*)
FROM fabriques_territoire
WHERE etatdudossier = 'Accepté'

Si l’on veut le nombre de lignes dans la table, soit dans ce cas le nombre total de dossiers, il suffit de retirer la clause WHERE.

Filtrage par territoire

Si vous souhaitez connaître le nombre d’occurences pour un territoire donné, il ne faut pas ajouter de clause telle que WHERE insee_dep = '79' dans la requête SQL. Le filtre approprié sera ajouté automatiquement à la requête par insitu au moment de calculer la valeur de l’indicateur.

Afficher le nombre de villes concernées par un programme

Si une ligne du tableau concerne un dossier du programme et que pour chaque ligne on a l’information de la ville dans laquelle se situe ce programme, on peut alors compter le nombre de villes concernées. Comme il peut y avoir plusieurs dossiers sur une même ville, on ne veut compter chaque ville qu’une seule fois. Pour cela on utilise le mot clé DISTINCT.

Nombre de communes concernées par le programme FRLA

SELECT COUNT(DISTINCT nom_ville)
FROM subventions_frla

Si l’on souhaite afficher la liste de ces villes, la commande SQL serait la suivante :

Noms des communes concernées par le programme FRLA

SELECT DISTINCT nom_ville
FROM subventions_frla

Afficher une somme

Afin d’afficher la somme d’une colonne de la table on utilise le mot clé SUM.

Nombre de chefs de projet Petites Villes de Demain (PVD)

SELECT COALESCE(SUM(nb_cdp), 0)
FROM pvd_chefs_de_projet

Pourquoi a-t-on besoin de ce COALESCE ?

Si on fait simplement SELECT SUM(colonne), lorsqu’aucune ligne ne correspond au filtre appliqué, le résultat ne sera pas 0 mais NULL, qui signale l’absence de valeur. Pour avoir le résultat souhaité, on ajoute la fonction COALESCE qui permet de préciser une valeur par défaut : COALESCE(SUM(colonne), 0).

Afficher un tableau avec plusieurs contraintes

Si l’on souhaite afficher un tableau de valeurs, il faut lister dans le SELECT les colonnes à afficher, faire le filtre des lignes qui nous intéressent avec le mot clé WHERE et enfin ordonner les lignes si on le souhaite avec le mot clé ORDER BY.

Projets validés par le PICT en revue de projets

SELECT nom_projet, numero_projet
FROM pict_sollicitations
WHERE
  numero_projet IS NOT NULL
  AND numero_projet != ''
  AND numero_projet != 'test'
ORDER BY nom_projet, numero_projet

Dans le WHERE on sélectionne les projets qui ne sont pas un test et qui ont obtenu un numéro de projet. Dans le ORDER BY on trie les projets par leur nom en premier lieu, et si deux projets ont le même nom, on les trie par leur numéro de projet.

Afficher une colonne qui contient elle-même une liste

Si une colonne contient une liste de valeur, on peut utiliser la fonction array_to_string pour l’afficher correctement. Cette fonction permet de transformer un tableau en une chaîne de caractères en indicant le séparateur de chaque élément composant cette liste (ici une virgule) : array_to_string (nom_de_la_colonne, ', '). On peut utiliser le mot clé [AS] (https://sql.sh/cours/alias) pour renommer la colonne afin d’avoir un affichage plus clair et compréhensible.

Reprenons l’exemple précédent pour lequel on va ajouter un élément dans le SELECT :

Projets validés par le PICT en revue de projets

SELECT
    nom_projet,
    numero_projet,
    array_to_string(villes, ', ') AS porteurs
FROM pict_sollicitations
WHERE
  numero_projet IS NOT NULL
  AND numero_projet != ''
  AND numero_projet != 'test'
ORDER BY nom_projet, numero_projet

Dans le tableau qui s’affiche en résultat on ajoute donc une colonne nommée porteurs qui est une chaîne de caractères, correspondant à l’ensemble des éléments contenus dans la colonne villes de la table pict_sollicitations.

Astuce

On peut utiliser le mot clé AS pour renommer n’importe quelle colonne.

Afficher une liste de communes

On peut souvent vouloir afficher la liste des communes (ou départements) bénéficiant d’un programme. Or, dans la table de données ce qui est généralement renseigné c’est le code INSEE de cette commune. Dans ce cas il faut lier la table commune à celle nous intéressant (on appelle cela une jointure). Vous pouvez directement utiliser les parties en gras dans votre requête. Ce code fonctionne si votre table contient une colonne insee_com.

Liste des communes ayant bénéficié du Fonds de soutien aux commerces ruraux

SELECT DISTINCT commune.lib_com AS lib_com
FROM subventions_commerces_ruraux
JOIN commune USING (insee_com)

Attention au choix du COG

La table commune correspond actuellement au millésime 2021 du code officiel géographique (COG. Si les données utilisent un COG plus récent, il sera préférable d’utiliser la table du bon millésime, par exemple commune_2024.