MySql upit sa procentima tačnih odgovora

[ zlatko @ 20.04.2012. 15:59 ] @

Pokušavam da u PHP-u napravim listu studenata i njihovu efikasnost na testovima.
U MySql bazi imam sledeće tabele

users - tabela studenata

courses - tabela testova

Code:
id | name
---+------
1 | Test 1
2 | Test 2

questions - tabela sa pitanjima testova. Red cours_id označava kom testu pitanje pripada

Code:
id | cours_id | question
---+----------+--------------
1  | 1        | Test 1 - pitanje 1
2  | 1        | Test 1 - pitanje 2
3  | 1        | Test 1 - pitanje 3
4  | 1        | Test 1 - pitanje 4
5  | 2        | Test 2 - pitanje 1
6  | 2        | Test 2 - pitanje 2
7  | 2        | Test 2 - pitanje 3
8  | 2        | Test 2 - pitanje 4

cours_invitations - svaki student dobija pozivnicu za test. Red user_id predstavlja ID pozvanog korisnika.
cours_id predstavlja ID testa koji bi student trebao da uradi.
Kada red status ima vrednost 0 to znači da student nije počeo sa testom (na čekanju), a ako ima verdnost 1 to znači da je student započeo ili završio test.

Code:
id | user_id | cours_id | status
---+---------+----------+------
1  | 1       | 1        | 1
2  | 1       | 2        | 0
3  | 2       | 1        | 0
4  | 3       | 1        | 1
5  | 4       | 1        | 1
6  | 4       | 2        | 1

Primer: Petar i Ana su pozvani da urade Test 1 i Test 2, Marija i Marko su pozvani da urade samo Test 1. Petar je uradio Test 1 ali ne i Test 2, Marija nije uradila nijedan test, a Marko je uradio Test 1

courses_stats - su statistike testova koje su studenti uradili. Status predstavlja tačnost odgovora. 0 je za netačan, a 1 za tačan odgovor.

Code:
id | user_id | question_id | status
---+---------+-------------+-------
1  | 1       | 1           | 1
2  | 1       | 2           | 1
3  | 1       | 3           | 0
4  | 2       | 1           | 1
5  | 2       | 2           | 1
6  | 2       | 3           | 1
7  | 2       | 4           | 1
8  | 4       | 1           | 1
9  | 4       | 2           | 1
10 | 4       | 3           | 0
11 | 4       | 4           | 0
12 | 4       | 5           | 1
13 | 4       | 6           | 1

Primer: Petar je uradio 3 pitanja iz prvog test. Primetićete da nije uradio sva pitanja iz tog testa, a jedno je pritom netačno.
Marko je uradio sva pitanja tačno, a Ana je uradila sva pitanja iz prvog testa (pola netačno) i pola iz drugog teksta (sve tačno).

Potreban mi je upit uz pomoć koga bih dobio imena studenata, procenat završenih kurseva i procenat tačnih odgovora sa mogućnošću sortiranja po tim procentima.
Nešto ovako:

Code:
ime    | zavrsteni testovi | zavrsena pitanja
-------+-------------------+----------------
Marko  |100%               |100%
Ana    |100%               |50%
Petar  |50%                |50%
Marija |0%                 |0%

Uspeo sam da nađem procenat završenih kurseva, ali ne i procenat tačnih odgovora. Znam čak i šta treba da radim samo ne znam kako.

Upit (uz još jednu tabelu koju koristim) trenutno izgleda ovako:

Code:

    SELECT
        `users`.`id`,
        `users`.`name`,
        `groups`.`name` as group_name,
        ROUND(
         100 * count(if(cours_invitations.status = '1', 1, null)) / count(cours_invitations.id)
        ) AS procenat_kurseva
    FROM users
    LEFT JOIN groups ON `users`.`group` = `groups`.`id`
    LEFT JOIN cours_invitations ON `users`.`id` = `cours_invitations`.`user_id`
    group by `users`.`id`

Može li mi neko pomoći sa ovim? Hvala unapred.

[ bogdan.kecman @ 20.04.2012. 19:51 ] @

napravi create table + insert into pa da moze da se proba .. pa ce neko mozda i da se potrudi

[ zlatko @ 21.04.2012. 21:01 ] @

Code:
--
-- Table structure for table `courses`
--

CREATE TABLE IF NOT EXISTS `courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `courses`
--

INSERT INTO `courses` (`id`, `name`) VALUES
(1, 'Test 1'),
(2, 'Test 2');

-- --------------------------------------------------------

--
-- Table structure for table `courses_stats`
--

CREATE TABLE IF NOT EXISTS `courses_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

