有如下表:
P_ID BEG_DATE NV_RATIO
3622 2008-08-01 0.081533984037
3622 2008-08-04 0.082591573711
3622 2008-08-05 0.086865772865
3622 2008-08-06 0.085439824955
3623 2008-08-01 0.036368377651
3623 2008-08-04 0.036360628372
3623 2008-08-05 0.03378277402
3623 2008-08-06 0.032583048812
3624 2008-08-01 0.1346154457316
3624 2008-08-04 0.1352286279365
3624 2008-08-05 0.1307188835939
3624 2008-08-06 0.0806435841214
3625 2008-08-01 0.0224598146893
3625 2008-08-04 0.0227747950255
3625 2008-08-05 0.0227315040113
3625 2008-08-06 0.0226752279185希望得到的是按P_ID进行归类,增加一列NV_RATIO_PRE将前一天的NV_RATIO放到后一天的NV_RATIO_PRE位置:
最终结果如下:
P_ID BEG_DATE NV_RATIO NV_RATIO_PRE
3622 2008-08-01 0.081533984037 NULL
3622 2008-08-04 0.082591573711 0.081533984037
3622 2008-08-05 0.086865772865 0.082591573711
3622 2008-08-06 0.085439824955 0.086865772865
3623 2008-08-01 0.036368377651 NULL
3623 2008-08-04 0.036360628372 0.036368377651
3623 2008-08-05 0.03378277402 0.036360628372
3623 2008-08-06 0.032583048812 0.03378277402
3624 2008-08-01 0.1346154457316 NULL
3624 2008-08-04 0.1352286279365 0.1346154457316
3624 2008-08-05 0.1307188835939 0.1352286279365
3624 2008-08-06 0.0806435841214 0.1307188835939
3625 2008-08-01 0.0224598146893 NULL
3625 2008-08-04 0.0227747950255 0.0224598146893
3625 2008-08-05 0.0227315040113 0.0227747950255
3625 2008-08-06 0.0226752279185 0.0226752279185
P_ID BEG_DATE NV_RATIO
3622 2008-08-01 0.081533984037
3622 2008-08-04 0.082591573711
3622 2008-08-05 0.086865772865
3622 2008-08-06 0.085439824955
3623 2008-08-01 0.036368377651
3623 2008-08-04 0.036360628372
3623 2008-08-05 0.03378277402
3623 2008-08-06 0.032583048812
3624 2008-08-01 0.1346154457316
3624 2008-08-04 0.1352286279365
3624 2008-08-05 0.1307188835939
3624 2008-08-06 0.0806435841214
3625 2008-08-01 0.0224598146893
3625 2008-08-04 0.0227747950255
3625 2008-08-05 0.0227315040113
3625 2008-08-06 0.0226752279185希望得到的是按P_ID进行归类,增加一列NV_RATIO_PRE将前一天的NV_RATIO放到后一天的NV_RATIO_PRE位置:
最终结果如下:
P_ID BEG_DATE NV_RATIO NV_RATIO_PRE
3622 2008-08-01 0.081533984037 NULL
3622 2008-08-04 0.082591573711 0.081533984037
3622 2008-08-05 0.086865772865 0.082591573711
3622 2008-08-06 0.085439824955 0.086865772865
3623 2008-08-01 0.036368377651 NULL
3623 2008-08-04 0.036360628372 0.036368377651
3623 2008-08-05 0.03378277402 0.036360628372
3623 2008-08-06 0.032583048812 0.03378277402
3624 2008-08-01 0.1346154457316 NULL
3624 2008-08-04 0.1352286279365 0.1346154457316
3624 2008-08-05 0.1307188835939 0.1352286279365
3624 2008-08-06 0.0806435841214 0.1307188835939
3625 2008-08-01 0.0224598146893 NULL
3625 2008-08-04 0.0227747950255 0.0224598146893
3625 2008-08-05 0.0227315040113 0.0227747950255
3625 2008-08-06 0.0226752279185 0.0226752279185
解决方案 »
- 求一个计数sql
- ora-12154 TNS:无法处理服务名
- 无意间删除了一个数据库实例的文件导致创建数据库提示数据库未打开
- ORACLE三表查询优化问题
- oracle 自定义类型可以用现有表作为其中一个属性的类型吗?
- 依次求002~009,003~009,004~009,005~009,006~009,007~009,008~009 ,009~009...........中的最大值
- oracle的问题
- 求救:关于oracle数据导出问题,在线等待
- 考计算机系的研究生有用吗?
- win8系统安装oracle11出(64位)后 创建数据库遇到的问题
- 请问用 sql loader 的时候,表中有一列是squence自动加1的,这样的ctl文件改怎么写呢,着急,谢谢!
- Orcale面试题
P_ID BEG_DATE NV_RATIO
3622 2008-08-01 0.081533984037
3622 2008-08-04 0.082591573711
3622 2008-08-05 0.086865772865
3622 2008-08-06 0.085439824955
3623 2008-08-01 0.036368377651
3623 2008-08-04 0.036360628372
3623 2008-08-05 0.03378277402
3623 2008-08-06 0.032583048812
3624 2008-08-01 0.1346154457316
3624 2008-08-04 0.1352286279365
3624 2008-08-05 0.1307188835939
3624 2008-08-06 0.0806435841214
3625 2008-08-01 0.0224598146893
3625 2008-08-04 0.0227747950255
3625 2008-08-05 0.0227315040113
3625 2008-08-06 0.0226752279185 希望得到的是按P_ID进行归类,增加一列NV_RATIO_PRE将前一天的NV_RATIO放到后一天的NV_RATIO_PRE位置:
最终结果如下:
P_ID BEG_DATE NV_RATIO NV_RATIO_PRE
3622 2008-08-01 0.081533984037 NULL
3622 2008-08-04 0.082591573711 0.081533984037
3622 2008-08-05 0.086865772865 0.082591573711
3622 2008-08-06 0.085439824955 0.086865772865
3623 2008-08-01 0.036368377651 NULL
3623 2008-08-04 0.036360628372 0.036368377651
3623 2008-08-05 0.03378277402 0.036360628372
3623 2008-08-06 0.032583048812 0.03378277402
3624 2008-08-01 0.1346154457316 NULL
3624 2008-08-04 0.1352286279365 0.1346154457316
3624 2008-08-05 0.1307188835939 0.1352286279365
3624 2008-08-06 0.0806435841214 0.1307188835939
3625 2008-08-01 0.0224598146893 NULL
3625 2008-08-04 0.0227747950255 0.0224598146893
3625 2008-08-05 0.0227315040113 0.0227747950255
3625 2008-08-06 0.0226752279185 0.0226752279185
from t;
TRY IT ..
SQL> SELECT TT.*,
2 LAG(NV_RATIO) OVER(PARTITION BY P_ID ORDER BY BEG_DATE) "NV_RATIO_PRE",
3 DECODE(TRUNC(BEG_DATE)-TRUNC((LAG(BEG_DATE) OVER(PARTITION BY P_ID ORDER BY BEG_DATE))),1,(LAG(NV_RATIO) OVER(PARTITION BY P_ID ORDER BY BEG_DATE)),NULL) "NV_RATIO_PRE_DAY_BY_DAY"
4 FROM TABLE_NAME TT; P_ID BEG_DATE NV_RATIO NV_RATIO_PRE NV_RATIO_PRE_DAY_BY_DAY
---------- ----------- ---------- ------------ -----------------------
3622 8/1/2008 0.08153398
3622 8/4/2008 0.08259157 0.0815339840
3622 8/5/2008 0.08686577 0.0825915737 0.082591573711
3622 8/6/2008 0.08543982 0.0868657728 0.086865772865
3623 8/1/2008 0.03636837
3623 8/4/2008 0.03636062 0.0363683776
3623 8/5/2008 0.03378277 0.0363606283 0.036360628372
3623 8/6/2008 0.03258304 0.0337827740 0.033782774028 rows selectedSQL>
from t;