与上一行的mysql百分比差异

Question

如何计算 mysql 中每个前一行的百分比差异？

我能够让它工作，但如果我的 date列不连续，我不确定如何动态拉取它:

SELECT c1.date, 
       c1.total, 
       ( ( c1.total - ifnull(c2.total, 0) ) / c1.total ) AS percentage_change 
FROM   table c1 
       LEFT OUTER JOIN table c2 
                    ON c1.id = c2.id 
                       AND c1.date - c2.date = 1 
ORDER  BY c1.date DESC 
LIMIT  30 

问题在于我的 date列不一定是 c1.total - c2.total = 1

我刚才的一个想法是做一个子查询和select where c2.date < c1.date order by c2.date DESC LIMIT 1 .我认为这会选择下一个最低的日期值，所以现在就试试吧。

Answer 1

这里是你如何做到的:

SELECT current.id, current.date, ((current.total - IFNULL(prev.total, 0)) / current.total) AS percentage_change
FROM
(SELECT c1.id, c1.total, count(c2.*) as ordinal
FROM some_table AS c1, some_table AS c2
WHERE c2.date < c1.date) AS curr

OUTER JOIN (SELECT c1.id, c1.total, count(c2.*) as ordinal
FROM some_table AS c1, some_table AS c2
WHERE c2.date < c1.date) AS prev
    ON curr.ordinal - 1 = prev.ordinal;

假设您没有任何具有相同日期的行(并且假设我的语法中没有任何拼写错误)，这将起作用。但是，如果可以避免，我建议不要这样做。

我的首选解决方案是使用这个(一个更高效的查询):

SELECT * FROM some_table ORDER BY date ASC;

然后在遍历结果集时在应用程序内部(无论它是什么)进行数学运算。