sql - Counting number of non-trading days/days without price changes -

I have the closing value for bonds over time with compulsory structure:

  Bond_ID | Tdate | Price --------- + ------------ + ------- EIX1923 | 2014-01-01 | 100.12 EIX1923 | 2014-01-02 | 100.10Ex1923 | 2014-01-05 | 100.10Ex1923 | 2014-01-10 | 100.15  

As you can see, I do not have the cost of every day - because the bond does not trade every day. I want to count that how often this happens in a given year and if the bond price has not changed between consecutive days, then I take it as the result.

For one year with this N trading days (excluding weekends, excluding holidays), I essentially want to prepare a series of dates and calculate how many days The price of (1) is unchanged from the previous day or (2) is not returned to the day and divide it on N.

I am using PostgreSQL, so I created the gener_series ('2014-01-01' :: timestamp, '2015-01-01' :: timestamp, '1 day' :: Interval) ; I can select from this series and take a WHERE to exclude the weekend:

 Select_series to choose from DD ('2014-01-01' :: Timestamp, '2015- 01-01 ':: timestamp,' 1 day ':: timestamp) DD is not extract (Dow FDD) (0, 6);  

Now, I think I want to generate the "column" of bond_id , with it join the business table, But I'm not sure how, essentially, I thought the simplest structure would be a LEFT JOIN , so that I get something:

  EIX1923 | 2014-01-01 | 100.12 EIX1923 | 2014-01-02 | 100.10Ex1923 | 2014-01-03 | EIX1923 | 2014-01-04 | EIX1923 | 2014-01-05 | 100.10Ex1923 | 2014-01-06 | EIX1923 | 2014-01-07 | EIX1923 | 2014-01-08 | EIX1923 | 2014-01-09 | EIX1923 | 2014-01-10 | 100.15  

Then I could just fill the gap with the most available price and calculate the code in the number of ABS (ΔP) == 0 is. But if there are solutions to do this completely in SQL, then it would be good too! I do not know that there is the right to go with the above approach.

(I was not bothered to see if there are weekends on the first day of January 2014, because it's just for example; but the results will be clearly excluded).

EDIT: It seems that there may be many such questions already in the hope that it is not very duplicate!

EDIT: So, I played a bit more with it and this solution "works" (and I feel stupid to not feel too soon): P <<> <<> SELECT 'EI653670', DD, t.price from gener_series ('2014-01-01' :: Timestamp, '2015-01-01' :: Timestamp, '1 day' :: Interval) Dd LEFT DD = t.tdate And t.id = 'EI653670' include trade t where extract (Dow to DD) (0, 6) order by DD;

Is there a better way?

I think you can do this with lag () Do the logic. The following idea is considered as general idea - get a past date and value and make some arguments:

  select bond_id, sum (case when prev_price = value then date - prev_date + 1 when prev_date = date + interval ' 1 day 'then 0 and date - prev_date end) (select t. *, End (t.date) is over (order by t.date by t.bond_id) prev_date, more than lag (t.price) T.B. Sorted by T.BA.ID) division as the prev_price trade t) T group by bond_id;  

One of the warnings is that it will probably not be able to handle the conditions of the boundary conditions that you want.