减去前一行以计算MS中的列值

Question

下面的数据需要格式化才能连续显示，不间断。

例如，我想用从2018-06-18到2018-06-21的日期显示记录，因为这些都是连续的日子。但是，我不想显示带有2018-06-27或2018-07-03日期的行，因为这些天没有序列。Friday-Monday是个例外，在那里会有明显的差距。例如，如果日期是2018-06-21 (星期四)、2018-06-22 (星期五)和2018-06-25 (星期一)，那么这将是一个有效的序列。

必须隐藏2018-6-1、2018-6-3、2018-6-4的序列，因为它被破坏了(表中没有2018-6-2的记录)。

我写了下面，但它未能交付我正在寻找的输出。任何帮助都将不胜感激。

select date, diff, Percent_gain, day, month, Year
from (
   select date
      ,datediff(day, '2018-1-29',date) - coalesce(lag(datediff(day, '2018-1-29', date)) over (order by date), 0) as diff
      ,Percent_gain, day, month, year
   from tab
) t1
where t1.diff < 2
   and t1.Percent_gain < 0

​

​

输出

​

​

Answer 1

您可以使用窗口函数和条件表达式：

select *
from (
    select t.*, lag(date) over(order by date) lag_date
    from mytable t
    where percent_gain < 0
) t
where 
    lag_date is null
    or date = dateadd(day, case when datename(dw, date) = 'Monday' then 3 else 1 end, lag_date)

子查询对percent_gain为负值的行进行筛选，并使用lag()检索“上一行”的date。然后，外部查询再次根据以下条件进行筛选：

• ：行是“第一”行(即没有前一个日期)
• 或当前行的日期在前一个日期之后的一天除外，如果当前日期是星期一，则当前日期应比前一个日期

晚3天。

请注意，查询实际上并不使用列day、month和year；它们看起来像派生值，在需要时可以轻松地动态计算。通常，我们使用datename()来获取日期名称。

Answer 2

您可以将此作为查找序列的替代方法。

WITH dates(id ,date)
AS
(
 SELECT 1, '2020-03-10'   UNION 
 SELECT 2, '2020-03-15'   UNION
 SELECT 3, '2020-04-17'   UNION
 SELECT 3, '2020-04-20'   UNION
 SELECT 3, '2020-04-21'   UNION
 SELECT 3, '2020-04-22'   UNION
 SELECT 4, '2020-04-25' 
)

Select A.*,CASE WHEN day_name = 'MONDAY'
                THEN CASE WHEN datediff(d,A.prev_date,A.date) = 3
                          THEN 1
                          ELSE 0 END
                ELSE CASE WHEN datediff(d,A.prev_date,A.date) = 1
                          THEN 1
                          ELSE 0 END
                END AS Is_seq

from (Select t.*, DATENAME(dw,date) as day_name,
             lag(date) over (order by date) as prev_date
      from dates t
     ) A