SQL Server SQL - Evaluate Record Over A Partition Using Window Functions

zaq34kh6 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(103)

The below data is at the trip level, a unique trip per row. I'm looking to set a 0 / 1 boolean when the same person has RETURNED to the same location, meaning their first trip to a destination will not have the 1 indicator. Ideally, I can do this with a window function but am open to other thoughts if we cannot compare a value within a partition against all of the other values within partition.

My initial attempt only compared against one record within the partition and won't pick up if they went back to the same Destination on a trip beyond their next one.

My initial attempt was: CASE WHEN Destination = LAG(Destination) OVER (PARTITION BY Person) THEN 1 ELSE 0 END

STARTING DATA EXAMPLE BELOW
| Person | Trip | Destination |
| ------------ | ------------ | ------------ |
| Bob | a | Hawaii |
| Bob | b | Hawaii |
| Bob | c | Florida |
| Kevin | a | Texas |
| Kevin | b | Iowa |
| Tina | a | Arizona |
| Tina | b | Arizona |
| Tina | c | California |
| Tina | d | New Mexico |
| Tina | e | Arizona |
| Tina | e | California |

DESIRED OUTPUT BELOW

Person	Trip	Destination	Same_Destination
Bob	a	Hawaii	0
Bob	b	Hawaii	1
Bob	c	Florida	0
Kevin	a	Texas	0
Kevin	b	Iowa	0
Tina	a	Arizona	0
Tina	b	Arizona	1
Tina	c	California	0
Tina	d	New Mexico	0
Tina	e	Arizona	1
Tina	f	California	1

sql-server

来源：https://stackoverflow.com/questions/76688636/sql-evaluate-record-over-a-partition-using-window-functions

1条答案

按热度按时间

vdgimpew1#

You can modify your condition to use first_value :

case when 
  destination = First_Value(destination) over(partition by person order by trip rows unbounded preceeding) 
    and trip != First_Value(trip) over(partition by person order by trip rows unbounded preceeding)
then 1 else 0 end

Edit

Based on your addition comments I think this would be simpler to implement using a correlated subquery. Does the following work for you?

select *, case when exists (
    select * from t t2 
    where t2.person = t.person 
      and t2.destination = t.destination 
      and t2.trip < t.trip
) then 1 else 0 end
from t;

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server SQL - Evaluate Record Over A Partition Using Window Functions

1条答案

相关问题

热门标签

最新问答