如何找到一列的最后N个读数的差异的平均值PHP MySQL [英] How To Find the Average of Differences of The Last N Readings of a Column PHP MySQL

问题描述

我在MySQL数据库(level_records)中有一个表，该表具有3列(id，日期，读数).我想将最近20个读数(按日期)之间的差异放入数组中，然后对它们进行平均，以找到平均差异.

I have a table in a MySQL database (level_records) which has 3 columns (id, date, reading). I want to put the differences between the most recent 20 readings (by date) into an array and then average them, to find the average difference.

我到处都看过，但没人能像我一样.

I have looked everywhere, but no one seems to have a scenario quite like mine.

我将非常感谢您的帮助.谢谢.

I will be very grateful for any help. Thanks.

推荐答案

SELECT AVG(difference)
FROM (
    SELECT @next_reading - reading AS difference, @next_reading := reading
    FROM (SELECT reading
          FROM level_records
          ORDER BY date DESC
          LIMIT 20) AS recent20
    CROSS JOIN (SELECT @next_reading := NULL) AS var
    ) AS recent_diffs

演示

这篇关于如何找到一列的最后N个读数的差异的平均值PHP MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！