Вычисление среднего времени : MySQL & MariaDB

OlegON · █ 18.06.2012 15:41

Есть желание отображать среднее время ответа на вопрос на форуме.
Есть такая табличка:

Код:

mysql> select dateline,postid,threadid from vbpost order by 3,2 limit 20;
+------------+--------+----------+
| dateline   | postid | threadid |
+------------+--------+----------+
| 1140106958 |      1 |        1 |
| 1150799998 |     87 |        1 |
| 1150800227 |     88 |        1 |
| 1150808535 |    102 |        1 |
| 1150808673 |    103 |        1 |
| 1152186027 |    706 |        1 |
| 1152186236 |    707 |        1 |
| 1152187238 |    709 |        1 |
| 1152187400 |    710 |        1 |
| 1140118505 |      2 |        2 |
| 1140120394 |      3 |        3 |
| 1140122372 |      4 |        4 |
| 1140162859 |      5 |        4 |
| 1140163953 |      6 |        4 |
| 1140254237 |     10 |        4 |
| 1140684807 |     15 |        4 |
| 1140759390 |     16 |        4 |
| 1142175382 |     31 |        4 |
| 1142176733 |     32 |        4 |
| 1142345192 |     33 |        4 |
+------------+--------+----------+

первая колонка - date, вторая - сообщения (сквозная нумерация), третья - тема. Помогите, пожалуйста, запросом посчитать среднее время между последними двумя сообщениями в темах?

student · █ 18.06.2012 16:30

:)
влом было что либо сложное сочинять - сделал по крестьянски - через временную табличку и 3-и отдельных запроса (да и не люблю я запрос в запросе - не так наглядно...)

табличку обозвал q_vbpost

запрос 1 - выбирается максимум по threadid в новую табличку q_vbpost_tmp из q_vbpost
SELECT Max(q_vbpost.dateline) AS [Max-dateline], q_vbpost.threadid INTO q_vbpost_tmp FROM q_vbpost GROUP BY q_vbpost.threadid;

запрос 2 - добавляется в табличку q_vbpost_tmp максимум по threadid который меньше предыдующего максимума
INSERT INTO q_vbpost_tmp ( threadid, [Max-dateline] )
SELECT q_vbpost.threadid, Max(q_vbpost.dateline) AS max_DT
FROM q_vbpost INNER JOIN q_vbpost_tmp ON q_vbpost.threadid = q_vbpost_tmp.threadid
WHERE q_vbpost.dateline<[Max-dateline]
GROUP BY q_vbpost.threadid;

запрос 3 - считается среднее для threadid которые встречаются более 1 раза в q_vbpost_tmp
SELECT q_vbpost_tmp.threadid, Avg(q_vbpost_tmp.[Max-dateline]) AS [Avg-Max-dateline]
FROM q_vbpost_tmp
GROUP BY q_vbpost_tmp.threadid
HAVING Count(q_vbpost_tmp.threadid)>1;

результат (если я все правильно понял)
threadid Avg-Max-dateline
1 1152187319
4 1142260962.5

ps
проверял в аксесе, не в мускуле :)

OlegON · █ 18.06.2012 17:44

Ну, мускул и аксесс различаются, я это заметил еще до того, как добрался до конца сообщения, но смысл ясен :)

student · █ 18.06.2012 17:55

Цитата:

OlegON ➤ но смысл ясен :)

был бы уникальный первичный ключ можно было бы попробовать и по другому....

кстати, можно обойтись без having и join если надо...
убрать having - не добавлять в ту же табличку, а в новую и связать новую с первой в результатирующем запросе по where (случае для убирания join) и далее простая арифметика в селекте (а+б)/2 - заодно и от count уйти :)

остальное все вроде бы должно быть в мускуле (ну нету его у меня сейчас чтобы проверить)