SQL问题：计算百分比并使用多个连接到同一个表 [英] SQL issue: Calculating percentages and using multiple joins to the same table

问题描述

这里是我在工作中想做的一个混淆的版本。说我已经给我这个月的数据给客户在我的商店 - 他们花了多少分开的食物类型：

Here's an obfuscated version of something I've been trying to do at work. Say I have been given this month's data for customers in my shop - how much they've spent split by the food type:

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是我们的理想客户，因为他符合人口统计资料，我们希望每月跟踪每个人的购物偏好分配与他的不同。

It has been decided that Customer 3 is our ideal customer as he fits the demographic profile, and we want to track month by month, how everybody's distribution of shopping preferences differs from his.

我可以为每个客户获得每种食物类型的百分比分配：

I can get the percentage allocation for each food type for each customer with:

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

但是我无法构造一个查询，它将为我的理想客户提供一个具有匹配百分比值的列。即：

But I am having trouble constructing a query that will give me a further column with the matching percentage values for my ideal customer. ie:

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

太麻烦了？

推荐答案

将其加入包含您理想客户的客户表的子集：

Join it on the subset of the customer table that contains your ideal customer:

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，因此：

Your comment to my answer suggests you need to divide by the FOOD_TYPE_VALUE of the ideal customer, so do this:

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

这篇关于SQL问题：计算百分比并使用多个连接到同一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！