The most common values ​​for a group that depends on the selected query

Question

I am puzzling over how to do this in SQL. I have a table:

| User_id | Question_ID | Answer_ID | | 1 | 1 | 1 | | 1 | 2 | 10 | | 2 | 1 | 2 | | 2 | 2 | 11 | | 3 | 1 | 1 | | 3 | 2 | 10 | | 4 | 1 | 1 | | 4 | 2 | 10 | 

It contains user responses to a specific question. A question may have several answers. The user cannot answer the same question twice. (Therefore, there is only one answer identifier for {User_id, Question_ID})

I am trying to find the answer to this query: for a specific question and id (related to the same question), I want to find the most general answer posed by the Other question to users with this answer.

For example, for the above table:

 For question_id = 1 -> For Answer_ID = 1 - (Question 2 - Answer ID 10) For Answer_ID = 2 - (Question 2 - Answer ID 11) 

Is it possible to do in one request? Should this be done in a single request? Should I just use a stored procedure or Java to do this?

Answer 1

Although @ rick-james is right, I'm not sure if it's easy to get started when you don’t know how such queries are usually written for MySQL.

• You need a request to find out the most common answers to questions:

   SELECT question_id, answer_id, COUNT(*) as cnt FROM user_answers GROUP BY 1, 2 ORDER BY 1, 3 DESC 

  This will return a table in which samples for each question_id are displayed in descending order.

   | 1 | 1 | 3 | | 1 | 2 | 1 | | 2 | 10 | 3 | | 2 | 11 | 1 | 

• And now we have to solve the so-called greatest-n-per-group problem. The problem is that in MySQL, for the sake of performance, such tasks are usually solved not in pure SQL, but using hacks that are based on knowledge of how queries are processed internally.

  In this case, we know that we can define a variable, and then iterate over the finished table, have knowledge about the previous row, which allows us to distinguish the first row in the group and others.

   SELECT question_id, answer_id, cnt, IF(question_id=@q_id, NULL, @q_id:=question_id) as v FROM ( SELECT question_id, answer_id, COUNT(*) as cnt FROM user_answers GROUP BY 1, 2 ORDER BY 1, 3 DESC) cnts JOIN ( SELECT @q_id:=-1 ) as init; 

  Make sure you initialize the variable (and respect its data type when initializing, otherwise it may be unexpectedly released later). Here is the result:

   | 1 | 1 | 3 | 1 | | 1 | 2 | 1 |(null)| | 2 | 10 | 3 | 2 | | 2 | 11 | 1 |(null)| 

• Now we just need to filter the rows with NULL in the last column. Since the column is not actually needed, we can move the same expression into the WHERE clause. The cnt column is also not needed, so we can also skip it:

   SELECT question_id, answer_id FROM ( SELECT question_id, answer_id FROM user_answers GROUP BY 1, 2 ORDER BY 1, COUNT(*) DESC) cnts JOIN ( SELECT @q_id:=-1 ) as init WHERE IF(question_id=@q_id, NULL, @q_id:=question_id) IS NOT NULL; 

• The last thing to mention for a query to be effective, you must have the correct indexes. This query requires an index starting with columns (question_id, answer_id). Since you need the UNIQUE index anyway, it makes sense to define it in the following order: (question_id, answer_id, user_id).

   CREATE TABLE user_answers ( user_id INTEGER, question_id INTEGER, answer_id INTEGER, UNIQUE INDEX (question_id, answer_id, user_id) ) engine=InnoDB; 

Here is the sqlfiddle to play with: http://sqlfiddle.com/#!9/bd12ad/20 .

Answer 2

Do you want fish Or do you want to learn how to fish?

Your question has several steps.

• Get information about "user questions with this answer." Create this SELECT and imagine that the results form a new table.

• Apply the "OTHER" constraint. This is probably a minor AND ... != ... added to SELECT #1 .

• Now find the "most common answer." This is probably due to ORDER BY COUNT(*) DESC LIMIT 1 . Probably,

use the view:

 SELECT ... FROM ( select#2 ) 

Answer 3

Your question will be somewhat arbitrary, you should get the first questions with a request from the user from the Question table:

 select question_id,user_id from question 

Then insert the answer to the asked question and do some checks in your Java code, for example (the user answered the same question as the user asking this question answered this question several times).

 select question_id,user_id from question where user_id=asking-user_id // gets all questions and show on UI select answer_id,user_id from answer where user_id=answering-user_id // checks the answers that particular user