[bivald]

Hej,
Jag sitter och skriver ett statistikprogram som samlar data från olika källor vid en regelbunden tid (ex. var femte minut). Det samlar värden från en chattserver jag driver såsom antal användare inloggade, antal vakter inloggade men även ram och cpu-förbrukning. Insamlingen i sig är inga problem men jag sitter fast på hur jag lättast ska hämta datan från tabellerna. Jag har två tabeller: irc och sys (irc sparar data såsom antal inloggade medan sys sparar om ramminne osv).

Förenklat så vill jag kunna hämta högsta värdet av en kolumn per dygn. Att hämta för ett specefikt datum är enkelt, t.ex:

select id,date, users from irc where year(date) = 2007 and month(date) = 07 and dayofmonth(date) = 14 order by users desc limit 0,1

Vilket ger:

+------+---------------------+-------+
| id   | date           | users |
+------+---------------------+-------+
| 1087 | 2007-07-14 00:20:01 |  369 |
+------+---------------------+-------+

id är auto-increment, date är datetime, users int. Unix timestamp finns också (time) för den som är mer bekväm med det.

Men för att få statistik för t.ex. en vecka, en månad eller ett år vill jag göra en fråga som ger min högst antalet användare nedbrutet per dag, alltså första raden visar högsta antal användare dag 1, andra radern visar högsta antal användare dag 2, osv. Kort sagt:

+-----------------+---------- +
| id  |date        | users   |
+-----------------+-----------+
| 1087 | 2007-07-14 | 369    |
| 1387 | 2007-07-15 | 378    |
| 1467 | 2007-07-16 | 323    |
+----------------+------------+

Några förslag på en lösning? Antar att ett alternativ är att köra stored proc som loopar igenom datumen. Tanken är att sedan kunna köra en query som tar ut när vi har högst antal users per dag och sedan slår ihop datan med information såsom vad den aktuella ramförbrukningen var då.

Då det är mycket som ska gås igenom kommer förfrågan antagligen bli rätt tung, detta i sig är nog inget större problem då dem egentligen bara behöver köras en gång per dygn (sedan kan man antingen spara det i cache eller helt enkelt skriva till en fil)

All hjälp skulle uppskattas,
Mvh/ Niklas

[melin]

select max(fält) - funkar inte det? (aldrig provat jag försöker bara vara logisk :P)

[fors]

En idé är väl att köra en GROUP-sats, exakt hur den ser ut vet jag inte. Jag sitter på jobbet just nu och kan inte titta så mycket närmare på det. Det som stör mig lite är hur man använder en GROUP BY-sats på datum med klockslag och allt, när man enbart vill komma åt datumet (ej klockslaget). Det får du försöka lösa på egen hand, eller om någon annan hjälper dig.

Men i princip:

SELECT date, MAX(users), MIN(users), AVG(users)
FROM stats
GROUP BY date
ORDER BY date DESC

En sak som ovanstående sats inte gör är att ta reda på när det är högst antal användare, alltså vilket klockslag.

Men som sagt, du får kolla in lite hur du skall göra med datumet, då ovanstående fråga inte är anpassad att det existerar klockslag, utan är enbart för vanliga datum. Det borde gå att ordna i alla fall.

[oller]

Originally posted by fors@Jul 16 2007, 16:07
Det som stör mig lite är hur man använder en GROUP BY-sats på datum med klockslag och allt, när man enbart vill komma åt datumet (ej klockslaget).

SELECT DATE_FORMAT(datetimecolumn, '%Y-%m-%d') as datum, COUNT(*) FROM temp GROUP BY 1

Mvh

[eg0master]

det som ställer till det antar jag är ID:t. Om du inte behöver ID:t är det ju trivialt:

SELECT DATE_FORMAT(datetimecolumn, '%Y-%m-%d') as datum, max(users) 
FROM irc 
GROUP BY datum

Om du verkligen behöver ID:t så grå dfet ju t.ex. genom att använda en sub-select (reservation för mySQL syntax):

SELECT DATE_FORMAT(datetimecolumn, '%Y-%m-%d') as datum, max(users), (select id from irc AS irc2 WHERE irc2.datetimecolumn = irc1.datetimecolumn) 
FROM irc AS irc1 
GROUP BY datum

[bivald]

