Discussion :

[fred!]

Bonjour à tous,
après plusieurs essais infructueux, je vous soumet mon problème de requête :

soit une table T avec
- un champs tu (bigint) qui est un temps epochs
- un champs val (double) qui est une valeur.

Je souhaiterais obtenir en résultat de requête la représentation suivante
tu_start, count_val_jour, count_val_heure.

avec tu_start représenté heure par heure,
count_val_jour : nombre de valeurs acquises sur la journée et
count_val_heure : nombre de valeurs acquises en 1 heure.
Pour une journée il doit donc y avoir répétition de 24 fois la valeur de count_val_jour dans le résultat de la requête.

soit :
tu_start+00 count_val_jour_1 count_val_heure(x)
tu_start+01 count_val_jour_1 count_val_heure(y)
tu_start+02 count_val_jour_1 count_val_heure(z)
...
tu_start+25 count_val_jour_2 count_val_heure(a)
tu_start+26 count_val_jour_2 count_val_heure(b)
tu_start+27 count_val_jour_2 count_val_heure(c)
...



L'idée ensuite est de pouvoir vérifier que pour chaque intervalle de 1 heure:
count_val_heure x 24 ~ count_val_jour et vérifier ainsi des "trous" d'acquisition de mesures.

Pour cela j'ai :

la requête pour avoir le comptage par heure :

Code :

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

1
2
3
4
5
6
7
 
select SQL_BIG_RESULT 
from_unixtime(min(T.`tu`)) AS 'tu_start',
count(T.`val`) as 'count_val_heure'
from T
group by from_unixtime(T.`tu`, '%Y-%m-%d %H')
order by tu_start asc

la requête pour avoir le comptage par jour:

Code :

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

1
2
3
4
5
6
7
 
select SQL_BIG_RESULT 
from_unixtime(min(T.`tu`)) AS 'tu_start',
count(T.`val`) as 'count_val_jour'
from T
group by from_unixtime(T.`tu`, '%Y-%m-%d')
order by tu_start asc

Mon problème est de "lier" ces 2 requêtes, j'imagine qu'il faut passer par une jointure de la table T avec elle-même mais pour le moment je n'ai pas réussi...

Si quelqu'un a un conseil à me fournir,
Merci d'avance.
Fred!

[fred!]

Bon je me réponds en partie à moi même, après avoir "découvert" les tables temporaires : une solution à mon problème serait :

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
 
DROP TEMPORARY TABLE IF EXISTS T_TEMP;
 
CREATE TEMPORARY TABLE T_TEMP
SELECT 
from_unixtime(min(T.`tu`),'%Y-%m-%d') AS 'TuStart',
count(*) AS 'count_val_jour'
FROM T
GROUP BY from_unixtime(T.`tu`, '%Y-%m-%d')
ORDER BY TuStart ASC;
 
SELECT 
from_unixtime(min(T.`tu`)) AS 'TuStart',
count_val_jour,
count(*) AS 'count_val_heure'
FROM T join T_TEMP on from_unixtime(T.`tu`, '%Y-%m-%d') = T_TEMP.TuStart
GROUP BY from_unixtime(T.`tu`, '%Y-%m-%d %H')
ORDER BY TuStart ASC;

Je maintiens néanmoins ma question : peut-on faire la même chose sans passer par une table temporaire ?

[punkoff]

Bonjour,

oui avec des sous-requêtes

[fred!]

Bon ben voila pour ceux que cela pourrait intéresser :
en ré-organisant la requête qui utilisait la table temporaire on obtient :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT from_unixtime(min(T.`tu`)) AS 'tu_start',
count_val_jour,
count(*) AS 'count_val_heure'
FROM T join 
(
	SELECT from_unixtime(min(T.`tu`),'%Y-%m-%d') AS 'tu_start',
	count(*) AS 'count_val_jour'
	FROM T
	GROUP BY from_unixtime(T.`tu`, '%Y-%m-%d')
) as T_TEMP
on from_unixtime(T.`tu`, '%Y-%m-%d') = T_TEMP.tu_start
GROUP BY from_unixtime(T.`tu`, '%Y-%m-%d %H')
ORDER BY tu_start ASC;

Cela fonctionne et en plus c'est un peu plus rapide.

[punkoff]

en passant par 2 sous-requêtes le resultat pourrai être encore plus rapide, je penses.

Là vous démultipliez les solutions pour ensuite les restreindre.

[fred!]

en passant par 2 sous-requêtes le resultat pourrai être encore plus rapide, je penses.

Là vous démultipliez les solutions pour ensuite les restreindre.

N'étant pas trop expérimenté en "sql" je veux bien un exemple ?
Merci d'avance.

[punkoff]

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
 
SELECT T_TEMP2.'tu_start',
count_val_jour,
count_val_heure
FROM 
(
	SELECT from_unixtime(min(T.`tu`),'%Y-%m-%d') AS 'tu_start',
	count(*) AS 'count_val_jour'
	FROM T
	GROUP BY from_unixtime(T.`tu`, '%Y-%m-%d')
) AS T_TEMP
inner join 
(
	SELECT from_unixtime(min(T.`tu`)) AS 'tu_start', count(*) AS 'count_val_heure'
	from T
	GROUP BY from_unixtime(T.`tu`, '%Y-%m-%d %H')
) as T_TEMP2 on T_TEMP.'tu_start' = from_unixtime(T_TEMP2.'tu_start', '%Y-%m-%d')
ORDER BY tu_start ASC;

[fred!]

> punkoff : merci je vais tester cela.

[fred!]

Juste une modification à faire pour le 'ON' :

Code :

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

as T_TEMP2 on T_TEMP.'tu_start' = from_unixtime(T_TEMP2.'tu_start', '%Y-%m-%d')

->

Code :

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

as T_TEMP2 on day(T_TEMP.'tu_start') = day(T_TEMP2.'tu_start')

Et ça marche nickel.
Temps d’exécution de la requête sur mon jeu de données : ~2.3s contre ~8.1s avant !!!

Un Grand Merci.

Fred!