如何将此SQL转换为Django查询? [英] How do I convert this SQL into a Django query?

问题描述

我正在编写一个Python/Django应用程序以进行一些库存分析.

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.

但是要简化这个问题，只需假设我想查找所有自'2017-05-04'以来的最低点发生在'2018-04-之后的股票.30'.以下是我为查找该SQL而编写的SQL.可以.

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.

但是我需要帮助弄清楚编写什么Django查询才能获得与此SQL相同的结果.我该怎么办?

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)

推荐答案

无需亲自构建它(这些查询需要花一些时间来思考它)，文档的这一部分应该可以帮助您:

Without trying to build it myself (these sorts of queries take a while to think about it) this part of the documentation should help you: Aggregation/Annotation.

这些可用于根据QuerySet的操作结果(例如Min，Avg等)进行复杂的查询.您可以用一个值(例如'year low')注释QuerySet，然后根据该值进行过滤.

These can be used to make complex queries based on the result of operations on the QuerySet (e.g. Min, Avg etc). You can annotate a QuerySet with a value (such as 'year low') and then filter based on that.

尝试一些尝试，但要记住的一件事是，Django查询可能看起来与SQL查询不一样-正确完成这些查询可能很困难，但是祝您好运！

Try a few things, but one thing to remember is that your Django query may look nothing like your SQL query - getting these queries right can be difficult, but good luck!

查询表达式在这样的查询.

这篇关于如何将此SQL转换为Django查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！