Requêtes SQL ODS et données (à partir de 2022)

National

5 dernières observations:
SELECT s.scientific_name, s.vernacular_name , CONCAT(day(o.created_at), '-', month(o.created_at), '-', year(o.created_at)) AS "jour de l'obs", CONCAT('stade', e.bbch_code, ' ', e.name, ' ', e.description) AS 'stade' FROM `observation` o
JOIN `individual` i ON o.individual_id = i.id
JOIN `event` e ON o.event_id = e.id
JOIN `species` s ON i.species_id = s.id
JOIN `type_species` ts ON s.type_id = ts.id
JOIN user ON o.user_id = user.id
WHERE o.deleted_at IS NULL

• AND NOT user.roles LIKE "%ROLE_ADMIN%"

order by o.created_at DESC LIMIT 5;

Nombre d'observations et d'utilisateurs actifs par an :
SELECT (YEAR(o.`created_at`)) as Année, count(o.id) as " NbObservations", count(DISTINCT u.id) as 'Utilisateurs actifs' FROM `observation` o JOIN `individual` i ON i.id = o.individual_id JOIN `station` s ON s.id = i.station_id JOIN `user` u ON o.user_id = u.id WHERE o.deleted_at IS NULL AND i.deleted_at IS NULL AND s.deleted_at IS NULL AND u.deleted_at IS NULL AND NOT u.roles LIKE "%ROLE_ADMIN%" GROUP BY YEAR(o.`created_at`) ORDER BY YEAR(o.`created_at`) desc;

Nouvelles inscriptions au site ODS :
SELECT CONCAT(YEAR(`created_at`), ' : ', count(*)) as nbNouveauxInscritsValidesParAn FROM `user` WHERE `status` = 1 GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) desc;

Nombre d'inscrits au site ODS :
SELECT CASE

• WHEN status = 0 THEN 'désactivé'
• WHEN status = 1 THEN 'actif'
• WHEN status = 2 THEN 'pas encore activé'

END as 'status du compte', count(*) as ' NbreUtilisateurs'
FROM `user`
WHERE deleted_at IS NULL
AND NOT roles LIKE "%ROLE_ADMIN%"
GROUP BY status;

Utilisateurs actifs :
SELECT CONCAT(YEAR(o.created_at), ' : ', count(DISTINCT u.id)) as nbUtilisateursActifsParAn FROM `user` u JOIN observation o ON o.user_id = u.id WHERE 1 GROUP BY YEAR(o.created_at) ORDER BY YEAR(o.created_at) desc;

Nombre de communes actives par année :
SELECT CONCAT(YEAR(o.created_at), ' : ', count(DISTINCT s.insee_code)) as nbCommunesActives FROM station s
JOIN individual i ON i.station_id = s.id
JOIN observation o ON o.individual_id = i.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL
GROUP BY YEAR(o.created_at)
ORDER BY YEAR(o.created_at) desc;

Nombre de communes actives sur toute la vie du programme :
SELECT CONCAT(YEAR(o.created_at), ' : ', count(DISTINCT s.insee_code)) as nbCommunesActives FROM station s
JOIN individual i ON i.station_id = s.id
JOIN observation o ON o.individual_id = i.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL;

Top 12 utilisateurs :
SELECT u.email as 'email utilisateur', u.display_name as "Nom de l'utilisateur", count(*) as "Nbr total d'obs", count(o.deleted_at) as "Nbr d'Obs supprimées", count(*) - count(o.deleted_at) as "Nbre d'Obs en ligne"
FROM `observation` o
JOIN `user` u ON o.user_id = u.id
AND YEAR(o.created_at) = 2025
GROUP BY o.user_id ORDER BY count(*) desc LIMIT 12;

Top 10 plantes :
SELECT CONCAT(s.scientific_name, ', ', s.vernacular_name, ' : ', count(*)) AS top10Especes
FROM `observation` o
JOIN `individual` i ON o.individual_id = i.id
JOIN `species` s ON i.species_id = s.id
JOIN `type_species` ts ON s.type_id = ts.id
JOIN user ON o.user_id = user.id
WHERE o.deleted_at IS NULL

