MySQL查询使用多个表中的数据计算最新平均价格 [英] MySQL query to calculate latest average prices using data from multiple tables
问题描述
我正在研究基于PHP和MySQL的系统，以组织餐馆的产品和费用.
我将数据整理成四个表.
项目表
id | name
1 | Beer
2 | Vodka
产品表
id | item_id | name
1 | 1 | Budweiser
2 | 1 | Sam Adams
3 | 2 | Smirnoff
4 | 2 | Grey Goose
供应商表
id | name
1 | Supplier 1
2 | Supplier 2
费用表
id | product_id | cost | quantity | supplier | date
1 | 1 | 2.99 | 1 | 1 | 2017-09-05
2 | 1 | 3.00 | 2 | 2 | 2017-09-10
3 | 1 | 2.50 | 1 | 1 | 2017-09-20
4 | 1 | 3.98 | 2 | 1 | 2017-09-22
5 | 1 | 4.00 | 1 | 2 | 2017-09-25
6 | 1 | 8.00 | 2 | 2 | 2017-09-27
我想编写一个MYSQL查询，该查询可以基于费用表(基于日期)中最新的3个整体的平均每件商品的平均成本(成本/数量)来找出特定产品的最便宜供应商./p>
这就是我要计算的:
供应商1个最后3个条目-单位成本:2.99、2.50和1.99.平均值= 2.49
供应商2个最后3个条目-每单位成本:1.50、4.00和4.00.平均值= 3.16
因此，SQL应该返回供应商1"是产品1(百威啤酒)的最便宜选择.
到目前为止，我已经尝试过了，但是我有点迷茫和困惑:
select * from products
INNER JOIN expenses
ON products.id = expenses.product
AND products.item = '1'
ORDER BY (expenses.cost/expenses.quantity)
LIMIT 3;
此查询的输出与我要弄清楚的是很长的距离:(:
id | item_id | name | id | product_id | cost | quantity | supplier | date
1 | 1 |Budweiser| 2 | 1 | 3.00 | 2 | 2 | 2017-09-10
1 | 1 |Budweiser| 4 | 1 | 3.98 | 2 | 1 | 2017-09-22
1 | 1 |Budweiser| 3 | 1 | 2.50 | 1 | 1 | 2017-09-20
基于示例数据，我正在寻找的输出将是:
cheapest_supplier
1
我想编写一个MYSQL查询，该查询可以根据平均每笔费用找出特定产品的最便宜供应商 费用表中的最近的3个全部的项目(成本/数量) (基于日期).
选项将是与用户变量配合使用以基于日期生成排名的查询. 并且仅选择最后三个日期.
查询
SELECT
*
FROM (
SELECT
*
, CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
AS rank
, @supplier := supplier
FROM
Expenses
CROSS JOIN (
SELECT
@supplier := NULL
, @rank := 0
)
AS
init_user_params
WHERE
product_id = 1
ORDER BY
supplier ASC
, DATE DESC
)
AS Expenses_ranked
WHERE
Expenses_ranked.rank <= 3
结果
id product_id cost quantity supplier date @supplier := NULL @rank := 0 rank @supplier := supplier
------ ---------- ------ -------- -------- ---------- ----------------- ---------- ------ -----------------------
4 1 3.98 2 1 2017-09-22 (NULL) 0 1 1
3 1 2.50 1 1 2017-09-20 (NULL) 0 2 1
1 1 2.99 1 1 2017-09-05 (NULL) 0 3 1
6 1 8.00 2 2 2017-09-27 (NULL) 0 1 2
5 1 4.00 1 2 2017-09-25 (NULL) 0 2 2
2 1 3.00 2 2 2017-09-10 (NULL) 0 3 2
使用该结果为每个供应商生成平均清单.
查询
SELECT
Expenses_ranked.supplier
, AVG(Expenses_ranked.cost / Expenses_ranked.quantity) AS AVG
FROM (
SELECT
*
, CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
AS rank
, @supplier := supplier
FROM
Expenses
CROSS JOIN (
SELECT
@supplier := NULL
, @rank := 0
)
AS
init_user_params
WHERE
product_id = 1
ORDER BY
supplier ASC
, DATE DESC
)
AS Expenses_ranked
WHERE
Expenses_ranked.rank <= 3
GROUP BY
Expenses_ranked.supplier
结果
supplier avg
-------- --------------
1 2.4933333333
2 3.1666666667
现在我们可以使用简单的ORDER BY [] ASC LIMIT 1来获取最便宜的供应商
查询
SELECT
Expenses_ranked_avg.supplier AS cheapest_supplier
FROM (
SELECT
Expenses_ranked.supplier
, AVG(Expenses_ranked.cost / Expenses_ranked.quantity) AS AVG
FROM (
SELECT
*
, CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
AS rank
, @supplier := supplier
FROM
Expenses
CROSS JOIN (
SELECT
@supplier := NULL
, @rank := 0
)
AS
init_user_params
WHERE
product_id = 1
ORDER BY
supplier ASC
, DATE DESC
)
AS
Expenses_ranked
WHERE
Expenses_ranked.rank <= 3
GROUP BY
Expenses_ranked.supplier
)
AS Expenses_ranked_avg
ORDER BY
Expenses_ranked_avg.avg ASC
LIMIT 1
结果
cheapest_supplier
-------------------
1
更多最佳查询.
还可以在where语句中声明用户变量. 直接过滤掉排名成为可能.
查询
SELECT
*
FROM
Expenses
WHERE
(
CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
)
AND
(@supplier := supplier )
AND
@rank <= 3
AND
product_id = 1
ORDER BY
supplier ASC
, DATE ASC
结果
id product_id cost quantity supplier date
------ ---------- ------ -------- -------- ------------
1 1 2.99 1 1 2017-09-05
3 1 2.50 1 1 2017-09-20
4 1 3.98 2 1 2017-09-22
2 1 3.00 2 2 2017-09-10
5 1 4.00 1 2 2017-09-25
6 1 8.00 2 2 2017-09-27
现在，使用此结果集轻松找到最便宜的供应商.
查询
SELECT
Expenses_ranked.supplier AS cheapest_supplier
FROM (
SELECT
*
FROM
Expenses
WHERE
(
CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
) IS NOT NULL
AND
(@supplier := supplier ) IS NOT NULL
AND
@rank <= 3
AND
product_id = 1
ORDER BY
supplier ASC
, DATE ASC
)
AS Expenses_ranked
GROUP BY
Expenses_ranked.supplier
ORDER BY
AVG(Expenses_ranked.cost / Expenses_ranked.quantity) ASC
LIMIT 1
结果
cheapest_supplier
-------------------
1
I am working on a PHP and MySQL based system to organise products and expenses for a restaurant.
I have data organised in to four tables.
Items table
id | name
1 | Beer
2 | Vodka
Products table
id | item_id | name
1 | 1 | Budweiser
2 | 1 | Sam Adams
3 | 2 | Smirnoff
4 | 2 | Grey Goose
Supplier table
id | name
1 | Supplier 1
2 | Supplier 2
Expenses table
id | product_id | cost | quantity | supplier | date
1 | 1 | 2.99 | 1 | 1 | 2017-09-05
2 | 1 | 3.00 | 2 | 2 | 2017-09-10
3 | 1 | 2.50 | 1 | 1 | 2017-09-20
4 | 1 | 3.98 | 2 | 1 | 2017-09-22
5 | 1 | 4.00 | 1 | 2 | 2017-09-25
6 | 1 | 8.00 | 2 | 2 | 2017-09-27
I would like to write a MYSQL Query that can figure out the cheapest supplier of a specific product based on the average cost per item (cost/quantity) of the latest 3 entires in the expenses table (based on date).
Here's what I want to compute:
Supplier 1 last 3 entries - costs per unit of: 2.99, 2.50 and 1.99. Average = 2.49
Supplier 2 last 3 entries - costs per unit of: 1.50, 4.00 and 4.00. Average = 3.16
So the SQL should return that Supplier 1 is the cheapest option for Product 1 (Budweiser).
So far I have attempted this, but I am a bit lost and confused:
select * from products
INNER JOIN expenses
ON products.id = expenses.product
AND products.item = '1'
ORDER BY (expenses.cost/expenses.quantity)
LIMIT 3;
The output of this query is which is a long way from what I'm trying to figure out :(:
id | item_id | name | id | product_id | cost | quantity | supplier | date
1 | 1 |Budweiser| 2 | 1 | 3.00 | 2 | 2 | 2017-09-10
1 | 1 |Budweiser| 4 | 1 | 3.98 | 2 | 1 | 2017-09-22
1 | 1 |Budweiser| 3 | 1 | 2.50 | 1 | 1 | 2017-09-20
The output I am looking for based on the sample data would be:
cheapest_supplier
1
I would like to write a MYSQL Query that can figure out the cheapest supplier of a specific product based on the average cost per item (cost/quantity) of the latest 3 entires in the expenses table (based on date).
A option would be a query that works with user variables to generate a ranking based on date. And only select the three last dates.
Query
SELECT
*
FROM (
SELECT
*
, CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
AS rank
, @supplier := supplier
FROM
Expenses
CROSS JOIN (
SELECT
@supplier := NULL
, @rank := 0
)
AS
init_user_params
WHERE
product_id = 1
ORDER BY
supplier ASC
, DATE DESC
)
AS Expenses_ranked
WHERE
Expenses_ranked.rank <= 3
Result
id product_id cost quantity supplier date @supplier := NULL @rank := 0 rank @supplier := supplier
------ ---------- ------ -------- -------- ---------- ----------------- ---------- ------ -----------------------
4 1 3.98 2 1 2017-09-22 (NULL) 0 1 1
3 1 2.50 1 1 2017-09-20 (NULL) 0 2 1
1 1 2.99 1 1 2017-09-05 (NULL) 0 3 1
6 1 8.00 2 2 2017-09-27 (NULL) 0 1 2
5 1 4.00 1 2 2017-09-25 (NULL) 0 2 2
2 1 3.00 2 2 2017-09-10 (NULL) 0 3 2
Using that results to generate a avg list per supplier.
Query
SELECT
Expenses_ranked.supplier
, AVG(Expenses_ranked.cost / Expenses_ranked.quantity) AS AVG
FROM (
SELECT
*
, CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
AS rank
, @supplier := supplier
FROM
Expenses
CROSS JOIN (
SELECT
@supplier := NULL
, @rank := 0
)
AS
init_user_params
WHERE
product_id = 1
ORDER BY
supplier ASC
, DATE DESC
)
AS Expenses_ranked
WHERE
Expenses_ranked.rank <= 3
GROUP BY
Expenses_ranked.supplier
Result
supplier avg
-------- --------------
1 2.4933333333
2 3.1666666667
Now we can use a simple ORDER BY [] ASC LIMIT 1 to get the cheapest supplier
Query
SELECT
Expenses_ranked_avg.supplier AS cheapest_supplier
FROM (
SELECT
Expenses_ranked.supplier
, AVG(Expenses_ranked.cost / Expenses_ranked.quantity) AS AVG
FROM (
SELECT
*
, CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
AS rank
, @supplier := supplier
FROM
Expenses
CROSS JOIN (
SELECT
@supplier := NULL
, @rank := 0
)
AS
init_user_params
WHERE
product_id = 1
ORDER BY
supplier ASC
, DATE DESC
)
AS
Expenses_ranked
WHERE
Expenses_ranked.rank <= 3
GROUP BY
Expenses_ranked.supplier
)
AS Expenses_ranked_avg
ORDER BY
Expenses_ranked_avg.avg ASC
LIMIT 1
Result
cheapest_supplier
-------------------
1
More optimal queries.
What also is possible to declare the user variables within the where statement. Making it directly possible to filter out the ranking.
Query
SELECT
*
FROM
Expenses
WHERE
(
CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
)
AND
(@supplier := supplier )
AND
@rank <= 3
AND
product_id = 1
ORDER BY
supplier ASC
, DATE ASC
Result
id product_id cost quantity supplier date
------ ---------- ------ -------- -------- ------------
1 1 2.99 1 1 2017-09-05
3 1 2.50 1 1 2017-09-20
4 1 3.98 2 1 2017-09-22
2 1 3.00 2 2 2017-09-10
5 1 4.00 1 2 2017-09-25
6 1 8.00 2 2 2017-09-27
Now it's easy the use this result set to find the cheapest supplier.
Query
SELECT
Expenses_ranked.supplier AS cheapest_supplier
FROM (
SELECT
*
FROM
Expenses
WHERE
(
CASE
WHEN @supplier = supplier
THEN @rank := @rank + 1
ELSE @rank := 1
END
) IS NOT NULL
AND
(@supplier := supplier ) IS NOT NULL
AND
@rank <= 3
AND
product_id = 1
ORDER BY
supplier ASC
, DATE ASC
)
AS Expenses_ranked
GROUP BY
Expenses_ranked.supplier
ORDER BY
AVG(Expenses_ranked.cost / Expenses_ranked.quantity) ASC
LIMIT 1
Result
cheapest_supplier
-------------------
1
这篇关于MySQL查询使用多个表中的数据计算最新平均价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