Tack så mycket för alla svar! eg0masters första query fungerar utmärkt, ger mig högsta antalet users per dag. Däremot får jag inte nummer två att fungera korrekt, den ger mig högsta antalet users men inte id numret för det antalet, utan första id'et per dag. Exempel:

+------------+------------+---------------------------------------------------------+
| datum   | max(users) | (select id from irc AS irc2 WHERE irc2.date = irc.date) |
+------------+------------+---------------------------------------------------------+
| 2007-07-11 |    403 |                           403 |
| 2007-07-12 |    413 |                           507 |
| 2007-07-13 |    411 |                           795 |
| 2007-07-14 |    369 |                          1083 |
| 2007-07-16 |    431 |                          1258 |
| 2007-07-17 |    430 |                          1380 |
+------------+------------+---------------------------------------------------------+

Datum och högst antal users stämmer precis, men id numret som retuneras är inte det id som har högst antal users, utan det första id´t den dagen. Anledningen till att jag behöver id´t är för att jag ska kunna korsköra mot andra tabeller (först se id´t på den som har flest users, sen hämta t.ex. ramförbrukning vid samma tidpunkt (matchande id)).

[fors]

Originally posted by bivald@Jul 17 2007, 22:35
Tack så mycket för alla svar eg0masters första query fungerar utmärkt, ger mig högsta antalet users per dag. Däremot får jag inte nummer två att fungera korrekt, den ger mig högsta antalet users men inte id numret för det antalet, utan första idet per dag. Exempel:

+------------+------------+---------------------------------------------------------+
 datum  maxusers select id from irc AS irc2 WHERE irc2.date = irc.date 
+------------+------------+---------------------------------------------------------+
 2007-07-11   403              403 
 2007-07-12   413              507 
 2007-07-13   411              795 
 2007-07-14   369              1083 
 2007-07-16   431              1258 
 2007-07-17   430              1380 
+------------+------------+---------------------------------------------------------+

Datum och högst antal users stämmer precis, men id numret som retuneras är inte det id som har högst antal users, utan det första id´t den dagen. Anledningen till att jag behöver id´t är för att jag ska kunna korsköra mot andra tabeller (först se id´t på den som har flest users, sen hämta t.ex. ramförbrukning vid samma tidpunkt (matchande id)).

Testa:

SELECT DATE_FORMAT(datetimecolumn, '%Y-%m-%d') as datum, max(users) AS max_users, (select id from irc AS irc2 WHERE irc2.datetimecolumn = irc1.datetimecolumn AND irc2.users = max_users)
FROM irc AS irc1
GROUP BY datum

[bivald]

Hej, körde en liknande förut men får svaret: "ERROR 1247 (42S22): Reference 'max_users' not supported (reference to group function)". Googlade felmeddelandet och fick upp artikeln "groupwise max":

"One of the common problems to solve in SQL is "Get row with the group-wise maximum". Getting just the maximum for the group is simple, getting the full row which is belonging to the maximum is the interesting step."

Som har adressen http:// jan.kneschke.de/projects/mysql/groupwise-max

Efter att väldigt snabbt ha skummat den bör den kunna läsa mina problem (i alla fall enligt sumeringen), ska lusläsa den under morgondagen. Håller denna tråd uppdaterad.

[eg0master]

Jag utgick ifrån att du hade en datetime kolumn (eftersom ditt exempel har det), men om du får "första" ID:t låter det som om du har en datekolumn. Är det det som är problemet?

Annars är ju en väg att byta ut "max_users" i selecten med en ny subselect, vilket väl inte är mums ur prestandasynvinkel (med två subselects i samma select) men måste man så måste man...

[bivald]

date är en datetime column, dock inte index:

+---------------+----------+------+-----+---------+-------+
| Field     | Type   | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| id      | int(11) | NO  | PRI | 0    |    |
| date     | datetime | YES |   | NULL  |    |
| time     | int(11) | YES |   | NULL  |    |
| users     | int(11) | YES |   | NULL  |    |

men det verkar som den gör såhär:

Först delar den upp efter dag, sen tar den högsta värdet av users, sen hämtar den det första id'et för den dagen.

Kommer nog bli med flera subselects, då jag kan schemalägga och lägga resultaten i cache bör det inte bli så belastande ändå.