Блог

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

 date         cluster  root_cat cat median_price
2020-12-07    A          X     1      20
2020-12-07    A          X     2      15
2020-12-07    A          X     2      30
2020-12-08    B          Y     3      24   
 

Вот запрос, который я написал для вычисления медианной цены.

 SELECT date,
         page_impressions_cluster,
         root_cat,
         cat,
         MAX(CASE
        WHEN tile2 = 1 THEN
        min_price/100 END) AS median
FROM 
    (SELECT pl.*,
         NTILE(2)
        OVER (PARTITION BY product_id
    ORDER BY  min_price) AS tile2
    FROM pl
    WHERE cluster is NOT null
          AND (date_parse(date, '%Y-%m-%d') >= current_date - interval '15' day) ) d
GROUP BY  1, 2, 3, 4
 

Теперь я хотел бы иметь еще один столбец, который показывает среднюю цену для каждого cat и root_cat за последние 14 дней, кроме последнего дня. Как я могу это сделать?

Вот желаемый результат:

 date         cluster  root_cat cat median_price  median_price_root     median_price_cat
2020-12-07    A          X     1      20                20                    20
2020-12-07    A          X     2      15                20                    22,5
2020-12-07    A          X     2      30                20                    22,5
2020-12-08    B          Y     3      24                24                    24 
 

Если аппроксимация медианы достаточно хороша, вы можете использовать

 SELECT date,
         page_impressions_cluster,
         root_cat,
         cat,
         MAX(CASE
        WHEN tile2 = 1 THEN
        min_price/100 END) AS median,
        approx_percentile(price, 0.5) -- <<== the 0.5 percentile is the median
FROM ...
 

Смотрите Документ для approc_percentile функции здесь.