Oracle SQL - самый покупаемый товар за последние 6 месяцев для каждой строки

Question

У меня есть данные в следующем формате:

date        fruit
======================
1-jan-14    orange
3-jan-14    orange
1-may-14    orange
2-may-14    apple
3-may-14    apple
1-aug-14    pineapple
2-aug-14    apple

Я хочу добавить столбец, который будет представлять: большинство купленных фруктов за последние 6 месяцев. Итак, для приведенного выше примера:

date        fruit       most_purchased_6_months
=======================================
1-jan-14    orange      orange
3-jan-14    orange      orange
1-may-14    orange      orange
2-may-14    apple       orange
3-may-14    apple       orange
1-aug-14    pineapple   apple
2-aug-14    apple       apple

Я пробовал использовать аналитические функции, но лучшее, что я достиг, - это показать, сколько раз один и тот же фрукт (из одной и той же строки) был приобретен за последние 6 месяцев с использованием "count(*) over (разделение на порядок фруктов по диапазону дат 180". предшествующий)".

Моя главная проблема заключается в том, что использование самосоединения легко решило бы мою проблему, но поскольку это таблица с примерно 20 миллионами строк данных, для ее запуска в нашей базе данных требуется несколько часов. Буду признателен за любое направление и / или помощь!

Answer 1

Это ваш основной запрос:

select date, fruit,
       count(*) over (partition by fruit order by date range 180 preceding) as NumberPurchased
from data;

Я думаю, что вы можете сделать это с помощью подзапроса и first_value():

select date, fruit,
       first_value(fruit) over (order by NumberPurchased desc range 180 preceding)
from (select date, fruit,
             count(*) over (partition by fruit order by date range 180 preceding) as NumberPurchased
      from data d
     ) d;

Я оставляю ответ, но вышеупомянутое не работает, потому что счетчики меняются в каждой строке в течение предыдущих шести месяцев.

РЕДАКТИРОВАТЬ:

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

create table FruitCountCume as
    select f.fruit, da.date, count(*) over (partition by f.fruit order by d.date) as cumecnt
    from (select distinct fruit from data) f cross join
         (select distinct date from data) d left outer join
         data da
         on f.fruit = da.fruit and f.date = da.date;

create index on FruitCountCume(fruit, date);

select d1.date, d1.fruit,
       max(fruit) keep (dense_rank first order by d1.cumecnt - coalesce(d2.cumecnt, 0) desc) as MostCommon
from FruitCountCume d1 left outer join
     FruitCountCume d2
     on d1.date = d2.date + 180
group by d1.date;

Полное перекрестное соединение может быть необязательным. Это как удобство, чтобы сделать left join более эффективным.