knifflige SQL

[noxz]

Hallo.
Folgende Situtation: Ich habe eine DB mit folgenden Spatlen
--> ID (id des Datensatzen)
--> DAY (datum im Format tt.mm.jjjj)
--> ta_firma_id_f (ID einer Firma)
--> user (Anzahl der Aufrufe der Firma)
In der DB werden die Aufrufe pro Tag der Firmen gespeichert.
Jetzt wollte ich über eine SQL den Monat mit den häufigsten Zugriffe einer bestimmten Firma haben.

Hier meine SQL, die ich bis jetzt habe.

SELECT sum(user) user, substring(day,4,2) monat, ta_firma_id_f
FROM ta_statistik_day
WHERE `ta_firma_id_f` =2 and substring(day,7,4)=2007
group by substring(day,4,2)

Damit bekomme ich die Summer der Aufrufe pro Monat im Jahr 2007 für die Firma mit der id 2.
Ich hab es schon mit max(sum(user)) probiert. Das funzt aber nicht.

Hat jemand vielleicht eine Idee?

[ghostgambler]

Re: knifflige SQL

SELECT sum(user) user, substring(day,4,2) monat, ta_firma_id_f
FROM ta_statistik_day
WHERE `ta_firma_id_f` =2 and substring(day,7,4)=2007
group by substring(day,4,2)
order by sum(user) DESC
LIMIT 1

[noxz]

hey danke...ich muste zwar die order-klausel nochmal bearbeiten, aber jetzt funzt es. Dank dir.
Hab ich mir wohl etwas zu kompliziert vorgestellt.

SELECT sum(user) user, substring(day,4,2) monat, ta_firma_id_f
FROM ta_statistik_day
WHERE `ta_firma_id_f` =2 and substring(day,7,4)=2007
group by substring(day,4,2)
order by user DESC
LIMIT 1

[PHP-Desaster]

Zu dem Ermitteln des Größten durch Sortieren sage ich diesmal einfach gar nichts!

[ghostgambler]

Original geschrieben von PHP-Desaster
Zu dem Ermitteln des Größten durch Sortieren sage ich diesmal einfach gar nichts!

Es geht nicht um den größten, sondern die größte Summe~

[PHP-Desaster]

Original geschrieben von ghostgambler
Es geht nicht um den größten, sondern die größte Summe~

Das Ermitteln des Größten kann meinetwegen auch auf die Summe angewandt werden, aber dazu gibt es max() und nicht die Sortierung!
Ich weiß, im Web mit einer Tabelle von 100 Datensätzen mag das egal sein, aber bei einer Millionen Datensätzen sieht das schon wieder ganz anders aus. Inho sollte man sich angewöhnen, direkt sauber zu programmieren, dann kann man sich eine spätere "Optimierung" der Queries ersparen!

[ghostgambler]

Original geschrieben von PHP-Desaster
Das Ermitteln des Größten kann meinetwegen auch auf die Summe angewandt werden, aber dazu gibt es max() und nicht die Sortierung!
Ich weiß, im Web mit einer Tabelle von 100 Datensätzen mag das egal sein, aber bei einer Millionen Datensätzen sieht das schon wieder ganz anders aus. Inho sollte man sich angewöhnen, direkt sauber zu programmieren, dann kann man sich eine spätere "Optimierung" der Queries ersparen!

Man kann MAX und SUM aber nicht verschachteln.

 SELECT MAX(SUM(fanart_id)) FROM `fanart_scanrand_ens` GROUP BY user_id;
ERROR 1111 (HY000): Invalid use of group function

Und auf einmal wird der Query komplizierter...

[PHP-Desaster]

Ich weiß, das Aggregatfunktionen nicht geschachtelt werden können! Da musst du eine Subquery mit einbasteln. Aber ok, belassen wir es mal dabei!

[ghostgambler]

Original geschrieben von PHP-Desaster
Ich weiß, das Aggregatfunktionen nicht geschachtelt werden können! Da musst du eine Subquery mit einbasteln. Aber ok, belassen wir es mal dabei!

Nein, bitte, wenn du eine Lösung weißt will ich sie wissen!

Ich habe es auch mit

SELECT MAX(summe) FROM (SELECT SUM(fanart_id) summe FROM `fanart_scanrand_ens` GROUP BY user_id) a;

probiert - das wurde mir aber mit einem "Unknown function ghostgambler.MAX" quittiert - und an dem Punkt gingen mir dann ehrlich gesagt die Ideen aus...

[PHP-Desaster]

select maxed.max, summed.name, summed.anyid
from (
  select sum(user) sum, name, anyid
  from channel
  group by name
) as summed
inner join (
  select max(user) as max, name
  from channel
  group by name
) as maxed
on( maxed.name=summed.name and maxed.max=summed.sum );

Die Query bringt es aber erst, wenn die Datensatzzahl entsprechend hoch ist, da der SQL-Parser einfach mehr Zeit benötigt! Aber die reine Selektionszeit würde ich hier geringer einschätzen! Ist aber vielleicht auch einfach ein blödes Beispiel!

