Menu
I have a sql table that stores the daily prices of stocks. New records are inserted every day after the market closes. I want to find the stocks that have consecutive increases in price.
The table has lots of columns, but this is the relevant subset:
quoteid stockid closeprice createdate
--------------------------------------------------
1 1 1 01/01/2012
2 2 10 01/01/2012
3 3 15 01/01/2012
4 1 2 01/02/2012
5 2 11 01/02/2012
6 3 13 01/02/2012
7 1 5 01/03/2012
8 2 13 01/03/2012
9 3 17 01/03/2012
10 1 7 01/04/2012
11 2 14 01/04/2012
12 3 18 01/04/2012
13 1 9 01/05/2012
14 2 11 01/05/2012
15 3 10 01/05/2012
The quoteid column is a primary key.
In the table, the closing price of stock id 1 increases every day. Stock id 3 fluctuates a lot, and the price for stock id 2 fell on the last day.
I am looking for a result like this:
stockid Consecutive Count (CC)
----------------------------------
1 5
2 4
If you can get output with dates for the consecutive streak, that would even better:
stockid Consecutive Count (CC) StartDate EndDate
---------------------------------------------------------------
1 5 01/01/2012 01/05/2012
2 4 01/01/2012 01/04/2012
StartDate is when the price started increasing and EndDate is when the bull run actually finished.
I have figured this is not an easy problem. I have looked at other posts here which also deal with this consecutive scenario but they don't fit my needs. If you know any post that is similar to mine, please do let me know.
623
In the select clause, we first check, if the variable values differ from the current row. If yes, we increment the group number, if not we leave it as it is. After that, we assign the values of the current row.
Without a column with order numbers if you perform SELECT from the database, the SQL officially does not make any guarantees that you will receive rows in the same order every time (and in many cases you won't). Solution Add another column, e.g. Id as integer and have each row have an incrementing value like this:
What this does is establish the sequence of records by means of the ROW_NUMBER () function (see the PostgreSQL tutorial here & a more comprehensive list here). Window functions are extremely powerful and well worth putting in the effort to get to know!
In any case, it helps to put it in terms of increasing rows-per-stock (the actual quoteid value isn't really helpful here). Count of days captured (in this table) is easiest-if you want something else (like only business days, ignoring weekends/holidays, or whatever) it gets more involved; you'd probably need a calendar file.
In any case, it helps to put it in terms of increasing rows-per-stock (the actual quoteid value isn't really helpful here). Count of days captured (in this table) is easiest - if you want something else (like only business days, ignoring weekends/holidays, or whatever) it gets more involved; you'd probably need a calendar file. You're going to want an index over [stockid, createdate], if you don't have one already.
WITH StockRow AS (SELECT stockId, closePrice, createdDate,
ROW_NUMBER() OVER(PARTITION BY stockId
ORDER BY createdDate) rn
FROM Quote),
RunGroup AS (SELECT Base.stockId, Base.createdDate,
MAX(Restart.rn) OVER(PARTITION BY Base.stockId
ORDER BY Base.createdDate) groupingId
FROM StockRow Base
LEFT JOIN StockRow Restart
ON Restart.stockId = Base.stockId
AND Restart.rn = Base.rn - 1
AND Restart.closePrice > Base.closePrice)
SELECT stockId,
COUNT(*) AS consecutiveCount,
MIN(createdDate) AS startDate, MAX(createdDate) AS endDate
FROM RunGroup
GROUP BY stockId, groupingId
HAVING COUNT(*) >= 3
ORDER BY stockId, startDate
Which yields the following results from the provided data:
Increasing_Run
stockId consecutiveCount startDate endDate
===================================================
1 5 2012-01-01 2012-01-05
2 4 2012-01-01 2012-01-04
3 3 2012-01-02 2012-01-04
SQL Fiddle Example
(Fiddle also has an example for multiple runs)
This analysis will ignore all gaps, correctly matches all runs (the next time a positive run starts).
So what's going on here?
StockRow AS (SELECT stockId, closePrice, createdDate,
ROW_NUMBER() OVER(PARTITION BY stockId
ORDER BY createdDate) rn
FROM Quote)
This CTE is being used for one purpose: we need a way to find the next/previous row, so first we number each row in order (of the date)...
RunGroup AS (SELECT Base.stockId, Base.createdDate,
MAX(Restart.rn) OVER(PARTITION BY Base.stockId
ORDER BY Base.createdDate) groupingId
FROM StockRow Base
LEFT JOIN StockRow Restart
ON Restart.stockId = Base.stockId
AND Restart.rn = Base.rn - 1
AND Restart.closePrice > Base.closePrice)
... and then join them based on the index. If you end up on something that has LAG()/LEAD(), using those instead will almost certainly be a better option. There's one critical thing here though - matches are only if the row is out-of-sequence (less than the previous row). Otherwise, the value end up being null (with LAG(), you'd need to use something like CASE afterwards to pull this off). You get a temporary set that looks something like this:
B.rn B.closePrice B.createdDate R.rn R.closePrice R.createdDate groupingId
1 15 2012-01-01 - - - -
2 13 2012-01-02 1 15 2012-01-01 1
3 17 2012-01-03 - - - 1
4 18 2012-01-04 - - - 1
5 10 2012-01-05 4 18 2012-01-04 4
... So there's values for Restart only when the previous was greater than the "current" row. The use of MAX() in the window function is being used to the greatest value seen so far... which because null is lowest, causes the row-index to be retained for all other rows until another mismatch occurs (which gives a new value). At this point, we essentially have the intermediate results of a gaps-and-islands query, ready for the final aggregation.
SELECT stockId,
COUNT(*) AS consecutiveCount,
MIN(createdDate) AS startDate, MAX(createdDate) AS endDate
FROM RunGroup
GROUP BY stockId, groupingId
HAVING COUNT(*) >= 3
ORDER BY stockId, startDate
The final part of the query is getting the start and end dates of the run, and counting the number of entries between those dates. If there was something more complicated for the date calculation, it probably needs to happen at this point. The GROUP BY is showing one of the few legitimate instances of not including a column in the SELECT clause. The HAVING clause is used to eliminate runs that are "too short".
185
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