--
-- Dumping data for table `courses_stats`
--

INSERT INTO `courses_stats` (`id`, `user_id`, `question_id`, `status`) VALUES
(1, 1, 1, 1),
(2, 1, 2, 1),
(3, 1, 3, 0),
(4, 2, 1, 1),
(5, 2, 2, 1),
(6, 2, 3, 1),
(7, 2, 4, 1),
(8, 4, 1, 1),
(9, 4, 2, 1),
(10, 4, 3, 0),
(11, 4, 4, 0),
(12, 4, 5, 1),
(13, 4, 6, 1);

-- --------------------------------------------------------

--
-- Table structure for table `cours_invitations`
--

CREATE TABLE IF NOT EXISTS `cours_invitations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `cours_id` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `cours_invitations`
--

INSERT INTO `cours_invitations` (`id`, `user_id`, `cours_id`, `status`) VALUES
(1, 1, 1, 1),
(2, 1, 2, 0),
(3, 2, 1, 0),
(4, 3, 1, 1),
(5, 4, 1, 1),
(6, 4, 2, 1);

-- --------------------------------------------------------

--
-- Table structure for table `groups`
--

CREATE TABLE IF NOT EXISTS `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `groups`
--

INSERT INTO `groups` (`id`, `name`) VALUES
(1, 'devojke');

-- --------------------------------------------------------

--
-- Table structure for table `questions`
--

CREATE TABLE IF NOT EXISTS `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cours_id` int(11) NOT NULL,
  `question` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `questions`
--

INSERT INTO `questions` (`id`, `cours_id`, `question`) VALUES
(1, 1, 'Test 1 - pitanje 1'),
(2, 1, 'Test 1 - pitanje 2'),
(3, 1, 'Test 1 - pitanje 3'),
(4, 1, 'Test 1 - pitanje 4'),
(5, 2, 'Test 2 - pitanje 1'),
(6, 2, 'Test 2 - pitanje 2'),
(7, 2, 'Test 2 - pitanje 3'),
(8, 2, 'Test 2 - pitanje 4');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  `group` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `group`) VALUES
(1, 'Petar', 0),
(2, 'Marija', 1),
(3, 'Marko', 0),
(4, 'Ana', 1);

Nadam se da će neko pokušati :)

[ bogdan.kecman @ 22.04.2012. 05:22 ] @

