是否有任何 MySQL 基准测试工具？[关闭]

Question

DBeck

Asked: 2016-07-18 04:48:07 +0800 CST2016-07-18 04:48:07 +0800 CST 2016-07-18 04:48:07 +0800 CST

使用多个子查询的加权平均值对行进行排名

772

所以我想做的是根据学生的加权平均值对学生进行排名，加权平均值将通过根据类型（无论是“考试”还是“非考试”）分离成绩来计算。然后将两者的平均值相加得到总数将用于对学生进行排名。

我使用了下面的查询，但它没有使用正确的平均值，因为它没有正确地对类型进行分组。例如..它应该得到所有非考试成绩的平均值，它还包括考试成绩：

SELECT * FROM 
(SELECT student_id, avg, exam,(exam) + (avg) as total, (@rn := @rn + 1) AS rank
FROM (SELECT student_id, 
((SELECT AVG(grade) where grades.type != 'exam')/100 * 40 ) as avg, 
((SELECT AVG(grade) where type = 'exam')/100 * 60 ) as exam FROM grades 
WHERE class_id = 1 AND subject_id = 1 GROUP BY student_id ) agg 
CROSS JOIN (SELECT @rn := 0) CONST ORDER BY total desc) t

见下表截图：

请参阅下面的查询以创建表：

表的表结构`grades`

