In my data set, each customer has some orders on different dates.
For each customer each month, I want to check his/her last order in the previous month in which city.
For example, it is my data for one of the customers.
| customer | year | month | day | order id | city id |
|---|---|---|---|---|---|
| 1544 | 2022 | 2 | 6 | 413 | 9 |
| 1544 | 2022 | 2 | 17 | 39 | 10 |
| 1544 | 2022 | 3 | 5 | 115 | 21 |
| 1544 | 2022 | 5 | 29 | 2153 | 4 |
| 1544 | 2022 | 5 | 30 | 955 | 9 |
the result should be the same as this:
| customer | year | month | city of last order of prev month(prevCity) |
|---|---|---|---|
| 1544 | 2022 | 2 | null or 9 |
| 1544 | 2022 | 3 | 10 |
| 1544 | 2022 | 5 | 21 |
(the first row of the above table is not my question now. )
I write my query using last_value the same as this:
select customer,
year,
month,
last_value(City) over (partition by customer, year, month order by created_at desc
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as prevCity
from table1
but the result is false!
How can I correct this?
>Solution :
Using the window function lag() over() in concert with the WITH TIES clause
Select top 1 with ties
customer
,year
,month
,LastCityID = lag([city id],1) over (partition by customer order by year, month,day)
From YourTable
order by row_number() over (partition by customer,year,month order by year, month,day)
Or an Nudge More Perforamt
with cte as (
Select *
,LastCityID = lag([city id],1) over (partition by customer order by year, month,day)
,RN = row_number() over (partition by customer,year,month order by year, month,day)
From YourTable
)
Select customer
,year
,month
,LastCityID
From cte
Where RN =1
Results
customer year month LastCityID
1544 2022 2 NULL
1544 2022 3 10
1544 2022 5 21