Как использовать функцию LEAD с условием?
Я использую свой запрос, чтобы отозвать случаи, когда один клиент покупает вещи в течение нескольких дней. Я заинтересован в том, чтобы вытягивать только те строки, где покупатель покупает один и тот же товар в разные дни.
Мой запрос выглядит так:
FROM (
SELECT *,ROW_NUMBER() OVER(Partition by ordernumber,onlydate ORDER BY ordernumber ) as ROW_Number2
FROM
(
SELECT
Firstname
,lastname
,customerid
,orderdate
,ordernumber
,producttype
,
convert(date, orderdate) as onlydate,
convert(time (0), orderdate)as onlytime
FROM sales a (nolock)
JOIN product p(nolock)
ON a.customerid = p.customerid
WHERE orderdate BETWEEN '07/01/2018' AND '09/01/2018'
)
)as A
) AS B
WHERE B.ROW_Number2=1
и это вытягивает случаи, когда покупатель покупает вещи в разные дни, но иногда это показывает, что покупатель покупает разные товары. Я думаю, что мне нужно использовать функцию LEAD с параметром для типа продукта, но я не уверен, как вставить ее в этот запрос.
Любая помощь будет принята с благодарностью!
Пример данных:
firstname lastname customerid b.department orderdate producttype
--------- --------- --------- --------- --------- ---------
alice johnson 1 athletic 12/7/17 shoes
alice johnson 1 athletic 12/8/18 headband
john parker 2 toiletries 12/9/18 cleaning
john parker 2 toiletries 12/10/18 personal
john parker 2 toiletries 12/10/18 cleaning
Желаемые данные:
firstname lastname customerid b.department orderdate producttype
--------- --------- --------- --------- --------- ---------
john parker 2 toiletries 12/9/18 cleaning
john parker 2 toiletries 12/10/18 cleaning
Хотелось бы показывать только случаи, когда Джон чистил уборку
5 ответов
Может быть, я что-то упустил, но я не вижу необходимости в оконных функциях. Почему бы просто не использовать EXISTS или CROSS APPLY:
-- prep data
SELECT *
into #t
FROM (values
('alice' , 'johnson', 1 ,'athletic', '12/7/17' , 'shoes' ),
('alice' , 'johnson', 1 ,'athletic', '12/8/18' , 'headband'),
('john' , 'parker' , 2 ,'toiletries', '12/9/18' , 'cleaning'),
('john' , 'parker' , 2 ,'toiletries', '12/10/18', 'personal'),
('john' , 'parker' , 2 ,'toiletries', '12/10/18', 'cleaning')
)t(firstname ,lastname ,customerid ,department ,orderdate ,producttype )
-- output using EXISTS
SELECT
t.firstname
,t.lastname
,t.customerid
,t.department
,t.orderdate
,t.producttype
FROM #t t
where exists (
SELECT *
FROM #t it
where
it.customerid = t.customerid
and it.producttype = t.producttype
and it.orderdate <> t.orderdate
)
-- output using a CROSS APPLY
SELECT
t.firstname
,t.lastname
,t.customerid
,t.department
,t.orderdate
,t.producttype
FROM #t t
cross apply (
SELECT *
FROM #t it
where
it.customerid = t.customerid
and it.producttype = t.producttype
and it.orderdate <> t.orderdate
) ct
Пример данных для игры:
DECLARE @Data TABLE (firstname NVARCHAR(255), lastname NVARCHAR(255), customerid INT, [b.department] NVARCHAR(255), orderdate DATE, producttype NVARCHAR(255));
INSERT INTO @Data (firstname,lastname,customerid,[b.department],orderdate,producttype) VALUES
('alice','johnson',1,'athletic','2017-07-12','shoes')
,('alice','johnson',1,'athletic','2018-08-12','headband')
,('john','parker',2,'toiletries','2018-09-12','cleaning')
,('john','parker',2,'toiletries','2018-10-12','personal')
,('john','parker',2,'toiletries','2018-10-12','cleaning')
;
Запрос:
SELECT a.*
FROM (SELECT COUNT(*)OVER(PARTITION BY d.customerid,d.producttype) AS [CountSameThingsDifferentDays],d.* FROM @Data d) a
WHERE a.CountSameThingsDifferentDays > 1
;
Ммм,,
select t.*
from (select t.*,
lag(orderdate) over (partition by customerid, department, producttype order by orderdate) as prev_orderdate,
lead(orderdate) over (partition by customerid, department, producttype order by orderdate) as lead_orderdate
from t
) t
where orderdate = dateadd(day, 1, prev_orderdate) or
orderdate = dateadd(day, -1, next_orderdate) ;
Это предполагает, что в один и тот же день не было нескольких покупок, хотя это можно было бы сделать с немного большей логикой.
WITH cte AS
( -- your base query
SELECT
Firstname
,lastname
,customerid
,ordernumber
,department
,producttype
,convert(DATE, orderdate) AS onlydate
,convert(time (0), orderdate)as onlytime
FROM sales a (nolock)
JOIN Product p(nolock)
ON a.customerid = p.customerid
WHERE orderdate BETWEEN '07/01/2018' AND '09/01/2018'
)
,MinMaxDate AS
( -- adding MIN/MAX
SELECT *
,MIN(onlydate) Over (PARTITION BY customerid, producttype) AS MinDate
,MAX(onlydate) Over (PARTITION BY customerid, producttype) AS MaxDate
FROM UniqueDates
)
SELECT *
FROM TypeCount
WHERE MinDate <> MaxDate -- same product purchased on at least two days
Согласно вашему описанию, вы предоставили нам одну таблицу, а в исходной таблице у вас есть две таблицы. Я не мог понять вашу логику ясно. Пожалуйста, покажите нам, как вы справляетесь со своими исходными данными.
Насколько я понимаю, вы не можете использовать lead(), чтобы получить ваше требование и только добавить некоторые условия.
Опишите больше?
-----prep data
SELECT *
into #t
FROM (values
('alice' , 'johnson', 1 ,'athletic', '12/7/17' , 'shoes' ),
('alice' , 'johnson', 1 ,'athletic', '12/8/18' , 'headband'),
('john' , 'parker' , 2 ,'toiletries', '12/9/18' , 'cleaning'),
('john' , 'parker' , 2 ,'toiletries', '12/10/18', 'personal'),
('john' , 'parker' , 2 ,'toiletries', '12/10/18', 'cleaning')
)t(firstname ,lastname ,customerid ,department ,orderdate ,producttype )
select * from #t where firstname='john' and producttype='cleaning'
/*
firstname lastname customerid department orderdate producttype
--------- -------- ----------- ---------- --------- -----------
john parker 2 toiletries 12/9/18 cleaning
john parker 2 toiletries 12/10/18 cleaning
*/
С уважением
Рейчел