[es] - MySql upit sa procentima tačnih odgovora

zlatko
Zlatko Mićić
Užice

Član broj: 907
Poruke: 100
195.216.81.*

Sajt: www.ue.co.rs

Profil

MySql upit sa procentima tačnih odgovora

^{20.04.2012. u 15:59 - pre 146 meseci}

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.

SlaveNewWorld

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs

+2377 Profil

Re: MySql upit sa procentima tačnih odgovora

^{20.04.2012. u 19:51 - pre 146 meseci}

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

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Odgovor na temu

zlatko
Zlatko Mićić
Užice

Član broj: 907
Poruke: 100
*.adslplus.ch.

Sajt: www.ue.co.rs

Profil

Re: MySql upit sa procentima tačnih odgovora

^{21.04.2012. u 21:01 - pre 146 meseci}

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 :)

SlaveNewWorld

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs

+2377 Profil

Re: MySql upit sa procentima tačnih odgovora

^{22.04.2012. u 05:22 - pre 146 meseci}

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 ?

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Odgovor na temu