sql-server - 规范化表格

Question

我有一张旧表，我无法更改。其中的值可以从遗留应用程序修改(应用程序也不能更改)。由于新应用程序(新要求)对表的大量访问，我想创建一个临时表，这有望加快查询速度。

实际需求，是计算从X到Y的工作日数。比如给我从2001年1月1日到2004年12月24日的所有工作日。该表用于标记哪些天是休息日，因为不同的公司可能有不同的休息日 - 不仅仅是周六 + 周日)

临时表将从 .NET 程序创建，每次用户进入此查询的屏幕时(用户可能多次运行查询，具有不同的值，表只创建一次)，所以我希望它是尽可能快。下面的方法运行不到一秒钟，但我只用一个小数据集测试了它，它仍然可能需要将近半秒，这对 UI 来说不是很好——尽管这只是第一次查询的开销。

旧表如下所示:

CREATE TABLE [business_days](
    [country_code] [char](3) ,
    [state_code] [varchar](4) ,
    [calendar_year] [int] ,
    [calendar_month] [varchar](31) ,
    [calendar_month2] [varchar](31) ,
    [calendar_month3] [varchar](31) ,
    [calendar_month4] [varchar](31) ,
    [calendar_month5] [varchar](31) ,
    [calendar_month6] [varchar](31) ,
    [calendar_month7] [varchar](31) ,
    [calendar_month8] [varchar](31) ,
    [calendar_month9] [varchar](31) ,
    [calendar_month10] [varchar](31) ,
    [calendar_month11] [varchar](31) ,
    [calendar_month12] [varchar](31) ,
misc.
)

每个月有 31 个字符，任何休息日(周六 + 周日 + 节假日)都用 X 标记。每半天用 'H' 标记。例如，如果一个月从星期四开始，那么它将看起来像(星期四+星期五工作日，星期六+星期日标有 X):

'  XX     XX ..'

我希望新表看起来像这样:

create table #Temp (country varchar(3), state varchar(4), date datetime, hours int)

而且我希望只有关闭天数的行(在之前的查询中用 X 或 H 标记)

我最终做了什么，到目前为止是这样的:创建一个临时中间表，如下所示:

create table #Temp_2 (country_code varchar(3), state_code varchar(4), calendar_year int, calendar_month varchar(31), month_code int)

为了填充它，我有一个基本上联合了 calendar_month、calendar_month2、calendar_month3 等的联合。

然后我有一个循环遍历#Temp_2 中的所有行，在处理每一行之后，它会从#Temp_2 中删除。为了处理该行，有一个从 1 到 31 的循环，并且检查 substring(calendar_month, counter, 1) 的 X 或 H，在这种情况下，有一个插入#Temp 表。[编辑添加的代码]

