Блог

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

Результирующий набор должен выглядеть следующим образом:

3/7 = 0.42%

Мой подход состоял в том, чтобы сначала выполнить самостоятельное объединение с таблицей, чтобы получить количество строк, которые попадают в 20%, но изо всех сил пытаются удалить повторяющиеся строки.

В любом случае, с нетерпением жду некоторых хитрых ответов.

Спасибо.

Вы можете использовать lead() и lag() :

 select avg( case when prev_order_date > order_date - interval '20 minute' or
                      next_order_date < order_date   interval '20 minute'
                 then 1.0 else 0
            end) as ratio_within_20_minutes
from (select t.*,
             lag(order_date) over (partition by customer_id order by order_date) as prev_order_date,
             lead(order_date) over (partition by customer_id order by order_date) as next_order_date
      from t
     ) t;
 

Обратите внимание, что функции даты/времени сильно различаются в разных базах данных. Для сравнения используется стандартный синтаксис SQL. Точный синтаксис, вероятно, варьируется в зависимости от вашей базы данных.

Если вы хотите, чтобы это было для каждого клиента, добавьте group by customer_id в запрос и customer_id в select .

Редактировать:

В SQL Server это было бы:

 select avg( case when prev_order_date > dateadd(minute, -20, order_date) or
                      next_order_date < dateadd(minute, 20, order_date)
                 then 1.0 else 0
            end) as ratio_within_20_minutes
from (select t.*,
             lag(order_date) over (partition by customer_id order by order_date) as prev_order_date,
             lead(order_date) over (partition by customer_id order by order_date) as next_order_date
      from t
     ) t;