ГРУППА SQL ПО тому, где любой столбец имеет одинаковое значение

#sql #average #aggregate-functions #unpivot

#sql #среднее #агрегатные функции #отменить

Вопрос:

У меня есть следующая таблица

 User A | User B | Value
------- -------- ------
   1   |    2   |  60
   3   |    1   |  10
   4   |    5   |  50
   3   |    5   |  50  
   5   |    1   |  80
   2   |    3   |  10

Я хочу сгруппировать записи, в которых либо пользователь a = x, либо пользователь b = x, чтобы найти средние значения.

например, пользователь 1 появляется в таблице 3 раза, один раз как «Пользователь A» и дважды как «Пользователь B». Поэтому я хотел бы выполнить свою AVG() функцию, используя эти три строки.

Мне нужны самые высокие и самые низкие средние значения. Такой запрос разбил бы приведенную выше таблицу на следующие группы:

 User | Avg Value
----- -----
  1  | 50
  2  | 35
  3  | 23.33
  4  | 50
  5  | 60

а затем возвращает

 Highest Avg | Lowest Avg
------------ -----------
     60     |   23.33

Я знаю, что GROUP BY собирает вместе записи, в которых столбец имеет одинаковое значение. Я хочу собрать вместе записи, в которых любой из двух столбцов имеет одинаковое значение. Я искал много решений, но, похоже, не могу найти того, которое соответствует моей проблеме.

Ответ №1:

Переносимый параметр использует union all :

 select usr, avg(value) avg_value
from (
    select usera usr, value from mytable
    union all select userb, value from mytable
) t
group by usr

Это дает вам первый результирующий набор. Затем вы можете добавить еще один уровень агрегирования, чтобы получить максимальное и минимальное среднее значение:

 select min(avg_value) min_avg_value, max(avg_value) max_avg_value
from (
    select usr, avg(value) avg_value
    from (
        select usera usr, value from mytable
        union all select userb, value from mytable
    ) t
    group by usr
) t

В базах данных, которые поддерживают боковые соединения и values() , это наиболее удобно (и эффективно) выражается следующим образом:

 select min(avg_value) min_avg_value, max(avg_value) max_avg_value
from (
    select usr, avg(value) avg_value
    from mytable t
    cross join lateral (values (usera, value), (userb, value)) as x(usr, value)
    group by usr
) t

Это будет работать, например, в Postgres. В SQL Server вы бы просто заменили cross join lateral на cross apply .

1. Фантастика, я ценю, что вы разбили его, чтобы показать, как получить первый результирующий набор — эта часть будет полезна для ситуаций, отличных от той, о которой я задал вопрос.

2. Должен ли быть usr после userb?

Ответ №2:

Вы можете отменить использование union all , а затем агрегировать:

 select user, avg(value)
from ((select usera as user, value) union all
      (select userb as user, value)
     ) u
group by user;

Вы можете получить экстремумы с другим уровнем агрегирования:

 select min(avg_value), max(avg_value)
from (select user, avg(value) as avg_value
      from ((select usera as user, value) union all
            (select userb as user, value)
           ) u
      group by user
     ) ua