Dénombrement temporel groupé complexe

[fanfouer]

Bonjour à tous,

Je me permets de venir parler d'un petit problème existentiel. Je cherche à dénombrer des éléments suivant une date bien précise (paramètre) et suivant la valeur d'une propriété particulière (fixée).
Ça permet de répondre à la question "Combien y-en avait-il possédant tel ou tel statut à telle ou telle date?".

La requête ne concerne qu'une seule table qui possède les champs suivant :
-id : Identifiant de l'enregistrement (auto_increment, PRIMARY INDEX)
-create_time (VARCHAR(30)) : Date de création de l'enregistrement sous la forme d'un timestamp UNIX.
-remove_time (VARCHAR(30)) : Date de suppression du jeu de données sous la forme d'un timestamp UNIX.
-status_id (INT) : Le statut de l'enregistrement. N valeurs quelconques (la liste des valeurs possibles peut varier d'un enregistrement à un autre).
-TOSTATUS_N_TIME (VARCHAR(30)) : Date de passage à la valeur "N" du champ status_id. Si il y a les 3 valeurs possibles {"1", "2", "3"} pour tel ou tel enregistrement, on utilisera les champs TOSTATUS_1_TIME, TOSTATUS_2_TIME et TOSTATUS_3_TIME.

La liste totale des statuts est connue à l'avance mais tous les enregistrements ne sont pas censés utiliser tous les statuts.

On est donc capable de déterminer les intervalles pendant lesquels le champ status_id a pris telle ou telle valeur.
La difficulté reviens à reconstituer cette valeur puisque status_id contient la valeur correspondant à l'instant où la requête est faite et non à l'instant qui nous intéresse.

J'ai tenté de faire l'ébauche d'une requête que voici :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT COUNT(*), (
  SELECT ..... ?????? .....) AS old_status_id
FROM
  ma_table
WHERE 
  create_time <= %TIME_RECHERCHE% AND (
    remove_time IS NULL OR 
    remove_time > %TIME_RECHERCHE%
  )
GROUP BY old_status_id

Mais je ne suis pas sur que ce soit bien correct. "SELECT ..... ?????? ....." étant une requête imbriquée (dont j'aimerai bien me passer si possible) qui permettrait de reconstituer la valeur du champ status_id prise à %TIME_RECHERCHE%, temps fixé pour la recherche en tant que paramètre.

Je précise que je souhaite éviter les solutions du genre "N valeurs -> N requêtes" et que je travaille sous MySQL 5

Est-ce que quelqu'un aurait une idée sur la meilleure manière de procéder. Peut-être que mon modèle de données n'est pas adapté pour cette utilisation mais sa remise en question n'est réellement pas souhaitable

Merci par avance, bonne journée.

[Waldar]

Il manque trop d'information pour qu'on puisse vous répondre, à lire :
http://www.developpez.net/forums/a69...gage-sql-lire/

[skuatamad]

Il y a au moins une chose à changer dans le modèle, c'est le type de donnée de create_time et remove_time.
Tu ne peux pas stocker un unix timestamp dans un VARCHAR il faut un INT, sinon 01/01/1980 (315554400) sera supérieur au 01/01/2011 (1293861600).

Mais pourquoi ne pas utiliser le type timestamp (ou date suivant le SGBD) en base, les opérations de manipulations seront nettement plus simples.

[fanfouer]

Merci pour vos réponses.

En effet j'avoue avoir oublié des choses.

J'ai rajouté le nom de mon SGBDR dans le post initial : MySQL 5 sous un serveur mutualisé chez OVH.

Voici une requête qui permettrai de créer une table comme celle que j'évoque :

Code :

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE `infosresnet`.`test` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `create_time` INT UNSIGNED NULL DEFAULT NULL ,
  `remove_time` INT UNSIGNED NULL DEFAULT NULL ,
  `status_id` INT NOT NULL ,
  `TOSTATUS_1_TIME` INT UNSIGNED NULL DEFAULT NULL ,
  `TOSTATUS_4_TIME` INT UNSIGNED NULL DEFAULT NULL ,
  `TOSTATUS_5_TIME` INT UNSIGNED NULL DEFAULT NULL
) ENGINE = InnoDB ;

J'ai dores et déjà pris en compte les modifications du typage des champ contenant des timestamps : j'ai opté pour Int.

On peut utiliser le dataset suivant :

Code :

1
2
3
4
5
6
7
8
9
10
INSERT INTO `infosresnet`.`test` (`id`,`create_time`,`remove_time`,`status_id`,`TOSTATUS_1_TIME`,`TOSTATUS_4_TIME`,`TOSTATUS_5_TIME`)
VALUES (
NULL , '1181559493', NULL , '1', '1181559493', NULL , NULL
), (
NULL , '1214391493', NULL , '5', '1214477893', '1221908293', '1229770693'
), (
NULL , '1232017093', '1263553093', '4', '1211281093', '1232449093', NULL
), (
NULL , '1214996293', NULL , '1', '1212404293', NULL , NULL
);

