mysql轮询_高级MySQL：查找轮询响应之间的相关性

我有四个MySQL表：

用户(id，姓名)

民意调查(身份证，文字)

选项(id，poll_id，文本)

响应(id，poll_id，option_id，user_id)

给定一个特定的民意调查和一个特定的选项，我想生成一个表格，显示来自其他民意调查的哪些选项最相关。

假设这是我们的数据集：

TABLE users:

+------+-------+

| id | name |

+------+-------+

| 1 | Abe |

| 2 | Bob |

| 3 | Che |

| 4 | Den |

+------+-------+

TABLE polls:

+------+-----------------------+

| id | text |

+------+-----------------------+

| 1 | Do you like apples? |

| 2 | What is your gender? |

| 3 | What is your height? |

| 4 | Do you like polls? |

+------+-----------------------+

TABLE options:

+------+----------+---------+

| id | poll_id | text |

+------+----------+---------+

| 1 | 1 | Yes |

| 2 | 1 | No |

| 3 | 2 | Male |

| 4 | 2 | Female |

| 5 | 3 | Short |

| 6 | 3 | Tall |

| 7 | 4 | Yes |

| 8 | 4 | No |

+------+----------+---------+

TABLE responses:

+------+----------+------------+----------+

| id | poll_id | option_id | user_id |

+------+----------+------------+----------+

| 1 | 1 | 1 | 1 |

| 2 | 1 | 2 | 2 |

| 3 | 1 | 2 | 3 |

| 4 | 1 | 2 | 4 |

| 5 | 2 | 3 | 1 |

| 6 | 2 | 3 | 2 |

| 7 | 2 | 3 | 3 |

| 8 | 2 | 4 | 4 |

| 9 | 3 | 5 | 1 |

| 10 | 3 | 6 | 2 |

| 10 | 3 | 5 | 3 |

| 10 | 3 | 6 | 4 |

| 10 | 4 | 7 | 1 |

| 10 | 4 | 7 | 2 |

| 10 | 4 | 7 | 3 |

| 10 | 4 | 7 | 4 |

+------+----------+------------+----------+给定轮询ID 1和选项ID 2，生成的表应该是这样的：

+----------+------------+-----------------------+

| poll_id | option_id | percent_correlated |

+----------+------------+-----------------------+

| 4 | 7 | 100 |

| 2 | 3 | 66.66 |

| 3 | 6 | 66.66 |

| 2 | 4 | 33.33 |

| 3 | 5 | 33.33 |

| 4 | 8 | 0 |

+----------+------------+-----------------------+因此，基本上，我们正在识别对投票ID 1和选定的选项ID 2作出回应的所有用户，并且我们正在审查所有其他投票，以查看他们还选择了哪些百分比。