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

#sql #postgresql

Вопрос:

У меня есть БД следующим образом:

 | company | timestamp  | value |
| ------- | ---------- | ----- |
| google  | 2020-09-01 | 5     |
| google  | 2020-08-01 | 4     |
| amazon  | 2020-09-02 | 3     |

Я хотел бы рассчитать среднее value значение для каждой компании за последний год, если имеется> = 20 точек данных. Если существует менее 20 точек данных, то мне бы хотелось получить среднее значение за весь период времени. Я знаю, что могу выполнить два отдельных запроса и получить средние значения для каждого сценария. Я полагаю, вопрос в том, как мне объединить их обратно в одну таблицу на основе имеющихся у меня критериев.

 select company, avg(value) from my_db GROUP BY company;

select company, avg(value) from my_db
where timestamp > (CURRENT_DATE - INTERVAL '12 months')
GROUP BY company;

Ответ №1:

 WITH last_year AS (
   SELECT company, avg(value), 'year' AS range  -- optional tag
   FROM   tbl      
   WHERE  timestamp >= now() - interval '1 year'
   GROUP  BY 1
   HAVING count(*) >= 20  -- 20  rows in range
   )
SELECT company, avg(value), 'all' AS range
FROM   tbl
WHERE  NOT EXISTS (SELECT FROM last_year WHERE company = t.company)
GROUP  BY 1
UNION ALL TABLE last_year;

db<> скрипка здесь

Индекс on (timestamp) будет использоваться только в том случае, если ваша таблица большая и содержит много лет.

Если в диапазоне большинства компаний более 20 строк (company) , для 2-го будет использоваться индекс on SELECT для извлечения нескольких выбросов.

1. Здесь много отличных ответов, но это, казалось, самый ясный синтаксис.

2. @Zaheer Я удивлен, что вы видите самый ясный синтаксис в запросе, который агрегирует дважды в таблице, а также использует UNION ALL для результатов.

Ответ №2:

Используйте условную агрегацию:

 select company, 
       case 
         when sum(case when timestamp > CURRENT_DATE - INTERVAL '12 months' then value end) >= 20 then 
              avg(case when timestamp > CURRENT_DATE - INTERVAL '12 months' then value end)
         else avg(value)
       end
from my_db 
group by company

Если под 20 точками данных вы подразумеваете 20 строк за последние 12 месяцев для каждой компании, то:

 select company, 
       case 
         when count(case when timestamp > CURRENT_DATE - INTERVAL '12 months' then value end) >= 20 then 
              avg(case when timestamp > CURRENT_DATE - INTERVAL '12 months' then value end)
         else avg(value)
       end
from my_db 
group by company

Ответ №3:

Вы можете использовать оконные функции для предоставления информации для фильтрации:

 select company, avg(value),
       (count(*) = cnt_this_year) as only_this_year
from (select t.*,
             count(*) filter (where date_trunc('year', datecol) = date_trunc('year', now()) over (partition by company) as cnt_this_year
      from t
     ) t
where cnt_this_year >= 20 and date_trunc('year', datecol) = date_trunc('year', now()) or
      cnt_this_year < 20
group by company;

В третьем столбце указывается, все ли строки относятся к этому году. С помощью фильтрации в where предложении легко добавить и другие вычисления (такие как min() , max() , и так далее).