Retour : Page Principale > sommaire applications botaniques > Détermination en Ligne

Requête SQL pour les stats de DEL

La base de données du DEL est : tb_del
Les requêtes SQL doivent se faire au sein de cette base.

Ces requêtes sont censées être implémentées dans le Widget de stats DEL :
http://www.tela-botanica.org/widget:del:stats

Création de la vue image avec ancien cel

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `del_image_old`  AS  select `i`.`id_image` AS `id_image`,`i`.`ce_observation` AS `ce_observation`,if(char_length(`i`.`ce_utilisateur`) <> 32 and `i`.`ce_utilisateur`  not like '%@%',cast(`i`.`ce_utilisateur` as unsigned),0) AS `ce_utilisateur`,`i`.`prenom_utilisateur` AS `prenom_utilisateur`,`i`.`nom_utilisateur` AS `nom_utilisateur`,`i`.`courriel_utilisateur` AS `courriel_utilisateur`,`i`.`hauteur` AS `hauteur`,`i`.`largeur` AS `largeur`,`i`.`date_prise_de_vue` AS `date_prise_de_vue`,`i`.`mots_cles_texte` AS `mots_cles_texte`,`i`.`commentaire` AS `commentaire`,`i`.`nom_original` AS `nom_original`,`i`.`date_creation` AS `date_creation`,`i`.`date_modification` AS `date_modification`,`i`.`date_liaison` AS `date_liaison`,`i`.`date_transmission` AS `date_transmission` from `tb_cel`.`cel_images` `i` where `i`.`transmission` = '1' ;
COMMIT;

Liste des stats demandées

identiplante :

utilisateurs
- nombre
  - proposition
  - commentaire
  - vote
  - action
- liste
  - faisant au moins une proposition par mois toute l'année
  - ayant fait le plus de vote positif
  - ayant eu le plus de votes positifs sur leur prop
