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

Question

我有以下数据需要格式化以仅显示连续天数而不会中断。

例如，我想显示日期来自 2018-06-18 的记录。直到 2018-06-21因为这些是连续的日子。但是我不想显示日期为 2018-06-27 的行或 2018-07-03因为这些日子没有顺序。
异常(exception)是 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()获取日期名称。