使用rank()或其他变体的SQL错过天数

Question

在这里运行 SQL Server 2008R2，我遇到一个问题，我的逻辑还没有完全发挥作用，哈哈

好的，这就是我所拥有的:

+-----------+------------+------------------+-------+----------+----------+
| DAILYDAYS | MISSED_DAY | MISSED_DAY_COUNT | COUNT |  START   |   END    |
+-----------+------------+------------------+-------+----------+----------+
|  20140114 | (null)     | (null)           |     0 | 20140114 | 20140122 |
|  20140115 | (null)     | (null)           |     1 | 20140114 | 20140122 |
|  20140116 | (null)     | (null)           |     2 | 20140114 | 20140122 |
|  20140117 | (null)     | (null)           |     3 | 20140114 | 20140122 |
|  20140118 | (null)     | (null)           |     4 | 20140114 | 20140122 |
|  20140119 | (null)     | (null)           |     5 | 20140114 | 20140122 |
|  20140120 | 20140120   | 1                |     6 | 20140114 | 20140122 |
|  20140121 | (null)     | (null)           |     7 | 20140114 | 20140122 |
|  20140122 | (null)     | (null)           |     8 | 20140114 | 20140122 |
+-----------+------------+------------------+-------+----------+----------+

这就是我需要去的地方:

+-----------+------------+------------------+-------+----------+----------+
| DAILYDAYS | MISSED_DAY | MISSED_DAY_COUNT | COUNT |  START   |   END    |
+-----------+------------+------------------+-------+----------+----------+
|  20140114 | (null)     | (null)           |     0 | 20140114 | 20140122 |
|  20140115 | (null)     | (null)           |     1 | 20140114 | 20140122 |
|  20140116 | (null)     | (null)           |     2 | 20140114 | 20140122 |
|  20140117 | (null)     | (null)           |     3 | 20140114 | 20140122 |
|  20140118 | (null)     | (null)           |     4 | 20140114 | 20140122 |
|  20140119 | (null)     | (null)           |     5 | 20140114 | 20140122 |
|  20140120 | 20140120   | 1                |     6 | 20140114 | 20140122 |
|  20140121 | (null)     | 2                |     7 | 20140114 | 20140122 |
|  20140122 | (null)     | 3                |     8 | 20140114 | 20140122 |
+-----------+------------+------------------+-------+----------+----------+

我为你们创建了一个SQL Fiddle，并提供了一个解决方案，我尝试使用左连接但失败了，因此我在这里询问:

http://sqlfiddle.com/#!3/043de/7

问题:

我有两个表:一个包含一个表，其中包含该期间每一天的行，另一个表仅包含“错过的日子”的日期

我需要计算错过的天数，并根据开始和结束[日期]在上表中保留连续计数

在我的示例中，我的第一个表包含从 2014-01-14 到 2014-01-22 的每一天的行，另一个表包含 2014-01-20 错过的一天

所以，我需要有一个 rank() 或 dense_rank() 或任何其他变体才能获得 1,2,3 计数。 .

请注意，在这种情况下我无法使用 CTE，因为我的完整表超过 800,000 行，因此 IMO 效率不高

希望有人能帮忙

谢谢:)

编辑:我忘了提到重复是不必要的，只是我想到用rank()计数1,2,3的唯一方法是让值一直出现，以便进行“分区” "在rank() 函数内

我已经更新了上面的表格，有没有办法计算您能想到的结束日期之前错过的天数？

Answer 1

我废弃了左连接并用联合代替。

select 
  [dailydays]
  ,null as [missed_day]
  ,null as [missed_day_count] 
  ,[count]
  ,[start]
  ,[end]
from tbl_1
where dailydays < (select min(missed_day) from tbl_2)
union 
select 
   t1.[dailydays]
  ,t2.[missed_day]
  ,(t1.[count] - (select min([count]) from tbl_1 where [dailydays] = (select min([missed_day]) from tbl_2)) + 1)
  ,t1.[count]
  ,t1.[start]
  ,t1.[end]
from tbl_1 t1
left join tbl_2 t2 on t1.[dailydays] = t2.[missed_day]
where t1.dailydays >= (select min(missed_day) from tbl_2)
order by [dailydays]

编辑:或者复杂的 case 语句:

select 
   t1.[dailydays]
  ,t2.[missed_day]
  , case when (t1.[count] - (select min([count]) from tbl_1 where [dailydays] = (select min([missed_day]) from tbl_2)) + 1) < 0 then 0 else
    (t1.[count] - (select min([count]) from tbl_1 where [dailydays] = (select min([missed_day]) from tbl_2)) + 1) end as [missed_day_count]
  ,t1.[count]
  ,t1.[start]
  ,t1.[end]
from tbl_1 t1
left join tbl_2 t2 on t1.[dailydays] = t2.[missed_day]
order by [dailydays]