TSQL Replicating LAG() function with dynamic offset

Question

I'm trying to recreate the LAG() function without using LAG() but has a dynamic Offset that is dependent on a column. I'm going to copy this code into SparkSQL.

Here is my sample data:

if object_id('tempdb.dbo.#myTable') is not null drop table #myTable
create table #myTable (id int,dates int, flag char, FromToFlagType varchar(2),FromToCounter INT)

insert into #myTable values(1,  '20181031','V','VV',1)
insert into #myTable values(2,  '20181130','V','VV',2)
insert into #myTable values(3,  '20181231','V','VV',3)
insert into #myTable values(4,  '20190131','F','VF',1)
insert into #myTable values(5,  '20190228','F','FF',2)
insert into #myTable values(6,  '20190331','F','FF',3)
insert into #myTable values(7,  '20190430','F','FF',4)
insert into #myTable values(8,  '20190531','V','FV',1)
insert into #myTable values(9,  '20190630','V','VV',2)
insert into #myTable values(10, '20190731','V','VV',3)

id  dates       flag    FromToFlagType  FromToCounter
1     20181031    V           VV                1
2     20181130    V           VV                2
3     20181231    V           VV                3
4     20190131    F           VF                1
5     20190228    F           FF                2
6     20190331    F           FF                3
7     20190430    F           FF                4
8     20190531    V           VF                1
9     20190630    V           VV                2
10  20190731      V           VV                3

So what I want to do is to replicate the following result, but without using a dynamic Offset:

select
  *
  ,LAG(FromToFlagType,FromToCounter-1) OVER ( ORDER BY dates) AS FromToStage
from 
  #mytable

id  dates     flag  FromToFlagType  FromToCounter   FromToStage
1     20181031  V             VV              1             VV
2     20181130  V             VV              2             VV
3     20181231  V             VV              3             VV
4     20190131  F             VF              1             VF
5     20190228  F             FF              2             VF
6     20190331  F             FF              3             VF
7     20190430  F             FF              4             VF
8     20190531  V             FV              1             FV
9     20190630  V             VV              2             FV
10  20190731    V             VV              3             FV

I know that you can replicate LAG() with CTE and JOIN, but it seems to only work if you know ahead of time what the offset is going to be. I've tried something similar here, but I'm just not able to get the same result.
I've found something similar here, but I'm new to Spark and I need a solution what uses SparkSQL. I figured that if I can just replicate the function that I can copy it over to 'Spark'.

WITH FromToStage AS(
select 
    *
    ,id-(FromToCounter-1) AS id_2 
    from #mytable
   --order by dates
)
SELECT
  a.*
  ,b.FromToFlagType as FromToStage
FROM 
  FromToStage a
    JOIN
  FromToStage b
  ON
  a.id = b.id_2
order by dates

user_1144035 · Answer

Based on your logic, you want first_value()

select t.*,
       first_value(FromToFlagType) over (partition by seqnum - seqnum_2 order by date)_ as first_FromToFlagType
from (select t.*,
             row_number() over order by date) as seqnum,
             row_number() over (partition by flag order by date) as seqnum_2
      from #mytable t
     ) t;

TSQL Replicating LAG() function with dynamic offset

1 Answers