I'm writing a Python/Django application to do some stock analysis.
I have two very simple models that look like this:
class Stock(models.Model): symbol = models.CharField(db_index=True, max_length=5, null=False, editable=False, unique=True) class StockHistory(models.Model): stock = models.ForeignKey(Stock, related_name='StockHistory_stock', editable=False) trading_date = models.DateField(db_index=True, null=False, editable=False) close = models.DecimalField(max_digits=12, db_index=True, decimal_places=5, null=False, editable=False) class Meta: unique_together = ('stock', 'trading_date') This is the dummy data I have populated them with:
import datetime a = Stock.objects.create(symbol='A') b = Stock.objects.create(symbol='B') c = Stock.objects.create(symbol='C') d = Stock.objects.create(symbol='D') StockHistory.objects.create(trading_date=datetime.date(2018,1,1), close=200, stock=a) StockHistory.objects.create(trading_date=datetime.date(2018,1,2), close=150, stock=a) StockHistory.objects.create(trading_date=datetime.date(2018,1,3), close=120, stock=a) StockHistory.objects.create(trading_date=datetime.date(2018,4,28), close=105, stock=a) StockHistory.objects.create(trading_date=datetime.date(2018,5,3), close=105, stock=a) StockHistory.objects.create(trading_date=datetime.date(2017,5,2), close=400, stock=b) StockHistory.objects.create(trading_date=datetime.date(2017,11,11), close=200, stock=b) StockHistory.objects.create(trading_date=datetime.date(2017,11,12), close=300, stock=b) StockHistory.objects.create(trading_date=datetime.date(2017,11,13), close=400, stock=b) StockHistory.objects.create(trading_date=datetime.date(2017,11,14), close=500, stock=b) StockHistory.objects.create(trading_date=datetime.date(2018,4,28), close=105, stock=c) StockHistory.objects.create(trading_date=datetime.date(2018,4,29), close=106, stock=c) StockHistory.objects.create(trading_date=datetime.date(2018,4,30), close=107, stock=c) StockHistory.objects.create(trading_date=datetime.date(2018,5,1), close=108, stock=c) StockHistory.objects.create(trading_date=datetime.date(2018,5,2), close=109, stock=c) StockHistory.objects.create(trading_date=datetime.date(2018,5,3), close=110, stock=c) StockHistory.objects.create(trading_date=datetime.date(2018,5,4), close=90, stock=c) I want to find all the stocks that made a yearly low within the past week.
But to make this question simpler, just assume that I want to find all the stocks whose lowest point since '2017-05-04' occurred on or after '2018-04-30'. Below is the SQL I wrote to find it. It works.
But I need help figuring out what Django Query to write to get the same results as this SQL. How can I do it?
mysql> select -> s.symbol, -> sh.trading_date, -> low_table.low -> from -> ( -> select -> stock_id, -> min(close) as low -> from -> stocks_stockhistory -> where -> trading_date >= '2017-05-04' -> group by -> stock_id -> ) as low_table, -> stocks_stockhistory as sh, -> stocks_stock as s -> where -> sh.stock_id = low_table.stock_id -> and sh.stock_id = s.id -> and sh.close = low_table.low -> and sh.trading_date >= '2018-04-30' -> order by -> s.symbol asc; +--------+--------------+-----------+ | symbol | trading_date | low | +--------+--------------+-----------+ | A | 2018-05-03 | 105.00000 | | C | 2018-05-04 | 90.00000 | +--------+--------------+-----------+ 2 rows in set (0.02 sec) 2 Answers
Answers 1
EDIT: I managed to reform the solution using Django subqueries.
We can translate the query to Django ORM using Django's aggregates with SubQuery expressions:
Create a subquery to retrieve the lowest
closefor everysymbol:from django.db.models import OuterRef, Subquery, Min lows = StockHistory.objects.filter( stock=OuterRef('stock'), trading_date__gte='2017-05-04' ).values('stock__symbol') .annotate(low=Min('close')) .filter(trading_date__gte='2018-04-30')Breakdown:
filterthe queryset to get only the stocks withtrading_date >= '2017-05-04'.- "GROUP BY"
stock__symbol(examples of group by in Djnago:GROUP BY ... MIN/MAX,GROUP BY ... COUNT/SUM). annotatethe lowest (low) price to every element.filterthe queryset again to get only the objects with alowfield occurring ontrading_date >= '2018-04-30'.
Intermediate Result:
Although we cannot get a result at this stage, the subquery will look like this:
[ {'stock__symbol': 'A', 'low': Decimal('105.00000')}, {'stock__symbol': 'C', 'low': Decimal('90.00000')} ]We are missing the
trading_date.
Utilize the subquery to retrieve the specific
StockHistoryobjects:StockHistory.objects.filter( stock__symbol=Subquery(lows.values('stock__symbol')), close=Subquery(lows.values('low')), trading_date__gte='2018-04-30' ).values('stock__symbol', 'trading_date', 'close') .order_by('stock__symbol')Breakdown:
lows.values('stock__symbol')and lows.values('low') retrieve the respective values from the subquery.filterthe queryset against thelowssubquery values. Alsofilteragainst the specified date in order to eliminate lowcloseprices occurring before that date.- Get the specified
values. - Order the result by
stock__symbol(by defaultascending).
Result:
[ { 'close': Decimal('105.00000'), 'trading_date': datetime.date(2018, 5, 3), 'stock__symbol': 'A' }, { 'close': Decimal('90.00000'), 'trading_date': datetime.date(2018, 5, 4), 'stock__symbol': 'C' } ]
Answers 2
For newer versions of Django (1.11, 2.0):
from django.db.models import Min low_stocks_qs = StockHistory.objects.filter(trading_date__gt='2017-05-04').annotate(low=Min('close')).filter(trading_date__gte='2018-04-30').order_by('stock__symbol') You can iterate through the queryset to get individual values of low and stock.symbol, maybe something like this:
low_stocks_dict = {} for inst in low_stocks_qs: low_stocks_dict[inst.stock.Symbol] = inst.low 
0 comments:
Post a Comment