CREATE TABLE `grades` (
  `grade_id` int(11) NOT NULL,
  `assessment_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `grade` double NOT NULL,
  `teacher_comment` text,
  `type` enum('Home Work','Class','Unit Test','Assignment','Exam') DEFAULT NULL,
  `term` enum('1','2','3') DEFAULT NULL,
  `sch_year` year(4) NOT NULL,
  `entered_by` int(11) DEFAULT NULL,
  `enter_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `grades`
--

INSERT INTO `grades` (`grade_id`, `assessment_id`, `subject_id`, `student_id`, `class_id`, `grade`, `teacher_comment`, `type`, `term`, `sch_year`, `entered_by`, `enter_date`) VALUES
(1, 1, 3, 10001, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:04:38'),
(5, 1, 3, 10001, 2, 80, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:12:17'),
(6, 1, 3, 10002, 2, 60, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:12:17'),
(7, 1, 3, 10001, 2, 80, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'),
(8, 1, 3, 10002, 2, 75, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'),
(9, 1, 3, 10003, 2, 95, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'),
(10, 1, 3, 10001, 2, 85, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:53'),
(11, 1, 3, 10002, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:53'),
(12, 1, 3, 10003, 2, 90, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:54'),
(13, 1, 3, 10001, 2, 98, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'),
(14, 1, 3, 10002, 2, 95, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'),
(15, 1, 3, 10003, 2, 90, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'),
(16, 1, 3, 10001, 2, 96, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'),
(17, 1, 3, 10002, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'),
(18, 1, 3, 10003, 2, 86, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'),
(22, 3, 4, 10001, 2, 55, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'),
(23, 3, 4, 10002, 2, 75, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'),
(24, 3, 4, 10003, 2, 85, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'),
(25, 4, 3, 10001, 2, 85, NULL, 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'),
(26, 4, 3, 10002, 2, 90, 'Test', 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'),
(27, 4, 3, 10003, 2, 65, NULL, 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'),
(28, 5, 3, 10001, 2, 96, NULL, 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'),
(29, 5, 3, 10002, 2, 90, 'Test', 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'),
(30, 5, 3, 10003, 2, 98, NULL, 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'),
(31, 6, 2, 10001, 2, 55, NULL, 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'),
(32, 6, 2, 10002, 2, 65, 'Test', 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'),
(33, 6, 2, 10003, 2, 60, NULL, 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'),
(34, 10, 3, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'),
(35, 10, 3, 10002, 2, 85, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'),
(36, 10, 3, 10003, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'),
(37, 9, 1, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'),
(38, 9, 1, 10002, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'),
(39, 9, 1, 10003, 2, 93, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'),
(40, 12, 2, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'),
(41, 12, 2, 10002, 2, 96, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'),
(42, 12, 2, 10003, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'),
(43, 11, 3, 10001, 2, 85, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'),
(44, 11, 3, 10002, 2, 88, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'),
(45, 11, 3, 10003, 2, 92, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'),
(46, 13, 4, 10001, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(47, 13, 4, 10002, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(48, 13, 4, 10003, 2, 52, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(49, 13, 4, 10004, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(50, 13, 4, 10005, 2, 74, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(51, 13, 4, 10006, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(52, 13, 4, 10007, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(53, 13, 4, 10008, 2, 74, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(54, 13, 4, 10009, 2, 58, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(55, 13, 4, 10010, 2, 97, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(56, 13, 4, 10011, 2, 88, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(57, 13, 4, 10024, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(58, 13, 4, 10012, 2, 88, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(59, 13, 4, 10013, 2, 89, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(60, 13, 4, 10014, 2, 54, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(61, 13, 4, 10015, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(62, 13, 4, 10016, 2, 86, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(63, 13, 4, 10017, 2, 57, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(64, 13, 4, 10018, 2, 89, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(65, 13, 4, 10019, 2, 93, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(66, 13, 4, 10020, 2, 99, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(67, 13, 4, 10021, 2, 72, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(68, 13, 4, 10022, 2, 100, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(69, 13, 4, 10023, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(70, 13, 4, 10025, 2, 95, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(71, 13, 4, 10026, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(72, 13, 4, 10027, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(73, 14, 5, 10001, 2, 85, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(74, 14, 5, 10002, 2, 53, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(75, 14, 5, 10003, 2, 21, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(76, 14, 5, 10004, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(77, 14, 5, 10005, 2, 96, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(78, 14, 5, 10006, 2, 55, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(79, 14, 5, 10007, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(80, 14, 5, 10008, 2, 89, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(81, 14, 5, 10009, 2, 93, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(82, 14, 5, 10010, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(83, 14, 5, 10011, 2, 57, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(84, 14, 5, 10024, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(85, 14, 5, 10012, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(86, 14, 5, 10013, 2, 80, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(87, 14, 5, 10014, 2, 97, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(88, 14, 5, 10015, 2, 95, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(89, 14, 5, 10016, 2, 45, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(90, 14, 5, 10017, 2, 77, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(91, 14, 5, 10018, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(92, 14, 5, 10019, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(93, 14, 5, 10020, 2, 87, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(94, 14, 5, 10021, 2, 57, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(95, 14, 5, 10022, 2, 85, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(96, 14, 5, 10023, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(97, 14, 5, 10025, 2, 82, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(98, 14, 5, 10026, 2, 78, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(99, 14, 5, 10027, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(100, 15, 5, 10001, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(101, 15, 5, 10002, 2, 90, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(102, 15, 5, 10003, 2, 99, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(103, 15, 5, 10004, 2, 55, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(104, 15, 5, 10005, 2, 85, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(105, 15, 5, 10006, 2, 87, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(106, 15, 5, 10007, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(107, 15, 5, 10008, 2, 58, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(108, 15, 5, 10009, 2, 98, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(109, 15, 5, 10010, 2, 75, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(110, 15, 5, 10011, 2, 77, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(111, 15, 5, 10024, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(112, 15, 5, 10012, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(113, 15, 5, 10013, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(114, 15, 5, 10014, 2, 79, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(115, 15, 5, 10015, 2, 98, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(116, 15, 5, 10016, 2, 83, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(117, 15, 5, 10017, 2, 80, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(118, 15, 5, 10018, 2, 84, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(119, 15, 5, 10019, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(120, 15, 5, 10020, 2, 89, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(121, 15, 5, 10021, 2, 78, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(122, 15, 5, 10022, 2, 89, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(123, 15, 5, 10023, 2, 87, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(124, 15, 5, 10025, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(125, 15, 5, 10026, 2, 99, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(126, 15, 5, 10027, 2, 95, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(127, 16, 1, 10028, 1, 85, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'),
(128, 16, 1, 10029, 1, 88, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'),
(129, 16, 1, 10030, 1, 89, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'),
(130, 17, 1, 10028, 1, 90, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18'),
(131, 17, 1, 10029, 1, 95, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18'),
(132, 17, 1, 10030, 1, 96, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `grades`
--
ALTER TABLE `grades`
  ADD PRIMARY KEY (`grade_id`),
  ADD KEY `subject_id` (`subject_id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `class_id` (`class_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `grades`
--
ALTER TABLE `grades`
  MODIFY `grade_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=133;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `grades`
--
ALTER TABLE `grades`
  ADD CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

2 个回答

Voted

dnoeth · Answer 1 · 2016-07-18T08:47:23+08:00

Best Answer

dnoeth

2016-07-18T08:47:23+08:002016-07-18T08:47:23+08:00

您发布的查询实际上没有语法错误地运行？

您需要像这样的 CASE 的平均值：

SELECT * 
FROM 
 ( SELECT student_id, avg, 
      exam,(exam) + (avg) as total,
      (@rn := @rn + 1)  AS rank
   FROM
    ( SELECT student_id, 
         AVG(CASE WHEN type <> 'exam' THEN grade END) / 100 * 40 as avg, 
         AVG(CASE WHEN type  = 'exam' THEN grade END) / 100 * 60 as exam 
       FROM grades 
       WHERE class_id = 1
         AND subject_id = 1
       GROUP BY student_id
    ) agg 
   CROSS JOIN (SELECT @rn := 0) CONST 
   ORDER BY total desc
 ) t

2

DBeck · Answer 2 · 2016-07-19T17:35:53+08:00

DBeck

2016-07-19T17:35:53+08:002016-07-19T17:35:53+08:00

这是问题的解决方案：

SELECT * FROM

(

SELECT student_id, avg, exam,(exam) + (avg) as total, (@rn := @rn + 1) AS rank 
FROM 
(SELECT student_id, 
    (
        SELECT AVG(g.grade)  / 100 * 40 
        FROM grades AS g
        WHERE g.type != 'Exam' AND  g.student_id = grades.student_id
    ) as avg,
    (
        SELECT AVG(s.grade)/100 * 60 
        FROM grades AS s
        WHERE s.type = 'Exam' AND  s.student_id = grades.student_id
    ) as exam 
FROM grades 
WHERE class_id = 2 AND subject_id = 1 GROUP BY student_id ) agg
CROSS JOIN (SELECT @rn := 0) CONST ORDER BY total desc

) 吨

0

使用多个子查询的加权平均值对行进行排名

表的表结构`grades`

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

使用多个子查询的加权平均值对行进行排名

表的表结构grades

2 个回答

相关问题

表的表结构`grades`