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