COUNT un GROUP BY sadarbība

[martins256]

ir 3 tabulas:

+------------+
| masinas_id |
+------------+
| 	1 |
+------------+

+-----------+------------+
| durvju_id | masinas_id |
+-----------+------------+
| 	1 | 	1 |
| 	2 | 	1 |
+-----------+------------+

+-----------+------------+
| riepas_id | masinas_id |
+-----------+------------+
| 	1 | 	1 |
| 	2 | 	1 |
| 	3 | 	1 |
| 	4 | 	1 |
+-----------+------------+

 

Vēlos iegūt rezultātu, kur būtu redzams mašīnas id, durvju skaits, riepu skaits

 

 

SELECT masina.masinas_id, COUNT( riepas.riepas_id ) , COUNT( durvis.durvju_id ) 
FROM masina
LEFT JOIN riepas ON masina.masinas_id = riepas.masinas_id
LEFT JOIN durvis ON masina.masinas_id = durvis.masinas_id

Šis pieprasījums atgriezīs nepareizu rezultātu 1 | 8 | 8

 

SELECT masinas_id,
(SELECT COUNT(*) FROM riepas WHERE masina.masinas_id=riepas.masinas_id),
(SELECT COUNT(*) FROM durvis WHERE masina.masinas_id=durvis.masinas_id)
FROM masina

Šis, atgriezīs pareizu rezultātu, bet man nepatīk tā ideja, ka pie katras mašīnas sanāk apakšpieprasījums

 

SELECT masina.masinas_id,riepas.riepas,durvis.durvis
FROM masina
,(SELECT masinas_id,COUNT(*) AS riepas FROM riepas GROUP BY masinas_id) AS riepas 
,(SELECT masinas_id,COUNT(*) AS durvis FROM durvis GROUP BY masinas_id) AS durvis
WHERE masina.masinas_id=riepas.masinas_id AND masina.masinas_id=durvis.masinas_id

Varbūt šādi ir pareizi?

 

Kāds varētu būt pareizais variants?

Edited March 26, 2010 by martins256

[2easy]

anyway sql serverim jau nekas cits neatliek, kā izskaitīt katrai mašīnai durvis un sagrupēt pēc mašīnas id. un to pašu izdarīt arī ar riepām. tā ka tehniski jau tur pa ļubomu būs tie subselekti. vienīgais, ko vari darīt, lai izvarītos no tādām skaitīšanām, ir ieviest papildus laukus tabulā masina: durvju_skaits, riepu_skaits (imho šo loģiskāk būtu saukt par riteņu skaitu :D), un tiklīdz mainās rindu skaits apakštabulās "durvis" vai "riepas", tā updeito attiecīgo skaita lauku arī galvenajā tabulā. tad nevajadzēs muhļīties ap kkādiem subselektiem un selekti būs daudz ātrāki :))

 

vsp selektus šādi ir ieteicams optimizēt (ieteica 2easy :D:D:D)

Edited March 26, 2010 by 2easy

[Aleksejs]

SELECT masina.masinas_id, COUNT( riepas.riepas_id ) , COUNT( durvis.durvju_id )

FROM masina

LEFT JOIN riepas ON masina.masinas_id = riepas.masinas_id

LEFT JOIN durvis ON masina.masinas_id = durvis.masinas_id

GROUP BY masinas_id;

 

Šitā nebūs? (nav kur pārbaudīt un domājamvieta atsākās domāt)

[2easy]

nē, tā nebūs ;)

jo viņam ir 2x dažādi skaiti... bet GROUP BY attiecas uz vienu. izvēlies kuru :D:D:D

 

ohh, adminiem ir tā priekšrocība ka var editot un neuzrādās, ka viņi to ir darījuši, bet anyway tā nebūs! :P

tā tu iegūsti pirmo rezultātu: 1 | 8 | 8

jo mašīnas id ir tikai viens, līdz ar to viss dekarta reizinājums 1 x 2 x 4 = 8 tiek sasummēts/sagrupēts kopā

kas arī bija jāpierāda :D:D:D

Edited March 26, 2010 by 2easy

[Aleksejs]

Hmmm varbūt ar DISTINCT kaut ko var panākt...

[2easy]

jaa jaa sākās minēšana :D:D:D

 

vai nu subselekti, vai fiziski lauki ar skaitiem

protams, būtu ļoti priecīgs, ja uzzīmētos Gints Plivna un pierādītu pretējo :))

Edited March 26, 2010 by 2easy

[Aleksejs]

