fork download 
  1. -- Window関数 --
  2. SELECT date, name, score, avg(score) OVER (
  3. 	PARTITION BY name
  4. 	ORDER BY date
  5. 	ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  6. ) AS moving_avg
  7. FROM score_table
  8. ORDER BY name;
  9.  
  10. -- 相関サブクエリ --
  11. SELECT date, name, score, 
  12. 	(SELECT avg(score)
  13. 		FROM score_table t2
  14.     	WHERE t2.name = t1.name
  15.     	AND t2.date BETWEEN date(t1.date, '-29 days') AND t1.date
  16. ) AS moving_avg
  17. FROM score_table t1;
  18.  
Success #stdin #stdout #stderr 0s 4392KB
comments (?)
stdin 
Standard input is empty
stdout 
Standard output is empty
stderr 
Error: near line 2: no such table: score_table
Error: near line 11: no such table: score_table
https://ideone.com/73jaTZ
language:
SQLite (sqlite 3.27.2)
created:
5 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!