| Line 45... |
Line 45... |
| 45 |
|
45 |
|
| 46 |
|
46 |
|
| 47 |
DROP TABLE IF EXISTS `BASEDEL`.`del_image_stat`;
|
47 |
DROP TABLE IF EXISTS `BASEDEL`.`del_image_stat`;
|
| 48 |
CREATE TABLE IF NOT EXISTS `BASEDEL`.`del_image_stat` (
|
48 |
CREATE TABLE IF NOT EXISTS `BASEDEL`.`del_image_stat` (
|
| 49 |
`ce_image` BIGINT(20) NOT NULL COMMENT 'id_image (tb_cel.cel_images)',
|
49 |
`ce_image` BIGINT(20) NOT NULL COMMENT 'id_image (tb_cel.cel_images)',
|
| - |
|
50 |
`ce_protocole` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'un id de protocole',
|
| 50 |
`ce_protocole` INT(11) NULL DEFAULT NULL COMMENT 'un id de protocole',
|
51 |
`moyenne` FLOAT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'moyenne des votes pour une image et un protocole donné',
|
| 51 |
`moyenne` SMALLINT NOT NULL DEFAULT 0 COMMENT 'moyenne des votes pour une image et un protocole donné',
|
52 |
`nb_votes` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'nombre de votes pour une image et un protocole donné',
|
| 52 |
`nb_tags` SMALLINT NOT NULL DEFAULT 0 COMMENT 'nombre de tags pictoflora associés à une image',
|
53 |
`nb_tags` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'nombre de tags pictoflora pour une image donnée',
|
| 53 |
PRIMARY KEY (`ce_image`, `ce_protocole`),
|
54 |
PRIMARY KEY (`ce_image`, `ce_protocole`),
|
| - |
|
55 |
KEY `ce_image` (`ce_image`),
|
| 54 |
KEY `ce_image` (`ce_image`),
|
56 |
KEY `ce_protocole` (`ce_protocole`, `moyenne` DESC),
|
| 55 |
KEY `ce_protocole` (`ce_protocole`, `moyenne` DESC),
|
57 |
KEY `nb_votes` (`nb_votes` DESC),
|
| Line 56... |
Line 58... |
| 56 |
KEY `nb_tags` (`nb_tags` DESC)
|
58 |
KEY `nb_tags` (`nb_tags` DESC)
|
| 57 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'table de stockage des statistiques utilisées pour les tri de PictoFlora';
|
59 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'table de stockage des statistiques utilisées pour les tri de PictoFlora';
|
| 58 |
|
60 |
|
| 59 |
INSERT INTO `BASEDEL`.`del_image_stat` (\
|
61 |
INSERT INTO `BASEDEL`.`del_image_stat` (\
|
| 60 |
SELECT id_image, divo.ce_protocole, divo.moyenne, dit.ctags \
|
62 |
SELECT id_image, divo.ce_protocole, divo.moyenne, divo.nb_votes, dit.ctags \
|
| 61 |
FROM `BASECEL`.`cel_images` ci \
|
63 |
FROM `BASECEL`.`cel_images` ci \
|
| 62 |
LEFT JOIN \
|
64 |
LEFT JOIN \
|
| 63 |
( SELECT ce_image, ce_protocole, AVG(valeur) AS moyenne FROM del_image_vote \
|
65 |
( SELECT ce_image, ce_protocole, AVG(valeur) AS moyenne, COUNT(valeur) AS nb_votes
|
| 64 |
GROUP BY ce_image, ce_protocole ) AS divo
|
66 |
FROM del_image_vote GROUP BY ce_image, ce_protocole ) AS divo
|
| 65 |
ON ci.id_image = divo.ce_image \
|
67 |
ON ci.id_image = divo.ce_image \
|
| 66 |
LEFT JOIN \
|
68 |
LEFT JOIN \
|