SQL 问题 : Calculating percentages and using multiple joins to the same table

Question

这是我在工作中一直尝试做的事情的混淆版本。假设我得到了我店里顾客本月的数据——他们花了多少钱按食物类型划分:

CUSTOMER    FOOD_TYPE      FOOD_TYPE_VALUE
1           SWEET          52.6
1           SAVOURY        31.0
1           DAIRY          45.8
1           DRINKS         12.1
2           SWEET          15.1
2           SAVOURY        44.1
2           DRINKS         23.4
3           SWEET          95.7
3           SAVOURY        20.0
3           DAIRY          10.8
3           DRINKS         57.1

已确定客户 3 是我们的理想客户，因为他符合人口统计特征，我们希望逐月跟踪每个人的购物偏好分布与他有何不同。

我可以通过以下方式获得每位顾客每种食物类型的百分比分配:

SELECT
  c1.customer,
  c1.food_type,
  100 * c1.food_type_value / sum(c2.food_type_value)
FROM
  mytable c1 INNER JOIN mytable c2
  ON c1.customer = c2.customer
group by c1.customer, c1.food_type, c1.food_type_value

但是我在构建一个查询时遇到了问题，该查询会为我提供另一列，其中包含与我的理想客户匹配的百分比值。即:

CUSTOMER    FOOD_TYPE      FOOD_TYPE_PERC  IDEAL_PERC
1           SWEET          37              52
1           SAVOURY        22              11
1           DAIRY          32              6
1           DRINKS         9               31

关于如何在不造成太多困惑的情况下实现这一目标的任何提示？

Answer 1

将它加入包含您的理想客户的客户表的子集:

SELECT
  c1.customer,
  c1.food_type,
  100 * c1.food_type_value / sum(c2.food_type_value),
  c3.FOOD_TYPE_VALUE / sum(c2.food_type_value) as IDEAL_PERC
FROM
  mytable c1 INNER JOIN mytable c2
  ON c1.customer = c2.customer
  INNER JOIN (
    SELECT FOOD_TYPE, FOOD_TYPE_VALUE
    FROM mytable
    WHERE customer = 3) c3
  ON c2.FOOD_TYPE = c3.FOOD_TYPE
group by c1.customer, c1.food_type, c1.food_type_value, c3.FOOD_TYPE_VALUE

您对我的回答的评论表明您需要除以理想客户的 FOOD_TYPE_VALUE，所以这样做:

SELECT
  c1.customer,
  c1.food_type,
  100 * c1.food_type_value / sum(c2.food_type_value),
  c3.IDEAL_PERC
FROM
  mytable c1 INNER JOIN mytable c2
  ON c1.customer = c2.customer
  INNER JOIN (
    SELECT s1.FOOD_TYPE,
           100 * s1.FOOD_TYPE_VALUE / sum(s2.FOOD_TYPE_VALUE) IDEAL_PERC
    FROM mytable s1
    INNER JOIN mytable s2
    on s1.customer = s2.customer
    WHERE s1.customer = 3
    GROUP BY s1.FOOD_TYPE, s1.FOOD_TYPE_VALUE) c3
  ON c2.FOOD_TYPE = c3.FOOD_TYPE
group by c1.customer, c1.food_type, c1.food_type_value, c3.IDEAL_PERC