Регрессионный анализ в MySQL

Ответ 1

Это может быть то, что вы ищете:

SELECT SUM((X-AVG_X)*(Y-AVG_Y)) / SUM((X-AVG_X)*(X-AVG_X)) AS Slope,
       PageId, LanguageId
FROM
(
SELECT Q0.Y, 
       Q0.X, 
       Q1.AVG_Y,
       Q1.AVG_X,
       Q1.PageId,
       Q1.LanguageId
FROM   (SELECT T0.likes AS Y,
               UNIX_TIMESTAMP(T0.created_at) AS X,
               T1.facebook_page_id AS PageId,
               T1.language_id AS LanguageId
        FROM   language_page_likes_history T0 INNER JOIN
               language_page_likes T1 ON 
               (T0.language_page_likes_id = T1.id)
        WHERE  T0.created_at > '2015-09-11 00:00:00' AND
               T0.created_at < '2015-09-15 00:00:00') Q0 INNER JOIN
       (SELECT AVG(T2.likes) AS AVG_Y,
               AVG(UNIX_TIMESTAMP(T2.created_at)) AS AVG_X,
               T3.facebook_page_id AS PageId,
               T3.language_id AS LanguageId
        FROM   language_page_likes_history T2 INNER JOIN
               language_page_likes T3 ON 
               (T2.language_page_likes_id = T3.id)
        WHERE  T2.created_at > '2015-09-11 00:00:00' AND
               T2.created_at < '2015-09-15 00:00:00'
        GROUP BY T3.facebook_page_id, T3.language_id) Q1
        ON (Q0.PageId = Q1.PageId) AND (Q0.LanguageId = Q1.LanguageId)
) Q2
GROUP BY PageId, LanguageId
ORDER BY Slope DESC

Он возвращает наклон линейной регрессии на страницу и язык. Столбец Slope представляет количество понравившихся в секунду. В ваших образцовых данных количество симпатий уменьшается для двух случаев. Я не знаю почему. Результат должен выглядеть следующим образом. Оператор SQL проверен, и я проверил два вычисления строк вручную для правильного вывода.

|           Slope | PageId | LanguageId |
|-----------------|--------|------------|
|  0.001786287345 |      3 |          3 |
|  0.001326183029 |      1 |          1 |
|  0.001252720995 |      2 |          1 |
|  0.001194724653 |      3 |          1 |
|  0.000924075055 |      1 |          3 |
|  0.000908609364 |      2 |          3 |
|  0.000050263497 |      3 |          2 |
| -0.001515637747 |      2 |          2 |
| -0.003746563717 |      1 |          2 |

Может возникнуть проблема, если в таблицах нет данных. Поэтому, возможно, необходимо добавить ISNULL-проверки.

Когда вы только хотите знать абсолютные значения, это проще. Вы можете принять следующее утверждение:

SELECT PageId, LanguageId,
       (likes_last_in_period - likes_before_period) AS Likes
FROM
(SELECT T1.facebook_page_id AS PageId,
       T1.language_id AS LanguageId,
       (SELECT likes 
        FROM   language_page_likes_history
        WHERE  created_at < '2015-09-12 00:00:00' AND
               language_page_likes_id = T1.id
        ORDER BY created_at DESC LIMIT 1) likes_before_period,
       (SELECT likes 
        FROM   language_page_likes_history
        WHERE  created_at >= '2015-09-12 00:00:00' AND
               language_page_likes_id = T1.id
        ORDER BY created_at ASC LIMIT 1) likes_first_in_period,
       (SELECT likes 
        FROM   language_page_likes_history
        WHERE  created_at <= '2015-09-15 00:00:00' AND
               language_page_likes_id = T1.id
        ORDER BY created_at DESC LIMIT 1) likes_last_in_period,
       (SELECT likes 
        FROM   language_page_likes_history
        WHERE  created_at > '2015-09-15 00:00:00' AND
               language_page_likes_id = T1.id
        ORDER BY created_at ASC LIMIT 1) likes_after_period

        FROM   language_page_likes T1) Q0
ORDER BY Likes DESC

который имеет 4 подзапроса. Вам нужны только два, которые вам нужно выбрать. Я решил использовать количество понравившихся людей до периода и последнего числа симпатий, которое находится в периоде для вычисления разницы. Результат выглядит следующим образом:

| PageId | LanguageId | Likes |
|--------|------------|-------|
|      3 |          3 |   462 |
|      1 |          1 |   343 |
|      2 |          1 |   324 |
|      3 |          1 |   309 |
|      1 |          3 |   239 |
|      2 |          3 |   235 |
|      3 |          2 |    13 |
|      2 |          2 |  -392 |
|      1 |          2 |  -969 |

Ответ 2

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

select 
    id, 
    new_date,
    max(increase)
from (
select 
    dg.id, 
    dg.date new_date, 
    dg.sum - (select sum from dg where dg.date = date_format((date_sub(str_to_date(new_date, '%Y-%m-%d') 1 DAY), '%Y-%m-%d') increase
from (
select 
    language_pages_likes_id id,
    date_format(created_at, '%Y%-m$-%d') date,
    sum(likes) likes_sum
from
    language_page_likes_history lplh
group by
    language_page_likes_id,
    date_format(created_at, '%Y%-m$-%d')
) day_grouping dg
) calculate_increases

Надеюсь, это поможет. Позже, когда я смогу, я буду дополнительно тестировать и улучшать этот запрос.