Enfin, le résultat attendu à la requête que je cherche serait, pour le 09/10/2008 (1223549893) :

Code :

1
2
3
4
5
6
 
status_id     COUNT(*)
----------  ------------
1               3
4               1
5               0

En vous remerciant.

[aieeeuuuuu]

Bonjour

Voici une solution :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
SELECT 1, COUNT(*)
FROM test
WHERE TOSTATUS_1_TIME <= 1223549893
AND (TOSTATUS_4_TIME IS NULL OR TOSTATUS_4_TIME > 1223549893)
UNION ALL
SELECT 4, COUNT(*)
FROM test
WHERE TOSTATUS_4_TIME <= 1223549893
AND (TOSTATUS_5_TIME IS NULL OR TOSTATUS_5_TIME > 1223549893)
UNION ALL
SELECT 5, COUNT(*)
FROM test
WHERE TOSTATUS_5_TIME <= 1223549893

Mais votre modélisation est... disons... disgracieuse

A première vue, vous devriez avoir une table paramétre, une table statut, et une table d'historique parametre_statut...

Comment ferez vous lorsqu'un nouveau statut apparaitera ? vous comptez rajouter une colonne à votre table... et réécrire toutes vos requetes ?

[fanfouer]

Bonjour aieeeuuuuu,

Envoyé par aieeeuuuuu

Code SQL :

En effet c'est une des solutions possibles.
Mais je ne connais pas à l'avance la liste des statut à cibler. Cela dépend de ce qu'il y a effectivement dans la table.
Par conséquent je comptais plus sur le GROUP BY pour me tirer d'affaire quant à cette liste.

Mais votre modélisation est... disons... disgracieuse

A première vue, vous devriez avoir une table paramétre, une table statut, et une table d'historique parametre_statut...

Comment ferez vous lorsqu'un nouveau statut apparaitera ? vous comptez rajouter une colonne à votre table... et réécrire toutes vos requetes ?

En effet c'est bien le cas.
Je ne voulais pas alourdir mon explication avec des clauses relationnelles mais peut-être que cela aidera finalement à trouver une solution plus abordable.
Les "champs" TOSTATUS_N_TIME sont dans une table annexe (en réalité des lignes de paires clé/valeur) et reliée à la table principale par une table annexe à deux colonnes faisant référence à la fois à la table principale et à la table contenant TOSTATUS_N_TIME.
On appelle ca une CIF je crois.

[aieeeuuuuu]

Ca change tout !
Et si j'ai a peu près compris votre modélisation (qui me semble finalement beaucoup moins disgracieuse ) la requête sera surement beaucoup plus digeste, plus performante, et pourra gérer un nombre indéfini de statuts...

