- android - RelativeLayout 背景可绘制重叠内容
- android - 如何链接 cpufeatures lib 以获取 native android 库?
- java - OnItemClickListener 不起作用,但 OnLongItemClickListener 在自定义 ListView 中起作用
- java - Android 文件转字符串
我有一个包含以下表和字段的 MySQL 数据库:
学生表和类(class)表在 id(主键)上建立索引。成绩表以 id(主键)和 student_id、class_id 和成绩为索引。
我需要构建一个查询,在给定类(class) ID 的情况下,给出所有其他类(class)的列表以及在其他类(class)中得分更高的学生人数。
基本上,给定成绩表中的以下数据:
student_id | class_id | grade
--------------------------------------
1 | 1 | 87
1 | 2 | 91
1 | 3 | 75
2 | 1 | 68
2 | 2 | 95
2 | 3 | 84
3 | 1 | 76
3 | 2 | 88
3 | 3 | 71
用类 ID 1 查询应该产生:
class_id | total
-------------------
2 | 3
3 | 1
理想情况下,我希望它能在几秒钟内执行,因为我希望它成为网络界面的一部分。
我遇到的问题是,在我的数据库中,我有超过 1300 个类(class)和 160,000 名学生。我的成绩表有将近 1500 万行,因此,查询需要很长时间才能执行。
这是我到目前为止尝试过的以及每个查询所用的时间:
-- I manually stopped execution after 2 hours
SELECT c.id, COUNT(*) AS total
FROM classes c
INNER JOIN grades a ON a.class_id = c.id
INNER JOIN grades b ON b.grade < a.grade AND
a.student_id = b.student_id AND
b.class_id = 1
WHERE c.id != 1 AND
GROUP BY c.id
-- I manually stopped execution after 20 minutes
SELECT c.id,
(
SELECT COUNT(*)
FROM grades g
WHERE g.class_id = c.id AND g.grade > (
SELECT grade
FROM grades
WHERE student_id = g.student_id AND
class_id = 1
)
) AS total
FROM classes c
WHERE c.id != 1;
-- 1 min 12 sec
CREATE TEMPORARY TABLE temp_blah (student_id INT(11) PRIMARY KEY, grade INT);
INSERT INTO temp_blah SELECT student_id, grade FROM grades WHERE class_id = 1;
SELECT o.id,
(
SELECT COUNT(*)
FROM grades g
INNER JOIN temp_blah t ON g.student_id = t.student_id
WHERE g.class_id = c.id AND t.grade < g.grade
) AS total
FROM classes c
WHERE c.id != 1;
-- Same thing but with joins instead of a subquery - 1 min 54 sec
SELECT c.id,
COUNT(*) AS total
FROM classes c
INNER JOIN grades g ON c.id = p.class_id
INNER JOIN temp_blah t ON g.student_id = t.student_id
WHERE c.id != 1
GROUP BY c.id;
我还考虑过创建一个二维表,以学生为行,以类(class)为列,但是我发现了两个问题:
我还考虑过将这些计算作为后台作业执行并将结果存储在某处,但为了使信息保持最新(必须),每次创建或更新学生、类(class)或成绩记录时都需要重新计算它们。
有人知道构造此查询的更有效方法吗?
编辑:创建表语句:
CREATE TABLE `classes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1331 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=160803 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$
CREATE TABLE `grades` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
`grade` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_grades_on_student_id` (`student_id`),
KEY `index_grades_on_class_id` (`class_id`),
KEY `index_grades_on_grade` (`grade`)
) ENGINE=InnoDB AUTO_INCREMENT=15507698 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$
最有效查询(1 分 12 秒)的解释输出:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | PRIMARY | c | range | PRIMARY | PRIMARY | 4 | | 683 | Using where; Using index
2 | DEPENDENT SUBQUERY | g | ref | index_grades_on_student_id,index_grades_on_class_id,index_grades_on_grade | index_grades_on_class_id | 5 | mydb.c.id | 830393 | Using where
2 | DEPENDENT SUBQUERY | t | eq_ref | PRIMARY | PRIMARY | 4 | mydb.g.student_id | 1 | Using where
另一个编辑 - 解释 sgeddes 建议的输出:
+----+-------------+------------+--------+---------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 14953992 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | Using filesort |
| 2 | DERIVED | G | ALL | NULL | NULL | NULL | NULL | 15115388 | |
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+----------+----------------------------------------------+
最佳答案
我认为这对使用 SUM
和 CASE
的你应该有效:
SELECT C.Id,
SUM(
CASE
WHEN G.Grade > C2.Grade THEN 1 ELSE 0
END
)
FROM Class C
INNER JOIN Grade G ON C.Id = G.Class_Id
LEFT JOIN (
SELECT Grade, Student_Id, Class_Id
FROM Class
JOIN Grade ON Class.Id = Grade.Class_Id
WHERE Class.Id = 1
) C2 ON G.Student_Id = C2.Student_Id
WHERE C.Id <> 1
GROUP BY C.Id
--编辑--
针对您的评论,这是另一种应该更快的尝试:
SELECT
Class_Id,
SUM(CASE WHEN Grade > minGrade THEN 1 ELSE 0 END)
FROM
(
SELECT
Student_Id,
@classToCheck:=
IF(G.Class_Id = 1, Grade, @classToCheck) minGrade ,
Class_Id,
Grade
FROM Grade G
JOIN (SELECT @classToCheck:= 0) t
ORDER BY Student_Id, IF(Class_Id = 1, 0, 1)
) t
WHERE Class_Id <> 1
GROUP BY Class_ID
关于mysql - 构造二维 MySQL 查询的最有效方法是什么?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15036871/
谁能解释一下原因: (define a (lambda() (cons a #f))) (car (a)) ==> procedure ((car (a))) ==> (procedure . #f)
这是 PyBrain 网站的摘录。我了解大部分正在发生的事情,但是一行让我完全难住了。我以前从未在 python 代码中看到过这样的东西。这是整个循环,对于上下文: for c in [0,
我是gradle / groovy的新手。我想创建将做一些事情的自定义任务。我的第一个问题是任务完成时该如何做?我可以覆盖doFirst / doLast闭包吗?也许我可以重写某些在开始和结束时都会执
我刚刚开始评估 MS 企业库。他们使用以下指令来获取实例: var customerDb = EnterpriseLibraryContainer.Current.GetInstance("C
这是我的 if else Ansible 逻辑.. - name: Check certs exist stat: path=/etc/letsencrypt/live/{{ rootDomain
我正在使用construct 2.8 对一些失传已久的 Pascal 程序创建的一些文件的 header 进行逆向工程。 header 由许多不同的记录组成,其中一些是可选的,我不确定顺序是否固定。
我在将 getchar() 的输入放入 char *arr[] 数组时遇到问题。我这样做的原因是因为输入数据(将是一个带有命令行参数的文件)将存储在一个 char 指针数组中以传递给 execvp 函
通常我们不能约束类型参数 T派生自密封类型(例如 struct 类型)。这将毫无意义,因为只有一种类型适合,因此不需要泛型。所以约束如下: where T : string 或: where T :
关闭。此题需要details or clarity 。目前不接受答案。 想要改进这个问题吗?通过 editing this post 添加详细信息并澄清问题. 已关闭 9 年前。 Improve th
#include using namespace std; class A { private: int m_i; friend int main(int argc, char cons
这个问题在这里已经有了答案: Are there legitimate uses for JavaScript's "with" statement? (33 个答案) 关闭 9 年前。 我有这个代
在this answer我看到了下一个 Bash 结构。 yes "$(< file.txt)" 什么意思 "$(< file.txt)" ? 我明白了 命令替换 - $(command)用命令的结
if (a == 1) //do something else if (a == 2) //do something else if (a == 3) //do somethi
关于构造的快速简单的问题。 我有以下用于将项目添加到 ListView 的代码。 ListViewItem item = new ListViewItem(); item.Text = file; i
我想使用 std::vector 来控制给定的内存。首先,我很确定这不是好的做法,但好奇心占了上风,无论如何我都想知道如何做到这一点。 我遇到的问题是这样的方法: vector getRow(unsi
下面显示了一段简单的javascript: var mystring = ("random","ignored","text","h") + ("ello world") 这个字符串会生成 hello
在 Java 中,创建对象的标准方法是使用 MyClass name = new MyClass(); 我也经常看到构造 new MyClass() { /*stuff goes in here*/
我正在编写 C++ ndarray 类。我需要动态大小和编译时大小已知的数组(分别分配自由存储和分配堆栈)。我想支持从嵌套的 std::initializer_list 进行初始化。 动态大小的没问题
我正在将一个项目从 Visual Studio 2005 转换为 Visual Studio 2008,并提出了上述结构。 using Castle.Core.Resource; using Cast
我想知道我在这里的想法是否正确,我主要针对接口(interface)进行编程,所以我想知道下面的类是否应该通过 DI 注入(inject),或者我应该自己实例化一个类... 注意:这些服务保存在我的核
我是一名优秀的程序员,十分优秀!