Това са няколко дребни трика, които ползвам за вадене на статистики и да видя кое как изглежда…
(предварително да кажа – това се отнася за postgresql, но се пренася за каквато и да е база доста лесно).
Да кажем, че имаме една таблица в базата данни, която ни е като log файл, с две колони – timestamp и event_id, който е integer и е от 1 до 5. Задачката е да извадим в по X минутни интервали от кой тип event колко са се случили и да ги нарисуваме на една графика.
Първата стъпка е следната заявка:
SELECT
((EXTRACT(epoch FROM timestamp)::int/90)*90) AS period,
SUM((event_id=1)::int) AS type_1,
SUM((event_id=2)::int) AS type_2,
SUM((event_id=3 OR event_id=4 OR event_id=5)::int) AS type_rest,
SUM(1) AS all
FROM
log
WHERE
timestamp => '2006-08-28 00:00:00' AND timestamp < '2006-08-29 00:00:00'
GROUP BY
period
ORDER BY
period ASC;
(благодарности на Aqua за тестването и забележките :) )
Кратко обяснение - period ни представлява timestamp, закръглен до 90 секунди (чрез целочисленото деление), по който групираме. За всеки такъв период броим от кой event_id колко имаме с дребния трик с typecast-а - true е 1, false е 0, което значи, че ако условието е изпълнено, самата сума ще се увеличи, ако не - няма. Така реално погледнато преброяваме какво се среща в таблицата с едно преминаване през нея.
Няколко забележки към заявката:
1) Може да се използва date_trunc вместо трика с EXTRACT, но не може да се закръгля на неща различни от минута или час.
2) В по-старите версии на postgresql (преди 8) няма по подразбиране typecast от boolean към integer, но в документацията има някъде един пример как може да се напише тоя typecast.
3) Вместо номера със SUM() може да се ползва и групиране по event_id, но за да се обединят няколко event_id-а пак трябва да се прибегне до някакъв boolean трик. Също така ще стане по-сложно взимането на общата бройка и решаването на проблема с 0 срещания на някой event_id за даден период.
4) Вместо SUM(1) накрая може да се ползва COUNT(1), но не вярвам да има особена разлика.
5) Може да се ползва BETWEEN в WHERE клаузата, но на мен така повече ми харесва :)
За да си докараме данните във вид, подходящ за рисуване, ни трябва следния ред:
psql -A -q -t -F ' ' $DBNAME -c "$QUERY" > data
(какво правят опциите може да видите в man-а:) DBNAME е базата, QUERY e заявката от по-горе)
Ще се озовем с един файл data, в който ще имаме следния тип редове:
1156748310 23 12 6 41
1156748400 28 21 5 54
1156748490 32 15 2 49
....
Тук влиза в действие gnuplot-а, който със следната команда ще ви направи една доста приятна рисунка:
set title "Events through the day, quantised at 90 sec. (fig. 1)"
set ylabel "number of events"
set xlabel "time through the day"
plot "data" using 5 with lines title "all", "data" using 4 with lines title "rest", \
"data" using 3 with lines title "type_1", "data" using 2 with lines title "type_1"
А с добавката на тези два реда в началото ще ви напише графиката във файл:
set terminal png font VeraSe 11 size 800,500
set output "test.png"
Разбира се, би било хубаво да можеше да се напише и часът отдолу както си трябва, но още не съм измислил как :)