Declare @country_code char(3)
Declare @state_code varchar(4)
Declare @calendar_year int
Declare @calendar_month varchar(31)
Declare @month_code int
Declare @calendar_date datetime
Declare @day_code int
WHILE EXISTS(SELECT * From #Temp_2) -- where processed = 0)
BEGIN
    Select Top 1 @country_code = t2.country_code, @state_code = t2.state_code, @calendar_year = t2.calendar_year, @calendar_month = t2.calendar_month, @month_code = t2.month_code From #Temp_2 t2 -- where processed = 0

    set @day_code = 1
    while @day_code <= 31
    begin
        if substring(@calendar_month, @day_code, 1) = 'X'
        begin
            set @calendar_date = convert(datetime, (cast(@month_code as varchar) + '/' + cast(@day_code as varchar) + '/' + cast(@calendar_year as varchar)))
            insert into #Temp (country, state, date, hours) values (@country_code, @state_code, @calendar_date, 8)
        end
        if substring(@calendar_month, @day_code, 1) = 'H'
        begin
            set @calendar_date = convert(datetime, (cast(@month_code as varchar) + '/' + cast(@day_code as varchar) + '/' + cast(@calendar_year as varchar)))
            insert into #Temp (country, state, date, hours) values (@country_code, @state_code, @calendar_date, 4)
        end

        set @day_code = @day_code + 1
    end
    delete from #Temp_2 where @country_code = country_code AND @state_code = state_code AND @calendar_year = calendar_year AND @calendar_month = calendar_month AND @month_code = month_code
    --update #Temp_2 set processed = 1 where @country_code = country_code AND @state_code = state_code AND @calendar_year = calendar_year AND @calendar_month = calendar_month AND @month_code = month_code
END

我不是 SQL 方面的专家，所以我希望获得一些关于我的方法的意见，甚至可能是更好的方法建议。

有了临时表后，我打算做(日期将来自表):

select cast(convert(datetime, ('01/31/2012'), 101) -convert(datetime, ('01/17/2012'), 101) as int) -  ((select sum(hours) from #Temp where date between convert(datetime, ('01/17/2012'), 101) and convert(datetime, ('01/31/2012'), 101)) / 8)

除了规范化表的解决方案，我现在实现的另一个解决方案是一个函数，它通过扫描当前表来完成所有这些获取工作日的逻辑。它运行得非常快，但我对调用函数犹豫不决，如果我可以添加一个更简单的查询来获取结果的话。

(我目前正在 MSSQL 上尝试这个，但我需要对 Sybase ASE 和 Oracle 做同样的事情)

Answer 1

这应该满足要求，“...计算从 X 到 Y 的工作日数。”

它将每个空间算作一个工作日，将 X 或空间以外的任何东西算作半天(根据 OP，应该只是 H)。

我在 SQL Server 2008 R2 中实现了这一点:

-- Calculate number of business days from X to Y
declare @start date = '20120101' -- X
declare @end date = '20120101' -- Y
-- Outer query sums the length of the full_year text minus non-work days
-- Spaces are doubled to help account for half-days...then divide by two
select sum(datalength(replace(replace(substring(full_year, first_day, last_day - first_day + 1), ' ', '  '), 'X', '')) / 2.0) as number_of_business_days
from (
    select
        -- Get substring start value for each year
        case
                when calendar_year = datepart(yyyy, @start) then datepart(dayofyear, @start)
                else 1
            end as first_day
        -- Get substring end value for each year
        , case
                when calendar_year = datepart(yyyy, @end) then datepart(dayofyear, @end)
                when calendar_year > datepart(yyyy, @end) then 0
                when calendar_year < datepart(yyyy, @start) then 0
                else datalength(full_year)
            end as last_day
        , full_year
    from (
        select calendar_year
            -- Get text representation of full year
            , calendar_month
            + calendar_month2
            + calendar_month3
            + calendar_month4
            + calendar_month5
            + calendar_month6
            + calendar_month7
            + calendar_month8
            + calendar_month9
            + calendar_month10
            + calendar_month11
            + calendar_month12 as full_year
        from business_days
        -- where country_code = 'USA' etc.
    ) as get_year
) as get_days

where 子句可以进行最内层的查询。

它不是遗留格式的反支点，OP 花费了大量时间，并且可能需要更多(并且可能是不必要的)计算周期。我假设这样的事情是“很高兴看到”而不是要求的一部分。 Jeff Moden 有关于如何 tally table 的精彩文章在这种情况下可能会有所帮助(无论如何对于 SQL Server)。

根据特定 DBMS 的设置方式，可能需要观察尾随空格(注意我使用的是数据长度而不是 len)。

更新:添加了 OP 请求的临时表:

select country_code
    , state_code
    , dateadd(d, t.N - 1, cast(cast(a.calendar_year as varchar(8)) as date)) as calendar_date
    , case substring(full_year, t.N, 1) when 'X' then 0 when 'H' then 4 else 8 end as business_hours
from (
    select country_code
        , state_code
        , calendar_year
        , calendar_month
            + calendar_month2
            + calendar_month3
            + calendar_month4
            + calendar_month5
            + calendar_month6
            + calendar_month7
            + calendar_month8
            + calendar_month9
            + calendar_month10
            + calendar_month11
            + calendar_month12
            as full_year
    from business_days
) as a, (
        select a.N + b.N * 10 + c.N * 100 + 1 as N
        from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a
            , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b
            , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
    ) as t -- cross join with Tally table built on the fly
where t.N <= datalength(a.full_year)