在一个查询中具有不同条件的多个SELECT语句

I have the following tables:

我有以下表格:

'Prices'

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| product_id | int(11)      | YES  |     | NULL    |                |
| price      | text         | YES  |     | NULL    |                |
| date       | text         | YES  |     | NULL    |                |
| time       | text         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

'Products'

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| category_id     | int(11)      | YES  |     | NULL    |                |
| product_urls    | text         | YES  |     | NULL    |                |
| product_title   | text         | YES  |     | NULL    |                |
| product_image   | text         | YES  |     | NULL    |                |
| product_content | text         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

The connection here is between Products.id and Prices.products_id.

此处的连接位于Products.id和Price.products_id之间。

I have a script running that grabs the URLs, scrapes prices from particular web pages and then updates the 'Prices' table every hour.

我有一个脚本运行,抓取URL,从特定网页上刮取价格,然后每小时更新“价格”表。

I'd like to display the following, ideally with one database query:

我想显示以下内容,理想情况下是一个数据库查询:

products.product_title
products.product_image
prices.price (current price, i.e. the very last insert for each product id)

prices.price(当前价格,即每个产品ID的最后一个插入)

The percentage difference between today's average price and yesterday's average price

今天的平均价格与昨天的平均价格之间的百分比差异

The percentage difference between the current month's average price and the previous month's average price

当月的平均价格与上个月的平均价格之间的百分比差异

I can do these things separately, but I cannot bring it together into one query.

我可以单独做这些事情,但我不能将它们整合到一个查询中。

Just for clarity, I am wanting to grab all of the products at once within a particular category - with that in mind I have been doing this:

为了清楚起见,我想在特定类别中同时获取所有产品 - 考虑到这一点,我一直这样做:

...WHERE category_id=%s...GROUP BY product_id...

I am writing my SQL directly, specifically using the MySQLdb Python library.

我直接编写SQL,特别是使用MySQLdb Python库。

2 个解决方案

#1

Untested example for the first subselect (make the other subselects on this template) :

第一个subselect的未经测试的示例(在此模板上进行其他子选择):

SELECT p.*,
     (SELECT prices.price
          FROM prices
          WHERE prices.product_id = p.id
          ORDER BY prices.id DESC
          LIMIT 1) as last_price
FROM product p
WHERE p.category_id = 4;

Probably not the most efficient way tough...

可能不是最有效的方式......

#2

Final snippet as per @Cosmin's suggestions...

根据@Cosmin的建议最终片段......

SELECT p.*,
     (SELECT prices.price
          FROM prices
          WHERE prices.product_id = p.id
          ORDER BY prices.id DESC
          LIMIT 1) as last_price,

     (SELECT ROUND(AVG(prices.price), 2)
          FROM prices
          WHERE prices.product_id = p.id AND date = CURRENT_DATE()
          ORDER BY prices.id DESC
          LIMIT 1) as todays_average_price,

     (SELECT ROUND(AVG(prices.price), 2)
          FROM prices
          WHERE prices.product_id = p.id AND date = SUBDATE(CURRENT_DATE, 1)
          ORDER BY prices.id DESC
          LIMIT 1) as yesterdays_average_price,

     (SELECT ROUND(AVG(prices.price), 2)
          FROM prices
          WHERE prices.product_id=p.id AND MONTH(date)=MONTH(current_date)
          ORDER BY prices.id DESC
          LIMIT 1) as current_months_average_price,

     (SELECT ROUND(AVG(prices.price), 2)
          FROM prices
          WHERE prices.product_id=p.id AND MONTH(date)=MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
          ORDER BY prices.id DESC
          LIMIT 1) as previous_months_average_price
FROM products p
WHERE p.category_id=%s;

Wasn't 100% sure on how to do the calculation of percentage difference within the query but I will just do that with Python to keep the query clean.

不是100%确定如何计算查询中的百分比差异,但我将使用Python来保持查询干净。

#1