teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問MySQLでの検索条件について

編集履歴

質問編集履歴

5

出力結果を追加

2019/07/01 00:44

投稿

miya71ma
miya71ma

スコア49

test CHANGED
File without changes
test CHANGED
@@ -106,9 +106,9 @@
106
106
 
107
107
  |2000|78|75|200|2019-06-20 15:00:00|
108
108
 
109
- |1000|75|81|300|2019-06-19 15:00:00|
109
+ |2000|75|81|300|2019-06-19 15:00:00|
110
110
 
111
- |1000|81|73|400|2019-06-18 15:00:00|
111
+ |2000|81|73|400|2019-06-18 15:00:00|
112
112
 
113
113
  |3000|90|100|1000|2019-06-27 15:00:00|
114
114
 
@@ -122,9 +122,9 @@
122
122
 
123
123
  |3000|90|88|100|2019-06-20 15:00:00|
124
124
 
125
- |1000|87|81|300|2019-06-19 15:00:00|
125
+ |3000|87|81|300|2019-06-19 15:00:00|
126
126
 
127
- |1000|81|76|200|2019-06-18 15:00:00|
127
+ |3000|81|76|200|2019-06-18 15:00:00|
128
128
 
129
129
 
130
130
 
@@ -147,3 +147,13 @@
147
147
 
148
148
 
149
149
  で平均は出せるのですがX%以上というのをどのように検索条件に反映させればいいのでしょうか
150
+
151
+ ##想定出力結果
152
+
153
+ |SC|volume|av|av_per|date|
154
+
155
+ |:--|:--|:--|:--|:--|
156
+
157
+ |1000|900|475|1.89|2019-06-27 15:00:00|
158
+
159
+ |3000|1000|425|2.35|2019-06-27 15:00:00|

4

データの修正

2019/07/01 00:44

投稿

miya71ma
miya71ma

スコア49

test CHANGED
File without changes
test CHANGED
@@ -44,11 +44,11 @@
44
44
 
45
45
  (2000,63,78,100,'2019-06-21 15:00:00'),
46
46
 
47
- (1000,78,75,200,'2019-06-20 15:00:00'),
47
+ (2000,78,75,200,'2019-06-20 15:00:00'),
48
48
 
49
- (1000,75,84,300,'2019-06-19 15:00:00'),
49
+ (2000,75,84,300,'2019-06-19 15:00:00'),
50
50
 
51
- (1000,81,73,400,'2019-06-18 15:00:00'),
51
+ (2000,81,73,400,'2019-06-18 15:00:00'),
52
52
 
53
53
  (3000,90,100,900,'2019-06-27 15:00:00'),
54
54
 
@@ -60,11 +60,11 @@
60
60
 
61
61
  (3000,90,100,900,'2019-06-21 15:00:00');
62
62
 
63
- (1000,90,88,100,'2019-06-20 15:00:00'),
63
+ (3000,90,88,100,'2019-06-20 15:00:00'),
64
64
 
65
- (1000,87,81,300,'2019-06-19 15:00:00'),
65
+ (3000,87,81,300,'2019-06-19 15:00:00'),
66
66
 
67
- (1000,81,76,200,'2019-06-18 15:00:00'),
67
+ (3000,81,76,200,'2019-06-18 15:00:00'),
68
68
 
