MAX(DECODE()) 内带有 AVG 的表透视

Question

大家好，我需要有关下面 SQL 的帮助。它不断为我的所有 a.name 提供单一输出：

 SELECT Drink.name,  
        MAX(DECODE(Size.type, 'Small', avg, NULL)) Small,
        MAX(DECODE(Size.type, 'Medium', avg, NULL)) Medium,
        MAX(DECODE(Size.type, 'Large', avg, NULL)) Large
 FROM Drink, Size (
             SELECT avg(Size.price) avg, Size.type, Drink.name FROM Drink, Size 
             GROUP BY Size.type, Drink.name )
 GROUP BY Drink.name
 ORDER BY Drink.name;

输出：例如，我将使用类型为小、中、大的饮料品牌名称。我想要全镇商店中所有饮料的平均值。

不使用枢轴

Drink       |   Size    |   Price
Dr. Pepper  |   Small   |    1.00
Dr. Pepper  |  Medium   |    1.50
Dr. Pepper  |   Large   |    2.00

使用枢轴（我想要的输出）：

Drink         |  Small    |   Medium  | Large
Dr. Pepper    |   1.00    |   1.50    |  2.00
Mountain Dew  |   0.50    |   0.75     |  1.25

我得到的输出：

Drink         |  Small    |   Medium  | Large
Dr. Pepper    |   1.00    |   1.00    |  1.00
Mountain Dew  |   1.00    |   1.00    |  1.00

Answer 1

使用 (Oracle 9i+)：

  SELECT x.name,
         MAX(CASE WHEN x.type = 'Small' THEN x.avg END) AS small,
         MAX(CASE WHEN x.type = 'Medium' THEN x.avg END) AS Medium,
         MAX(CASE WHEN x.type = 'Large' THEN x.avg END) AS Large
    FROM (SELECT d.name,
                 s.type, 
                 AVG(s.price) avg
            FROM DRINK d
            JOIN SIZE s ON s.size_id = d.size_id
        GROUP BY d.name, s.type) x
GROUP BY x.name

您的信息仍然缺少 DRINK 和 SIZE 表之间的 JOIN 标准 - 我做了假设。如果没有条件，查询只会产生笛卡尔积 - 它永远不会产生您期望的输出。

使用WITH 子句(Oracle 9i+)：

WITH sample AS (
   SELECT d.name,
          s.type, 
          AVG(s.price) avg
     FROM DRINK d
     JOIN SIZE s ON s.size_id = d.size_id
 GROUP BY d.name, s.type)
  SELECT x.name,
         MAX(CASE WHEN x.type = 'Small' THEN x.avg END) AS small,
         MAX(CASE WHEN x.type = 'Medium' THEN x.avg END) AS Medium,
         MAX(CASE WHEN x.type = 'Large' THEN x.avg END) AS Large
    FROM sample x
GROUP BY x.name

PIVOT/UNPIVOT

Oracle 直到11g 才支持ANSI 语法。

Answer 2

这是对我有用的解决方案 - 感谢 OMG Ponies 解决了一些问题。

SELECT x.NAME as "Drink", 
(MAX(DECODE(x.TYPE, 'Small', avg, NULL))) Small,
(MAX(DECODE(x."TYPE", 'Medium', avg, NULL))) Medium,
(MAX(DECODE(x."TYPE", 'Large', avg, NULL))) Large
FROM (
      SELECT Size."TYPE", Drink.NAME, Round(AVG(Size.price),2) avg 
      FROM Drink, Price 
      WHERE Drink.drink_id = Size.drink_id
      GROUP BY Size."TYPE", Drink.NAME) x
GROUP BY x.NAME
ORDER BY x.NAME;

我仍然不确定为什么 JOIN on 给了我这样的结果；我用 WHERE 替换了它，它工作了......很奇怪。