knifflige SQL

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • knifflige SQL

    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.
    Code:
    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?
    Zuletzt geändert von noxz; 11.09.2007, 13:07.

  • #2
    Re: knifflige SQL

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

    Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

    bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
    Wie man Fragen richtig stellt

    Kommentar


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

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

      Kommentar


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

        Kommentar


        • #5
          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~

          Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

          bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
          Wie man Fragen richtig stellt

          Kommentar


          • #6
            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!

            Kommentar


            • #7
              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.
              Code:
               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...

              Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

              bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
              Wie man Fragen richtig stellt

              Kommentar


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

                Kommentar


                • #9
                  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
                  Code:
                  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...

                  Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

                  bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
                  Wie man Fragen richtig stellt

                  Kommentar


                  • #10
                    Code:
                    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!

                    Kommentar


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

                      Code:
                      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');
                      Code:
                      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
                      Code:
                      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...)
                      Code:
                      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:

                      Code:
                      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
                      Code:
                       ALTER TABLE `tabelle` ADD PRIMARY KEY ( `datum` , `user` ) ;
                      bringt
                      Code:
                      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 ~_~

                      Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

                      bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
                      Wie man Fragen richtig stellt

                      Kommentar


                      • #12
                        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

                        Kommentar

                        Lädt...
                        X