SQL Server How to use Dynamic Lag function to avoid joining a table to itself to retrieve date value

a1o7rhls 于 2023-10-15 发布在其他

关注(0)|答案(4)|浏览(83)

I'm currently writing code in SQL to add the column in red to the following table:

The logic is the following:

For every row:

if flag for this row =1 then use date of this row
if flag for this row =0 then find the latest row (based on date) on which flag was = 1 for the same party and return the date of that row. If no such row exists, return null

I've found a way to do this by joining the table to itself but I would like to avoid doing that as the size of the table is pretty massive.

What I have

select b.*, a.date, from table a left join table b on a.party=b.party where a.flag =1

Someone told me I could use the lag function, the partition over function and a case when to return the value I'm after but I haven't been able to figure it out.

Can someone help? Thank you so much!

sql-server

来源：https://stackoverflow.com/questions/51492390/how-to-use-dynamic-lag-function-to-avoid-joining-a-table-to-itself-to-retrieve-d

4条答案

按热度按时间

cpjpxq1n1#

try this

DECLARE @tab1 TABLE(PARTY CHAR(1),DATE DATE,Flag bit)
    INSERT INTO @tab1
    SELECT 'A','7-24-2018',1 Union ALL
    SELECT 'A','7-28-2018',0 Union ALL
    SELECT 'A','7-29-2018',0 Union ALL
    SELECT 'A','7-29-2018',0 Union ALL
    SELECT 'B','7-13-2018',1 Union ALL
    SELECT 'B','7-17-2018',0 Union ALL
    SELECT 'B','7-18-2018',0 Union ALL
    SELECT 'C','7-8-2018',1 Union ALL
    SELECT 'C','7-13-2018',0 Union ALL
    SELECT 'C','7-19-2018',0 Union ALL
    SELECT 'C','7-19-2018',0 Union ALL
    SELECT 'C','7-20-2018',0

    select t.*,
           max(case when flag = 1 then date end) over (partition by PARTY order by date) as [Last Flag On Date]
    from @tab1 t

赞(0）回复(0）举报 2023-10-15

jdgnovmf2#

use CROSS APPLY() to obtain the latest row with flag 1

SELECT *
FROM   yourtable t
       CROSS APPLY
       (
           SELECT TOP 1 x.Date as [Last flag on date]
           FROM   yourtable x
           WHERE  x.Party = t.Party
           AND    x.Flag = 1
           ORDER BY x.Date desc
       ) d

赞(0）回复(0）举报 2023-10-15

gjmwrych3#

Yes it can be done by joining table, if written properly.

@Sahi query is also good and simple.

Since you were asking for Dynamic LAG()

This query may or may not be very performant,but it certainly worth learning.

Test this with various sample data and tell me for which scenario it do not work. So that I correct my script accordingly.

DECLARE @tab1 TABLE(PARTY CHAR(1),DATE DATE,Flag bit)
INSERT INTO @tab1
SELECT 'A','7-24-2018',1 Union ALL
SELECT 'A','7-28-2018',0 Union ALL
SELECT 'A','7-29-2018',0 Union ALL
SELECT 'A','7-29-2018',0 Union ALL
SELECT 'B','7-13-2018',1 Union ALL
SELECT 'B','7-17-2018',0 Union ALL
SELECT 'B','7-18-2018',0 Union ALL
SELECT 'C','7-8-2018',1 Union ALL
SELECT 'C','7-13-2018',0 Union ALL
SELECT 'C','7-19-2018',0 Union ALL
SELECT 'C','7-19-2018',0 Union ALL
SELECT 'C','7-20-2018',0; 

WITH cte 
     AS (SELECT *, 
                Row_number() 
                  OVER ( 
                    partition BY party 
                    ORDER BY flag DESC, [date] DESC ) rn 
         FROM   @tab1) 
SELECT *, 
       CASE 
         WHEN flag = 1 THEN [date] 
         ELSE Lag([date], (SELECT TOP 1 a.rn - a1.rn 
                           FROM   cte a1 
                           WHERE  a1.party = a.party)) 
                OVER ( 
                  ORDER BY party ) 
       END 
FROM   cte a

赞(0）回复(0）举报 2023-10-15

vsdwdz234#

try this :->

select 
  b.*, 
  a.date, 
from 
  table a 
  left join table b on a.party = b.party 
where 
  a.flag = CASE WHEN a.flag = 1 THEN a.date WHEN a.flag = 0 THEN (
    SELECT 
      date 
    FROM 
      (
        SELECT 
          TOP 1 row_number() OVER (
            ORDER BY 
              a.date DESC
          ) rs, 
          a.date 
        FROM 
          a 
        WHERE 
          a.flag = 1 
        GROUP BY 
          a.date
      ) s
  ) END

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How to use Dynamic Lag function to avoid joining a table to itself to retrieve date value

4条答案

相关问题

热门标签

最新问答