[ghostgambler]

Ähm... ehrlich gesagt kriege ich deinen Query nicht mal zum Laufen x_X

CREATE TABLE IF NOT EXISTS `tabelle` (
  `user` int(11) NOT NULL,
  `datum` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

INSERT INTO `tabelle` (`user`, `datum`) VALUES
(2, '2007-09-12'),
(4, '2007-09-12'),
(9, '2007-09-13'),
(1, '2007-09-13');

mysql> SELECT SUM( user ) , datum FROM `tabelle` GROUP BY datum ORDER BY SUM( user ) DESC LIMIT 1;
+-------------+------------+
| SUM( user ) | datum      |
+-------------+------------+
|          10 | 2007-09-13 |
+-------------+------------+
1 row in set (0.00 sec)

Das ist das was wir haben wollen.

Hier dein Query leicht abgeändert

mysql> select maxed.max, summed.datum
    -> from (
    ->   select sum(user) sum, datum
    ->   from tabelle
    ->   group by datum
    -> ) as summed
    -> inner join (
    ->   select max(user) as max, datum
    ->   from tabelle
    ->   group by datum
    -> ) as maxed
    -> on( maxed.datum=summed.datum and maxed.max=summed.sum );
Empty set (0.00 sec)

Allerdings hab ich bei der Tabelle die MAX Lösung mit derived table ans Laufen gebracht (weiß nicht warum das gestern nicht ging...)

mysql> SELECT MAX(summe) FROM (SELECT SUM(user) summe FROM tabelle GROUP BY datum) a;
+------------+
| MAX(summe) |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec)

Zwischen den beiden funktionierenden Queries findet sich aber so kein Unterschied:

mysql> EXPLAIN EXTENDED SELECT SUM( user ) , datum FROM `tabelle` GROUP BY datum ORDER BY SUM( user ) DESC LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | tabelle | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings \g
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                     |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select sum(`ghostgambler`.`tabelle`.`user`) AS `SUM( user )`,`ghostgambler`.`tabelle`.`datum` AS `datum` from `ghostgambler`.`tabelle` group by `ghostgambler`.`tabelle`.`datum` order by sum(`ghostgambler`.`tabelle`.`user`) desc limit 1 |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT MAX(summe) FROM (SELECT SUM(user) summe FROM tabelle GROUP BY datum) a;
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                 |
|  2 | DERIVED     | tabelle    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set, 1 warning (0.03 sec)

mysql> show warnings \g
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                           |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select max(`a`.`summe`) AS `MAX(summe)` from (select sum(`ghostgambler`.`tabelle`.`user`) AS `summe` from `ghostgambler`.`tabelle` group by `ghostgambler`.`tabelle`.`datum`) `a` |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Eine Erweiterung der Tabelle um

 ALTER TABLE `tabelle` ADD PRIMARY KEY ( `datum` , `user` ) ;

bringt

mysql> EXPLAIN EXTENDED SELECT SUM( user ) , datum FROM `tabelle` GROUP BY datum ORDER BY SUM( user ) DESC LIMIT 1;
+----+-------------+---------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+---------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | tabelle | index | NULL          | PRIMARY | 7       | NULL |    4 | Using index; Using temporary; Using filesort |
+----+-------------+---------+-------+---------------+---------+---------+------+------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings \g
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                     |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select sum(`ghostgambler`.`tabelle`.`user`) AS `SUM( user )`,`ghostgambler`.`tabelle`.`datum` AS `datum` from `ghostgambler`.`tabelle` group by `ghostgambler`.`tabelle`.`datum` order by sum(`ghostgambler`.`tabelle`.`user`) desc limit 1 |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT MAX(summe) FROM (SELECT SUM(user) summe FROM tabelle GROUP BY datum) a;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    2 |             |
|  2 | DERIVED     | tabelle    | index | NULL          | PRIMARY | 7       | NULL |    4 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings \g
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                           |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select max(`a`.`summe`) AS `MAX(summe)` from (select sum(`ghostgambler`.`tabelle`.`user`) AS `summe` from `ghostgambler`.`tabelle` group by `ghostgambler`.`tabelle`.`datum`) `a` |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Da ist dann die Variante über derived table besser - kein Filesort und kein temporary - allerdings halt zugeschnitten auf die jetzige Tabelle

OffTopic:

---

Sorry für die langen Zeilen, aber ich finde es doof die EXPLAIN-Ausgaben umzubrechen... das sieht dann immer so kaputt aus ~_~

---

[PHP-Desaster]

Ahh lol, da hab ich mich aber voll verzettelt! Man sollte kurz vor Feierabend nicht mehr son Zeugs machen! ^^
Ich wähle einmal die Summen der Datensätze und verbinde sie mit den Maximalwerten. Diese Maximalwerte müssten natürlich die Maxima der Summen sein
Hab jetzt aber auch kein Bock mehr