татко Крокодил

Това са няколко дребни трика, които ползвам за вадене на статистики и да видя кое как изглежда…

(предварително да кажа – това се отнася за 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"

Разбира се, би било хубаво да можеше да се напише и часът отдолу както си трябва, но още не съм измислил как :)

Tags: работа

This entry was posted on Sunday, September 3rd, 2006 at 11:23 and is filed under General. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.