votes
- nombre
commentaire
- nombre
action
- moyenne total/jour
  - par utilisateur par jour (liste du nombre d'actions par jour par utilisateur) par mois ?
proposition
- total des actions 2014 sur toutes obs
  - nombre (sans initiale)
  - retenu
  - avec consensus
- sur obs 2014
  - nombre (sans initiale)
  - retenu
  - avec consensus
observation
- nombre
- nombre total sans identification (ou tag ou certitude)
- nombre sans identification
- nombre avec tag à déterminer ou certitude incertaine
- nombre avec tag à déterminer ou certitude incertaine et retenue
- nombre avec tag à déterminer ou certitude incertaine et avec consensus
- nombre avec tag à déterminer ou certitude incertaine et avec consensus mais non validé

- sauvages
- pictoflora
Tags nombre total
Votes nombre total
Utilisateurs ayant fait une action

ayant fait un tag
ayant fait un vote

Images ayant au moins un tag

ayant au moins un vote
sur image 2014
- ayant au moins un tag
- ayant au moins un vote
- nombre total

Aide pour calculer à partir des nombres

moyenne par mois

SELECT AVG(nb_total) FROM (		GROUP BY CONCAT(year(date),month(date)) ) AS nombre

nombre sur liste

SELECT COUNT(*) FROM (		) AS liste

Attention ne calculer les pourcentages que si le nombre de proposition est calculé sur obs 2014

Liste des requêtes

IdentiPlante

Utilisateurs

nombre

proposition

SELECT COUNT(DISTINCT `utilisateur_courriel`) AS nb_total FROM del_commentaire WHERE `ce_proposition` = '' AND `nom_sel` IS NOT NULL AND `nom_sel` != "" AND  year(`date`) = 2014

commentaire

SELECT COUNT(DISTINCT `utilisateur_courriel`) AS nb_total FROM del_commentaire WHERE `ce_proposition` != '' AND (`nom_sel` IS NULL OR `nom_sel` = "") AND  year(`date`) = 2014

vote

SELECT COUNT(DISTINCT ce_utilisateur) AS nb_total  FROM del_commentaire_vote WHERE year(`date`) = 2014

vote identifié

SELECT COUNT(DISTINCT ce_utilisateur) AS nb_total  FROM del_commentaire_vote WHERE year(`date`) = 2014 AND ce_utilisateur REGEXP '^-?[0-9]+$'

vote anonyme

SELECT COUNT(DISTINCT ce_utilisateur) AS nb_total  FROM del_commentaire_vote WHERE year(`date`) = 2014 AND ce_utilisateur NOT REGEXP '^-?[0-9]+$'

action

SELECT COUNT(*) AS nb_total FROM (SELECT ce_utilisateur FROM del_commentaire_vote UNION SELECT ce_utilisateur FROM del_commentaire) AS action

liste

faisant au moins une proposition par mois toute l'année

SELECT cal.nbmois, SUM(somme) / cal.nbmois as moyenne, ce_utilisateur, utilisateur_courriel FROM (SELECT count(*) as somme, CONCAT(YEAR(date),'-',MONTH(date)) as anneemois, ce_utilisateur, utilisateur_courriel, id_commentaire FROM del_commentaire WHERE ce_proposition = '' AND nom_sel_nn != '' AND nom_sel_nn IS NOT NULL AND YEAR(date) = '2013' GROUP BY anneemois, ce_utilisateur, utilisateur_courriel) as ppm, (SELECT count(distinct CONCAT(YEAR(date),'-',MONTH(date))) as nbmois FROM del_commentaire WHERE YEAR(date) = '2013' AND ce_proposition = '' AND nom_sel_nn != '' AND nom_sel_nn IS NOT NULL) as cal GROUP BY ce_utilisateur, utilisateur_courriel HAVING SUM(somme) / cal.nbmois >= 1 ORDER BY moyenne;

ayant fait le plus de vote positif

SELECT * FROM (SELECT courriel, COUNT(DISTINCT `id_vote`) AS nombre FROM del_commentaire_vote, del_utilisateur where year(`date`) = 2014 AND ce_utilisateur = id_utilisateur AND valeur = 1 GROUP BY `ce_utilisateur`) AS utilisateurs WHERE nombre > 100 ORDER BY nombre DESC

ayant eu le plus de votes positifs sur leur prop

SELECT * FROM (SELECT utilisateur_courriel, count(prop) as nb_prop FROM (SELECT `ce_proposition` as prop, COUNT(DISTINCT `id_vote`) AS nb_vote FROM del_commentaire_vote where year(`date`) = 2014 AND valeur = 1 GROUP BY `ce_proposition`) AS vote, del_commentaire WHERE nb_vote > 3 AND prop = id_commentaire AND `ce_utilisateur` != 0 GROUP BY `ce_utilisateur`) AS utlisateurs WHERE nb_prop > 10 ORDER BY nb_prop  DESC

Votes

nombre

SELECT COUNT(DISTINCT `id_vote`) FROM del_commentaire_vote where year(`date`) = 2014

Commentaires

nombre

SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM del_commentaire WHERE `ce_proposition` != '' AND (`nom_sel` IS NULL OR `nom_sel` = "") AND  year(`date`) = 2014

Actions (moyenne)

total/jour

SELECT (SELECT (SELECT count(*) FROM del_commentaire WHERE YEAR(date) = '2013')  +  (SELECT count(*) FROM del_commentaire_vote WHERE YEAR(date) = '2013') +  (SELECT count(*) FROM del_commentaire WHERE YEAR(date_validation) = '2013')) / (SELECT IF( YEAR(CURDATE()) = '2013', DAYOFYEAR(CURDATE()), 365) );

par utilisateur par jour (liste du nombre d'actions par jour par utilisateur) par mois ?

Propositions

total des actions 2014 sur toutes obs

nombre (sans initiale)

SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE year(date) = 2014 AND (`nom_sel_nn` IS NOT NULL OR `nom_sel_nn` != '') AND `proposition_initiale` = 0

retenu

SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE year(date) = 2014 AND `proposition_retenue` = 1

avec consensus

SELECT COUNT(DISTINCT ce_observation) AS nb_total FROM del_commentaire dc WHERE
		dc.proposition_retenue = 1 
		OR (dc.proposition_initiale = 1 
			AND dc.nom_sel_nn != 0
			AND dc.nom_sel_nn IS NOT NULL 
			AND dc.id_commentaire IN 
				(SELECT ce_proposition FROM del_commentaire_vote dcv 
					WHERE year(date) < 2015 AND 
						ce_proposition NOT IN (
							SELECT ce_proposition FROM del_commentaire_vote dcv 
								WHERE year(date) < 2014 
								GROUP BY ce_proposition 
								HAVING SUM(CASE  
									WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3 
									WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3 
									WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1 
									WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1 
								END) >= 4) 
					GROUP BY ce_proposition 
					HAVING SUM(CASE  
							WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3 
							WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3 
							WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1 
							WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1 
						END) >= 4
				) 
			)

sur obs 2014

nombre (sans initiale)

SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE year(date) = 2014 AND (`nom_sel_nn` IS NOT NULL OR `nom_sel_nn` != '') AND `proposition_initiale` = 0 AND ce_observation in (SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = 2014)

retenu

SELECT COUNT(DISTINCT `id_commentaire`) AS nb_total FROM `del_commentaire` WHERE year(date) = 2014 AND `proposition_retenue` = 1 AND ce_observation in (SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = 2014)

avec consensus

SELECT COUNT(DISTINCT ce_observation) AS nb_total FROM del_commentaire dc WHERE ce_observation in (SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = 2014) AND (
		dc.proposition_retenue = 1 
		OR (dc.proposition_initiale = 1 
			AND dc.nom_sel_nn != 0
			AND dc.nom_sel_nn IS NOT NULL 
			AND dc.id_commentaire IN 
				(SELECT ce_proposition FROM del_commentaire_vote dcv 
					WHERE year(date) < 2015 
					GROUP BY ce_proposition 
					HAVING SUM(CASE  
							WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3 
							WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3 
							WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1 
							WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1 
						END) >= 4
				) 
			))

Observations

nombre

SELECT COUNT(`id_observation`) AS nb_total FROM `del_observation` WHERE year(date_transmission) = 2014

nombre total sans identification (ou tag ou certitude)

SELECT COUNT(`id_observation`) AS nb_total FROM `del_observation` WHERE year(date_transmission) = 2014 AND (`mots_cles_texte` LIKE '%determiner%' OR `nom_sel_nn` = "" OR certitude IN ("aDeterminer","douteux"))

nombre sans identification

SELECT COUNT(`id_observation`) AS nb_total FROM `del_observation` WHERE year(date_transmission) = 2014 AND `nom_sel_nn` = ""

nombre avec tag à déterminer ou certitude incertaine

SELECT COUNT(`id_observation`) AS nb_total FROM `del_observation` WHERE year(date_transmission) = 2014 AND (`mots_cles_texte` LIKE '%determiner%' OR certitude IN ("aDeterminer","douteux"))

nombre avec tag à déterminer ou certitude incertaine et retenue

SELECT COUNT(*) AS nb_total FROM `del_commentaire` WHERE proposition_retenue = 1 AND `ce_observation` IN (SELECT `id_observation` FROM `del_observation` WHERE year(date_transmission) = 2014 AND (certitude IN ("aDeterminer","douteux")))

nombre avec tag à déterminer ou certitude incertaine et avec consensus

SELECT COUNT(`id_observation`) AS nb_total FROM `del_observation` WHERE year(date_transmission) = 2014 AND (`mots_cles_texte` LIKE '%determiner%' OR certitude IN ("aDeterminer","douteux")) AND id_observation IN (SELECT ce_observation FROM del_commentaire WHERE proposition_initiale = 1 
			AND nom_sel_nn != 0
			AND nom_sel_nn IS NOT NULL 
			AND id_commentaire IN (SELECT ce_proposition FROM del_commentaire_vote dcv 
					WHERE year(date) < 2015 
					GROUP BY ce_proposition 
					HAVING SUM(CASE  
							WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3 
							WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3 
							WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1 
							WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1 
						END) >= 4))

nombre avec tag à déterminer ou certitude incertaine et avec consensus mais non validé

SELECT COUNT(`id_observation`) AS nb_total FROM `del_observation` WHERE year(date_transmission) = 2014 AND (`mots_cles_texte` LIKE '%determiner%' OR certitude IN ("aDeterminer","douteux")) AND id_observation IN (SELECT ce_observation FROM del_commentaire WHERE proposition_initiale = 1  AND proposition_retenue = 0
			AND nom_sel_nn != 0
			AND nom_sel_nn IS NOT NULL
			AND id_commentaire IN (SELECT ce_proposition FROM del_commentaire_vote dcv 
					WHERE year(date) < 2015 
					GROUP BY ce_proposition 
					HAVING SUM(CASE  
							WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN 3 
							WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' != 0 THEN -3 
							WHEN valeur = 1 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN 1 
							WHEN valeur = 0 AND dcv.ce_utilisateur REGEXP '^-?[0-9]+$' = 0 THEN -1 
						END) >= 4))

Sauvages

- nombre d'observation en région PACA (toutes années confondues : date d'envoi et non de relevé) sur laquelle au moins une action (proposition détermination, vote, etc.) a été faite sur IdentiPlante.
SELECT COUNT(DISTINCT `ce_observation`) FROM `del_commentaire` WHERE `ce_observation` in (SELECT `id_observation` FROM `del_observation` WHERE SUBSTR(`ce_zone_geo`, 9 , 2) IN (13,04,05,06,83,84) AND `mots_cles_texte` like '%sauvages%');
283
- nombre d'observation en France (toutes années confondues : date d'envoi et non de relevé) sur laquelle au moins une action (proposition détermination, vote, etc.) a été faite sur IdentiPlante.
SELECT COUNT(DISTINCT `ce_observation`) FROM `del_commentaire` c WHERE `ce_observation` in (SELECT `id_observation` FROM `del_observation` WHERE `mots_cles_texte` like '%sauvages%') ;
898
- nombre d'observation en région PACA (2015 : date d'envoi et non de relevé) sur laquelle au moins une action (proposition détermination, vote, etc.) a été faite sur IdentiPlante.
SELECT COUNT(DISTINCT `ce_observation`) FROM `del_commentaire` WHERE `ce_observation` in (SELECT `id_observation` FROM `del_observation` WHERE SUBSTR(`ce_zone_geo`, 9 , 2) IN (13,04,05,06,83,84) AND `mots_cles_texte` like '%sauvages%' and year(date_creation) < 2015);
208
- nombre d'observation en France (2015 : date d'envoi et non de relevé) sur laquelle au moins une action (proposition détermination, vote, etc.) a été faite sur IdentiPlante.
SELECT COUNT(DISTINCT `ce_observation`) FROM `del_commentaire` WHERE `ce_observation` in (SELECT `id_observation` FROM `del_observation` WHERE `mots_cles_texte` like '%sauvages%' and year(date_creation) = 2015) ;
547