69
69
  ```
70
70
 

3

データ数追加

2019/07/01 00:37

投稿

miya71ma
miya71ma

スコア49

test CHANGED
File without changes
test CHANGED
@@ -28,6 +28,12 @@
28
28
 
29
29
  (1000,80,76,600,'2019-06-21 15:00:00'),
30
30
 
31
+ (1000,79,73,200,'2019-06-20 15:00:00'),
32
+
33
+ (1000,73,72,100,'2019-06-19 15:00:00'),
34
+
35
+ (1000,72,71,200,'2019-06-18 15:00:00'),
36
+
31
37
  (2000,100,90,300,'2019-06-27 15:00:00'),
32
38
 
33
39
  (2000,90,93,300,'2019-06-26 15:00:00'),
@@ -38,6 +44,12 @@
38
44
 
39
45
  (2000,63,78,100,'2019-06-21 15:00:00'),
40
46
 
47
+ (1000,78,75,200,'2019-06-20 15:00:00'),
48
+
49
+ (1000,75,84,300,'2019-06-19 15:00:00'),
50
+
51
+ (1000,81,73,400,'2019-06-18 15:00:00'),
52
+
41
53
  (3000,90,100,900,'2019-06-27 15:00:00'),
42
54
 
43
55
  (3000,100,90,900,'2019-06-26 15:00:00'),
@@ -47,6 +59,12 @@
47
59
  (3000,100,90,900,'2019-06-24 15:00:00'),
48
60
 
49
61
  (3000,90,100,900,'2019-06-21 15:00:00');
62
+
63
+ (1000,90,88,100,'2019-06-20 15:00:00'),
64
+
65
+ (1000,87,81,300,'2019-06-19 15:00:00'),
66
+
67
+ (1000,81,76,200,'2019-06-18 15:00:00'),
50
68
 
51
69
  ```
52
70
 
@@ -70,6 +88,12 @@
70
88
 
71
89
  |1000|80|76|600|2019-06-21 15:00:00|
72
90
 
91
+ |1000|79|73|200|2019-06-20 15:00:00|
92
+
93
+ |1000|73|72|100|2019-06-19 15:00:00|
94
+
95
+ |1000|72|71|200|2019-06-18 15:00:00|
96
+
73
97
  |2000|100|90|300|2019-06-27 15:00:00|
74
98
 
75
99
  |2000|90|93|300|2019-06-26 15:00:00|
@@ -80,6 +104,12 @@
80
104
 
81
105
  |2000|63|78|500|2019-06-21 15:00:00|
82
106
 
107
+ |2000|78|75|200|2019-06-20 15:00:00|
108
+
109
+ |1000|75|81|300|2019-06-19 15:00:00|
110
+
111
+ |1000|81|73|400|2019-06-18 15:00:00|
112
+
83
113
  |3000|90|100|1000|2019-06-27 15:00:00|
84
114
 
85
115
  |3000|100|90|400|2019-06-26 15:00:00|
@@ -89,6 +119,12 @@
89
119
  |3000|100|90|100|2019-06-24 15:00:00|
90
120
 
91
121
  |3000|90|100|200|2019-06-21 15:00:00|
122
+
123
+ |3000|90|88|100|2019-06-20 15:00:00|
124
+
125
+ |1000|87|81|300|2019-06-19 15:00:00|
126
+
127
+ |1000|81|76|200|2019-06-18 15:00:00|
92
128
 
93
129
 
94
130
 

2

nの条件追加

2019/07/01 00:36

投稿

miya71ma
miya71ma

スコア49

test CHANGED
File without changes
test CHANGED
@@ -92,7 +92,7 @@
92
92
 
93
93
 
94
94
 
95
- として過去n日の平均出来高がx%以上であるを検索したい
95
+ として過去n日の平均出来高がx%以上であるを検索したい　nは土日を除く1日を指すとする
96
96
 
97
97
  ex.)n=4,x>1(100%より大きい)
98
98
 
@@ -100,7 +100,7 @@
100
100
 
101
101
  SELECT
102
102
 
103
- SC,volume,(SELECT avg(volume) from db1 WHERE SC=t1.SC LIMIT 5) 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
103
+ 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
104
104
 
105
105
  from db1 as t1
106
106
 

1

タイトルの訂正

2019/06/28 07:07

投稿

miya71ma
miya71ma

スコア49

test CHANGED
@@ -1 +1 @@
1
- phpのformat()について
1
+ MySQLでの検索条件について
test CHANGED
File without changes