Discussion :

[Guardi_Guedj]

Bonjour,

Tout d’abord, j’espère poster au bon endroit ! Et désolé si le titre est peu explicite, je ne sais trop comment résumer le sujet.
Je suis confronté à un jeu de données sur lequel j’aimerais appliquer un traitement particulier sur certaines lignes où il manque une information.

Voici tout d’abord rapidement l’exposé du problème, que je simplifie. Je pars d’une table row_data qui contient 4 colonnes :
- Colonne 1 : timestamp, je m’en fiche un peu pour l’instant
- Colonne 2 : ID, qui identifie un objet que l’on va retrouver une ou plusieurs fois dans ma liste
- Colonne 3 : Action, qui identifie un type d’action. Ici pour simplifier il n’y a que 2 actions possibles : MOVE et STOP (en réalité il y’en a beaucoup plus)
- Colonne 4 : Value, une valeur mesurée sous forme de chaine de caractère

Pour les actions STOP, et seulement pour cette action, aucune valeur n’est mesurée. Mon but serait de pouvoir retrouver, pour ces actions particulières, la dernière valeur mesurée associée à l’ID.
Dans l’exemple ci-dessous, on voit un STOP à la ligne 14 pour l’ID 444182. La dernière action vue pour cet ID est en ligne 6 : A2911311.



Ainsi je cherche à extraire de cette table brute une table ne contenant que les lignes correspondant aux actions de type STOP mais auxquelles j’ajouterai les dernières valeurs lues dans la table row data sur la base de l’ID.



J’ai déjà réalisé l’exercice sous Excel en utilisant la fonction d’index, mais le problème est que ce n’est pas du tout adapté au volume de données que j’ai à traiter (100000 lignes pour commencer).

C’est pourquoi je me tourne vers le SQL, auquel j’essaie de m’initier un peu en urgence…
Dans le code ci-dessous, comment puis-je m’y prendre pour récupérer le dernier « value » lue pour chaque ID ? Existe-t-il une fonction dédiée à ce que je veux faire ? C'est-à-dire recherche vers le haut et récupération de la première ligne vue sur la base de l’ID ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
drop table if exists result;
create table result (time integer(10),`ID` integer(10), Start_action varchar(10),`Last_Value` varchar(10));
 
insert into result
	select
		timestamp as time,
		ID as ID,
		Action as Start_action,
		Value as Last_Value 
	from test_algo.row_data_algo
where Action LIKE 'STOP'
 
group by time, ID, Start_action, Last_Value

Ou bien faut-il que je parcours la table depuis le début avec une condition de type ID = IDrecherché et je crée un vecteur contenant les numéros de lignes associés, puis je demande de lire la valeur de la dernière ligne de cette table ?

Merci par avance pour votre aide !

[SQLpro]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT T1.*
FROM   T AS T0
       JOIN (SELECT ID, MAX(timestamp) AS ts_max
			 FROM   T AS TX
			 WHERE  EXISTS(SELECT *
						   FROM   T 
						   WHERE  T.action = 'STOP'
						     AND  T.ID =TX.ID
							 AND  T.timestamp > TX.timestamp)
			 GROUP  BY ID) AS T1
			 ON T0.ID = T1.ID
			 AND T0.timestamp = T1.ts_max

A +

[Guardi_Guedj]

Bonjour SQLpro,

Merci beaucoup pour ta réponse !
Excuse-moi mais je ne suis pas sûr d’arriver à te suivre.

Déjà je bloque un peu sur les deux premières lignes (oui je pars de loin !) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT T1.*
FROM   row_data_algo AS T0

J’ai commencé par appliquer ce code dans une nouvelle requête, en remplaçant simplement le « T » par ma table brute.
Cela me renvoi un tableau contenant pour chaque ID, le timestamp qui correspond au MOVE qui précède le dernier STOP sur toute la liste. (et déjà je te dis encore un grand merci :-) )
Toutefois il me faut faire cela non pas uniquement pour le dernier STOP, mais pour tous les STOP de la liste, et qui plus est récupérer la valeur Value associée au MOVE qui précède.
Le but final est de partir ensuite de la table ne contenant plus que les STOP et d’étudier le last_value.