- le nombre d'observateurs en région PACA en 2015 
SELECT count(distinct `ce_utilisateur`) FROM `cel_obs` WHERE `mots_cles_texte` like '%sauvages%' and year(date_creation) = 2015 and SUBSTR(`ce_zone_geo`, 9 , 2) IN (13,04,05,06,83,84)
48
- le nombre d'observations en région PACA envoyées en 2015
SELECT count(*) FROM `cel_obs` WHERE `mots_cles_texte` like '%sauvages%' and year(date_creation) = 2015 and SUBSTR(`ce_zone_geo`, 9 , 2) IN (13,04,05,06,83,84)
1087
- le nombre d'observateurs en région PACA jusqu'à aujourd'hui
SELECT count(distinct `ce_utilisateur`) FROM `cel_obs` WHERE `mots_cles_texte` like '%sauvages%' and SUBSTR(`ce_zone_geo`, 9 , 2) IN (13,04,05,06,83,84)
102
- le nombre d'observateurs en France en 2015 
SELECT count(distinct `ce_utilisateur`) FROM `cel_obs` WHERE `mots_cles_texte` like '%sauvages%' and year(date_creation) = 2015 
226
- le nombre d'observations envoyées en 2015
SELECT count(*) FROM `cel_obs` WHERE WHERE `mots_cles_texte` like '%sauvages%' and year(date_creation) = 2015
9391
- le nombre d'observateurs en France jusqu'à aujourd'hui
SELECT count(distinct `ce_utilisateur`) FROM `cel_obs` WHERE `mots_cles_texte` like '%sauvages%'
695

