我正在编写一个python/django应用程序来进行股票分析。
我有两个非常简单的模型，看起来像这样：
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')这是我填充的虚拟数据：
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)我想找出过去一周内所有创下年内新低的股票。
但为了让这个问题更简单，假设我想找出所有股票的最低点 '2017-05-04' 发生在或之后 '2018-04-30' . 下面是我为找到它而编写的sql。它起作用了。
但是，我需要帮助确定要编写什么django查询才能获得与此sql相同的结果。我该怎么做？
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条答案
按热度按时间xfyts7mz1#
对于较新版本的django（1.11、2.0）：
您可以迭代查询集以获得low和stock.symbol的单独值，可能类似于：
lkaoscv72#
编辑：我设法用django子查询修改了解决方案。
我们可以使用django的
aggregates with SubQuery expressions:创建子查询以检索最低
close每symbol:分解：
filter查询设置为只获取trading_date >= '2017-05-04'.“分组依据”
stock__symbol（djnago中的group by示例：GROUP BY ... MIN/MAX,GROUP BY ... COUNT/SUM).annotate最低的(low)每个元素的价格。filter查询再次设置以仅获取具有low发生在上的字段trading_date >= '2018-04-30'.中间结果：
虽然我们在这个阶段无法得到结果，但子查询将如下所示：
我们错过了
trading_date.利用子查询检索特定的
StockHistory物体：分解：
lows.values('stock__symbol')values（'low'）从子查询中检索相应的值。filter质询针对lows子查询值。也filter为了消除低成本close在此日期之前发生的价格。获取指定的
values.按顺序排列结果
stock__symbol（默认情况下ascending).结果：