SQL自动为表中的缺少日期自动生成前一日期的缺失日期和价格 [英] SQL to automatically generate missing dates and price from immediately previous date for missing date in table

问题描述

我有一个表MKT，其中包含以下字段value_date,stk_exch,security,mkt_price,source,currency,name 每天说500种证券(不包括周六和周日及其他市场假期).

I have a table MKT which contains following fields value_date,stk_exch,security,mkt_price,source,currency,name of say 500 securities for each day (excluding Saturday and Sunday and other Market Holidays).

我需要一个SQL自动生成缺少日期和价格，该日期和价格来自前一个日期.因此，如果星期五是2013年7月26日，星期六和星期日是27日和28日，则此表中将缺少27日和28日的日期和价格.

I need an sql to automatically generate missing dates and price from immediately previous date for missing date. So if Friday is 26.07.2013 and Saturday and Sunday are 27th and 28th, then date and prices for 27th and 28th would be missing from this table.

因此，在假冒2013年7月整个月的价格时，我应该获取所有日期和缺少日期的信息，例如:sql和27，sql的价格为26日.

So while spooling the prices for entire month July 2013 I should get all dates and for missing dates eg: 27 and 28 the sql would take price of 26th.

我正在使用Oracle

I am using Oracle

value_date   stk_exch    security      mkt_price
-------------------------------------------------    
26/07/2013   BSE         BANKBARODA    565.85   
29/07/2013   BSE         BANKBARODA    585.85   

现在SQL应该返回

value_date  stk_exch     security      mkt_price
-------------------------------------------------
26/07/2013  BSE          BANKBARODA    565.85   
27/07/2013  BSE          BANKBARODA    565.85   
28/07/2013  BSE          BANKBARODA    565.85   
29/07/2013  BSE          BANKBARODA    585.85   

推荐答案

我已将your_table与outerjoin用作一个表，并使用last_value插入了先前的数据， 看看这个:)

I've used your_table as one table with outerjoin, as used last_value to insert previous data, have a look at this :)

SELECT last_value(m.data ignore nulls) over (order by n.mydate) data,
     n.mydate
FROM
     (SELECT DATA, mydate FROM your_table
     ) m,
     (SELECT TRUNC(SYSDATE, 'MM')-1+LEVEL mydate FROM dual CONNECT BY LEVEL <= 30
     )n
WHERE m.mydate(+) = n.mydate
ORDER BY n.mydate;

在这里拨弄

您也可以使用lag()函数，但是如果日期间隔超过一个，它将不会填充数据.它只会填充之前的即时数据

You could use lag() function also , but it won't fill data if date gap is more than one., it only fills immediate previous data,

nvl(m.data, lag(m.data)over(order by n.mydate))

- 编辑 -

获取数据:

SELECT n.mydate VALUE_DATE,
     last_value(m.STK_EXCH IGNORE NULLS) OVER (ORDER BY n.mydate) STK_EXCH,
     last_value(m.SECURITY IGNORE NULLS) OVER (ORDER BY n.mydate) SECURITY,
     last_value(m.mkt_price IGNORE NULLS) OVER (ORDER BY n.mydate) MKT_PRICE
FROM
     (SELECT VALUE_DATE, STK_EXCH, SECURITY, MKT_PRICE FROM MKT
     ) m,
     (SELECT TRUNC(SYSDATE, 'MM')-1+LEVEL mydate FROM dual CONNECT BY LEVEL <= 30
     )n
WHERE TRUNC(m.VALUE_DATE(+)) = TRUNC(n.mydate)
ORDER BY n.mydate;

或更具体一点:在下面使用:

or little more specific: use below:

SELECT full_date,
     NVL(stk_exch,last_value(stk_exch IGNORE NULLS)OVER(ORDER BY full_date))stk_exch,
     NVL(security,last_value(security IGNORE NULLS)OVER(ORDER BY full_date))security,
     NVL(mkt_price,last_value(mkt_price IGNORE NULLS)OVER(ORDER BY full_date))mkt_price
FROM
     (SELECT TRUNC(m.vd,'MM')-1+LEVEL FULL_DATE
     FROM
          (SELECT MIN(VALUE_DATE) vd FROM mkt
          WHERE TO_CHAR( value_date, 'MM/YYYY') = TO_CHAR(sysdate-12,'MM/YYYY') -- this line may vary to your requirement
          ) m
     CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(M.VD), 'DD')
     )first_q,
     ( SELECT value_date, stk_exch, SECURITY, mkt_price FROM mkt
     )r
WHERE first_q.full_date = TRUNC(r.value_date(+))
ORDER BY full_date;

在此处查看小提琴

这篇关于SQL自动为表中的缺少日期自动生成前一日期的缺失日期和价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！