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

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

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

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

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

и посмотрите на каждую 150-ю строку.

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

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

Ответ 1

Michal Sznajder почти имел это, но вы не можете использовать псевдонимы столбцов в предложении WHERE в SQL. Поэтому вы должны обернуть его как производную таблицу. Я попробовал это, и он возвращает 20 строк:

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

Ответ 2

Что-то вроде этого пришло мне в голову

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

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

Ответ 3

Что касается визуализации, я знаю, что это не периодическая выборка, о которой вы говорите, но я бы посмотрел на все строки для пользователя и выберет интервал, 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)

Или, если вам не нравится, как вы должны повторять себя, или если вы играете с разными ведрами и хотите проанализировать многих пользователей в трехмерном (измерение в Z против x, y uid, 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

Если бы я хотел построить сюжет в 3-D, я бы, вероятно, определил способ заказа пользователей в соответствии с какой-то значимой общей метрикой для пользователя.

Ответ 4

@Michal

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

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

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

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

Ответ 5

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

Ответ 6

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

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

Ответ 7

Для моей справки - и для тех, кто использует 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/