Excuse mon faible niveau, mais comment dois-je incorporer ce code dans le mien pour que, au lieu du simple « value as Last_Value », je récupère directement la dernière valeur lue à chaque fois que je rencontre un STOP ?

Pour revenir sur ton code, je vois 4 tables : T, Tx, T0, T1

De ce que je comprends,
- T doit être directement le nom de la table de données brute que j’appelle « row_data_algo »
- T0 est une table temporaire qui sert à parcourir T ? Je n'arrive pas bien à voir à quoi elle sert
- Tx sert à renvoyer un couple ID et son Timestamp max dans la table row_data
- T1 sert à repérer le « STOP » qui vient depuis timestamp_max ?

Je vois ensuite à peu près l’idée de la jointure, qui permet au final, pour chaque ID, d’extraire de la table T0 la ligne complète (donc avec la valeur qui m’intéresse) à timestamp = ts_max qui est stockée dans T1

Merci encore d’avance, et bonne soirée !

[SQLpro]

Je ne vais pas vous donner un cours particulier de SQL en ligne ! La requête postée est en langage SQL standard et fait appel à des choses bien connues qui fonctionne même sur des SGBD très limités comme MySQmerde...

Il me semble plus simple que vous appreniez ce langage...

Pour ce faire, vous disposez des pages que j'ai écrite ici même : http://sqlpro.developpez.com/
Section "le SQL de A à Z"
Si vous voulez quelque chose de plus approfondis, il y a mes livres :

A +

[Artemus24]

Salut à tous.

Colonne 1 : timestamp, je m’en fiche un peu pour l’instant

Votre colonne n'est pas un timestamp, mais une colonne auto incrémentée.
Un timestamp c'est ceci : "2017-03-17 19:15:23.123456", sur 26 caractères à l'affichage.

Et non, vous ne devez pas vous en foutre car elle va servir à sélectionner le dernier tuple pour l'action "STOP".

Mon but serait de pouvoir retrouver, pour ces actions particulières, la dernière valeur mesurée associée à l’ID.

La dernière valeur mesurée est donc celle ayant le timestamp le plus grand !

Ce qui donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `timestamp`    integer unsigned  not null auto_increment primary key,
  `id`           integer unsigned  not null,
  `action`       char(04)          not null,
  `value`        char(08)          not null
 ) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`id`,`action`,`value`) values
  (635228, 'MOVE', 'A1913221'),
  (444182, 'MOVE', 'A2512411'),
  (444182, 'MOVE', 'A2911311'),
  (444182, 'STOP', ''),
  (782314, 'MOVE', 'A1913121'),
  (444182, 'MOVE', 'A3311211')
--------------
 
--------------
select * from `test`
--------------
 
+-----------+--------+--------+----------+
| timestamp | id     | action | value    |
+-----------+--------+--------+----------+
|         1 | 635228 | MOVE   | A1913221 |
|         2 | 444182 | MOVE   | A2512411 |
|         3 | 444182 | MOVE   | A2911311 |
|         4 | 444182 | STOP   |          |
|         5 | 782314 | MOVE   | A1913121 |
|         6 | 444182 | MOVE   | A3311211 |
+-----------+--------+--------+----------+
--------------
select      t1.timestamp,
            t1.id,
            t1.action,
            t2.value
      from  test      as t1
 
inner join  test      as t2
        on  t2.id     = t1.id
       and  t2.action = 'MOVE'
 
     where  t1.action = 'STOP'
       and  t2.timestamp = (select max(timestamp) from test as t3 where t3.id = t2.id and t3.action = 'MOVE')
--------------
 
+-----------+--------+--------+----------+
| timestamp | id     | action | value    |
+-----------+--------+--------+----------+
|         4 | 444182 | STOP   | A3311211 |
+-----------+--------+--------+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+