填写行的缺失天数 [英] Filling in missing days for rows

问题描述

给出具有以下模式的表:

Given a table with schema like this:

id1    id2    day    number

我该如何转动:

a    b    day1    2
a    b    day5    4
a    b    day9    8
c    d    day2    1
c    d    day3    2
c    d    day5    4
c    d    day8    3

进入这个?

a    b    day1    2
a    b    day2    2
a    b    day3    2
a    b    day4    2
a    b    day5    4
a    b    day6    4
a    b    day7    4
a    b    day8    4
a    b    day9    8
c    d    day2    1
c    d    day3    2
c    d    day4    2
c    d    day5    4
c    d    day6    4
c    d    day7    4
c    d    day8    3

为澄清起见，对于id1和id2的每组，我需要填写缺少的行 日期范围从该分组的最小日期到最大 日期.此外，要填写的行必须使用上一个条目的 数字列.它是数字列.

To clarify, for each group of id1 and id2, I need to fill in the missing rows with dates ranging from the minimum date for that grouping to the maximum date. Furthermore, the rows that get filled in must use the previous entry's number column for it's number column.

我需要它尽快运行.

如果可以在LINQ to SQL中进行奖励(假设存在该类的话) 表格).

Bonus points if you can do it in LINQ to SQL (assuming the class exists for the table).

日期"列实际上是一个表示日期的int值，但出于争论的考虑，它可能是一个日期.

The day column is actually an int that represents the day, but for the sake of argument, it could be a date.

我已经完成了天真的方法，即遍历每个组并添加缺失的日子，但这似乎效率很低.我必须以为有更快的速度，或者以前有人遇到过这种情况.

I've done the naive approach of iterating over each group and adding in the missing days, but it just seems terribly inefficient. I have to think that there's something faster or that someone has encountered this situation before.

推荐答案

WITH    dates (id1, id2, ds, de) AS
        (
        SELECT  id1, id2, MIN(d), MAX(d)
        FROM    mytable m
        GROUP BY
               id1, id2
        UNION ALL
        SELECT  d.id1, d.id2, DATEADD(d, 1, ds), de
        FROM    dates d
        WHERE   ds < de
        )
SELECT  id1, id2, ds, m.number
FROM    dates d
CROSS APPLY
        (
        SELECT  TOP 1 number
        FROM    mytable m
        WHERE   m.id1 = d.id1
                AND m.id2 = d.id2
                AND m.d <= d.ds
        ORDER BY
                m.d DESC
        ) m
OPTION (MAXRECURSION 0)

这篇关于填写行的缺失天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！