• AND ts.reign = 'plantes'
• AND YEAR(o.created_at) = 2022
• AND NOT user.roles LIKE "%ROLE_ADMIN%"

GROUP BY s.id
ORDER BY count(*) DESC LIMIT 10;

Flop 10 plantes :
SELECT CONCAT(s.scientific_name, ', ', s.vernacular_name, ' : ', count(*)) AS top10Especes
FROM `observation` o
JOIN `individual` i ON o.individual_id = i.id
JOIN `species` s ON i.species_id = s.id
JOIN `type_species` ts ON s.type_id = ts.id
JOIN user ON o.user_id = user.id
WHERE o.deleted_at IS NULL

• AND ts.reign = 'plantes'
• AND YEAR(o.created_at) = 2022
• AND NOT user.roles LIKE "%ROLE_ADMIN%"

GROUP BY s.id
ORDER BY count(*) ASC LIMIT 10;

Top 10 animaux :
SELECT CONCAT(s.scientific_name, ', ', s.vernacular_name, ' : ', count(*)) AS top10Especes
FROM `observation` o
JOIN `individual` i ON o.individual_id = i.id
JOIN `species` s ON i.species_id = s.id
JOIN `type_species` ts ON s.type_id = ts.id
JOIN user ON o.user_id = user.id
WHERE o.deleted_at IS NULL

• AND ts.reign = 'animaux'
• AND YEAR(o.created_at) = 2022
• AND NOT user.roles LIKE "%ROLE_ADMIN%"

GROUP BY s.id
ORDER BY count(*) DESC LIMIT 10;

Flop 10 animaux :
SELECT CONCAT(s.scientific_name, ', ', s.vernacular_name, ' : ', count(*)) AS top10Especes
FROM `observation` o
JOIN `individual` i ON o.individual_id = i.id
JOIN `species` s ON i.species_id = s.id
JOIN `type_species` ts ON s.type_id = ts.id
JOIN user ON o.user_id = user.id
WHERE o.deleted_at IS NULL

• AND ts.reign = 'animaux'
• AND YEAR(o.created_at) = 2022
• AND NOT user.roles LIKE "%ROLE_ADMIN%"

GROUP BY s.id
ORDER BY count(*) ASC LIMIT 10;

Nombre stations actives :
SELECT CONCAT(YEAR(o.`created_at`), ' : ', count(DISTINCT s.id)) as nbStationsActivesParAnnee FROM `station` s
JOIN `individual` i ON i.station_id = s.id
JOIN `observation` o ON o.individual_id = i.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL
GROUP BY YEAR(o.`created_at`)
ORDER BY YEAR(o.`created_at`) desc;

Nombre stations avec des données :
SELECT count(DISTINCT s.id) as nbStationsAvecDonnees FROM `station` s
JOIN `individual` i ON i.station_id = s.id
JOIN `observation` o ON o.individual_id = i.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL;

Nombre d'observations par type de profil (asso, pro, citoyen) :
SELECT DISTINCT profile_type,count(o.id) FROM user u INNER JOIN observation o ON o.user_id=u.id GROUP BY profile_type;

Requête pour l'histogramme :
SELECT

• MONTH(o.date) AS mois,
• e.name AS etape,
• COUNT(o.id) AS nb_obs,
• (
• SELECT
  • COUNT(o3.id)
• FROM
  • observation o3
• INNER JOIN event e3 ON
  • o3.event_id = e3.id
• WHERE
  • o3.is_missing = 1 AND o3.deleted_at IS NULL AND e3.id <= 7 AND MONTH(o3.date) = mois AND e3.name = etape
• GROUP BY
  • MONTH(o3.date),
  • e3.name

) AS nb_obs_manquantes,
(

• SELECT
  • COUNT(o2.id)
• FROM
  • observation o2
• INNER JOIN event e2 ON
  • o2.event_id = e2.id
• WHERE
  • o2.deleted_at IS NULL AND e2.id <= 7

) AS nb_obs_total
FROM

