Postgresql:需要帮助将我的编程解决方案替换为纯SQL解决方案(跨几个表的多列聚合)

Question

我有以下三张桌子。

Customer
+----+------------+-----------+
| ID | First_Name | Last_Name |
+----+------------+-----------+

Purchase
+----+-----+-------+
| ID | VIN | PRICE |
+----+-----+-------+

Part
+-----+-----------+--------------+------+
| VIN | PART_NAME | ORDER_NUMBER | COST |
+-----+-----------+--------------+------+

对于每一个客户，我需要找到：

• 客户姓名
• 从客户那里购买了多少辆汽车。
• 从客户处购买的车辆的平均价格是多少？
• 从这个客户那里购买的汽车的平均零部件数量是多少？
• 从这位顾客那里购买的汽车零件的平均成本是多少？

我目前正在Python中工作，并且已经成功地使用了几个查询，并且坦率地说，使用了hacky代码，以获得所需的结果。但是，我希望尽可能多地使用SQL，因为这是一个入门数据库课程的作业。

1. 首先，我找到了我们购买车辆的所有客户的in，以及从他们那里购买的车辆数量，并将其存储在一个列表(CUSTOMER_LIST)中。

SELECT T1.ID, COUNT(*) 
FROM CUSTOMER AS T1, PURCHASE AS T2 
WHERE T1.ID = T2.ID
GROUP BY T1.ID

1. 对于列表中的每个ID，我查询每个客户的名字和姓氏，并将它们连接起来，并将它们附加到CUSTOMER_LIST中。

SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME)
FROM CUSTOMER
WHERE ID = (CUSTOMER_LIST[0])

该列表现在保存了客户的ID、售出的车辆数量以及它们的全名。

1. 然后我查询从客户那里购买的每一辆车。

SELECT VIN
FROM PURCHASE
WHERE ID = (CUSTOMER_LIST[0])

1. 对于从该特定客户购买的每一辆车，我将检索所需车辆的总数量以及零部件的总成本。

SELECT COUNT(*) FROM PART WHERE VIN = (VEHICLE_LIST[0])
SELECT SUM(COST) FROM PART WHERE VIN = (VEHICLE_LIST[0])

这些数值除以购买的车辆数目，以达到零件的平均数量和平均费用。平均数附在CUSTOMER_LIST之后。

1. 最后，我询问从客户那里购买的车辆的平均价格。

SELECT to_char(AVG(PRICE), '9999999999D99') FROM PURCHASE WHERE ID = (CUSTOMER_LIST[0])

这些值也附加到CUSTOMER_LIST。

这个样本数据

+---------------+------------+-----------+
|      ID       | First_Name | Last_Name |
+---------------+------------+-----------+
| S530460864050 | JOHN       | SMITH     |
+---------------+------------+-----------+

+---------------+-------------------+-------+
|      ID       |        VIN        | PRICE |
+---------------+-------------------+-------+
| S530460864050 | 1GCHG39R5W1012259 |  2500 |
| S530460864050 | 1FD0X4HT5FEB20353 |  5000 |
+---------------+-------------------+-------+

+-------------------+-----------------------------+----------------------+-------+
|        VIN        |          PART_NAME          |     ORDER_NUMBER     | COST  |
+-------------------+-----------------------------+----------------------+-------+
| 1GCHG39R5W1012259 | Spark Plug Asm              | 1FD0X4HT5FEB20353-01 | 20.84 |
| 1GCHG39R5W1012259 | Filter Asm,Oil              | 1FD0X4HT5FEB20353-01 | 58.83 |
| 1GCHG39R5W1012259 | Switch Asm-Ignition & Start | 1FD0X4HT5FEB20353-01 | 13.72 |
| 1GCHG39R5W1012259 | Bearing Asm-Front Wheel     | 1FD0X4HT5FEB20353-02 | 61.52 |
| 1GCHG39R5W1012259 | Element-Air Cleaner         | 1FD0X4HT5FEB20353-02 | 21.85 |

应产生以下结果

+------------+---------------+-------------------------+-------------------------+-----------------------+
| Full Name  | Vehicles Sold | Average Cost of Vehicle | Average Number of Parts | Average Cost of Parts |
+------------+---------------+-------------------------+-------------------------+-----------------------+
| JOHN SMITH |             2 |                 3750.00 |                     2.5 |                 35.35 |
+------------+---------------+-------------------------+-------------------------+-----------------------+

我用我的解决方案得到了正确的值，但是代码的编写方式并不是非常直观或高效，如果可能的话，我希望完全通过SQL来实现它。

Answer 1

这应该可以做到：

SELECT
  c.first_name || ' ' || c.last_name AS "Full Name", 
  COUNT(DISTINCT p.VIN) AS "Vehicles Sold", 
  AVG(p.PRICE) AS "Average Cost of Vehicle", 
  MAX(num_parts_per_vehicle * 1.00) / COUNT(DISTINCT p.VIN) AS "Average Number of Parts", 
  MAX(cost_parts_per_vehicle) AS "Average Cost of Parts"
FROM customer c
LEFT JOIN purchase p ON c.ID = p.ID -- Get car info
LEFT JOIN (
  SELECT p.id,
    -- avg parts per vehicle
    COUNT(pt.PART_NAME) / COUNT(DISTINCT p.VIN) AS num_parts_per_vehicle,
    -- avg cost per vehicle
    CAST(SUM(pt.COST) / COUNT(pt.ORDER_NUMBER) AS DECIMAL(10,2)) AS cost_parts_per_vehicle
  FROM purchase p
  INNER JOIN part pt ON p.VIN = pt.VIN
  GROUP BY 1 -- Get info per customer
) pt ON c.id = pt.id -- Get summarized parts info
GROUP BY c.first_name, c.last_name;
;

SQL Fiddle

我看到的一个潜在问题是Average number of parts和Average cost of parts的计算似乎是不同的。对于零件的数量，你有两辆车和五个零件，平均为2.5。对于平均成本，你总共有176.76美元的五部分，这给你的平均$35.35，意味着你只计算一辆车。似乎您正在使用不同的逻辑来计算这两个值。