Dopasowywanie pojedynczej kolumny do wielu wartości bez samodzielnego łączenia tabeli w MySQL

Mamy tabelę, której używamy do przechowywania odpowiedzi na pytania. Musimy być w stanie znaleźć użytkowników, którzy mają określone odpowiedzi na określone pytania. Jeśli więc nasza tabela składa się z następujących danych:

user_id     question_id     answer_value  
Sally        1               Pooch  
Sally        2               Peach  
John         1               Pooch  
John         2               Duke

i chcemy znaleźć użytkowników, którzy odpowiedzą na „Pooch” w przypadku pytania 1 i „Peach” w przypadku pytania 2, następujący kod SQL (oczywiście) nie będzie działać:

select user_id 
from answers 
where question_id=1 
  and answer_value = 'Pooch'
  and question_id=2
  and answer_value='Peach'

Moją pierwszą myślą było przyłączyć się do stołu dla każdej szukanej odpowiedzi:

select a.user_id 
from answers a, answers b 
where a.user_id = b.user_id
  and a.question_id=1
  and a.answer_value = 'Pooch'
  and b.question_id=2
  and b.answer_value='Peach'

Działa to, ale ponieważ zezwalamy na dowolną liczbę filtrów wyszukiwania, musimy znaleźć coś znacznie wydajniejszego. Moje następne rozwiązanie wyglądało mniej więcej tak:

select user_id, count(question_id) 
from answers 
where (
       (question_id=2 and answer_value = 'Peach') 
    or (question_id=1 and answer_value = 'Pooch')
      )
group by user_id 
having count(question_id)>1

Chcemy jednak, aby użytkownicy mogli dwa razy wypełnić ten sam kwestionariusz, aby potencjalnie mieli dwie odpowiedzi na pytanie 1 w tabeli odpowiedzi.

Więc teraz jestem zagubiony. Jak najlepiej to podejść? Dzięki!

Znalazłem sprytny sposób na wykonanie tego zapytania bez samodzielnego połączenia.

Uruchomiłem te polecenia w MySQL 5.5.8 dla Windows i uzyskałem następujące wyniki:

use test
DROP TABLE IF EXISTS answers;
CREATE TABLE answers (user_id VARCHAR(10),question_id INT,answer_value VARCHAR(20));
INSERT INTO answers VALUES
('Sally',1,'Pouch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duke');
INSERT INTO answers VALUES
('Sally',1,'Pooch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duck');

SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id;

+---------+-------------+---------------+
| user_id | question_id | given_answers |
+---------+-------------+---------------+
| John    |           1 | Pooch         |
| John    |           2 | Duke,Duck     |
| Sally   |           1 | Pouch,Pooch   |
| Sally   |           2 | Peach         |
+---------+-------------+---------------+

Ten ekran pokazuje, że John udzielił dwóch różnych odpowiedzi na pytanie 2, a Sally udzieliła dwóch różnych odpowiedzi na pytanie 1.

Aby sprawdzić, na które pytania odpowiedzieli wszyscy użytkownicy, po prostu umieść powyższe zapytanie w podzapytaniu i sprawdź przecinek na liście podanych odpowiedzi, aby uzyskać liczbę różnych odpowiedzi w następujący sposób:

SELECT user_id,question_id,given_answers,
(LENGTH(given_answers) - LENGTH(REPLACE(given_answers,',','')))+1 multianswer_count
FROM (SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id) A;

Mam to:

+---------+-------------+---------------+-------------------+
| user_id | question_id | given_answers | multianswer_count |
+---------+-------------+---------------+-------------------+
| John    |           1 | Pooch         |                 1 |
| John    |           2 | Duke,Duck     |                 2 |
| Sally   |           1 | Pouch,Pooch   |                 2 |
| Sally   |           2 | Peach         |                 1 |
+---------+-------------+---------------+-------------------+

Teraz wystarczy odfiltrować wiersze, w których multianswer_count = 1, używając innego podzapytania:

