mysql - SQL Find the average of 3 day closest

Question

I have an SQL structure like this:

Create Table Transactions (
Id integer primary key not null auto_increment,
ResourceId varchar(255),
Price Integer,
TransactionTime date
);

I would like to get the time (TransactionTime) along with the average of 3 days price. For example, the 3 day average of the 22nd will be the average of the 20th, 21st, and 22nd. Thanks so much.

标签： mysqlsql

Answer 1

大概，您需要每一行和给定资源的这些信息。如果是这样：

select t.*,
       avg(price) over (partition by resourceid
                        order by transactiontime
                        range between interval 2 day preceding and current row
                       ) as avg_3
from transactions t;