Проблема сортировки по разнице изменения числа

Цитата JoeUser @ 18.04.16, 15:24

"почасовые" данные у тебя теряются.

На текущий момент информация от ТС предполагает, что суточной дискретизации ему достаточно. Если нужно более мелко - соответствующим образом дробится и диапазон сбора статистики. В пределе - фиксация штампа времени каждого отдельного обращения.

Цитата Akina @ 18.04.16, 15:53

На текущий момент информация от ТС предполагает, что суточной дискретизации ему достаточно.

Как говорится - "аппетит приходит во время еды начальства"

---

Цитата n0wheremany @ 18.04.16, 11:36

Исходные данные таблица post с полями id,read. Обновление поля read происходит update post set read=read+1 про просмотре поста.

Да нет же!!! Исходные данные - это не то, что ты сделал. Нет еще этой таблицы.
Есть, к примеру: сервер, который отдает какую информацию по URL.
Нужно не вводить поля read1, read2, ... read78 (это тоже твоя костыльная реализация).
А нужно, к примеру: мочь рассчитать рейтинг запросов в разрезе URL за период времени.

Предлагаю такой вариант

Данные посещений собираются в следующую таблицу "Logs" (здесь и далее - синтаксис PostgreSQL, и "нормализация" тут хромает, ибо URL'ы желательно вынести в отдельную таблицу и организовать с таблицей логов отношения, спецом не делал, чтобы было нагляднее):

CREATE TABLE public."Logs" (
  "Id" BIGSERIAL,
  "URL" VARCHAR NOT NULL,
  "Timestamp" TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
  CONSTRAINT "Logs_pkey" PRIMARY KEY("Id")
) WITHOUT OIDS;

В результате мы учитываем все посещения, и имеем отметку времени вплоть до секунд.

Далее, к примеру, нам нужен рейтинг всех URL, которые были бы посещены хотя бы один (или более) раз за последние две календарные недели, без учета текущей календарной недели.

Делаем запрос:

WITH Rec AS (
  SELECT
    *,
    extract(week from NOW()) - extract(week from L."Timestamp") AS "WeekNum"
  FROM
    "Logs" AS L
  WHERE
    extract(week from NOW()) - extract(week from L."Timestamp") BETWEEN 1 AND 2
)
SELECT
  *
FROM (
  SELECT
    Rec."WeekNum",
    Rec."URL",
    COUNT(*) AS "Cnt"
  FROM
    Rec
  GROUP BY
    Rec."WeekNum", Rec."URL"
) AS J
ORDER BY
  J."WeekNum" DESC, J."Cnt" DESC

Получаем нечто типа:

week  URL      Count
====================
2     post.php  3
2     mail.php  1
2     info.php  1
1     mail.php  1
1     post.php  1
1     info.php  1

Все! Нужный рейтинг получен. Т.е. задачу получение нужных цифр мы решили.

Теперь нужно предусмотреть, а что делать с накапливаемой информацией? Допустим, в день регистрируется 10 посещений. Вообще ниче делать не надо - ибо, 3650 записей в год, 365000 записей может появится за ближайшие 100 лет. Однако, вопрос может встать, когда в день будет регистрироваться 10000-100000 посещений, тогда действительно таблица логов будет пухнуть. А вот тут уже по желанию:

* считать статистику и запоминать ее в таблице статистики
* "старые данные" переностить в архивнуюю БД
* "старые данные" просто удалять после пересчета статистики

Короч, варианты "обслуживания" таблицы логов нужно решать исходя из условий эксплуатации. Тут однозначного ответа нет.
Ну вот как-то так.

Добавлено 18.04.16, 16:15

Цитата Akina @ 18.04.16, 15:53

В пределе - фиксация штампа времени каждого отдельного обращения.

Ну да, в принципе я это выше и описал.

Цитата JoeUser @ 18.04.16, 16:03

Предлагаю такой вариант

Правильный вариант хранить лог по полям как отдает его сервер

Цитата Павел Калугин @ 18.04.16, 18:55

Правильный вариант хранить лог по полям как отдает его сервер

Во во.
Хотя, если есть вариант, нормализовать на-лету - почему бы и нет.

Сообщение отредактировано: JoeUser - 18.04.16, 20:41

Цитата JoeUser @ 18.04.16, 16:03

а что делать с накапливаемой информацией?

Я бы вообще рекомендовал двухуровневую структуру.
На первом уровне - сбор логов, т.е. каждое посещение даёт запись в таблицу.
На втором - предрасчётную статистику прошедших периодов с минимальной либо наиболее часто используемой дискретизацией. Для описанного выше случая (чаще всего требуется статистика за предыдущую неделю) предрасчитывать статистику по неделям (если под неделей понимается строго с пн по вс, например) или по дням (если неделя - это строго 7 суток с плавающей границей).
При таком подходе подавляющее большинство запросов будут быстро получать данные из таблицы с предрасчётными значениями, а нестандартные запросы могут как обсчитывать таблицу логов, так и брать основные данные из таблицы предрасчёта, а "хвосты" - из таблицы логов.

Цитата JoeUser @ 18.04.16, 16:03

вопрос может встать, когда в день будет регистрироваться 10000-100000 посещений,

30кк записей в год - объём не чрезмерный. К тому же таблица логов может партиционироваться по времени.

Цитата JoeUser @ 18.04.16, 20:40

Хотя, если есть вариант, нормализовать на-лету - почему бы и нет.

Потому что нет. Лог только на инсеррт. А все разборы нормализация потом джобами или еще чем главное чтоб не блокировало таблицу лога на инсерт и не тормозило его. Пока обращений десяток два в час фихня. Когда несколько сотен в секунду - любые тормоза на записи в лог акунутся потерями и дедлоками

Добавлено 19.04.16, 06:27
Akina в таких сайтах одно посещение это обращение юзера к 3-5 страницам как минимум, нагрузки считаем по "верхнему" пределу итого 100К посещений это 400К обращений в сутки, половина из которых в "пиковые" 4 часа = 50К в час, то есть примерно 14 обращений в секунду. Да, немного. Но уже и не мало

Сообщение отредактировано: Павел Калугин - 19.04.16, 06:28

Цитата Павел Калугин @ 19.04.16, 06:22

Лог только на инсеррт. А все разборы нормализация потом джобами или еще чем главное чтоб не блокировало таблицу лога на инсерт и не тормозило его.

Обсчёт статистики не дешевле её предрасчёта, а его могут запустить в любой момент (да ещё, как показывает опыт, несколько штук параллельно).

Akina, собственно, я написал ровно тоже что и ты Хочешь поспорить?

Павел Калугин
Да не вижу я предмета для спора. Чтобы иметь хоть какие-то данные для него, надо как минимум знать, о каком конкретно сервере идёт речь, и иметь хоть какие-то данные о БД и нагрузке на неё. А счас что? конь - идеальный, вакуум - сферический...

Сообщение отредактировано: Akina - 19.04.16, 06:55

Ну как о чем? О структуре бидэ, о порядке предподготовки данных (я это срезами назвал), о логике построения отчетов ....
Хотя если окажется что сервер типа укоза а логи это посещение всех сайтов укоза тут все равно все придется перерисовывать

Цитата Павел Калугин @ 19.04.16, 06:22

Потому что нет. Лог только на инсеррт.

Не вижу противоречий. Да и "нормализация" тут совсем ненапряжная. Меня смущает запись в логи URL в сыром виде. Туда не URL писать нужно, IdURL, а сам список возможных URL хранить в отдельной таблице. Не думаю, что по ней поиск будет заметным для БД, если нормально отстроить кэширование движка БД.

JoeUser Как верно Акина написал выше - гадание на кофейной гуще.
если там 100 просмотров страниц в день - любое решение будет работать. Если сотни-тысячи в секунду - ой как думать надо шо с этим делать и как.
Вопрос в том, что все предложенные решения (в том числе и решение автора) работают в неких границах по нагрузке. И чем больше обработки при записи потока данных в бд тем уже эти пределы.

Сообщение отредактировано: Павел Калугин - 19.04.16, 09:35

Цитата JoeUser @ 19.04.16, 09:20

Меня смущает запись в логи URL в сыром виде. Туда не URL писать нужно, IdURL, а сам список возможных URL хранить в отдельной таблице.

Ну как бы это решение вообще необсуждаемое. Таблица посещений - самая в текущем бизнес-процессе высоконагруженная, следовательно, просто обязана быть максимально компактной. Так что только компактные типы (BIGINT, DATETIME), а всякий текстовый хлам - наружу, и по ссылке.

ну как бы спорный вопрос. не сильно ли замедлит запись подбирать иды под весь этот текстовый хлам и что писать если ида не оказалось? Надо жеж тогда "справочник" обновить - а это еще большее раздувание транзакции
Мне кажется или писать во вьюху с инстед тригером тоже не будет оптимальным решением?

Цитата Павел Калугин @ 19.04.16, 10:55

не сильно ли замедлит запись подбирать иды под весь этот текстовый хлам и что писать если ида не оказалось? Надо жеж тогда "справочник" обновить - а это еще большее раздувание транзакции

? не понял... чего там подбирать? автоинкремент с этим справится на раз-два. Так что просто делаем вставку в таблицу УРЛов, ошибку дублирования злостно игнорируем, зато гарантированно имеем запись - а затем уже делаем вставку в таблицу лога. А поскольку имеем два несвязанных INSERT, а между ними SELECT заведомо статической записи (а если средства позволяют - то сразу получение генерированного ID соотв. функцией) - то каждый INSERT является самостоятельной транзакцией, так что транзакции по сути и не нужны вовсе.

Вьюхи (которые к тому же любят в самый неподходящий момент захотеть материализоваться) или триггеры - это явно лишнее. Ну то есть триггер вроде бы и мог быть по месту - но, как мне кажется, от записи лога трудно ожидать пакетного поступления записей, а в этих условиях триггер только породит ненужные накладные расходы.