• observation o

INNER JOIN event e ON

• o.event_id = e.id

WHERE

• o.deleted_at IS NULL AND e.id <= 7

GROUP BY

• mois,

Requête pour la particpation (différence entre date de première obs et la dernière enregistrée) :
SELECT DISTINCT email

• ,
• ABS(
  • DATEDIFF(
  • (SELECT
    • MIN(o.created_at)
  • FROM
    • user u
  • INNER JOIN individual i ON
    • i.user_id = u.id
  • INNER JOIN observation o ON
    • o.individual_id = i.id
  • WHERE
    • u.id = us.id),
  • (SELECT
    • MAX(o.created_at)
  • FROM
    • user u
  • INNER JOIN individual i ON
    • i.user_id = u.id
  • INNER JOIN observation o ON
    • o.individual_id = i.id
  • WHERE
    • u.id = us.id)
• )) AS duree, (SELECT MIN(o.created_at) FROM user u INNER JOIN individual i ON i.user_id = u.id INNER JOIN observation o ON o.individual_id = i.id WHERE u.id = us.id) as date_debut, (SELECT MAX(o.created_at) FROM user u INNER JOIN individual i ON i.user_id = u.id INNER JOIN observation o ON o.individual_id = i.id WHERE u.id = us.id) as date_fin

FROM

• user us order by duree DESC;

Occitanie

Nombre d'obervations par année en Occitanie + nbre inscrits actifs:
SELECT (YEAR(o.`created_at`)) as Année, count(o.id) as "Observations en Occitanie", count(DISTINCT u.id) as 'Utilisateurs actifs' FROM `observation` o
JOIN `individual` i ON i.id = o.individual_id
JOIN `station` s ON s.id = i.station_id
JOIN `user` u ON o.user_id = u.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL
AND u.deleted_at IS NULL
AND (s.department = 09 OR s.department = 11 OR s.department = 12 OR s.department = 30 OR s.department = 31 OR s.department = 32 OR s.department = 34 OR s.department = 46 OR s.department = 48 OR s.department = 65 OR s.department = 66 OR s.department = 81 OR s.department = 82)
AND NOT u.roles LIKE "%ROLE_ADMIN%"
GROUP BY YEAR(o.`created_at`)
ORDER BY YEAR(o.`created_at`) desc;

Nombre d'utilisateurs actifs par type et par année en Occitanie:
SELECT (YEAR(o.`created_at`)) as Année, count(o.id) as "Observations en Occitanie", count(DISTINCT u.id) as 'Utilisateurs actifs', u.profile_type as "Type d'utilisateur"
FROM `observation` o
JOIN `individual` i ON i.id = o.individual_id
JOIN `station` s ON s.id = i.station_id
JOIN `user` u ON o.user_id = u.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL
AND u.deleted_at IS NULL
AND (s.department = 09 OR s.department = 11 OR s.department = 12 OR s.department = 30 OR s.department = 31 OR s.department = 32 OR s.department = 34 OR s.department = 46 OR s.department = 48 OR s.department = 65 OR s.department = 66 OR s.department = 81 OR s.department = 82)
AND NOT u.roles LIKE "%ROLE_ADMIN%"
GROUP BY u.profile_type, YEAR(o.`created_at`)
ORDER BY u.profile_type, YEAR(o.`created_at`) DESC;

Nombre de communes touchées par année
SELECT CONCAT(YEAR(o.created_at), ' : ', count(DISTINCT s.insee_code)) as nbCommunesActivesEn2022 FROM station s
JOIN individual i ON i.station_id = s.id
JOIN observation o ON o.individual_id = i.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL
GROUP BY YEAR(o.created_at)
ORDER BY YEAR(o.created_at) desc;

