MysqlCreateInsert
1CREATE TABLE db1( 2SC int(4) NOT NULL, 3todays_close double, 4ago_close double, 5volume double, 6`Date` datetime NOT NULL 7); 8 9insert into db1 values 10(1000,100,90,900,'2019-06-27 15:00:00'), 11(1000,91,87,100,'2019-06-26 15:00:00'), 12(1000,88,87,500,'2019-06-25 15:00:00'), 13(1000,87,81,400,'2019-06-24 15:00:00'), 14(1000,80,76,600,'2019-06-21 15:00:00'), 15(1000,79,73,200,'2019-06-20 15:00:00'), 16(1000,73,72,100,'2019-06-19 15:00:00'), 17(1000,72,71,200,'2019-06-18 15:00:00'), 18(2000,100,90,300,'2019-06-27 15:00:00'), 19(2000,90,93,300,'2019-06-26 15:00:00'), 20(2000,93,90,500,'2019-06-25 15:00:00'), 21(2000,90,64,300,'2019-06-24 15:00:00'), 22(2000,63,78,100,'2019-06-21 15:00:00'), 23(2000,78,75,200,'2019-06-20 15:00:00'), 24(2000,75,84,300,'2019-06-19 15:00:00'), 25(2000,81,73,400,'2019-06-18 15:00:00'), 26(3000,90,100,900,'2019-06-27 15:00:00'), 27(3000,100,90,900,'2019-06-26 15:00:00'), 28(3000,90,100,900,'2019-06-25 15:00:00'), 29(3000,100,90,900,'2019-06-24 15:00:00'), 30(3000,90,100,900,'2019-06-21 15:00:00'); 31(3000,90,88,100,'2019-06-20 15:00:00'), 32(3000,87,81,300,'2019-06-19 15:00:00'), 33(3000,81,76,200,'2019-06-18 15:00:00'),
#Database内
SC | todays_close | ago_close | volume | date |
---|---|---|---|---|
1000 | 100 | 90 | 900 | 2019-06-27 15:00:00 |
1000 | 91 | 87 | 100 | 2019-06-26 15:00:00 |
1000 | 88 | 87 | 500 | 2019-06-25 15:00:00 |
1000 | 87 | 81 | 400 | 2019-06-24 15:00:00 |
1000 | 80 | 76 | 600 | 2019-06-21 15:00:00 |
1000 | 79 | 73 | 200 | 2019-06-20 15:00:00 |
1000 | 73 | 72 | 100 | 2019-06-19 15:00:00 |
1000 | 72 | 71 | 200 | 2019-06-18 15:00:00 |
2000 | 100 | 90 | 300 | 2019-06-27 15:00:00 |
2000 | 90 | 93 | 300 | 2019-06-26 15:00:00 |
2000 | 93 | 90 | 100 | 2019-06-25 15:00:00 |
2000 | 90 | 64 | 300 | 2019-06-24 15:00:00 |
2000 | 63 | 78 | 500 | 2019-06-21 15:00:00 |
2000 | 78 | 75 | 200 | 2019-06-20 15:00:00 |
2000 | 75 | 81 | 300 | 2019-06-19 15:00:00 |
2000 | 81 | 73 | 400 | 2019-06-18 15:00:00 |
3000 | 90 | 100 | 1000 | 2019-06-27 15:00:00 |
3000 | 100 | 90 | 400 | 2019-06-26 15:00:00 |
3000 | 90 | 100 | 200 | 2019-06-25 15:00:00 |
3000 | 100 | 90 | 100 | 2019-06-24 15:00:00 |
3000 | 90 | 100 | 200 | 2019-06-21 15:00:00 |
3000 | 90 | 88 | 100 | 2019-06-20 15:00:00 |
3000 | 87 | 81 | 300 | 2019-06-19 15:00:00 |
3000 | 81 | 76 | 200 | 2019-06-18 15:00:00 |
として過去n日の平均出来高がx%以上であるを検索したい nは土日を除く1日を指すとする
ex.)n=4,x>1(100%より大きい)
MySQL
1SELECT 2 SC,volume,(SELECT avg(volume) from db1 WHERE SC=t1.SC AND date BETWEEN '2019-06-24 15:00:00' AND '2019-06-27 15:00:00') as av,volume/(SELECT avg(volume) from db1 WHERE SC=t1.SC AND date BETWEEN '2019-06-24 15:00:00' AND '2019-06-27 15:00:00') as av_per 3from db1 as t1 4WHERE not exists(SELECT 1 from db1 WHERE SC=t1.SC and Date>t1.date)
で平均は出せるのですがX%以上というのをどのように検索条件に反映させればいいのでしょうか
##想定出力結果
SC | volume | av | av_per | date |
---|---|---|---|---|
1000 | 900 | 475 | 1.89 | 2019-06-27 15:00:00 |
3000 | 1000 | 425 | 2.35 | 2019-06-27 15:00:00 |
回答2件
あなたの回答
tips
プレビュー