SELECT * FROM (SELECT user_id,question_id,given_answers,
(LENGTH(given_answers) - LENGTH(REPLACE(given_answers,',','')))+1 multianswer_count
FROM (SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id) A) AA WHERE multianswer_count > 1;

Oto co mam:

+---------+-------------+---------------+-------------------+
| user_id | question_id | given_answers | multianswer_count |
+---------+-------------+---------------+-------------------+
| John    |           2 | Duke,Duck     |                 2 |
| Sally   |           1 | Pouch,Pooch   |                 2 |
+---------+-------------+---------------+-------------------+

Zasadniczo wykonałem trzy skany tabeli: 1 na głównym stole, 2 na małych podkwerendach. BRAK ŁĄCZEŃ !!!

Spróbuj !!!

Lubię metodę dołączania, ja sam:

SELECT a.user_id FROM answers a
INNER JOIN answers a1 ON a1.question_id=1 AND a1.answer_value='Pooch'
INNER JOIN answers a2 ON a2.question_id=2 AND a2.answer_value='Peach'
GROUP BY a.user_id

Aktualizacja Po przetestowaniu z większą tabelą (~ 1 milion wierszy) ta metoda zajęła znacznie więcej czasu niż prosta ORmetoda wspomniana w pierwotnym pytaniu.

Łączyliśmy się user_idz answerstabeli w łańcuchu złączeń, aby uzyskać dane z innych tabel, ale izolowanie SQL tabeli odpowiedzi i pisanie jej w tak prostych terminach pomogło mi znaleźć rozwiązanie:

SELECT user_id, COUNT(question_id) 
FROM answers 
WHERE
  (question_id = 2 AND answer_value = 'Peach') 
  OR (question_id = 1 AND answer_value = 'Pooch')
GROUP by user_id 
HAVING COUNT(question_id) > 1

Niepotrzebnie korzystaliśmy z drugiego zapytania częściowego.

Jeśli masz duży zestaw danych, zrobiłbym dwa indeksy:

• pytanie_id, wartość_odpowiedzi, identyfikator_użytkownika; i
• identyfikator_użytkownika, identyfikator_dokumentu, wartość_odpowiedzi.

Musisz dołączyć wiele razy ze względu na sposób organizacji danych. Jeśli wiesz, dla której wartości pytanie jest najmniej powszechne, możesz nieco przyspieszyć zapytanie, ale optymalizator powinien to zrobić za Ciebie.

Wypróbuj zapytanie jako:

WYBIERZ a1.user_id OD odpowiedzi a1
GDZIE a1.question_id = 1 ORAZ a1.answer_value = „Pooch”
INNER JOIN odpowiada a2 ON a2.question_id = 2 
   ORAZ a2.answer_value = „Brzoskwinia” ORAZ a1.user_id = a2.user_id

Tabela a1 powinna używać pierwszego indeksu. W zależności od dystrybucji danych optymalizator może użyć dowolnego indeksu. Całe zapytanie powinno być spełnione z indeksów.

Jednym ze sposobów podejścia jest uzyskanie podzbioru identyfikator_użytkownika i przetestowanie go pod kątem drugiego dopasowania:

SELECT user_id 
FROM answers 
WHERE question_id = 1 
AND answer_value = 'Pooch'
AND user_id IN (SELECT user_id FROM answers WHERE question_id=2 AND answer_value = 'Peach');

Korzystanie ze struktury Rolando:

CREATE TABLE answers (user_id VARCHAR(10),question_id INT,answer_value VARCHAR(20));
INSERT INTO answers VALUES
('Sally',1,'Pouch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duke');
INSERT INTO answers VALUES
('Sally',1,'Pooch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duck');

Wydajność:

mysql> SELECT user_id FROM answers WHERE question_id = 1 AND answer_value = 'Pooch' AND user_id IN (SELECT user_id FROM answers WHERE question_id=2 AND answer_value = 'Peach');
+---------+
| user_id |
+---------+
| Sally   |
+---------+
1 row in set (0.00 sec)