Data aggregation for each record over the last seven days

I'd like to aggregate data for each record over the last 7 days in SQL.
The DB used is SQLite3.
For example,

Date | Number of steps
--------------------------
2018-01-01|6000
2018-01-02 | 5500
2018-01-03 | 200
2018-01-04 | 9700
2018-01-05|1100
2018-01-06 | 5000
2018-01-07|800
2018-01-08|3600
2018-01-09|6000
2018-01-10 | 0

for the data that

Date | Average number of steps taken in the past week
-----------------------

2018-01-08|3187
2018-01-09 | 3700 2018-01-10 | 3771

I'd like to add up as shown in . (2018-01-01 to 2018-01-07 may or may not be added up.)

Could you write this aggregate in one SQL?

sqlite

2022-09-30 21:31

1 Answers

Is it easy to understand if you use a correlation subquery?

SELECT
  Date,
  (SELECT AVG (t2. Number of steps)
   FROM t AS t2
   WHERE date(t1.date, '-7days')<=t2.date AND t2.date<t1.date)AS Average number of steps in the past week
FROM tAS t1;

If there are too many lines and performance is a problem, should I self-combine and aggregate them?

SELECT t1. Date, AVG (t2. Number of steps) AS Average number of steps in the past week
FROM t AS t1
INNER JOINT AST2
ON date(t1.date, '-7days')<=t2.date AND t2.date<t1.date
GROUP BY t1.Date;

2022-09-30 21:31

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656