有难度的查询啊

breeze_air 2008-09-10 04:19:01

有如下表：
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

...全文

83 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

bugchen888 2008-09-11

打赏
举报

4楼正解, 在取lag()的时候不能简单的只使用order by BEG_DATE, 还要用partition by p_id.

hebo2005 2008-09-10

打赏
举报

咦，楼主居然是负的结贴率，看样子有贴子被删除了

vc555 2008-09-10

打赏
举报

select p_id,beg_date,nv_ratio,lag(NV_RATIO) over(partition by p_id order by BEG_DATE)
from t;

mantisXF 2008-09-10

打赏
举报

如果是按前一天的来取就按第4列的写法(It means: 2008-08-05和2008-08-04相差一天就取2008-08-04的NV_RATIO；2008-08-04和2008-08-01相差>1天，所以就是NULL,前面看错需求了)，
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.03378277402



8 rows selected



SQL>