mysql - 依赖于选择查询的组的最常见值

coder 2023-10-02 原文

我正在为如何在 SQL 中执行此操作而绞尽脑汁。我有一张 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     |

它包含用户对特定问题的回答。一个问题可能有多个答案。用户不能两次回答同一个问题。 (因此，每个 {User_id, Question_ID} 只有一个 Answer_ID)

我正在尝试找到此查询的答案:对于特定的问题和答案 ID(与同一问题相关)，我想找到具有给定答案的用户对其他问题给出的最常见答案。

例如，对于上表:

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

是否可以在一个查询中完成？应该在一个查询中完成吗？我应该只使用存储过程还是 Java？

最佳答案

虽然@rick-james 是对的，但我不确定如果您不知道这样的查询通常是如何为 MySQL 编写的，那么它是否容易上手。

您需要一个查询来找出最常见的问题答案:

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

这将返回一个表，其中对于每个 question_id，我们按降序输出计数。

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

现在我们应该解决一个所谓的greatest-n-per-group任务。问题在于，在 MySQL 中，为了性能起见，像这样的任务通常不是用纯 SQL 解决的，而是使用依赖于内部如何处理查询的知识的 hack。

在这种情况下，我们知道我们可以定义一个变量，然后遍历就绪表，了解前一行，这使我们能够区分组中的第一行和其他行。
```
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;
```
确保您已初始化变量(并在初始化时遵守其数据类型，否则稍后可能会意外转换)。这是结果:
```
| 1 |  1 | 3 |    1 |
| 1 |  2 | 1 |(null)|
| 2 | 10 | 3 |    2 |
| 2 | 11 | 1 |(null)|
```

现在我们只需要过滤掉最后一列中包含 NULL 的行。由于实际上不需要该列，我们可以将相同的表达式移动到 WHERE 子句中。 cnt 列实际上也不需要，所以我们也可以跳过它:

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;

最后值得一提的是，要使查询高效，您应该拥有正确的索引。此查询需要一个以 (question_id, answer_id) 列开头的索引。由于您无论如何都需要一个 UNIQUE 索引，因此按以下顺序定义它是有意义的:(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;
```

这是一个可以玩的 sqlfiddle:http://sqlfiddle.com/#!9/bd12ad/20 .

关于mysql - 依赖于选择查询的组的最常见值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/44891102/

有关mysql - 依赖于选择查询的组的最常见值的更多相关文章

ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2
我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.
ruby-on-rails - 在 ruby .gemspec 文件中，如何指定依赖项的多个版本？ - 2
我正在尝试修改当前依赖于定义为activeresource的gem:s.add_dependency"activeresource","~>3.0"为了让gem与Rails4一起工作，我需要扩展依赖关系以与activeresource的版本3或4一起工作。我不想简单地添加以下内容，因为它可能会在以后引起问题:s.add_dependency"activeresource",">=3.0"有没有办法指定可接受版本的列表？~>3.0还是~>4.0？最佳答案根据thedocumentation,如果你想要3到4之间的所有版本，你可以这
ruby - RuntimeError(自动加载常量 Apps 多线程时检测到循环依赖 - 2
我收到这个错误:RuntimeError(自动加载常量Apps时检测到循环依赖当我使用多线程时。下面是我的代码。为什么会这样？我尝试多线程的原因是因为我正在编写一个HTML抓取应用程序。对Nokogiri::HTML(open())的调用是一个同步阻塞调用，需要1秒才能返回，我有100,000多个页面要访问，所以我试图运行多个线程来解决这个问题。有更好的方法吗？classToolsController0)app.website=array.join(',')putsapp.websiteelseapp.website="NONE"endapp.saveapps=Apps.order("
ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2
我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr
ruby - Rails 3 的 RGB 颜色选择器 - 2
状态:我正在构建一个应用程序，其中需要一个可供用户选择颜色的字段，该字段将包含RGB颜色代码字符串。我已经测试了一个看起来很漂亮但效果不佳的。它是“挑剔的颜色”，并托管在此存储库中:https://github.com/Astorsoft/picky-color.在这里我打开一个关于它的一些问题的问题。问题:请建议我在Rails3应用程序中使用一些颜色选择器。最佳答案也许页面上的列表jQueryUIDevelopment:ColorPicker为您提供开箱即用的产品。原因是jQuery现在包含在Rails3应用程序中，因此使用基
使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
git使用常见问题（提交代码，合并冲突） - 2
文章目录git常用命令（简介，详细参数往下看）Git提交代码步骤gitpullgitstatusgitaddgitcommitgitpushgit代码冲突合并问题方法一：放弃本地代码方法二：合并代码常用命令以及详细参数gitadd将文件添加到仓库：gitdiff比较文件异同gitlog查看历史记录gitreset代码回滚版本库相关操作远程仓库相关操作分支相关操作创建分支查看分支：gitbranch合并分支：gitmerge删除分支：gitbranch-ddev查看分支合并图：gitlog–graph–pretty=oneline–abbrev-commit撤消某次提交git用户名密码相关配置g
sql - 查询忽略时间戳日期的时间范围 - 2
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。最佳答案您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时
ruby - 获取数组中的值并最小化某个类属性的最优雅的方法是什么？ - 2
假设我有以下类(class):classPersondefinitialize(name,age)@name=name@age=ageenddefget_agereturn@ageendend我有一组Person对象。是否有一种简洁的、类似于Ruby的方法来获取最小(或最大)年龄的人？如何根据它对它们进行排序？最佳答案这样做会:people_array.min_by(&:get_age)people_array.max_by(&:get_age)people_array.sort_by(&:get_age)
ruby - 鸭子输入字符串、符号和数组的优雅方式？ - 2
这是针对我无法破坏的现有公共(public)API，但我确实希望对其进行扩展。目前，该方法采用字符串或符号或任何其他在作为第一个参数传递给send时有意义的内容我想添加发送字符串、符号等列表的功能。我可以只使用is_a吗？数组，但还有其他发送列表的方法，这不是很像ruby。我将调用列表中的map，所以第一个倾向是使用respond_to?:map。但是字符串也会响应:map，所以这行不通。最佳答案如何将它们全部视为数组？String的行为与仅包含String的Array相同:deffoo(obj,arg)[*arg].eac

mysql - 依赖于选择查询的组的最常见值

有关mysql - 依赖于选择查询的组的最常见值的更多相关文章

随机推荐