Как выбрать из таблицы A сумму сгруппированных чисел из таблицы B выше их средней суммы в Oracle?

У меня есть такие данные:

tableA.ID
---------
1
2
3

tableB.ID tableB.NUM
--------------------
1         10
1         15
2         18
3         12
2         12
2         15
3         13
1         12

Мне нужно выбрать идентификаторы tableA, где сумма их NUM в tableB выше среднего значения всех сумм идентификаторов tableA. Другими словами:

SUM ID=1 -> 10+15+12 = 37
SUM ID=2 -> 18+12+15 = 45
SUM ID=3 -> 12+13    = 25

AVG ALL IDs -> (37+45+25)/3 = 35

SELECT должен показывать только ID 1 и 2, потому что 37 > 35, 45 > 35, а 25 ‹ 35.

Это мой текущий запрос, который работает нормально:

SELECT tableA.ID
FROM tableA, tableB
WHERE tableA.ID = tableB.ID
HAVING SUM(tableB.NUM) > (
    SELECT AVG(MY_SUM)
    FROM (
        SELECT SUM(tableB.NUM) MY_SUM
        FROM tableA, tableB
        WHERE tableA.ID = tableB.ID
        GROUP BY tableA.ID
    )
)
GROUP BY tableA.ID

Но у меня есть ощущение, что может быть лучший способ без всех этих вложенных SELECT. Возможно, 2, но 3 кажется слишком много. Хотя я наверное ошибаюсь.

Например, почему я не могу сделать что-то простое:

SELECT tableA.ID
FROM tableA, tableB
WHERE tableA.ID = tableB.ID
HAVING SUM(tableB.NUM) > AVG(SUM(tableB.NUM))
GROUP BY tableA.ID

Или это:

SELECT tableA.ID, SUM(tableB.NUM) MY_SUM
FROM tableA, tableB
WHERE tableA.ID = tableB.ID
HAVING MY_SUM > AVG(MY_SUM)
GROUP BY tableA.ID

sql oracle average sum nested-queries
person rfgamaral    schedule 13.01.2011    source источник
comment
Кстати, в данных вашего примера отсутствует строка для 2,12, я думаю.   -  person Martin Smith    schedule 13.01.2011

Ответы (2)


arrow_upward
2
arrow_downward

В SQL Server вы можете сделать это. Не знаю, работает ли это в Oracle. Я уверен, что скоро узнаю, если это не так!

WITH cte As (
SELECT 
      tableA.ID, 
      SUM(tableB.NUM) AS MY_SUM, 
      AVG(SUM(tableB.NUM)) over() As Average
FROM tableA, tableB
WHERE tableA.ID = tableB.ID
GROUP BY tableA.ID
)
SELECT ID 
FROM cte 
WHERE MY_SUM > Average
person Martin Smith    schedule 13.01.2011
comment
@Nazgulled - он избегает повторения JOIN дважды (по крайней мере, теоретически, я понятия не имею, поддерживается ли он в Oracle и как он будет реализован, если да) - person Martin Smith; 13.01.2011
comment
Это работало в моей базе данных примера. Могу ли я предположить, что это не присоединение дважды, или это все еще может быть неправдой? И что именно делает OVER()? - person rfgamaral; 13.01.2011
comment
@Nazgulled — пустое предложение OVER() вычисляет совокупность всего (неразделенного) результата. Вероятно, вам лучше просмотреть планы выполнения, чтобы определить, какой из различных вариантов наиболее эффективен. - person Martin Smith; 13.01.2011

arrow_upward
1
arrow_downward

Это должно уменьшить его на один уровень

SELECT tableA.ID
FROM tableA, tableB
WHERE tableA.ID = tableB.ID
GROUP BY tableA.ID
HAVING SUM(tableB.NUM) > (
    SELECT SUM(tableB.NUM)/COUNT(DISTINCT tableA.ID)
    FROM tableA, tableB
    WHERE tableA.ID = tableB.ID
)
person RichardTheKiwi    schedule 13.01.2011
comment
Тогда забудьте об AVG()? Это похоже на хорошее решение... Я просто подожду еще несколько часов, чтобы посмотреть, не придумает ли кто-нибудь хорошие альтернативы. - person rfgamaral; 13.01.2011