Как рассчитать наклон в SQL

Question

У меня есть некоторые данные в базе данных SQL, и я хотел бы рассчитать наклон. Данные имеют такой макет:

Date        |  Keyword  |  Score    
2012-01-10  |  ipad     |  0.12    
2012-01-11  |  ipad     |  0.17    
2012-01-12  |  ipad     |  0.24    
2012-01-10  |  taco     |  0.19    
2012-01-11  |  taco     |  0.34    
2012-01-12  |  taco     |  0.45    

Я бы хотел, чтобы конечный результат выглядел так, создав новую таблицу с использованием SQL:

Date        |  Keyword  |  Score |  Slope    
2012-01-10  |  ipad     |  0.12  |  0.06    
2012-01-11  |  ipad     |  0.17  |  0.06    
2012-01-12  |  ipad     |  0.24  |  0.06    
2012-01-10  |  taco     |  0.19  |  0.13    
2012-01-11  |  taco     |  0.34  |  0.13    
2012-01-12  |  taco     |  0.45  |  0.13

Чтобы усложнить ситуацию, не все ключевые слова имеют данные за 3 даты, а некоторые, например, только за 2.

Чем проще SQL, тем лучше, поскольку моя база данных проприетарна, и я не совсем уверен, какие формулы доступны, хотя я знаю, что это может сделать OVER(PARTITION BY), если это поможет. Спасибо!

ОБНОВЛЕНИЕ: я определяю наклон как наиболее подходящий y=mx+pka в Excel, это было бы =slope()

Вот еще один пример, которым я обычно манипулирую в Excel:

date        keyword         score       slope   
1/22/2012   water bottle    0.010885442 0.000334784  
1/23/2012   water bottle    0.011203949 0.000334784  
1/24/2012   water bottle    0.008460835 0.000334784  
1/25/2012   water bottle    0.010363991 0.000334784  
1/26/2012   water bottle    0.011800716 0.000334784  
1/27/2012   water bottle    0.012948411 0.000334784  
1/28/2012   water bottle    0.012732459 0.000334784  
1/29/2012   water bottle    0.011682568 0.000334784  

Answer 1

Самый чистый, который я мог сделать:

SELECT
    Scores.Date, Scores.Keyword, Scores.Score,
    (N * Sum_XY - Sum_X * Sum_Y)/(N * Sum_X2 - Sum_X * Sum_X) AS Slope
FROM Scores
INNER JOIN (
    SELECT
        Keyword,
        COUNT(*) AS N,
        SUM(CAST(Date as float)) AS Sum_X,
        SUM(CAST(Date as float) * CAST(Date as float)) AS Sum_X2,
        SUM(Score) AS Sum_Y,
        SUM(Score*Score) AS Sum_Y2,
        SUM(CAST(Date as float) * Score) AS Sum_XY
    FROM Scores
    GROUP BY Keyword
) G ON G.Keyword = Scores.Keyword;

Он использует простую линейную регрессию для расчета наклона.

Результат:

Date         Keyword        Score         Slope
2012-01-22   water bottle   0,010885442   0,000334784345222076
2012-01-23   water bottle   0,011203949   0,000334784345222076
2012-01-24   water bottle   0,008460835   0,000334784345222076
2012-01-25   water bottle   0,010363991   0,000334784345222076
2012-01-26   water bottle   0,011800716   0,000334784345222076
2012-01-27   water bottle   0,012948411   0,000334784345222076
2012-01-28   water bottle   0,012732459   0,000334784345222076
2012-01-29   water bottle   0,011682568   0,000334784345222076

Кажется, что каждая система баз данных имеет свой подход к преобразованию дат в числа:

• MySQL: TO_SECONDS(date) или же TO_DAYS(date)
• Oracle: TO_NUMBER(TO_CHAR(date, 'J')) или же date - TO_DATE('1','yyyy')
• MS SQL Server: CAST(date AS float) (или эквивалент CONVERT)

Answer 2

Если вы определяете уклон как просто уклон от самой ранней точки к самой последней точке, и если оценка только увеличивается с датой, то вы можете получить вывод выше с помощью этого:

SELECT *
  FROM scores
  JOIN
    (SELECT foo.keyword,
            (MAX(score)-MIN(score)) / DATEDIFF(MAX(date),MIN(date)) AS score
     FROM scores
     GROUP BY keyword) a
  USING(keyword);

Однако если вам нужна линейная регрессия или если оценки могут уменьшаться, а также увеличиваться со временем, вам понадобится нечто более сложное.

Answer 3

Использовать это

SUM(CONVERT(float, datediff(dd, '1/1/1900', date_field)))

вместо того

SUM(CAST(date_field AS float))

Answer 4

Приведение к десятичному числу не дает правильных результатов для меня, оно не линейно по датам. использование TO_DAYS(date_field) вместо этого это становится правильным.