Utilisateurs les + actifs en occitanie
SELECT u.email as 'email utilisateur', u.display_name as "Nom de l'utilisateur",u.post_code as "Ville", count(*) as "Nbr total d'obs", count(*) - count(o.deleted_at) as "Nbre d'Obs en ligne"
FROM `observation` o
JOIN `individual` i ON i.id = o.individual_id
JOIN `station` s ON s.id = i.station_id
JOIN `user` u ON o.user_id = u.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL
AND u.deleted_at IS NULL
AND (s.department = 09 OR s.department = 11 OR s.department = 12 OR s.department = 30 OR s.department = 31 OR s.department = 32 OR s.department = 34 OR s.department = 46 OR s.department = 48 OR s.department = 65 OR s.department = 66 OR s.department = 81 OR s.department = 82)
GROUP BY o.user_id ORDER BY count(*) desc LIMIT 5;

Provence

Nombre de stations en PACA

SELECT count(DISTINCT s.id) as nbStationsPACA FROM `station` s
WHERE s.deleted_at IS NULL
AND (s.department = 04 OR s.department = 05 OR s.department = 06 OR s.department = 13 OR s.department = 83 OR s.department = 84);

Détail des stations en PACA
SELECT count(DISTINCT s.id) as nbStationsPACA, u.profile_type as type FROM `station` s
JOIN user u ON s.user_id = u.id
WHERE s.deleted_at IS NULL
AND u.deleted_at IS NULL
AND (s.department = 04 OR s.department = 05 OR s.department = 06 OR s.department = 13 OR s.department = 83 OR s.department = 84)
GROUP BY profile_type;

Nombre d'obs entre 2 dates en PACA

SELECT count(DISTINCT o.id) as nbObsPACA FROM `station` s
JOIN `individual` i ON i.station_id = s.id
JOIN `observation` o ON o.individual_id = i.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL
AND (s.department = 04 OR s.department = 05 OR s.department = 06 OR s.department = 13 OR s.department = 83 OR s.department = 84)
AND o.created_at between '2022-06-01' and '2023-06-30';

Nombre d'obs depuis 2015 en PACA

SELECT count(DISTINCT o.id) as nbObsPACA FROM `station` s
JOIN `individual` i ON i.station_id = s.id
JOIN `observation` o ON o.individual_id = i.id
WHERE o.deleted_at IS NULL
AND i.deleted_at IS NULL
AND s.deleted_at IS NULL
AND (s.department = 04 OR s.department = 05 OR s.department = 06 OR s.department = 13 OR s.department = 83 OR s.department = 84)
AND YEAR(o.created_at) >= '2015';

Nombre total d'espèces

SELECT count(DISTINCT s.id) as nbEspeces FROM `species` s;

Nombre total d'utilisateurs (actif ou non)

SELECT count(DISTINCT u.id) as nbUtilisateurs FROM `user` u;

Top 3 des espèces depuis 2015

SELECT CONCAT(s.scientific_name, ', ', s.vernacular_name, ' : ', count(*)) AS top3Especes
FROM `observation` o
JOIN `individual` i ON o.individual_id = i.id
JOIN `species` s ON i.species_id = s.id
JOIN `type_species` ts ON s.type_id = ts.id
JOIN user ON o.user_id = user.id
WHERE o.deleted_at IS NULL

• AND YEAR(o.created_at) >= 2015
• AND NOT user.roles LIKE "%ROLE_ADMIN%"

GROUP BY s.id
ORDER BY count(*) DESC LIMIT 3;

Top 3 des epeces depuis 2015 en PACA

SELECT CONCAT(s.scientific_name, ', ', s.vernacular_name, ' : ', count(*)) AS top3Especes
FROM `observation` o
JOIN `individual` i ON o.individual_id = i.id
JOIN `species` s ON i.species_id = s.id
JOIN `type_species` ts ON s.type_id = ts.id
JOIN user ON o.user_id = user.id
Join `station` st ON i.station_id = st.id
WHERE o.deleted_at IS NULL

• AND YEAR(o.created_at) >= 2015
• AND NOT user.roles LIKE "%ROLE_ADMIN%"
• AND (st.department = 04 OR st.department = 05 OR st.department = 06 OR st.department = 13 OR st.department = 83 OR st.department = 84)

GROUP BY s.id
ORDER BY count(*) DESC LIMIT 3;

---