Блог

Я хочу вычислить среднемесячное значение некоторых данных, используя SQL-запрос, где данные хранятся в redshift DB. Данные представлены в таблице в следующем формате.

    s_date   | sales 
------------ -------
 2020-08-04 |    10
 2020-08-05 |    20
   ----     |    --
   ----     |    --
  

Данные могут отсутствовать за все даты месяца. Если данные отсутствуют в течение дня, их следует считать равными 0.
Следующий запрос с использованием функции AVG () «группировать по» месяцам as выдает среднее значение на основе данных на доступную дату.

 select trunc(date_trunc('MONTH', s_date)::timestamp) as month, avg(sales) from sales group by month;
  

Однако он не учитывает данные для отсутствующих дат как 0. Каким должен быть правильный запрос для вычисления среднемесячного значения, как ожидалось?

Еще одно ожидание состоит в том, что за текущий месяц среднее значение должно быть рассчитано на основе данных по состоянию на сегодняшний день. Таким образом, он не должен учитывать весь месяц (например, 30 или 31 день).

С уважением,
Пол

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

 WITH dates AS (
    SELECT date_trunc('day', t)::date AS dt
    FROM generate_series('2020-01-01'::timestamp, '2020-12-31'::timestamp, '1 day'::interval) t
),
cte AS (
    SELECT t.dt, COALESCE(SUM(s.sales), 0) AS sales
    FROM dates t
    LEFT JOIN sales s ON t.dt = s.s_date
    GROUP BY t.dt
)

SELECT
    LEFT(dt::text, 7) AS ym,
    AVG(sales) AS avg_sales
FROM cte
GROUP BY
    LEFT(dt::text, 7);
  

Логика здесь заключается в том, чтобы сначала сгенерировать промежуточную таблицу во втором CTE, которая содержит по одной записи для каждой информации в вашем наборе данных, а также общий объем продаж на эту дату. Затем мы агрегируем по годам / месяцам и сообщаем о средних продажах.