На главную
ПРАВИЛА FAQ Помощь Участники Календарь Избранное RSS
Дорогие друзья! Поздравляем вас с днём защитника отечества!



msm.ru
! информация о разделе
user posted imageДанный раздел предназначается для обсуждения вопросов использования баз данных, за исключением составления запросов на SQL. Для этого выделен специальный раздел. Убедительная просьба - соблюдать "Правила форума" и не пренебрегать "Правильным оформлением своих тем". Прежде, чем создавать тему, имеет смысл заглянуть в раздел "Базы данных: FAQ", возможно там уже есть ответ.

Модераторы: Chow, Bas, MIF, JoeUser
Страницы: (4) 1 [2] 3 4  все  ( Перейти к последнему сообщению )  
> Проблема сортировки по разнице изменения числа
    Цитата JoeUser @
    "почасовые" данные у тебя теряются.

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

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




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

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

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

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

      ExpandedWrap disabled
        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, которые были бы посещены хотя бы один (или более) раз за последние две календарные недели, без учета текущей календарной недели.

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

      ExpandedWrap disabled
        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

      Получаем нечто типа:
      ExpandedWrap disabled
        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 посещений, тогда действительно таблица логов будет пухнуть. А вот тут уже по желанию:

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

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

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

      Ну да, в принципе я это выше и описал.
      Мои программные ништякиhttps://majestio.info
        Цитата JoeUser @
        Предлагаю такой вариант

        Правильный вариант хранить лог по полям как отдает его сервер ;)
          Цитата Павел Калугин @
          Правильный вариант хранить лог по полям как отдает его сервер

          Во во.
          Хотя, если есть вариант, нормализовать на-лету - почему бы и нет.
          Сообщение отредактировано: JoeUser -
          Мои программные ништякиhttps://majestio.info
            Цитата JoeUser @
            а что делать с накапливаемой информацией?

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

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

            30кк записей в год - объём не чрезмерный. К тому же таблица логов может партиционироваться по времени.
            Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
            Есть претензии ко мне как к участнику? да ради бога.
            Не нравятся мои ответы? не читайте их.
            В общем, берегите себя. Нервные клетки не восстанавливаются.
              Цитата JoeUser @
              Хотя, если есть вариант, нормализовать на-лету - почему бы и нет.

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

              Добавлено
              Akina в таких сайтах одно посещение это обращение юзера к 3-5 страницам как минимум, нагрузки считаем по "верхнему" пределу итого 100К посещений это 400К обращений в сутки, половина из которых в "пиковые" 4 часа = 50К в час, то есть примерно 14 обращений в секунду. Да, немного. Но уже и не мало ;)
              Сообщение отредактировано: Павел Калугин -
                Цитата Павел Калугин @
                Лог только на инсеррт. А все разборы нормализация потом джобами или еще чем главное чтоб не блокировало таблицу лога на инсерт и не тормозило его.

                Обсчёт статистики не дешевле её предрасчёта, а его могут запустить в любой момент (да ещё, как показывает опыт, несколько штук параллельно).
                Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                Есть претензии ко мне как к участнику? да ради бога.
                Не нравятся мои ответы? не читайте их.
                В общем, берегите себя. Нервные клетки не восстанавливаются.
                  Akina, собственно, я написал ровно тоже что и ты ;) Хочешь поспорить?
                    Павел Калугин
                    Да не вижу я предмета для спора. Чтобы иметь хоть какие-то данные для него, надо как минимум знать, о каком конкретно сервере идёт речь, и иметь хоть какие-то данные о БД и нагрузке на неё. А счас что? конь - идеальный, вакуум - сферический...
                    Сообщение отредактировано: Akina -
                    Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                    Есть претензии ко мне как к участнику? да ради бога.
                    Не нравятся мои ответы? не читайте их.
                    В общем, берегите себя. Нервные клетки не восстанавливаются.
                      Ну как о чем? О структуре бидэ, о порядке предподготовки данных (я это срезами назвал), о логике построения отчетов .... ;)
                      Хотя если окажется что сервер типа укоза а логи это посещение всех сайтов укоза тут все равно все придется перерисовывать ;) :D
                        Цитата Павел Калугин @
                        Потому что нет. Лог только на инсеррт.


                        Не вижу противоречий. Да и "нормализация" тут совсем ненапряжная. Меня смущает запись в логи URL в сыром виде. Туда не URL писать нужно, IdURL, а сам список возможных URL хранить в отдельной таблице. Не думаю, что по ней поиск будет заметным для БД, если нормально отстроить кэширование движка БД.
                        Мои программные ништякиhttps://majestio.info
                          JoeUser Как верно Акина написал выше - гадание на кофейной гуще.
                          если там 100 просмотров страниц в день - любое решение будет работать. Если сотни-тысячи в секунду - ой как думать надо шо с этим делать и как.
                          Вопрос в том, что все предложенные решения (в том числе и решение автора) работают в неких границах по нагрузке. И чем больше обработки при записи потока данных в бд тем уже эти пределы. ;)
                          Сообщение отредактировано: Павел Калугин -
                            Цитата JoeUser @
                            Меня смущает запись в логи URL в сыром виде. Туда не URL писать нужно, IdURL, а сам список возможных URL хранить в отдельной таблице.

                            Ну как бы это решение вообще необсуждаемое. Таблица посещений - самая в текущем бизнес-процессе высоконагруженная, следовательно, просто обязана быть максимально компактной. Так что только компактные типы (BIGINT, DATETIME), а всякий текстовый хлам - наружу, и по ссылке.
                            Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                            Есть претензии ко мне как к участнику? да ради бога.
                            Не нравятся мои ответы? не читайте их.
                            В общем, берегите себя. Нервные клетки не восстанавливаются.
                              ну как бы спорный вопрос. не сильно ли замедлит запись подбирать иды под весь этот текстовый хлам и что писать если ида не оказалось? Надо жеж тогда "справочник" обновить - а это еще большее раздувание транзакции
                              Мне кажется или писать во вьюху с инстед тригером тоже не будет оптимальным решением?
                                Цитата Павел Калугин @
                                не сильно ли замедлит запись подбирать иды под весь этот текстовый хлам и что писать если ида не оказалось? Надо жеж тогда "справочник" обновить - а это еще большее раздувание транзакции

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

                                Вьюхи (которые к тому же любят в самый неподходящий момент захотеть материализоваться) или триггеры - это явно лишнее. Ну то есть триггер вроде бы и мог быть по месту - но, как мне кажется, от записи лога трудно ожидать пакетного поступления записей, а в этих условиях триггер только породит ненужные накладные расходы.
                                Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                Есть претензии ко мне как к участнику? да ради бога.
                                Не нравятся мои ответы? не читайте их.
                                В общем, берегите себя. Нервные клетки не восстанавливаются.
                                1 пользователей читают эту тему (1 гостей и 0 скрытых пользователей)
                                0 пользователей:
                                Страницы: (4) 1 [2] 3 4  все


                                Рейтинг@Mail.ru
                                [ Script Execution time: 0,1319 ]   [ 15 queries used ]   [ Generated: 25.02.20, 13:07 GMT ]