Распределение таблицы по времени

Question

Распределение таблицы по времени

У меня есть таблица MySQL с примерно 3000 строк на пользователя. Один из столбцов - это поле даты и времени, которое является изменяемым, поэтому строки расположены не в хронологическом порядке.

Я хотел бы визуализировать распределение времени на графике, поэтому мне нужно несколько отдельных точек данных. 20 точек данных будет достаточно.

Я мог бы сделать это:

select timefield from entries where uid = ? order by timefield;

и посмотрите на каждый 150-й ряд.

Или я мог бы сделать 20 отдельных запросов и использовать limit 1и offset.

Но должно быть более эффективное решение ...

12 mysql sql

Michiel de Mare 6.08.2008 16:58:34

Можете ли вы описать вопрос немного больше? Какой выход вы ищете? Хотите ли вы увидеть частотную диаграмму (например: количество записей в январе = 132, февраль = 112, март = 173 и т. Д.), Или вам нужны отдельные значения самой ранней записи, 150-й самой ранней записи, 300-й, и т.д?

nickf 1.10.2008 02:11:40

7 ОТВЕТОВ

РЕШЕНИЕ

У Михала Снайдера это почти что было, но вы не можете использовать псевдонимы столбцов в предложении WHERE в SQL. Таким образом, вы должны обернуть его как производную таблицу. Я попробовал это, и он возвращает 20 строк:

SELECT * FROM (
    SELECT @rownum:=@rownum+1 AS rownum, e.*
    FROM (SELECT @rownum := 0) r, entries e) AS e2
WHERE uid = ? AND rownum % 150 = 0;

6

Bill Karwin 1.10.2008 01:49:27

это должно быть "rand () <.01"

nickf 1.10.2008 02:08:29

вы можете сделать "GROUP BY bucket ORDER BY bucket"? кажется, что это было бы намного эффективнее (без необходимости каждый раз пересчитывать этот столбец)

nickf 1.10.2008 03:12:24

Нет, вы не можете, однако, оптимизатор фактически не пересчитывает эти выражения, потому что он знает, что функции являются детерминированными.

Cade Roux 1.10.2008 04:31:44

Michal Sznajder · Answer 1

Нечто подобное пришло мне в голову

select @rownum:=@rownum+1 rownum, entries.* 
from (select @rownum:=0) r, entries 
where uid = ? and rownum % 150 = 0

У меня нет MySQL под рукой, но, возможно, это поможет ...

Ryan Ahearn · Answer 2

@Michal

По какой-то причине ваш пример работает только тогда, когда в @recnum используется оператор меньше чем. Я думаю, что когда where отфильтровывает строку, значение rownum не увеличивается, и больше ничего не может соответствовать.

Если исходная таблица имеет столбец идентификатора с автоинкрементом и строки были вставлены в хронологическом порядке, то это должно работать:

select timefield from entries
where uid = ? and id % 150 = 0 order by timefield;

Конечно, это не сработает, если нет корреляции между идентификатором и временным полем, если только вы на самом деле не заботитесь о том, чтобы получить равномерно распределенные временные поля, только 20 случайных.

Scott Noyes · Answer 3

Вы действительно заботитесь об отдельных точках данных? Или вместо этого будет достаточно использовать статистические агрегатные функции для номера дня, чтобы сказать вам, что вы хотите знать?

jason saldo · Answer 4

select timefield
from entries
where rand() = .01 --will return 1% of rows adjust as needed.

Не эксперт MySQL, поэтому я не уверен, как rand () работает в этой среде.

Cade Roux · Answer 5

Что касается визуализации, я знаю, что это не периодическая выборка, о которой вы говорите, но я бы посмотрел на все строки для пользователя и выбрал интервал, SUM внутри сегментов и отобразил их на гистограмме или подобном. Это показало бы реальное «распределение», так как много случаев в пределах временного интервала могут быть существенными.

SELECT DATEADD(day, DATEDIFF(day, 0, timefield), 0) AS bucket -- choose an appropriate granularity (days used here)
     ,COUNT(*)
FROM entries
WHERE uid = ?
GROUP BY DATEADD(day, DATEDIFF(day, 0, timefield), 0)
ORDER BY DATEADD(day, DATEDIFF(day, 0, timefield), 0)

Или, если вам не нравится то, как вы должны повторять себя, или если вы играете с разными сегментами и хотите анализировать данные по многим пользователям в 3-D (измерение по Z против x, yid, bucket):

SELECT uid
    ,bucket
    ,COUNT(*) AS measure
FROM (
    SELECT uid
        ,DATEADD(day, DATEDIFF(day, 0, timefield), 0) AS bucket
    FROM entries
) AS buckets
GROUP BY uid
    ,bucket
ORDER BY uid
    ,bucket

Если бы я хотел построить трехмерный график, я бы, вероятно, определил способ упорядочения пользователей в соответствии с какой-либо значимой общей метрикой для пользователя.

Michiel de Mare · Answer 6

Для справки - и для тех, кто использует postgres - Postgres 9.4 заказал набор агрегатов, который должен решить эту проблему:

SELECT percentile_disc(0.95) 
WITHIN GROUP (ORDER BY response_time) 
FROM pageviews;

Источник: http://www.craigkerstiens.com/2014/02/02/Examining-PostgreSQL-9.4/