Tu labāk būtu pa šo laiku padomājis pie risinājuma, a? :P Nevis mani pieGdienas vakarā kaitinājis! :(

[2easy]

oi oi oi kādi mēs dusmīgi :D:D:D

[martins256]

DISTINCT nebūs. To jau čatā prasīju un beigās pats pārliecinājos.

[2easy]

hmm, un ko tu domā par skaitu glabāšanu atsevišķos laukos?

[Gints Plivna]

Vai kāds minēja manu vārdu? ;)

 

Ko lai saka, vajag paeksperimentēt, tas taču nemaz nav tik grūti. Pie tam lūdzu citreiz jutātājiem iedot create table un insert skriptus, ka jau to reiz rakstīju šeit. Un vispār Aleksejs jau gandrīz nonāca līdz pareizajam variantam, un GROUP BY attiecas uz to, pēc kā vajag grupēt. Cik tai piekarina klāt agregātfunkcijas vairs galīgi nav svarīgi.

 

mysql> create table masina (masinas_id int not null primary key);
Query OK, 0 rows affected (0.13 sec)

mysql> create table durvis (durvju_id int not null primary key, masinas_id int n
ot null);
Query OK, 0 rows affected (0.14 sec)

mysql> create table riepas (riepas_id int not null primary key, masinas_id int n
ot null);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into masina values (1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into durvis values (1, 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into durvis values (2, 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into riepas values (2, 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into riepas values (1, 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into riepas values (3, 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into riepas values (4, 1);
Query OK, 1 row affected (0.03 sec)

Ja uzliek pareizu indeksu, tad nav nekāda pat temporāra tabula vai filesort operācija, kā tas redzams no izpildes plāna. Tas ir vienkārši prasts savienojums (join).

mysql> create index durvis_masinas_idx1 on durvis(masinas_id, durvju_id);
Query OK, 2 rows affected (0.38 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create index riepas_masinas_idx1 on riepas(masinas_id, riepas_id);
Query OK, 4 rows affected (0.39 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain
   -> SELECT masina.masinas_id, COUNT( distinct riepas.riepas_id ) , COUNT( dis
tinct durvis.durvju_id )
   -> FROM masina
   -> LEFT JOIN riepas ON masina.masinas_id = riepas.masinas_id
   -> LEFT JOIN durvis ON masina.masinas_id = durvis.masinas_id
   -> group by masina.masinas_id;
+----+-------------+--------+-------+---------------------+---------------------
+---------+------------------------+------+-------------+
| id | select_type | table  | type  | possible_keys       | key
| key_len | ref                    | rows | Extra       |
+----+-------------+--------+-------+---------------------+---------------------
+---------+------------------------+------+-------------+
|  1 | SIMPLE      | masina | index | NULL                | PRIMARY
| 4       | NULL                   |    1 | Using index |
|  1 | SIMPLE      | riepas | ref   | riepas_masinas_idx1 | riepas_masinas_idx1
| 4       | test.masina.masinas_id |    2 | Using index |
|  1 | SIMPLE      | durvis | ref   | durvis_masinas_idx1 | durvis_masinas_idx1
| 4       | test.masina.masinas_id |    1 | Using index |
+----+-------------+--------+-------+---------------------+---------------------
+---------+------------------------+------+-------------+
3 rows in set (0.00 sec)

Ir acīmtredzami, ka šeit būs vismaz 2 atvasinātas tabulas un vispār soļi vairāk nekā, sākotnējā

mysql> explain
   -> SELECT masina.masinas_id,riepas.riepas,durvis.durvis
   -> FROM masina
   -> ,(SELECT masinas_id,COUNT(*) AS riepas FROM riepas GROUP BY masinas_id) A
S riepas
   -> ,(SELECT masinas_id,COUNT(*) AS durvis FROM durvis GROUP BY masinas_id) A
S durvis
   -> WHERE masina.masinas_id=riepas.masinas_id AND masina.masinas_id=durvis.ma
sinas_id;
+----+-------------+------------+--------+---------------+---------------------+
---------+-------+------+-------------+
| id | select_type | table      | type   | possible_keys | key                 |
key_len | ref   | rows | Extra       |
+----+-------------+------------+--------+---------------+---------------------+
---------+-------+------+-------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL                |
NULL    | NULL  |    1 |             |
|  1 | PRIMARY     | <derived3> | system | NULL          | NULL                |
NULL    | NULL  |    1 |             |
|  1 | PRIMARY     | masina     | const  | PRIMARY       | PRIMARY             |
4       | const |    1 | Using index |
|  3 | DERIVED     | durvis     | index  | NULL          | durvis_masinas_idx1 |
8       | NULL  |    2 | Using index |
|  2 | DERIVED     | riepas     | index  | NULL          | riepas_masinas_idx1 |
8       | NULL  |    4 | Using index |
+----+-------------+------------+--------+---------------+---------------------+
---------+-------+------+-------------+
5 rows in set (0.00 sec)

 

Gints Plivna

http://datubazes.wordpress.com

[2easy]

oo, beizot pro arī ierunājās! :))

[Gints Plivna]

DISTINCT nebūs. To jau čatā prasīju un beigās pats pārliecinājos.

Hmmmm :O

mysql> SELECT masina.masinas_id, COUNT( distinct riepas.riepas_id ) , COUNT( dis
tinct durvis.durvju_id )
   -> FROM masina
   -> LEFT JOIN riepas ON masina.masinas_id = riepas.masinas_id
   -> LEFT JOIN durvis ON masina.masinas_id = durvis.masinas_id
   -> group by masina.masinas_id;
+------------+------------------------------------+-----------------------------
-------+
| masinas_id | COUNT( distinct riepas.riepas_id ) | COUNT( distinct durvis.durvj
u_id ) |
+------------+------------------------------------+-----------------------------
-------+
|          1 |                                  4 |
    2 |
+------------+------------------------------------+-----------------------------
-------+
1 row in set (0.00 sec)

 

Gints Plivna

http://datubazes.wordpress.com

[2easy]

nē nu, ko lai tur vēl pasaka. vnk ģeniāli!!! :))

 

Gints Plivna +1000000000

 

 

tiešām nebiju iedomājies, ka iekš COUNT() var iebarot arī DISTINCT...

un tādu it kā pašsaprotamu funkciju jau manuālī taču neskatās ;)

 

Aleksejs jau 100pt tgd teiks, ka viņš to distinct gribēja likt iekš count(), bet es viņu no tā atrunāju :D:D:D

Edited March 26, 2010 by 2easy

[2easy]

for the sake of spam, varu vienīgi vēl iepostot savu php testu, jo pirms tam jau biju uztaisījis example un tabulu struktūru (triviāli jau ir, kas labi priekš piemēra), bet līdz tādam DISTINCT pielietojumam vnk neaizdomājos...

 

tiešām respect, Gint

 

function dbconn($sSrv, $sDb, $sUsr, $sPw) {  // inicializē mysql connection
@mysql_connect($sSrv, $sUsr, $sPw) or exit('<b>mysql_connect() error ' . mysql_errno() . ':</b> ' . mysql_error());
mysql_select_db($sDb) or exit('<b>mysql_select_db() error ' . mysql_errno() . ':</b> ' . mysql_error());
go('SET NAMES utf8');
}
function go($sSql) {  // izpilda mysql query
$h = mysql_query($sSql) or exit('<b>mysql_query() error ' . mysql_errno() . ':</b> ' . mysql_error() . '<br /><b>query:</b> ' . substr($sSql, 0, 1000));
return $h;
}
function esql($sSql) {  // echo sql & its returned data
$h = go($sSql);

echo $sSql . '<table cellpadding="1" cellspacing="0" border="1">';
while ($o = mysql_fetch_field($h)) $a[] = $o->name;
echo '<tr><th>' . implode('</th><th>', $a) . '</th></tr>';

$f = create_function('$v', 'return is_null($v) ? "NULL" : $v;');
while ($r = mysql_fetch_row($h)) echo '<tr><td>' . implode('</td><td>', array_map($f, $r)) . '</td></tr>';
echo '</table><br />';
}

dbconn('localhost', 'test', 'root', '123');

go('DROP TABLE IF EXISTS masina, durvis, riepas');
go('CREATE TABLE masina (masinas_id int AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM');
go('CREATE TABLE durvis (durvju_id int AUTO_INCREMENT PRIMARY KEY, masinas_id int NOT NULL) ENGINE=MyISAM');
go('CREATE TABLE riepas (riepas_id int AUTO_INCREMENT PRIMARY KEY, masinas_id int NOT NULL) ENGINE=MyISAM');
go('INSERT masina () VALUES ()');
go('INSERT durvis (masinas_id) VALUES (1),(1)');
go('INSERT riepas (masinas_id) VALUES (1),(1),(1),(1)');

esql('SELECT masina.masinas_id, COUNT(DISTINCT durvis.durvju_id), COUNT(DISTINCT riepas.riepas_id)
FROM masina
LEFT JOIN durvis ON masina.masinas_id = durvis.masinas_id
LEFT JOIN riepas ON masina.masinas_id = riepas.masinas_id
GROUP BY masina.masinas_id');