(Si j'ai bien compris, le N de TOSTATUS_N_TIME est la clé dans vos "paires clé/valeur" ??)

Donnez-nous votre vrai modèle, on pourra peut-être vous aider !

[fanfouer]

Bonjour,

Très bien, j'ai surement fait une erreur en voulant simplifier le topic, ça a surement plus compliqué les choses que ça ne les a simplifiée en réalité

Le modèle actuel est celui-ci. J'espère que c'est compréhensible et j'ai repris le jeu de données que j'ai donné au dessus.


Voici les requêtes pour créer les tables, le résultat attendu est toujours le même que précédemment.

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
# SYSTEM
CREATE TABLE `SYSTEM` (
  `record_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `create_time` int(11) UNSIGNED NOT NULL,
  `remove_time` int(11) UNSIGNED DEFAULT NULL,
  `status_id` int(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY  (`record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
#TAGSc
CREATE TABLE `collection` (
  `record_id` int(11) UNSIGNED NOT NULL,
  `tag_id` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY  (`record_id`,`tag_id`),
  KEY `tag_id` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE `collection`
  ADD CONSTRAINT `collection_ibfk_6` FOREIGN KEY (`tag_id`) REFERENCES `TAGS` (`tag_id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `collection_ibfk_5` FOREIGN KEY (`record_id`) REFERENCES `SYSTEM` (`record_id`) ON DELETE CASCADE ON UPDATE CASCADE
 
#TAGS
CREATE TABLE `TAGS` (
  `tag_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY  (`tag_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

(Si j'ai bien compris, le N de TOSTATUS_N_TIME est la clé dans vos "paires clé/valeur" ??)

Non, en fait les clés sont le nom du champ complet : TOSTATUS_N_TIME.
Là mon exemple est volontairement orienté sur le cas qui nous intéresse. Les noms des clés peuvent être divers et variés sans concerner forcément le changement de statut.

Merci pour votre aide.

[aieeeuuuuu]

Ok, c'est plus clair comme ça !

Donc, en supposant qu'un "système" ne puisse pas changer de statut deux fois à la même seconde :

que donne ceci ?

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
SELECT
	S.name,
	COUNT(*)
FROM (
	SELECT 
		C.record_id,
		MAX(T.value) LastChange
	FROM collection C 
	INNER JOIN TAGS T 
		ON T.tag_id = C.tag_id
	WHERE T.value < 1223549893
	GROUP BY C.record_id
	) LastT
INNER JOIN (
	SELECT
		C2.record_id,
		T2.name,
		T2.value
	FROM collection C 
	INNER JOIN TAGS T 
		ON T.tag_id = C.tag_id
	) S
	ON S.record_id = LastT.record_id
	AND S.value = LastT.LastChange
GROUP BY S.name

[fanfouer]

Envoyé par aieeeuuuuu

Ok, c'est plus clair comme ça !
Donc, en supposant qu'un "système" ne puisse pas changer de statut deux fois à la même seconde

C'est exact.

que donne ceci ?

Le serveur SQL me renvoie un message "Server shutdown in progress". Visiblement le traitement est trop long. J'ai pour l'instant ~30 000 enregistrements dans SYSTEM et ~130 000 dans TAGS (260 000 dans collection).

Et même la première partie me chiffonne.
Le "WHERE TAGS.value < 1223549893" serait valable si il n'y avait que des timestamps dans TAGS.value. Ce n'est pas le cas, il y a tout et n'importe quoi dans cette table.

Peut-etre qu'en rajoutant "AND TAGS.name LIKE "TOSTATUS_%" ca pourrait être valable mais le résultat est toujours le même : Server shutdown in progress.

[skuatamad]

Même problème que précédemment avec un int stocké dans du text.
Peut être :

Code :

1
2
3
4
5
6
7
8
9
SELECT C.record_id,
       MAX(T.value) LastChange
  FROM collection C 
  JOIN TAGS T 
    ON T.tag_id = C.tag_id
 WHERE case when t.name LIKE "TOSTATUS_%" 
         then cast(T.value AS int)
       end < 1223549893
 GROUP BY C.record_id

Mais je ne sais pas si le cast du type TEXT vers INT fonctionnera, as tu vraiment besoin de TEXT, VARCHAR n'est il pas suffisant ?

Mais je ne sais pas si ça supprimera l'erreur qui semble être un crash mysql.

En definitive on est passé d'un modèle "trop statique" a un modèle "trop flexible" (Le EAV modèle peut être très galère à utiliser en select).

[fanfouer]

Bonjour skuatamad,

Envoyé par skuatamad

Même problème que précédemment avec un int stocké dans du text.

Oui, mais je ne peux pas faire autrement. Le contenu de TAGS peut-être vraiment très divers. J'ai par exemple un tag "COMMENT" qui contient les commentaires des utilisateurs et qui peuvent être longs.

Peut être :

Code :

Cela ne fonctionne pas, j'ai une erreur au niveau de "cast (T.value AS int)".

Mais je ne sais pas si ça supprimera l'erreur qui semble être un crash mysql.

Est-il possible de travailler avec des jointures plutôt que des requêtes imbriquées dans mon cas? Il m'avait semblé que les jointures, suivant comment elles étaient pensées, pouvaient accélérer le traitement par rapport à des requêtes imbriquées.

En definitive on est passé d'un modèle "trop statique" a un modèle "trop flexible" (Le EAV modèle peut être très galère à utiliser en select).

Oui, c'est vrai.
Mais j'arrive à faire les select que je veux, le plus compliqué étant de restreindre la sélection suivant la valeur de tel ou tel attribut. On y arrive avec une requête imbriquée et une jointure sans problème et ça fonctionne.
C'est pour ça que je ne veux vraiment pas faire de remise en question pour ce cas là sachant que plein d'autres choses marchent aujourd'hui avec ce modèle.

Merci pour ta réponse.

[skuatamad]

Envoyé par fanfouer

Cela ne fonctionne pas, j'ai une erreur au niveau de "cast (T.value AS int)".

Quelle est l'erreur ?

Envoyé par fanfouer

Oui, c'est vrai.
Mais j'arrive à faire les select que je veux, le plus compliqué étant de restreindre la sélection suivant la valeur de tel ou tel attribut. On y arrive avec une requête imbriquée et une jointure sans problème et ca fonctionne.
C'est pour ça que je ne veux vraiment pas faire de remise en question pour ce cas là sachant que plein d'autres choses marchent aujourd'hui avec ce modèle.

Je ne dis pas qu'il faut modifier le modèle, je ne connais pas le besoin.
Ce modèle a une valeur ajoutée mais comme il n'est pas adapté au fonctionnement relationnel d'un SGBD il faut l'utiliser avec parcimonie, c'est juste un conseil pour la suite.

[fanfouer]

Envoyé par skuatamad

Quelle est l'erreur ?

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int) end < 1223549893 GROUP BY C.record_id ) LastT INNER JOIN ( S' at line 11

Pour info, voici la version précise de MySQL que j'utilise :
Version du serveur: 5.0.51a-24+lenny5-log
Version du protocole: 10

Je ne dis pas qu'il faut modifier le modèle, je ne connais pas le besoin.
Ce modèle a une valeur ajoutée mais comme il n'est pas adapté au fonctionnement relationel d'un SGBD il faut l'utiliser avec partimonie, c'est juste un conseil pour la suite.

Bien compris

[skuatamad]

Essaie peut être avec DECIMAL
cast (t.value as DECIMAL)

[edit]Pour être cohérent il faudrait peut être aussi caster l'unix timestamp :
cast(1223549893 as decimal)

[fanfouer]

Parfait cela marche cette fois en exécutant ce que tu as proposé avec DECIMAL au lieu de int.

Mais la grosse requête me renvoie malheureusement toujours :
#1053 - Server shutdown in progress

[skuatamad]

Zut, bon indépendamment du crash le cast est nécessaire pour les raisons précédemment citées (surtout qu'on est sur une inégalité)

Je n'ai malheureusement pas d'idée là maintenant mais on va continuer à y réfléchir...
aieeeuuuuu on a besoin de tes neuronnes

Bonne soirée

[fanfouer]

Envoyé par skuatamad

Zut, bon indépendamment du crash le cast est nécessaire pour les raisons précédemment citées (surtout qu'on est sur une inégalité)

Oui, je comprends ça.

Je n'ai malheureusement pas d'idée là maintenant mais on va continuer à y réfléchir...
aieeeuuuuu on a besoin de tes neuronnes

Bonne soirée

C'est super, merci d'y passer du temps

Pour info, cette recherche va me servir pour établir ce genre de graphiques :
http://www.infos-reseaux.com/apps/AD...n=monthlyDploy

Je surveille des équipements d'accès au réseau pour le grand public qui passent par différentes phases d'activations. En gardant les dates de passage sous la forme d'attributs, on évite de complexifier le stockage avec du versionage des enregistrements par exemple. Mais le tout est de savoir les réutiliser.
Ca me permet aussi d'établir le suivi sans avoir inséré les enregistrements dans la base aux dates réelles puisque mon appli n'a pas tourné depuis 1 an. La partie plate sera à terme remplacée par une pente régulière, au rythme des mises en prod effectuées par le FAI.

Bonne soirée.

[aieeeuuuuu]

Bonjour,

Il faudrait déjà savoir pourquoi MySQL s'arrete de façon aussi brutale (Logs ?)

on va essayer "d'aider" un peu mysql

que donne ceci

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
 
SELECT
	S.name,
	COUNT(*)
FROM (
	SELECT DISTINCT
		C.record_id,
		MAX(T.value) LastChange
	FROM collection C 
	INNER JOIN TAGS T 
		ON T.tag_id = C.tag_id
	WHERE T.name LIKE 'TOSTATUS_%'
		AND T.value < 1223549893
	GROUP BY C.record_id
	) LastT
INNER JOIN (
	SELECT
		C2.record_id,
		T2.name,
		T2.value
	FROM collection C 
	INNER JOIN TAGS T 
		ON T.tag_id = C.tag_id
	WHERE T.name LIKE 'TOSTATUS_%'
		AND T.value < 1223549893
	) S
	ON S.record_id = LastT.record_id
	AND S.value = LastT.LastChange
GROUP BY S.name

En plaçant un index sur TAGS(name, value, tag_id)

et en vérifiant qu'il est utilisé (avec le like, j'ai un doute sous MySQL)

Sinon, as tu possibilité d'ajouter une colonne à TAGS ? tu pourrais spécifier le type contenu dans value, et filtrer dessus, ça serait mieux qu'un LIKE...

[skuatamad]

Je ne pense pas que mysql autorisera la création de l'index combiné à cause des types de données TEXT.
fanfouer, autant pour la colonne value je comprends qu'il faille le type TEXT, mais pour la colonne name (qui représente des noms de colonnes en fait) je pense que VARCHAR(100) sera amplement suffisant, non?
Ca permettra au moins de créer l'index sur (name, tag_id).

Effectivement il faudrait regarder les logs de mysql.