MySQL: intervalle moyen entre les enregistrements
supposons que ce tableau:
id date
----------------
1 2010-12-12
2 2010-12-13
3 2010-12-18
4 2010-12-22
5 2010-12-23
Comment puis-je trouver les intervalles moyens entre ces dates, en utilisant les requêtes MySQL uniquement?
Par exemple, le calcul sur ce tableau sera
(
( 2010-12-13 - 2010-12-12 )
+ ( 2010-12-18 - 2010-12-13 )
+ ( 2010-12-22 - 2010-12-18 )
+ ( 2010-12-23 - 2010-12-22 )
) / 4
----------------------------------
= ( 1 DAY + 5 DAY + 4 DAY + 1 DAY ) / 4
= 2.75 DAY
4 réponses
intuitivement, ce que vous demandez devrait être équivalent à l'intervalle entre la première et la dernière date, divisé par le nombre de dates moins 1.
laissez-moi vous expliquer plus en détail. Imaginez que les dates sont des points sur une ligne (+ les dates sont-elles présentes, - sont des dates manquantes, la première date est le 12, et j'ai changé la dernière date au 24 décembre pour des raisons d'illustration):
++----+---+-+
Maintenant, ce que vous voulez vraiment faire, c'est d'espacer correctement vos dates entre ces lignes, et de trouver combien de temps il est entre chacun d'eux:
+--+--+--+--+
pour faire cela, vous prenez simplement le nombre de jours entre le dernier et le premier jour, dans ce cas 24 - 12 = 12, et le divisez par le nombre d'intervalles que vous devez espacer, dans ce cas 4: 12 / 4 = 3.
avec une requête MySQL
SELECT DATEDIFF(MAX(dt), MIN(dt)) / (COUNT(dt) - 1) FROM a;
cela fonctionne sur cette table (avec vos valeurs il retourne 2.75):
CREATE TABLE IF NOT EXISTS `a` (
`dt` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `a` (`dt`) VALUES
('2010-12-12'),
('2010-12-13'),
('2010-12-18'),
('2010-12-22'),
('2010-12-24');
si les ID sont uniformément incrémentés sans lacunes, joindre la table à elle - même sur id+1:
SELECT d.id, d.date, n.date, datediff(d.date, n.date)
FROM dates d
JOIN dates n ON(n.id = d.id + 1)
puis grouper et faire la moyenne au besoin.
si les ID ne sont pas uniformes, faites une requête interne pour assigner les ID ordonnés en premier.
je suppose que vous aurez aussi besoin d'ajouter un sous-jeu pour obtenir le nombre total de lignes.
alternativement
créer une fonction aggregate qui garde la trace de la date précédente, et une somme en cours d'exécution et compter. Vous aurez toujours besoin de sélectionner à partir d'un sous-jeu pour forcer la commande par date (en fait, je ne suis pas sûr si C'est garanti dans MySQL).
Venez pour penser à elle, c'est une bien meilleure façon de le faire.
Et Encore Plus Simple
je note juste que la solution de Vegard est bien meilleure.
la requête suivante retourne le résultat correct
SELECT AVG(
DATEDIFF(i.date, (SELECT MAX(date)
FROM intervals WHERE date < i.date)
)
)
FROM intervals i
mais il exécute un sous-jeu dépendant qui pourrait être vraiment inefficace sans index et sur un plus grand nombre de lignes.