zastrasujuce su ti losa imena tabela i atributa :(, pisi imena svega na srpskom, bice ti lakse, ovako nisi konzistentan

Code:

-- sta je koji jadnik popunjavao

mysql> select distinct courses.name, users.name from courses_stats join questions on courses_stats.question_id = questions.id join courses on courses.id = questions.cours_id join users on users.id = courses_stats.user_id;
+--------+--------+
| name   | name   |
+--------+--------+
| Test 1 | Petar  |
| Test 1 | Marija |
| Test 1 | Ana    |
| Test 2 | Ana    |
+--------+--------+
4 rows in set (0.00 sec)

-- koliko % testova je poceo da radi (ili zavrsio)

mysql> select user_id, 100 * sum(status) / count(*) as procenat from cours_invitations group by user_id;
+---------+----------+
| user_id | procenat |
+---------+----------+
|       1 |  50.0000 |
|       2 |   0.0000 |
|       3 | 100.0000 |
|       4 | 100.0000 |
+---------+----------+
4 rows in set (0.00 sec)

mysql> select cours_invitations.user_id, users.name, 100 * sum(cours_invitations.status) / count(*) as procenat from cours_invitations join users on cours_invitations.user_id=users.id group by cours_invitations.user_id;
+---------+--------+----------+
| user_id | name   | procenat |
+---------+--------+----------+
|       1 | Petar  |  50.0000 |
|       2 | Marija |   0.0000 |
|       3 | Marko  | 100.0000 |
|       4 | Ana    | 100.0000 |
+---------+--------+----------+
4 rows in set (0.00 sec)

-- na sta je jadnik pozvan
mysql> select user_id, group_concat(cours_id) from cours_invitations group by user_id;
+---------+------------------------+
| user_id | group_concat(cours_id) |
+---------+------------------------+
|       1 | 1,2                    |
|       2 | 1                      |
|       3 | 1                      |
|       4 | 1,2                    |
+---------+------------------------+
4 rows in set (0.00 sec)

-- koja / koliko ukupno pitanja (na testovima koje je poceo i na testovima koje nije ni poceo) treba da uradi

mysql> select cours_invitations.user_id, group_concat(questions.question) from cours_invitations join questions on cours_invitations.cours_id = questions.cours_id group by cours_invitations.user_id;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_id | group_concat(questions.question)                                                                                                                        |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
|       1 | Test 1 - pitanje 2,Test 2 - pitanje 3,Test 1 - pitanje 3,Test 2 - pitanje 2,Test 1 - pitanje 4,Test 2 - pitanje 4,Test 1 - pitanje 1,Test 2 - pitanje 1 |
|       2 | Test 1 - pitanje 3,Test 1 - pitanje 4,Test 1 - pitanje 1,Test 1 - pitanje 2                                                                             |
|       3 | Test 1 - pitanje 4,Test 1 - pitanje 1,Test 1 - pitanje 2,Test 1 - pitanje 3                                                                             |
|       4 | Test 1 - pitanje 4,Test 1 - pitanje 1,Test 2 - pitanje 2,Test 1 - pitanje 2,Test 2 - pitanje 4,Test 2 - pitanje 1,Test 1 - pitanje 3,Test 2 - pitanje 3 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select cours_invitations.user_id, count(questions.question) from cours_invitations join questions on cours_invitations.cours_id = questions.cours_id group by cours_invitations.user_id;
+---------+---------------------------+
| user_id | count(questions.question) |
+---------+---------------------------+
|       1 |                         8 |
|       2 |                         4 |
|       3 |                         4 |
|       4 |                         8 |
+---------+---------------------------+
4 rows in set (0.00 sec)

-- na koliko je pitanja jadnik odgovorio (tacno + netacno)

mysql> select user_id, count(*) from courses_stats group by user_id;
+---------+----------+
| user_id | count(*) |
+---------+----------+
|       1 |        3 |
|       2 |        4 |
|       4 |        6 |
+---------+----------+
3 rows in set (0.00 sec)

-- procenat tacnih i netacnih (od radjenih)

mysql> select user_id, 100* (select count(*) from courses_stats x where x.user_id=courses_stats.user_id and x.status=1) / count(*) as pogodio, 100* (select count(*) from courses_stats x where x.user_id=courses_stats.user_id and x.status=0) / count(*) as promasio from courses_stats group by user_id;
+---------+----------+----------+
| user_id | pogodio  | promasio |
+---------+----------+----------+
|       1 |  66.6667 |  33.3333 |
|       2 | 100.0000 |   0.0000 |
|       4 |  66.6667 |  33.3333 |
+---------+----------+----------+
3 rows in set (0.00 sec)

-- koliko procenata je "probao"

mysql> select cours_invitations.user_id, 100* (select count(*) from courses_stats cs where cs.user_id = cours_invitations.user_id) / count(questions.question) as radio from cours_invitations join questions on cours_invitations.cours_id = questions.cours_id group by cours_invitations.user_id;
+---------+----------+
| user_id | radio    |
+---------+----------+
|       1 |  37.5000 |
|       2 | 100.0000 |
|       3 |   0.0000 |
|       4 |  75.0000 |
+---------+----------+
4 rows in set (0.00 sec)

jesi to hteo ?

[ zlatko @ 23.04.2012. 13:10 ] @

To je to. Car si! :)

Hvala

Citat:

bogdan.kecman: zastrasujuce su ti losa imena tabela i atributa :(, pisi imena svega na srpskom, bice ti lakse, ovako nisi konzistentan

Uglavnom se trudim da sve radim na engleskom (iako sam ovde imao slovnih grešaka), pošto imam dosta stranih klijenata i kasnije mi je lakše da radim copy/paste.

[ bogdan.kecman @ 23.04.2012. 14:02 ] @

onda koristi klasicna pravila za pisanje imena polja i tabela

1. SVE JEDNINA (posto ti ocigledno mnozina inace ne ide u engleskom)
2. prefix uvek

dakle

user:
user_id
user_name

question:
question_question -- ovde ti je bolje da bude question_name ili question_text
question_id
course_id

course:
course_id
course_name -- isto kao za question, dakle treba da imas konzistentna imena, recimo ili oba da se zovu course_name i question_name ili oba course_text i question_text

course_stat:
course_stat_id
course_stat_status
user_id
question_id

course_invitation:
course_invitation_id
course_invitation_status
user_id
course_id

pogledaj malo istoriju po forumu vec sam ja pisao o imenovanju i klasicnoj nomenklaturi