mysql - 聚合数据并 JOIN 两个没有对应键的表

Question

我有两个表:area_costs 和 products_shipped。

area_costs 包含每个区域的成本，即 express 成本、运输成本。

---------------------
|year|week|area|cost|
---------------------

表 products_shipped 包含已运送给客户的所有产品:

-------------------------------------------------------
|product_id|area|customer_id|drop_id|date_id|product_price|
-------------------------------------------------------

现在如上所述，没有匹配的键来连接这些表。我想做一个计算，基本上是用每个区域的成本除以每个区域的滴数，得到每个滴的平均花费。如果所有信息都在一张表中，我可能会输入:

SELECT area, SUM(cost) / COUNT(drop_id) AS spent_per_drop FROM full_table GROUP by AREA

有没有什么办法可以利用所提供的信息来做到这一点？

示例数据:

区域成本:

---------------------
|year|week|area|cost|
|2016|20  |mel |5000|
|2016|20  |syd |7500|
|2016|20  |bri |3000|
|2016|21  |mel |5200|
|2016|21  |syd |7400|
---------------------

products_shipped:

-----------------------------------------------------
|product_id|area|customer_id|drop_id       |date_id |
|515       |syd |100        |515-syd-100-01|20160607|
|515       |syd |102        |515-syd-102-12|20160607|
|508       |mel |103        |508-mel-103-03|20160607|
-----------------------------------------------------

假设我在第 20 周在 area_costs 表中执行 SUM():

SELECT area, SUM(cost_actual) FROM area_costs GROUP BY area

我会为 Syd 获得 7500。我想用它除以 products_shipped 表中 drop_id 的数量，样本数据中的数量为 2。因此，结果应该是 7500/2 = 3750。

我可以在 2 个查询中执行此操作，分别获取两个聚合然后划分结果，但这绝不是一个灵活的解决方案。

我的问题是:是否可以在一个查询中执行此操作？

感谢您的回答。

Answer 1

因为您的示例数据并没有真正展示一个简单的内部联接如何出错，我选择只对 area_costs 和 products_shipped 表进行两次聚合单独的子查询。然后我将这两个子查询连接在一起以获得最终结果。请注意，我使用了 LEFT JOIN 以防万一 area_costs 中的 area 在 products_shipped 中没有任何条目。在本例中，我显示 NA 用于每次转换统计。

SELECT t1.area,
    CASE WHEN t2.drop_count IS NULL
         THEN "NA"
         ELSE CAST((t1.cost_sum / t2.drop_count) AS VARCHAR)
    END AS spent_per_drop
FROM
(
    SELECT area, SUM(cost_actual) AS cost_sum
    FROM area_costs
    GROUP BY area
) t1
LEFT JOIN
(
    SELECT area, COUNT(*) AS drop_count
    FROM products_shipped
    GROUP BY area
) t2
    ON t1.area = t2.area