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

У меня есть данные в базе данных 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), если это поможет. Спасибо!

UPDATE: я определяю наклон как наилучшим образом подходящий y = mx + p aka в 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

Ответы

Ответ 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)

Ответ 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);

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

Ответ 3

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

Ответ 4

Используйте это

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

вместо

SUM(CAST(date_field AS float))