Disons que j'ai une table avec deux colonnes: Ce dont j'ai besoin est la table des fusionnées : tous les intervalles qui se chevauchent ou adjacents ont largué en une.
Il peut être construit avec une requête de jointure, mais qui est quadratique dans le nombre de lignes, soit 4 millions de lignes dans mon cas (j'ai décidé de composer cette question parce que la requête est toujours en cours d'exécution).
Il peut également être fait dans un Single Pass, en parcourant chaque ligne et en gardant une trace de l'heure de fin maximale - mais comment faire cela, ou quelque chose d'équivalent, dans SQL standard? Y a-t-il tout O (n) de le faire dans SQL? J'utilise SQLite en ce moment; Une solution spécifique à SQLite m'aiderait également à sortir cette fois.
des réponses aux questions connexes ( 1 , , 3 , 4 < / a>, 5 , , 7 , 8 , 9 ) Je ne peux pas dire si C'est possible.
Pouvez-vous? Démarrer et extrémité , les deux entiers et la table sont commandés par la première, puis la deuxième colonne. Chaque rangée représente un intervalle.
4 Réponses :
Basé sur la réponse à ma question dans les commentaires, je ne pense pas que mon idée aurait fonctionné. Puisque vous vous avez mentionné, cela peut (et je suppose que vous savez comment) être fait avec des jointures, j'ai eu une idée de minimiser le nombre de lignes à relever en contenant uniquement des gammes qui appartiennent à des points distincts comme les suivants:
select start, max(end) as end
from (
select min(start) as start,end
from table
group by end
) in_tab
group by in_tab.start
J'ai pensé à votre approche et je pense que ce sera plus efficace que la jointure (le nombre de fins par démarrage ou le début de la fin, est beaucoup plus petit que le nombre total de lignes). J'ai peut-être besoin d'ajouter des index. Donc, c'est une sorte de mi-chemin.
Le problème est que juste obtenir le min et max une fois pas assez bon. Les intervalles (1,4), (2,7), (5,9) doivent être fusionnés, même si le maximum (fin) de 2 est 7 et le min (début) de 7 est 2.
vous avez raison. Mais j'ai mentionné dans mon texte que le Select est uniquement pour minimiser les lignes à travailler. Cela signifie, après la requête ci-dessus, vous devez toujours faire la jointure pour la fusion finale. (Dans votre texte original, vous avez mentionné que vous pouvez le faire avec une jointure). Tout ce que je voulais, c'est de minimiser le nombre de lignes que vous utilisez dans les jointures, donc ce que j'ai fait était la première étape (j'ai écrit que les résultats peuvent être stockés dans la table TEMP, puis votre méthode appliquée sur elles).
Oui, j'ai également pensé à cette idée, mais après la navigation sur les données, déterminé que cela ne ferait pas une différence dans mon cas.
Dans vos liens, vous avez omis one: Puis-je utiliser un serveur SQL Server pour fusionner les dates d'intersection? où je présente une solution CTE récursive au problème des intervalles de chevauchement. Les CTE récursives peuvent être traités différemment (par rapport aux jointures de soi ordinaires) et se produisent souvent incroyablement rapidement.
mySQL n'a pas de CTES récursif. Postgres les a, Oracle les a, Microsoft les a.
ici Interrogation pour un "Run '" de colonnes consécutives à Postgres en est une autre, avec un facteur de fudge.
ici Obtenir le total Intervalle de temps provenant de plusieurs rangées si la séquence n'est pas cassée en est une autre.
Je n'avais aucune idée - merci (malheureusement. SQLite ne les soutient pas: Stackoverflow.com/questions/7456957/... )
Est-ce plus efficace que l'approche d'A.J. et si c'est le cas, pourquoi?
L'approche de A.J. L'approche n'utilise pas un premier composant clé, seulement min (début) à la fin. Normalement, ce serait un "Sélection d'une pièce d'identité, min (ddate)" (ou max (DDate)) par ID. @ReinierPost: Ce n'est pas seulement plus efficace, c'est aussi correct ;-)
L'approche que j'ai mentionnée n'est pas complète du tout pour commencer. Ce n'est que la première étape afin de minimiser les lignes qui forment le point de départ d'une solution de fusion. L'approche est ciblée vers des bases de données dans lesquelles les concepts mentionnés dans votre solution ne sont pas disponibles, tels que le cas actuel lorsque SQLite est utilisé.
Pour l'instant, la meilleure réponse que j'ai trouvée est la suivante: utilisez l'indexation. Cela apporte la complexité du quadratique à O (n log n).
avec un Index de couvrement , les requêtes ont été suffisamment rapides pour mes besoins;
Avec juste un index sur la colonne de début ou de fin, il était plus lent mais toujours ok.
Dans chaque cas, Trouver un élément dans l'index n'est pas tout à fait O (1), mais s'est avéré assez proche.
Et construire l'index n'est pas lent, non plus.
Ce qui reste est la preuve qu'un véritable algorithme O (n) ne peut pas être écrit dans SQL.
Donc, une autre réponse est de l'écrire dans une langue différente puis de l'appliquer à une table SQLite.
Il existe différentes façons de faire ce travail:
Expliquez le plan de requête m'a dit qu'une seule analyse de table est combinée à l'utilisation de l'index, comme prévu.
Eh bien, voici une solution qui fonctionne dans MySQL (je ne sais pas si cela fonctionnera dans SQLite). Je pense, mais ne peut pas prouver, c'est O (n) (Jeter le temps qu'il faut pour trier la table d'événements initialement, c'est-à-dire s'il est déjà trié comme je pense que la question pose la question.)
Oui, il est trié. Je n'avais aucune idée que vous pourriez faire cela dans MySQL.
Je peux penser aux moyens d'accomplir cela en utilisant des expressions de table communes ou des requêtes récursives, mais SQLite ne prend pas en charge ces fonctionnalités. PostgreSQL fait si :)
Est-ce que Speed Trump tout le reste? Les tables temporaires ou quelque chose vont bien pour le souci de vitesse?
Quel est le minimum possible "Démarrer" et le maximum possible "fin"? Ou il n'y a pas de limite du tout dans votre cas? Existe-t-il une limite connue pour ces valeurs? (Même si non utilisé dans les intervalles de la table)
L'utilisation de tables temporaires est bien. Le premier départ et la dernière extrémité comporte environ 4 millions d'unités (avec coïncidence), tandis que la différence la plus grande dans la même ligne est généralement de 1 ou 2, mais des pics à 1000.
SQLite vous permet de créer des fonctions définies par l'utilisateur dans votre langage de programmation hôte. Considérant que vous pouvez créer des fonctions agrégées, vous pouvez le retirer en une seule passe. Mais, je ne sais pas à quel point cela vaut mieux que d'aller chercher toutes les données et d'utiliser une boucle dans votre langue d'hôte considérant que SQLite est intégré.
@chris: J'ai examiné cela, mais ce n'est pas une fonction agrégée régulière dans laquelle le problème consiste à déterminer les groupes, pas à calculer les valeurs globales pour des groupes donnés.