Discussion :

[grunk]

Bonjour à tous,

j'ai actuellement une base sqlite avec 2 tables :
Les tables
detection :

Code :

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

1
2
3
4
5
CREATE TABLE `detection` (
	`created_at`	TEXT NOT NULL,
	`count_value`	INTEGER NOT NULL,
	`type_obj`	INTEGER NOT NULL DEFAULT 0
);

et caracteristique :

Code :

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

1
2
3
4
5
CREATE TABLE `caracteristique ` (
	`key`	TEXT NOT NULL,
	`value`	TEXT NOT NULL,
	`id_datas`INTEGER NOT NULL
);

Il n'ya pas de clé étrangère entre ces deux tables car la table caracteristique peux contenir des données pour d'autres tables (j'ai volontairement exclu les champs non utile à ma question).

Donc une ligne de la table "detection" peux avoir n lignes dans la table "caracteristique". L'idée derrière les champs key et value c'est que les caracterisitques et valeurs sont des champs "libre" et donc les utilisateur peuvent créer des caracteristique sans avoir à intervenir sur la structure de la bdd.

Les données

Exemples de données pour bien comprendre :
detection :
"2019-06-26 09:40:00" "1" "1"

caracteristique :
"age" "35" "4"
"gender" "M" "4"

Avec cette architecture je peux donc facilement trouver toutes les détections qui ont par exemple un genre égal à "M" :

Code :

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

SELECT * FROM detection d LEFT JOIN caracteristique ca ON d.rowid = ca.id_datas AND ca.type_table = 1 WHERE ca.key = 'gender' AND ca.value = 'M';

Le select * est juste là pour la démo , pas tapé

Mon problème arrive quand je veux combiner plusieurs "caracteristiques" , par exemple faire la somme de toutes les détections ayant un genre à 'M' et un age égale à "35".
J'ai réussi en faisant une jointure supplémentaire mais je trouve ça fastidieux (surtout si j'ai bcp de caracteristiques à combiner) :

Code :

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

SELECT * FROM detection d LEFT JOIN caracteristique ca ON d.rowid = ca.id_datas AND ca.type_table = 1 LEFT JOIN carac ca2 ON d.rowid = ca2.id_datas AND ca2.type_table = 1 WHERE ca.key = 'gender' AND ca.value = 'M' AND ca2.key = 'age' AND ca2.value='35';

Y'a t'il moyen de faire mieux ?

Merci

[aieeeuuuuu]

bonjour,

Vous avez une alternative avec un regroupement, mais pas sûr du coup que ça soit plus efficace, surtout si la table caracteristique est correctement indexée

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT 
		rowid
	,	SUM(count_value) AS somme
FROM detection d 
LEFT JOIN caracteristique ca 
	ON d.rowid = ca.id_datas 
	AND ca.type_table = 1 
WHERE ca.key IN ('gender', 'age')
GROUP BY rowid
HAVING MAX(CASE WHEN ca.key = 'gender' AND ca.value = 'M' THEN 1 END) = 1
AND MAX(CASE WHEN ca.key = 'age' AND ca.value = '35' THEN 1 END) = 1
;

[grunk]

Merci de la réponse.

La table caracteristique est indexée avec un index unique (key,id_datas).

En passant par un regroupement la requête semble être plus facile à générer dans le code , c'est une alternative intéressante.

En revanche dans ton exemple le WHERE n'a pas lieux d'être je pense.

[aieeeuuuuu]

Concernant le WHERE, le but et de permettre enventuellement un filtre en amont, pour manipuler moins de données. il est donc là juste pour aider l'optimiseur.

Cela dit, je ne sais pas si le résultat de ma requete est vraiment exact : suite à la jointure, il y aura donc deux lignes pour chaque ligne dans la table detection, chaque valeur sera donc comptée deux fois.

quelle somme voulez-vous faire exactement ?

un petit jeu d'essai + résultat attendu aiderait certainement.

Enfin, concernant l'index, mettre id_datas en premier serait peut-être préférable (certainement plus discriminant), mais surtout il faudrait ajouter la colonne type_table afin que l'index soit couvrant

[grunk]

Je suis pas non plus certains que les résultats soient juste.
Je n'arrive pas aux même résultat qu'avec les mutiples join sur certaines recherche.

mais surtout il faudrait ajouter la colonne type_table afin que l'index soit couvrant

il l'est , je l'ai pas précisé car je l'avais pas mis dans mon create table , mais sur le jeux de données réel il est bien présent.

Le regroupement avec having n'est pas forcément plus claire à la lecture, donc je vais peut être choisir la simplicité et resté avec les join.

[aieeeuuuuu]

il l'est , je l'ai pas précisé car je l'avais pas mis dans mon create table , mais sur le jeux de données réel il est bien présent.

je voulais dire : ajouter la colonne type_table à l'index
ainsi, il ne devrait plus y avoir d’accès à la table caractéristique, l'index suffira