Блог

Главная - Вопросы по программированию - Усреднение нескольких столбцов в Postgres

Усреднение нескольких столбцов в Postgres

#sql #postgresql #aggregate-functions

#sql #postgresql #агрегатные функции

Вопрос:

У меня есть таблица с несколькими строками, каждая из которых содержит эти четыре столбца:

продукт price_instore price_pickup price_delivery price_ship
1 $13.00 $13.50 $14.50 $18.00.
2 $4.00 $4.00 NULL NULL
3 $10.00 $10.00 $12.00 NULL

Я хотел бы иметь пятый столбец average_price, который дает среднюю цену продукта, но не учитывает НУЛИ в отношении суммарной цены или в отношении количества, используемого для деления среднего.

Итак, средняя цена продукта 1: ($13 $13.50 $14.50 $18)/4=$14.75
Средняя цена товара 2: ($4 $4)/2 = $4.00
Средняя цена товара 3: ($10 $10 $12)/3 = $ 10,67

Есть ли какой-либо способ сделать это с помощью SQL? Я пробовал такие подзапросы, как приведенный ниже, но безуспешно:

 (select coalesce((PRICE_INSTORE   PRICE_PICKUP   PRICE_DELIVERY   PRICE_SHIP) / 4, 
PRICE_INSTORE, PRICE_PICKUP, PRICE_DELIVERY, PRICE_SHIP)

но тогда я получаю только одну цену, если какой-либо из них равен нулю

Комментарии:

1. Почему бы не объединить каждый из четырех столбцов вместо их суммы?

2. Итак, ВЫБЕРИТЕ СРЕДНЕЕ ((ОБЪЕДИНИТЬ (PRICE_INSTORE) ОБЪЕДИНИТЬ (PRICE_PICKUP) ОБЪЕДИНИТЬ (PRICE_DELIVERY) ОБЪЕДИНИТЬ (PRICE_SHIP) ? @JohnnyFitz

3. да, так думаю, но использую coalesce(col name,0)

Ответ №1:

Я пьян, так что, вероятно, есть гораздо лучший способ сделать это, чем поворот, но я не вижу его сейчас, когда комната вращается вокруг меня, как она есть.

 with j as (
  select product, to_jsonb(mytable) - 'product' as jdata
    from mytable
)
select j.product, avg(e.value::numeric) as avg_price
  from j
       cross join lateral jsonb_each(j.jdata) as e(key, value)
 where e.value is not null
 group by j.product.

Ответ №2:

 select
    *,
    (select avg(x) from unnest(array[price_instore,price_pickup,price_delivery,price_ship]) as x) as avg_price
from
   your_table;

Ответ №3:

Попробуйте это

   select coalesce (PRICE_INSTORE, 0 )    coalesce (PRICE_PICKUP, 0)   coalesce (PRICE_DELIVERY , 0)   coalesce (PRICE_SHIP , 0)  / 
 ((case when PRICE_INSTORE is null then 0 else 1 end)   (case when PRICE_PICKUP is null then 0 else 1 end)  
 (case when PRICE_DELIVERY is null then 0 else 1 end)   (case when PRICE_SHIP is null then 0 else 1 end) )
 from product

Комментарии:

1. Знаете ли вы, что anything = null всегда равно null? dbfiddle.uk /… Должно быть ... is null

Ответ №4:

Одним из методов является боковое соединение:

 select t.*, avg_price
from t cross join lateral
     (select avg(price) as avg_price
      from (values (price_instore), (price_pickup), (price_delivery), (price_ship)
           ) v(price)
     ) x

Ответ №5:

попробуйте это:

    select coalesce(PRICE_INSTORE,0)   coalesce(PRICE_PICKUP,0)   coalesce(PRICE_DELIVERY,0)   coalesce(PRICE_SHIP,0)) / 4

Комментарии:

1. Единственная проблема, которую я вижу с этим, — это жестко заданное «4» — мне нужно среднее значение доступных цен, поэтому, например, если доставка недоступна, я бы разделил на 3. Это проблема, с которой я продолжал сталкиваться в своих попытках

2. @ClintonSorrel как вы, наверное, уже видели, вы могли бы использовать оператор Case, как описано в ответе Мухтиара выше, со случаем, когда PRICE_DELIVERY равно нулю, тогда 0 еще 1 конец.

Ответ №6:

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

  with cte (product, prices) as 
  
(select product, price_instore from t union all
 select product, price_pickup from t union all
 select product, price_delivery from t union all
 select product, price_ship from t)
  
select product, avg(prices)
from cte
group by product;

Вы можете либо использовать вывод как есть, либо обернуть его вокруг другого CTE и использовать его для объединения в вашей исходной таблице, чтобы получить средние значения

Метки: