单个字段的MySQL斜率(趋势)(最适合的行) [英] MySQL slope (trend) of single field (line of best fit)
问题描述
我有一个名为LOGENTRY的简单表,其中的字段名为"DATE"和"COST".示例:
I have a simple table called LOGENTRY with fields called "DATE" and "COST". Example:
+--------------+-------+
| DATE | COST |
+--------------+-------+
| MAY 1 2013 | 0.8 |
| SEP 1 2013 | 0.4 |
| NOV 1 2013 | 0.6 |
| DEC 1 2013 | 0.2 |
+--------------+-------+
我想找到COST字段随时间(选择的行范围)的斜率,结果是 SLOPE = -0.00216(这等效于Excel的SLOPE函数,又名线性回归).
I would like to find the slope of the COST field over time (a range of rows selected), resulting in SLOPE=-0.00216 (This is equivalent to Excel's SLOPE function, aka linear regression).
是否有一种简单的方法来选择COST的斜率?如果我用调用语言(php)进行数学运算,则可以找到斜率:
Is there a simple way to SELECT the slope of COST? If I do the math in the calling language (php) I can find slope as:
SLOPE = (N * Sum_XY - Sum_X * Sum_Y)/(N * Sum_X2 - Sum_X * Sum_X);
我看到了一些类似的问题,但它们更为复杂.我试图将这个示例简化为最简单的情况-这样我就可以理解答案了:)这与我所知道的非常接近...但是MYSQL抱怨附近的语法: 'float))AS Sum_X,SUM(CAST(LOGENTRY.DATE为float)* CAST(LOGENTRY.DATE'
I saw some similar questions posted but they are more complex. I'm trying to strip this example down to the simplest situation - so I can understand the answer :) Here's as close as I got...but MYSQL complains about the syntax near: 'float)) AS Sum_X, SUM(CAST(LOGENTRY.DATE as float) * CAST(LOGENTRY.DATE'
SELECT
COUNT( * ) AS N,
SUM( CAST( LOGENTRY.DATE AS FLOAT ) ) AS Sum_X,
SUM( CAST( LOGENTRY.DATE AS FLOAT ) * CAST( LOGENTRY.DATE AS FLOAT ) ) AS Sum_X2,
SUM( LOGENTRY.COST ) AS Sum_Y, SUM( LOGENTRY.COST * LOGENTRY.COST ) AS Sum_Y2,
SUM( CAST( LOGENTRY.DATE AS FLOAT ) * LOGENTRY.COST ) AS Sum_XY
FROM LOGENTRY
推荐答案
似乎MySQL无法将日期强制转换为float(根据stackoverflow中的其他示例).也许其他示例引用了另一个数据库.因此,通过将日期转换为unix_timestamps,我可以得到答案……用PHP进行最终计算.如果这不正确...请发表,我将删除答案...
It seems that MySQL cannot cast a date as float (as per the other examples in stackoverflow). Perhaps the other examples refer to another database. So by converting dates to unix_timestamps I am able to get an answer...with the final calculation in PHP. If this is WRONG...please post and I will remove answer...
SELECT
COUNT(*) AS N,
SUM(UNIX_TIMESTAMP(LOGENTRY.DATE)) AS Sum_X,
SUM(UNIX_TIMESTAMP(LOGENTRY.DATE) * UNIX_TIMESTAMP(LOGENTRY.DATE)) AS Sum_X2,
SUM(LOGENTRY.COST) AS Sum_Y,
SUM(LOGENTRY.COST*LOGENTRY.COST) AS Sum_Y2,
SUM(UNIX_TIMESTAMP(LOGENTRY.DATE) * LOGENTRY.COST) AS Sum_XY
FROM LOGENTRY
这篇关于单个字段的MySQL斜率(趋势)(最适合的行)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!