PictoFlora

Votes

nombre total

SELECT COUNT(*) AS nb_total FROM `del_image_vote` WHERE year(date)=2014

Utilisateurs

ayant fait une action

SELECT COUNT(*) AS nb_total FROM (SELECT `ce_utilisateur` FROM `del_image_tag` UNION SELECT `ce_utilisateur` FROM `del_image_vote`) AS action

ayant fait un tag

SELECT COUNT(DISTINCT `ce_utilisateur`) FROM `del_image_tag` WHERE year(date) = 2014

ayant fait un vote

SELECT COUNT(DISTINCT `ce_utilisateur`) FROM `del_image_vote` WHERE year(date) = 2014

Images

action de 2014 sur toutes les images

ayant au moins un tag

SELECT COUNT(DISTINCT ce_image) AS nb_total FROM `del_image_tag` WHERE year(date)=2014

ayant au moins un vote

SELECT COUNT(DISTINCT ce_image) AS nb_total FROM `del_image_vote` WHERE year(date)=2014

sur image 2014

ayant au moins un tag

SELECT COUNT(DISTINCT ce_image) AS nb_total FROM `del_image_tag` WHERE year(date)=2014 AND ce_image IN (SELECT id_image FROM `del_image` WHERE year(date_transmission) = 2014)

ayant au moins un vote

SELECT COUNT(DISTINCT ce_image) AS nb_total FROM `del_image_vote` WHERE year(date)=2014 AND ce_image IN (SELECT id_image FROM `del_image` WHERE year(date_transmission) = 2014)

nombre total

SELECT COUNT(*) FROM `del_image` WHERE year(date_transmission) = 2014

Liste des utilisateurs ayant ajouté le plus de mots clés

SELECT count(*) as nombre, IF(ce_utilisateur REGEXP '^-?[0-9]+$' OR ce_utilisateur REGEXP '^.+@.+$', ce_utilisateur, null) as ce_util FROM del_image_tag GROUP BY ce_util ORDER BY nombre DESC LIMIT 20;

