SQL Server: аналитическая функция опережения / запаздывания по группам (а не внутри групп)

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

У меня есть следующая таблица

 Dt          customer_id  buy_time     money_spent
 -------------------------------------------------
 2000-01-04  100          11:00:00.00  2
 2000-01-05  100          16:00:00.00  1
 2000-01-10  100          13:00:00.00  4
 2000-01-10  100          14:00:00.00  3
 2000-01-04  200          09:00:00.00  10
 2000-01-06  200          10:00:00.00  11
 2000-01-06  200          11:00:00.00  5
 2000-01-10  200          08:00:00.00  20

и хотите, чтобы запрос получил этот набор результатов

 Dt          Dt_next     customer_id  buy_time     money_spent
 -------------------------------------------------------------
 2000-01-04  2000-01-05  100          11:00:00.00  2
 2000-01-05  2000-01-10  100          16:00:00.00  1
 2000-01-10  NULL        100          13:00:00.00  4
 2000-01-10  NULL        100          14:00:00.00  3
 2000-01-04  2000-01-06  200          09:00:00.00  10
 2000-01-06  2000-01-10  200          10:00:00.00  11
 2000-01-06  2000-01-10  200          11:00:00.00  5
 2000-01-10  NULL        200          08:00:00.00  20

То есть: я хочу, чтобы каждый покупатель (customer_id) и каждый день (Dt) на следующий день посетил один и тот же клиент (Dt_next).

У меня уже есть один запрос, который дает последний набор результатов (данные и запрос заключены под горизонтальной линейкой). Однако он включает left outer join и две dense_rank агрегатные функции. Этот подход кажется мне немного неуклюжим, и я думаю, что должно быть лучшее решение. Любые указатели на альтернативные решения приветствуются! Спасибо!

Кстати: я использую SQL Server 11, и в таблице есть записи >> 1 млн.

Мой запрос:

 select
   customer_table.Dt
   ,customer_table_lead.Dt as Dt_next
   ,customer_table.customer_id
   ,customer_table.buy_time
   ,customer_table.money_spent
 from
 (
   select 
     #customer_data.*
     ,dense_rank() over (partition by customer_id order by customer_id asc, Dt asc) as Dt_int
   from #customer_data
 ) as customer_table
 left outer join
 (
   select distinct
     #customer_data.Dt
     ,#customer_data.customer_id
     ,dense_rank() over (partition by customer_id order by customer_id asc, Dt asc)-1 as Dt_int
   from #customer_data
 ) as customer_table_lead
 on
 (
   customer_table.Dt_int=customer_table_lead.Dt_int
   and customer_table.customer_id=customer_table_lead.customer_id
 )

Образец данных:

 create table #customer_data (
   Dt date not null,
   customer_id int not null,
   buy_time time(2) not null,
   money_spent float not null
 );

 insert into #customer_data values ('2000-01-04',100,'11:00:00',2);
 insert into #customer_data values ('2000-01-05',100,'16:00:00',1);
 insert into #customer_data values ('2000-01-10',100,'13:00:00',4);
 insert into #customer_data values ('2000-01-10',100,'14:00:00',3);

 insert into #customer_data values ('2000-01-04',200,'09:00:00',10);
 insert into #customer_data values ('2000-01-06',200,'10:00:00',11);
 insert into #customer_data values ('2000-01-06',200,'11:00:00',5);
 insert into #customer_data values ('2000-01-10',200,'08:00:00',20);

sql-server sql-server-2012 lag lead sliding-window
person cryo111    schedule 20.09.2013    source источник

Ответы (1)


arrow_upward
18
arrow_downward

Попробуйте этот запрос:

select cd.Dt
    , t.Dt_next
    , cd.customer_id
    , cd.buy_time
    , cd.money_spent
from (
    select Dt
        , LEAD(Dt) OVER (PARTITION BY customer_id ORDER BY Dt) AS Dt_next
        , customer_id
    from (
        select distinct Dt, customer_id
        from #customer_data
    ) t
) t
inner join #customer_data cd on t.customer_id = cd.customer_id and t.Dt = cd.Dt

Почему поле money_spent имеет тип с плавающей запятой? У вас могут возникнуть проблемы с расчетами. Преобразуйте его в десятичный тип.

person GriGrim    schedule 20.09.2013
comment
Только что проверил. Работает и запрос намного короче! Я также проверю производительность с большей таблицей. Спасибо за подсказку с типом float (это был только первый тип, который пришел мне в голову при создании выборочных данных - фактическая таблица отличается от примеров данных, представленных здесь). - person cryo111; 20.09.2013
comment
Нашел этот пост по аналогичному требованию, и PARTITION BY - это ответ в моем сценарии. Тебе тоже спасибо :) - person Onur Omer; 21.02.2017
comment
Проверил, работает. - person DaniKR; 11.06.2021