LEAD/LAG SQL Server 2012/间隙和孤岛 [英] LEAD/LAG SQL Server 2012/Gaps and Islands

问题描述

我在遇到LEAD/LAG时遇到了一些问题.对于一组ID中的每一行，我想获取isAQI = 1的上一个/下一个源.prevAQI和nextAQI列中的所需输出如下.

I'm having a few issues with LEAD/LAG. For each row within a set of IDs I'm wanting to get the previous/next source where isAQI = 1. Desired output is as follows in prevAQI and nextAQI columns.

我尝试了与 Lag()在SQL Server中带有条件的方法相同的方法，但没有运气.任何帮助将不胜感激！

I've tried the same approach as Lag() with conditon in sql server, but with no luck. Any help would be much appreciated!

采样数据如下:

DECLARE @a TABLE ( id int, timest datetime, source char(2),
                   isAQI int, prevAQI char(2), nextAQI char(2))
INSERT @a VALUES
(6694   ,'2015-06-11 08:55:06.000'  ,'I'    ,1, NULL, 'A'), 
(6694   ,'2015-06-11 09:00:00.000'  ,'A'    ,1, 'I', 'I'),
(6694   ,'2015-06-11 09:11:49.000'  ,'C'    ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 09:29:06.000'  ,'O'    ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 09:29:06.000'  ,'DT'   ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 09:34:11.000'  ,'DT'   ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 09:34:11.000'  ,'O'    ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 10:06:27.000'  ,'I'    ,1, 'A', 'I'),
(6694   ,'2015-06-11 11:25:09.000'  ,'DT'   ,NULL, 'I', 'I'),
(6694   ,'2015-06-11 18:25:24.000'  ,'C'    ,NULL, 'I', 'I'),
(6694   ,'2015-06-12 17:57:16.000'  ,'I'    ,1, 'I', NULL);

SELECT *
FROM @a  

推荐答案

查看以下查询是否对您有用:

See if the following query works for you:

WITH C AS
(
  SELECT *,
    MAX(goodval) OVER(PARTITION BY id
                      ORDER BY timest
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prv,
    MIN(goodval) OVER(PARTITION BY id
                      ORDER BY timest
                      ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS nxt
  FROM @a
    CROSS APPLY ( VALUES( CONVERT(VARCHAR(23), timest, 121) 
                          + CASE WHEN isAQI = 1 THEN source END ) ) AS A(goodval)
)
SELECT id, timest, source,
CASE WHEN prv IS NOT NULL THEN SUBSTRING(prv, 24, 2) END AS prevAQI,
CASE WHEN nxt IS NOT NULL THEN SUBSTRING(nxt, 24, 2) END AS nextAQI
FROM C
ORDER BY id, timest;

这篇关于LEAD/LAG SQL Server 2012/间隙和孤岛的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！