Le IF permet de regrouper les anonymes (tous ceux qui n'ont pas un numéro d'inscrit) tout en séparant ceux qui ont spécifié un email (uniquement plantnet-datastore pour l'instant).

IDENTIPLANTE (http://www.tela-botanica.org/appli:identiplante)

Aout/2013 - 536 - Octobre/2013 - 616 - Janvier/2014 - 725 - utilisateurs ont déposés un commentaire ou une proposition :
- SELECT COUNT( DISTINCT ce_utilisateur ) FROM del_commentaire
Aout 896 _ octobre 1074 _ JANVIER/2014 - 1309 - utilisateurs ont votés pour un commentaire ou une proposition :
- SELECT COUNT(DISTINCT ce_utilisateur) FROM del_commentaire_vote
Aout 400 + 308 = 708 - Octobre ? utilisateurs distincts ont réalisé une action sur linterface :
- SELECT COUNT( DISTINCT ce_utilisateur ) FROM del_commentaire WHERE ce_utilisateur NOT IN (SELECT DISTINCT ce_utilisateur FROM del_commentaire_vote )
- SELECT COUNT( DISTINCT ce_utilisateur ) FROM del_commentaire_vote WHERE ce_utilisateur NOT IN (SELECT DISTINCT ce_utilisateur FROM del_commentaire )
Aout 8 737 - octobre 10 474 - janvier-2014 14 256 votes sur les identifications ont été réalisés :
- SELECT COUNT(id_vote) FROM del_commentaire_vote
Aout 6 928 - Octobre 8039 proposition ou commentaire :
- SELECT COUNT(id_commentaire) FROM del_commentaire
Aout 6 202 - octobre 7134 - Janvier 9470 propositions de déterminations :
- SELECT COUNT(id_commentaire) FROM del_commentaire WHERE nom_sel is not NULL AND nom_sel != ''
Aout 726 - octobre 905 janvier 2026 commentaires ont été enregistrés
- SELECT COUNT(id_commentaire) FROM del_commentaire WHERE nom_sel is NULL OR nom_sel = ''

PICTOFLORA (http://www.tela-botanica.org/appli:pictoflora)

Aout 38 + 672 = 710 - octobre 43 + 790 = 833 / février 2014 49 + 1338 utilisateurs ont réalisé au moins une action sur linterface
- SELECT COUNT( DISTINCT ce_utilisateur ) FROM del_image_tag WHERE ce_utilisateur NOT IN (SELECT DISTINCT ce_utilisateur FROM del_image_vote )
- SELECT COUNT( DISTINCT ce_utilisateur ) FROM del_image_vote WHERE ce_utilisateur NOT IN (SELECT DISTINCT ce_utilisateur FROM del_image_tag )
Aout 109 598 - octobre 136 953 - Février 2014 (165 640) votes sur les images
- SELECT COUNT( id_vote ) FROM del_image_vote
Aout 45 870 - Octobre 62 318 - Février 2014 - 78 536 tags ajoutés
- SELECT COUNT( id_tag ) FROM del_image_tag

Demande de Véro

Nombre d'images taguées defiPhoto en 2013

SELECT distinct id_element_lie FROM `cel_mots_cles_images_liaison` where 
`id_element_lie` in 
	(SELECT id_element_lie FROM `cel_mots_cles_images_liaison` where 
		`id_mot_cle` in (SELECT id_mot_cle FROM `cel_arbre_mots_cles_images` where mot_cle = 'defiPhoto')) and 
`id_element_lie` in  
	(SELECT id_element_lie FROM `cel_mots_cles_images_liaison` where `id_mot_cle` in 
		(SELECT id_mot_cle FROM `cel_arbre_mots_cles_images` where mot_cle in ("Givre", "Tropiques", "Bulbeuses", "Printemps", "Etamines", "Messicoles", "Unis", "Aromatique", "Jaune", "Graine", "CouleursAutomne", "Preles")))

Demandes de Jennifer

Nombres d'observations envoyées sans nom / mois

Objectif : 100

On considère comme "sans nom" un observation répondant à au moins un des critères suivants:

certitude "aDeterminer"
certitude "douteux"
nom_sel_nn nul ou vide
a une proposition initiale dont le nom_sel_nn est nul ou vide (permet de remonter le temps) => au cas où les utilisateurs aient modifié leurs observations directement dans leur cel

Compte par mois pour une année:

SELECT count(*), CONCAT(YEAR(date_transmission),'-',MONTH(date_transmission)) as anneemois
FROM del_observation
WHERE YEAR(date_transmission) = '2013'
AND (mots_cles_texte LIKE '%aDeterminer%'
OR certitude = 'aDeterminer'
OR certitude = 'douteux'
OR nom_sel_nn IS NULL
OR nom_sel_nn = 0
OR id_observation IN
	(SELECT DISTINCT ce_observation FROM del_commentaire WHERE proposition_initiale = 1 AND (nom_sel_nn IS NULL OR nom_sel_nn = '')))
GROUP BY anneemois
ORDER BY anneemois DESC;

Moyenne sur l'année:

SELECT AVG(parMois.compte) FROM
(SELECT count(*) as compte, CONCAT(YEAR(date_transmission),'-',MONTH(date_transmission)) as anneemois
FROM del_observation
WHERE YEAR(date_transmission) = '2013'
AND (mots_cles_texte LIKE '%aDeterminer%'
OR certitude = 'aDeterminer'
OR certitude = 'douteux'
OR nom_sel_nn IS NULL
OR nom_sel_nn = 0
OR id_observation IN
	(SELECT DISTINCT ce_observation FROM del_commentaire WHERE proposition_initiale = 1 AND (nom_sel_nn IS NULL OR nom_sel_nn = '')))
GROUP BY anneemois
ORDER BY anneemois DESC) as parMois;

Pourcentage d'observations identifiées à la fin de l'année

Objectif : 90,00%

On considère comme "identifiée" une observation répondant à au moins un des critères suivants :

a une proposition notée comme "retenue" (proposition_retenue = 1)
n'a pas de proposition retenue mais une proposition "la plus probable"

On considère comme date à laquelle l'observation est "identifiée" :

la date du dernier vote sur la proposition la plus probable
la date de modification de l'observation dans le CeL, si le nom_sel_nn de l'obs. est égal à celui de la proposition la plus probable

Ce qui serait plus mieux :

fonctionner avec un nombre de points
considérer un nombre de points à partir duquel la proposition est "retenue" (et si 2 propositions se battent et ont un grand nombre de points, on fait quoi ?)
noter la date à laquelle ce nombre est atteint

Version 1/

Ne compte que les observations ayant une proposition marquée comme "retenue"; correspond en principe à celles dont l'auteur a appuyé sur "valider"

SELECT (
	SELECT count(*) FROM (
		SELECT count(DISTINCT id_observation), MAX(dcv.date) as maxdate
		FROM del_commentaire dc
		LEFT JOIN del_commentaire_vote dcv ON dcv.ce_proposition = dc.id_commentaire
		LEFT JOIN del_observation do ON do.id_observation = dc.ce_observation
		WHERE proposition_retenue = 1
		AND YEAR(do.date_transmission) = '2013'
		GROUP BY dc.id_commentaire
		HAVING MAX(YEAR(dcv.date)) = '2013')
	as temp)
	/
	(SELECT count(*)
	FROM del_observation
	WHERE YEAR(date_transmission) = '2013'
	AND (mots_cles_texte LIKE '%aDeterminer%'
	OR certitude = 'aDeterminer'
	OR certitude = 'douteux'
	OR nom_sel_nn IS NULL
	OR nom_sel_nn = 0 OR
		id_observation IN (
		SELECT DISTINCT ce_observation
		FROM del_commentaire
		WHERE proposition_initiale = 1
		AND (nom_sel_nn IS NULL OR nom_sel_nn = '')
		)
	)
);

à remplacer par ?
nombre de proposition retenue (validée en 2014) des observations transmis 2014 / nombre d'obs transmis en 2014

select (SELECT count(distinct ce_observation) FROM `del_commentaire` WHERE `proposition_retenue` = 1 and year(`date_validation`) = 2014 and ce_observation in (select id_observation from del_observation where year(date_transmission)=2014)) / (select count(*) from del_observation where year(date_transmission)=2014) * 100

Version 2/

Fait la somme des observations ayant une proposition retenue, et de celles étant en état de consensus (au moins un vote positif sur une proposition) et n'ayant pas de proposition retenue
ATTENTION : dépasse parfois les 100% :-P
Problèmes probables :

le calcul du consensus est faux - 1 vote positif ne suffit pas, il faut qu'un maximum se dégage
on prend en compte des obs validées datant de l'année précédente

// Obs ayant atteint un consensus cette année
		$req1 = "SELECT count(*) as nombre FROM(".
			" SELECT id_observation, id_commentaire, id_vote, nbvotes FROM (".
				" SELECT do.id_observation, dc.id_commentaire, dcv.id_vote, count(dcv.id_vote) as nbvotes".
				" FROM del_commentaire dc".
				" LEFT JOIN del_observation do ON do.id_observation = dc.ce_observation".
				" LEFT JOIN del_commentaire_vote dcv ON dc.id_commentaire = dcv.ce_proposition".
				" AND dcv.valeur = 1".
				" AND dc.proposition_retenue = 0".
				" GROUP BY dc.id_commentaire".
				" HAVING MAX(YEAR(dcv.date)) = '" . $annee . "'".
			" ) as temp GROUP BY id_observation".
		") as temp2;";
		$obsEnConsensus = $this->bdd->recupererTous($req1);
		$oc = intval($obsEnConsensus[0]['nombre']);

		// Obs ayant une "proposition retenue" cette année
		$req2 = "SELECT count(*) as nombre FROM (".
			" SELECT count(DISTINCT id_observation), MAX(dcv.date) as maxdate".
			" FROM del_commentaire dc".
			" LEFT JOIN del_commentaire_vote dcv ON dcv.ce_proposition = dc.id_commentaire".
			" LEFT JOIN del_observation do ON do.id_observation = dc.ce_observation".
			" WHERE proposition_retenue = 1".
			" AND YEAR(do.date_transmission) = '" . $annee . "'".
			" GROUP BY dc.id_commentaire".
			" HAVING MAX(YEAR(dcv.date)) = '" . $annee . "')".
		" as temp;";
		$nbObsValidees = $this->bdd->recupererTous($req2);
		$ov = intval($nbObsValidees[0]['nombre']);

		// Nombre d'obs sans nom soumises cette année
		$req3 = "SELECT count(*) as nombre".
			" FROM del_observation".
			" WHERE YEAR(date_transmission) = '" . $annee . "'".
			" AND (mots_cles_texte LIKE '%aDeterminer%'".
			" OR certitude = 'aDeterminer'".
			" OR certitude = 'douteux'".
			" OR nom_sel_nn IS NULL".
			" OR nom_sel_nn = 0 OR".
				" id_observation IN (".
				" SELECT DISTINCT ce_observation".
				" FROM del_commentaire".
				" WHERE proposition_initiale = 1".
				" AND (nom_sel_nn IS NULL OR nom_sel_nn = '')".
			" ))";
		$nbObsSansNom = $this->bdd->recupererTous($req3);
		$osn = intval($nbObsSansNom[0]['nombre']);

		return array(
			'observationsEnConsensus' => $oc,
			'observationsValidees' => $ov,
			'observationsSansNom' => $osn,
			'pourcentage' => ($osn == 0 ? 0 : (($oc + $ov) / $osn) * 100)
		);

à remplacer par ?
nombre de proposition retenue (créée ou validée) de 2014 + nombre de proposition non retenue liée à un référentiel ayant plus de 2 votes / nombre d'obs transmis en 2014

NOTE CRACRA
date_obs -> 1 obs de 2014
date_prop -> prop
date_vote -> nbVote > 2 et 2014

select (
     SELECT count(distinct ce_observation) 
         FROM `del_commentaire` 
         WHERE (
           ( year(date)=2014 
            and nom_sel_nn is not null 
            and nom_sel_nn != 0 
            and proposition_retenue = 0 
            and `id_commentaire` in (
                 select ce_proposition from (
                    SELECT `ce_proposition`, SUM(`valeur`) AS vote_total 
                         FROM `del_commentaire_vote` GROUP BY `ce_proposition`) as vote 
                         where vote_total > 2)
          )
          or  (`proposition_retenue` = 1 and year(`date_validation`) = 2014)) and ce_observation in (select id_observation from del_observation where year(date_transmission)=2014)
       )
     / (select count(*) from del_observation where year(date_transmission)=2014) * 100

Confirmation - Nombres d'observations identifées / mois

Objectif : 100
Nombre d'observations ayant une proposition retenue, sur laquelle le dernier vote date du mois en cours

SELECT AVG(valideesparmois) FROM (
	SELECT count(*) as valideesparmois, CONCAT(YEAR(maxdate), '-', MONTH(maxdate)) as anneemois
	FROM (
		SELECT count(*), MAX(dcv.date) as maxdate
		FROM del_commentaire dc
		LEFT JOIN del_commentaire_vote dcv ON dcv.ce_proposition = dc.id_commentaire
		WHERE proposition_retenue = 1
		GROUP BY dc.id_commentaire
		HAVING MAX(YEAR(dcv.date)) = '2013') as temp
	GROUP BY anneemois) as temp2
;

réflexion
propositions ayant une valeur inférieure à 2 votes avant le mois

select * from (SELECT `ce_proposition`, sum(valeur) as vote FROM `del_commentaire_vote`
where concat(year(date),month(date)) < "201401" group by `ce_proposition`) as vote_prec where vote < 2

propositions du mois ayant une valeur supérieure à 2 dans le mois

select * from (SELECT v.`ce_proposition`, sum(valeur) as vote FROM `del_commentaire_vote` v, del_commentaire c
where year(v.date) = 2014 and month(v.date) = 01  and v.`ce_proposition` = c.`ce_proposition` and year(c.date) = 2014 and month(c.date)=01 group by `ce_proposition`) as vote_act where vote > 1

Utilisateurs - Nombre d'actions/jours

(pour le nombre de visites, il faudrait utiliser Google Analytics)
Objectif : 100
Une "action" est soit un commentaire / proposition, soit un vote sur une proposition, soit une validation de proposition.
On compte 365 jours, ou tous les jours entre le premier janvier jour d'hui si l'année est en cours.

SELECT
(SELECT
	(SELECT count(*) FROM del_commentaire WHERE YEAR(date) = '2013')
	+
	(SELECT count(*) FROM del_commentaire_vote WHERE YEAR(date) = '2013')
       +
	(SELECT count(*) FROM del_commentaire WHERE YEAR(date_validation) = '2013')
	)
/ (SELECT IF( YEAR(CURDATE()) = '2013', DAYOFYEAR(CURDATE()), 365)
);

Nombres de personnes envoyant une proposition/mois

Objectif : 40

Obtenir la liste de ces personnes (après y a plus qu'à la compter). Attention :

ne garantit pas la régularité : la personne a pu participer 12 fois en avril et jamais les autres mois

=> liste du nombre d'actions moyennes par mois par utilisateur

SELECT cal.nbmois, SUM(somme) / cal.nbmois as moyenne, ce_utilisateur, utilisateur_courriel FROM
	(SELECT count(*) as somme, CONCAT(YEAR(date),'-',MONTH(date)) as anneemois, ce_utilisateur, utilisateur_courriel, id_commentaire
	FROM del_commentaire
	WHERE ce_proposition = ''
	AND nom_sel_nn != ''
	AND nom_sel_nn IS NOT NULL
	AND YEAR(date) = '2013'
	GROUP BY anneemois, ce_utilisateur, utilisateur_courriel) as ppm,
	(SELECT count(distinct CONCAT(YEAR(date),'-',MONTH(date))) as nbmois
	FROM del_commentaire
	WHERE YEAR(date) = '2013'
	AND ce_proposition = ''
	AND nom_sel_nn != ''
	AND nom_sel_nn IS NOT NULL) as cal
GROUP BY ce_utilisateur, utilisateur_courriel
HAVING SUM(somme) / cal.nbmois >= 1
ORDER BY moyenne;

à remplacer par moyenne du nombre d'utilisateurs faisant une proposition par mois

SELECT AVG(somme) FROM (SELECT concat(year(date),month(date)), count(distinct `utilisateur_courriel`) as somme FROM `del_commentaire` WHERE `ce_proposition` = '' and `nom_sel_nn` != 0 and `nom_sel_nn` is not null and year(date) = 2014 group by concat(year(date),month(date)) ) as utilisateurs

Observations ayant atteint un consensus

Idée : les obs ayant au moins une proposition votée au moins une fois positivement, et qui si elles ont plusieurs propositions, en ont une qui a strictement plus de votes que les autres.
Cette technique, totalement foireuse, fait ressortir environ 50% des obs chaque année, ce qui semble pas trop loin de la vérité.

1) Proposition ayant la plus grande somme de valeurs de votes - on cherche à faire le max par groupe maic c'est difficile enMySQL (voir https://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html). Ici la technique utilisée est le ORDER BY nombre DESC sur cette requête, qui donnera une table temporaire. En regroupant derrière c'est censé conserver la première ligne pour les valeurs non groupées. Mais ça ne semble pas parfaitement déterministe (tu m'étonnes!) - en exécutant plusieurs fois la requête finale on trouve des résultats un chouia différents (le somme > 0 de la requête suivante doit varier car ladite somme doit varier à cause du non déterminisme en question).
On ne considère que les votes de l'année demandée.

SELECT ce_proposition as id_com , sum(valeur) as somme FROM del_commentaire_vote dcv WHERE YEAR(dcv.date) = 2014 GROUP BY ce_proposition ORDER BY somme DESC;

2) On regroupe pour garder le max supposé pour chaque observation.

SELECT dc.ce_observation, dc.id_commentaire, dc.nom_sel_nn, temp.id_com, temp.somme FROM (SELECT ce_proposition as id_com , sum(valeur) as somme FROM del_commentaire_vote dcv WHERE YEAR(dcv.date) = 2014 GROUP BY ce_proposition ORDER BY somme DESC) as temp LEFT JOIN del_commentaire dc ON dc.id_commentaire = temp.id_com GROUP BY dc.ce_observation;

3) On filtre pour enlever les données caca qui sortent d'on sait pas où (ah, j'ai peut-être fait le JOIN dans le mauvais sens tiens...), ne considérer que les propositions ayant un nom_sel_nn non nul (il y a parfois consensus sur des propositions non liées au référentiel), et ne considérer que les obs de l'année demandée.

SELECT count(*) FROM (SELECT dc.ce_observation, dc.id_commentaire, dc.nom_sel_nn, temp.id_com, temp.somme FROM (SELECT ce_proposition as id_com , sum(valeur) as somme FROM del_commentaire_vote dcv WHERE YEAR(dcv.date) = 2014 GROUP BY ce_proposition ORDER BY somme DESC) as temp LEFT JOIN del_commentaire dc ON dc.id_commentaire = temp.id_com GROUP BY dc.ce_observation) as autretemp LEFT JOIN del_observation do ON do.id_observation = ce_observation WHERE ce_observation IS NOT NULL and somme > 0 AND autretemp.nom_sel_nn IS NOT NULL AND YEAR(do.date_transmission) = 2014;

Utilisateurs inscrits ayant fait le plus de votes "pour"

Ajouté en signet dans PHPMyAdmin sur Agathis / prod.

SELECT dcv.ce_utilisateur, U_NAME, U_SURNAME, U_MAIL, count(*) as nombre FROM del_commentaire_vote dcv
LEFT JOIN tela_prod_v4.annuaire_tela ON ce_utilisateur = U_ID -- pour avoir les noms, prenoms, emails
LEFT JOIN del_commentaire dc ON dcv.ce_proposition = dc.id_commentaire LEFT JOIN del_observation do ON dc.ce_observation = do.id_observation -- pour avoir l'obs
WHERE dcv.valeur = 1 -- mettre 0 pour les votes "contre"
AND dcv.ce_utilisateur REGEXP '^[0-9]+$' -- exclut les utilisateurs anonymes
AND do.mots_cles_texte LIKE '%sauvages%' -- limite aux observations ayant le mot-clé "sauvages", tu peux changer le mot-clé ou supprimer cette clause
AND YEAR(dcv.date) = '2013' -- remplace par l'année de ton choix ou supprime cette clause
GROUP BY dcv.ce_utilisateur ORDER BY nombre DESC
LIMIT 30 -- les 30 premiers

Déjà membre

Nouveau membre

Requête SQL pour les stats de DEL

Création de la vue image avec ancien cel

Liste des stats demandées

Aide pour calculer à partir des nombres

Attention ne calculer les pourcentages que si le nombre de proposition est calculé sur obs 2014

Liste des requêtes

IdentiPlante

Utilisateurs

nombre

liste

Votes

nombre

Commentaires

nombre

Actions (moyenne)

total/jour

par utilisateur par jour (liste du nombre d'actions par jour par utilisateur) par mois ?

Propositions

total des actions 2014 sur toutes obs

sur obs 2014

Observations

nombre

nombre total sans identification (ou tag ou certitude)

nombre sans identification

nombre avec tag à déterminer ou certitude incertaine

nombre avec tag à déterminer ou certitude incertaine et retenue

nombre avec tag à déterminer ou certitude incertaine et avec consensus

nombre avec tag à déterminer ou certitude incertaine et avec consensus mais non validé

Sauvages

PictoFlora

Tags

nombre total

Votes

nombre total

Utilisateurs

ayant fait une action

ayant fait un tag

ayant fait un vote

Images

action de 2014 sur toutes les images

sur image 2014

Liste des utilisateurs ayant ajouté le plus de mots clés

Demande de Véro

Nombre d'images taguées defiPhoto en 2013

Demandes de Jennifer

Nombres d'observations envoyées sans nom / mois

Pourcentage d'observations identifiées à la fin de l'année

Version 1/

Version 2/

Confirmation - Nombres d'observations identifées / mois

Utilisateurs - Nombre d'actions/jours

Nombres de personnes envoyant une proposition/mois

Observations ayant atteint un consensus

Utilisateurs inscrits ayant fait le plus de votes "pour"

Demandes de Florent

Nombre total d'utilisateurs

Nombre d'utilisateurs réguliers

Nombre d'observations avec une demande d'identification par rapport à nbre total d'observations

Nombre d'utilisateurs prenant part aux votes

Nombre d'observations avec une demande d'identification mais non validées

Utilisateurs ayant fait le plus de votes positifs sur une période de temps

Nombre de participants actifs pour le mois, la semaine, l'année

Nombre de proposition faites (au total et sur les derniers 15 jours)

Nombre de propositions validées(au total et sur le dernier mois)

Nombres de votes (au total et sur les derniers 15 jours)