这题是我上周的题目, 经过问老师之后, 发现我理解错了, 现在急需大家帮忙以下是英文原题Consider the following relational schema about daily stock prices.
StockPrice (stockid; timeid; price)
解决方案 »
- mysql 一个存储过程内部顺序执行还是并发执行的
- 菜鸟求学:如何用MYSQL实现图片文件的使用啊?
- 我在本地安装了MySQL Server 5.1,如何连接到远程的一个MySQL服务器呢?
- SQL问题..
- 如何执行d:\workspace\dam.sql文件
- 高分求教postgresql 数组字段的使用方法
- 请教各位关于POSTGRESQL的性能问题!!
- Mysql有关 like '%a%' 的查询问题?
- mysql读写分离到底有没有什么实际意义
- 杭州博讯软件有限公司招聘PHP+MYSQL 程序员
- mysql在win2008 server下的64位驱动问题
- MySQl的存储过程被人删除了,怎么知道是谁干的?
StockPrice (stockid; timeid; price)
将日期 timeid 简化为整形,以表示第 X 天的股价
from StockPrice a ,StockPrice b
where a.stockid=b.stockid
and a.timeid>=b.timeid and a.timeid<=b.timeid+14
group by a.stockid,a.timeidNote that we want strict 15-day moving average, meaning that
There should be no 15-day moving average for the first 14 days (as shown in
the above example).
If there are missing data (e.g., the price for a stock at some date is missing)
within the 15-day (sliding) window, the computation on this window should
be abandoned.注意,这里是严格的15的平均价,即本日+前14天,如果缺少,则放弃。
and st.time_id between st.time_id and st.time_id+5
and st.time_id>=10
group by st.time_id;
+----+---------+-------+---------+
| id | time_id | price | stockid |
+----+---------+-------+---------+
| 1 | 5 | 12 | 101 |
| 2 | 6 | 10 | 101 |
| 3 | 7 | 8 | 101 |
| 4 | 8 | 13 | 101 |
| 5 | 9 | 11 | 101 |
| 6 | 10 | 11 | 101 |
| 7 | 11 | 11 | 101 |
| 8 | 12 | 16 | 101 |
| 9 | 13 | 16 | 101 |
| 10 | 14 | 15 | 101 |
| 11 | 15 | 15 | 101 |
| 12 | 16 | 14 | 101 |
| 13 | 17 | 11 | 101 |
| 14 | 18 | 10 | 101 |
| 15 | 19 | 10 | 101 |
| 16 | 20 | 10 | 101 |
| 17 | 21 | 10 | 101 |
| 18 | 22 | 12 | 101 |
| 19 | 23 | NULL | 101 |
| 20 | 24 | 10 | 101 |
+----+---------+-------+---------+
20 rows in set (0.00 sec)
这个query 是包含了缺少则放弃的 限制吗?
我看不懂这个是怎么判断缺少则放弃
select a.stockid,a.timeid,avg(b.price)
from StockPrice a ,StockPrice b
where a.stockid=b.stockid
and a.timeid>=b.timeid and a.timeid <=b.timeid+14
group by a.stockid,a.timeid
hvaing count(*)=15
哦 谢了 但是 能解析一下GROUP BY 那里 为什么要timeid呢