17,082
社区成员
发帖
与我相关
我的任务
分享5年sql经验也不能解决的问题，帮个忙，谢谢了
需要用sql语句求移动平均线，这个比较容易，可以使用oracle分析函数，但是如何处理缺失数据（第二列为零的行均为缺失数据），这对移动平均值的影响很大。
需求：将连续为零的行用第一个遇到的部位零的数据填补，如，2007-03-01到2007-03-08日的数据应该补为3.47，2007-03-16到
2007-03-27的数据应该补为4.64，请问如何使用sql语句做到这一点。
下列数据，各位可用sqlldr导入数据库中。
2007-03-01,0
2007-03-02,0
2007-03-05,0
2007-03-06,0
2007-03-07,0
2007-03-08,3.47
2007-03-09,3.64
2007-03-12,3.82
2007-03-13,4.01
2007-03-14,4.21
2007-03-15,4.42
2007-03-16,0
2007-03-19,0
2007-03-20,0
2007-03-21,0
2007-03-22,0
2007-03-23,0
2007-03-26,0
2007-03-27,0
2007-03-28,4.64
2007-03-29,4.87
2007-03-30,5.11
2007-04-02,5.37
2007-04-03,5.64
2007-04-04,0
2007-04-05,5.92
2007-04-06,6.22
2007-04-09,6.18
2007-04-10,6.49
2007-04-11,6.45
2007-04-12,6.42
2007-04-13,6.74
2007-04-16,7.08
2007-04-17,7.43
2007-04-18,7.8
2007-04-19,8.19
2007-04-20,8.6
2007-04-23,0
2007-04-24,8.17
2007-04-25,8.37
2007-04-26,8.79
2007-04-27,9.23
2007-04-30,0
2007-05-08,0
2007-05-09,0
2007-05-10,0
2007-05-11,0
2007-05-14,9.69
2007-05-15,10.17
2007-05-16,10.68
2007-05-17,11.21
2007-05-18,0
2007-05-21,0
2007-05-22,0
2007-05-23,0
2007-05-24,0
2007-05-25,0
2007-05-28,0
2007-05-29,0
2007-05-30,0
2007-05-31,0
2007-06-01,0
2007-06-04,0
2007-06-05,0
2007-06-06,0
2007-06-07,0
2007-06-08,0
2007-06-11,0
2007-06-12,0
2007-06-13,0
2007-06-14,0
2007-06-15,0
2007-06-18,0
2007-06-19,0
2007-06-20,0
2007-06-21,0
2007-06-22,0
2007-06-25,0
2007-06-26,0
2007-06-27,0
2007-06-28,11.12
2007-06-29,10.56
2007-07-02,10.03
2007-07-03,9.53
2007-07-04,9.05
2007-07-05,8.6
2007-07-06,8.2
2007-07-09,7.79
2007-07-10,7.4
2007-07-11,7.03
2007-07-12,6.69
2007-07-13,7.02
2007-07-16,6.81
2007-07-17,6.77
2007-07-18,6.44
2007-07-19,6.62
2007-07-20,6.8
2007-07-23,7.02
2007-07-24,6.91
2007-07-25,7.1
2007-07-26,7.46
2007-07-27,7.83
2007-07-30,8.22
2007-07-31,8.63
2007-08-01,8.69
2007-08-02,8.8
2007-08-03,8.41
需求：将连续为零的行用第一个遇到的部位零的数据填补，如，2007-03-01到2007-03-08日的数据应该补为3.47，2007-03-16到
2007-03-27的数据应该补为4.64，请问如何使用sql语句做到这一点。
下列数据，各位可用sqlldr导入数据库中。
2007-03-01,0
2007-03-02,0
2007-03-05,0
2007-03-06,0
2007-03-07,0
2007-03-08,3.47
2007-03-09,3.64
2007-03-12,3.82
2007-03-13,4.01
2007-03-14,4.21
2007-03-15,4.42
2007-03-16,0
2007-03-19,0
2007-03-20,0
2007-03-21,0
2007-03-22,0
2007-03-23,0
2007-03-26,0
2007-03-27,0
2007-03-28,4.64
2007-03-29,4.87
2007-03-30,5.11
2007-04-02,5.37
2007-04-03,5.64
2007-04-04,0
2007-04-05,5.92
2007-04-06,6.22
2007-04-09,6.18
2007-04-10,6.49
2007-04-11,6.45
2007-04-12,6.42
2007-04-13,6.74
2007-04-16,7.08
2007-04-17,7.43
2007-04-18,7.8
2007-04-19,8.19
2007-04-20,8.6
2007-04-23,0
2007-04-24,8.17
2007-04-25,8.37
2007-04-26,8.79
2007-04-27,9.23
2007-04-30,0
2007-05-08,0
2007-05-09,0
2007-05-10,0
2007-05-11,0
2007-05-14,9.69
2007-05-15,10.17
2007-05-16,10.68
2007-05-17,11.21
2007-05-18,0
2007-05-21,0
2007-05-22,0
2007-05-23,0
2007-05-24,0
2007-05-25,0
2007-05-28,0
2007-05-29,0
2007-05-30,0
2007-05-31,0
2007-06-01,0
2007-06-04,0
2007-06-05,0
2007-06-06,0
2007-06-07,0
2007-06-08,0
2007-06-11,0
2007-06-12,0
2007-06-13,0
2007-06-14,0
2007-06-15,0
2007-06-18,0
2007-06-19,0
2007-06-20,0
2007-06-21,0
2007-06-22,0
2007-06-25,0
2007-06-26,0
2007-06-27,0
2007-06-28,11.12
2007-06-29,10.56
2007-07-02,10.03
2007-07-03,9.53
2007-07-04,9.05
2007-07-05,8.6
2007-07-06,8.2
2007-07-09,7.79
2007-07-10,7.4
2007-07-11,7.03
2007-07-12,6.69
2007-07-13,7.02
2007-07-16,6.81
2007-07-17,6.77
2007-07-18,6.44
2007-07-19,6.62
2007-07-20,6.8
2007-07-23,7.02
2007-07-24,6.91
2007-07-25,7.1
2007-07-26,7.46
2007-07-27,7.83
2007-07-30,8.22
2007-07-31,8.63
2007-08-01,8.69
2007-08-02,8.8
2007-08-03,8.41
...全文
请发表友善的回复…
发表回复
sunqcn 2007-09-12
- 打赏
- 举报
忘了说一声，多谢多谢！
sunqcn 2007-09-12
- 打赏
- 举报
高手啊，给分！
dragon188 2007-09-12
- 打赏
- 举报
表为T_A,用这个语句可以抽取,怎么更新就明白了撒
SELECT A.F_RQ,
DECODE(A.F_JE,0,
(SELECT B.F_JE
FROM (SELECT F_RQ, F_JE FROM T_A WHERE F_JE > 0 ORDER BY F_RQ) B
WHERE B.F_RQ > A.F_RQ
AND ROWNUM = 1),
A.F_JE) F_JE
FROM T_A A
SELECT A.F_RQ,
DECODE(A.F_JE,0,
(SELECT B.F_JE
FROM (SELECT F_RQ, F_JE FROM T_A WHERE F_JE > 0 ORDER BY F_RQ) B
WHERE B.F_RQ > A.F_RQ
AND ROWNUM = 1),
A.F_JE) F_JE
FROM T_A A
