python sql语句转换_python – 如何将此SQL语句转换为Django QuerySet？

请注意：这个问题是我几天前问过的

this question的后续问题.

它不是重复的.我正在尝试在Django中建模的SQL查询和我加载的虚拟数据之间存在轻微但显着的差异.

我正在编写一个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,2), 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 Query以获得与此SQL相同的结果.我该怎么做？在我上一个问题的答案中提供的Django产生了3行结果而不是2行.

select

s.symbol,

min(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'

group by

s.symbol,

low_table.low

order by

s.symbol asc;

+--------+----------------------+--------------------+

| symbol | min(sh.trading_date) | min(low_table.low) |

+--------+----------------------+--------------------+

| A | 2018-05-02 | 105.00000 |

| C | 2018-05-04 | 90.00000 |

+--------+----------------------+--------------------+

2 rows in set (0.01 sec)