У меня есть таблица MySQL с примерно 3000 строк на пользователя. Один из столбцов - это поле datetime, которое является изменяемым, поэтому строки не находятся в хронологическом порядке.
Я хотел бы визуализировать распределение времени в диаграмме, поэтому мне нужно несколько отдельных точек данных. Достаточно 20 баз данных.
Я мог бы сделать это:
select timefield from entries where uid = ? order by timefield;
и посмотрите на каждую 150-ю строку.
Или я мог бы сделать 20 отдельных запросов и использовать limit 1 и offset.
Но должно быть более эффективное решение...
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;
Что-то вроде этого пришло мне в голову
select @rownum:[email protected]+1 rownum, entries.*
from (select @rownum:=0) r, entries
where uid = ? and rownum % 150 = 0
У меня нет MySQL у меня, но, возможно, это поможет...
Что касается визуализации, я знаю, что это не периодическая выборка, о которой вы говорите, но я бы посмотрел на все строки для пользователя и выберет интервал, 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, я бы, вероятно, определил способ заказа пользователей в соответствии с какой-то значимой общей метрикой для пользователя.
По какой-либо причине ваш пример работает только тогда, когда @recnum использует меньше оператора. Я думаю, что когда отфильтровывает строку, rownum не увеличивается и не может сравниться ни с чем другим.
Если в исходной таблице есть столбец с автоматическим увеличением, а строки были вставлены в хронологическом порядке, тогда это должно работать:
select timefield from entries
where uid = ? and id % 150 = 0 order by timefield;
Конечно, это не работает, если нет никакой корреляции между id и временным полем, если вы на самом деле не заботитесь о равномерно распределенных временных полях, всего 20 случайных.
Вы действительно заботитесь о отдельных точках данных? Или использовать статистические агрегированные функции на число дней, а не достаточно, чтобы рассказать вам, что вы хотите знать?
select timefield
from entries
where rand() = .01 --will return 1% of rows adjust as needed.
Не эксперт mysql, поэтому я не уверен, как работает rand() в этой среде.
Для моей справки - и для тех, кто использует 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/