主页 / dba / 问题 / 183087
Accepted
Philᵀᴹ
Asked: 2017-08-10 05:58:35 +0800 CST

根据之前的月末值填充缺失数据

  • 772

给定以下数据：

create table #histories
(
    username varchar(10),
    account varchar(10),
    assigned date  
);

insert into #histories 
values 
('PHIL','ACCOUNT1','2017-01-04'),
('PETER','ACCOUNT1','2017-01-15'),
('DAVE','ACCOUNT1','2017-03-04'),
('ANDY','ACCOUNT1','2017-05-06'),
('DAVE','ACCOUNT1','2017-05-07'),
('FRED','ACCOUNT1','2017-05-08'),
('JAMES','ACCOUNT1','2017-08-05'),
('DAVE','ACCOUNT2','2017-01-02'),
('PHIL','ACCOUNT2','2017-01-18'),
('JOSH','ACCOUNT2','2017-04-08'),
('JAMES','ACCOUNT2','2017-04-09'),
('DAVE','ACCOUNT2','2017-05-06'),
('PHIL','ACCOUNT2','2017-05-07') ;

...表示将给定用户分配给帐户的时间。

我正在寻找在每个月的最后一天确定谁拥有给定帐户（指定日期是帐户转移所有权的日期），并填充任何缺少的月末dates（可能是从我可用的方便表创建的，带有有用的列DateKey,Date和LastDayOfMonth, [@AaronBertrand 提供]) 1。

期望的结果是：

PETER, ACCOUNT1, 2017-01-31
PETER, ACCOUNT1, 2017-02-28
DAVE, ACCOUNT1, 2017-03-31
DAVE, ACCOUNT1, 2017-04-30
FRED, ACCOUNT1, 2017-05-31
FRED, ACCOUNT1, 2017-06-30
FRED, ACCOUNT1, 2017-07-31
JAMES, ACCOUNT1, 2017-08-31
PHIL, ACCOUNT2, 2017-01-31
PHIL, ACCOUNT2, 2017-02-28
PHIL, ACCOUNT2, 2017-03-31
JAMES, ACCOUNT2, 2017-04-30
PHIL, ACCOUNT2, 2017-05-31

使用窗口函数执行此操作的初始部分是微不足道的，它添加了我正在努力解决的“缺失”行。

sql-server sql-server-2008-r2

5 个回答

  1. Best Answer

    解决此问题的一种方法是执行以下操作：

    1. LEAD在 SQL Server 2008 上进行仿真。您可以APPLY为此使用或 suquery。
    2. 对于没有下一行的行，将其帐户日期添加一个月。
    3. 加入包含月末日期的维度表。这将消除所有不跨越至少一个月的行，并根据需要添加行以填补空白。

    我稍微修改了您的测试数据以使结果具有确定性。还添加了一个索引：

    create table #histories
(
    username varchar(10),
    account varchar(10),
    assigned date  
);

insert into #histories 
values 
('PHIL','ACCOUNT1','2017-01-04'),
('PETER','ACCOUNT1','2017-01-15'),
('DAVE','ACCOUNT1','2017-03-04'),
('ANDY','ACCOUNT1','2017-05-06'),
('DAVE','ACCOUNT1','2017-05-07'),
('FRED','ACCOUNT1','2017-05-08'),
('JAMES','ACCOUNT1','2017-08-05'),
('DAVE','ACCOUNT2','2017-01-02'),
('PHIL','ACCOUNT2','2017-01-18'),
('JOSH','ACCOUNT2','2017-04-08'), -- changed this date to have deterministic results
('JAMES','ACCOUNT2','2017-04-09'),
('DAVE','ACCOUNT2','2017-05-06'),
('PHIL','ACCOUNT2','2017-05-07') ;

-- make life easy
create index gotta_go_fast ON #histories (account, assigned);

    这是有史以来最懒惰的日期维度表：

    create table #date_dim_months_only (
    month_date date,
    primary key (month_date)
);

-- put 2500 month ends into table
INSERT INTO #date_dim_months_only WITH (TABLOCK)
SELECT DATEADD(DAY, -1, DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '20000101'))
FROM master..spt_values;

    For step 1, there are plenty of ways to emulate LEAD. Here's one method:

    SELECT 
  h1.username
, h1.account
, h1.assigned
, next_date.assigned
FROM #histories h1
OUTER APPLY (
    SELECT TOP 1 h2.assigned
    FROM #histories h2
    WHERE h1.account = h2.account
    AND h1.assigned < h2.assigned
    ORDER BY h2.assigned ASC
) next_date;

    For step 2, we need to change the NULL values to something else. You want to include the final month for each account, so adding one month to the starting date suffices:

    ISNULL(next_date.assigned, DATEADD(MONTH, 1, h1.assigned))

    For step 3, we can join to the date dimension table. The column from the dimension table is exactly the column you need for the result set:

    INNER JOIN #date_dim_months_only dd ON
    dd.month_date >= h1.assigned AND
    dd.month_date < ISNULL(next_date.assigned, DATEADD(MONTH, 1, h1.assigned))

    I didn't like the query that I got when I put it all together. There can be issues with join order when combining OUTER APPLY and INNER JOIN. To get the join order I wanted I rewrote it with a subquery:

    SELECT 
  hist.username
, hist.account
, dd.month_date 
FROM
(
    SELECT 
      h1.username
    , h1.account
    , h1.assigned
    , ISNULL(
        (SELECT TOP 1 h2.assigned
            FROM #histories h2
            WHERE h1.account = h2.account
            AND h1.assigned < h2.assigned
            ORDER BY h2.assigned ASC
        )
        , DATEADD(MONTH, 1, h1.assigned)
    ) next_assigned
    FROM #histories h1
) hist
INNER JOIN #date_dim_months_only dd ON
    dd.month_date >= hist.assigned AND
    dd.month_date < hist.next_assigned;

    I don't know how much data you have so it might not matter for you. But the plan looks how I want it to:

    good plan

    The results match yours:

    ╔══════════╦══════════╦════════════╗
║ username ║ account  ║ month_date ║
╠══════════╬══════════╬════════════╣
║ PETER    ║ ACCOUNT1 ║ 2017-01-31 ║
║ PETER    ║ ACCOUNT1 ║ 2017-02-28 ║
║ DAVE     ║ ACCOUNT1 ║ 2017-03-31 ║
║ DAVE     ║ ACCOUNT1 ║ 2017-04-30 ║
║ FRED     ║ ACCOUNT1 ║ 2017-05-31 ║
║ FRED     ║ ACCOUNT1 ║ 2017-06-30 ║
║ FRED     ║ ACCOUNT1 ║ 2017-07-31 ║
║ JAMES    ║ ACCOUNT1 ║ 2017-08-31 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-01-31 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-02-28 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-03-31 ║
║ JAMES    ║ ACCOUNT2 ║ 2017-04-30 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-05-31 ║
╚══════════╩══════════╩════════════╝
    • 9

  2. 这里我不使用日历表，而是使用自然数表 nums.dbo.nums （希望你也有，如果没有，也可以轻松生成）

    我的答案与您的答案略有不同（'JOSH' <-> 'JAMES'），因为您的数据包含以下 2 行：

    ('JOSH','ACCOUNT2','2017-04-09'),
('JAMES','ACCOUNT2','2017-04-09'),

    使用相同的帐户和指定的日期，并且您没有准确说明在这种情况下应该采取哪一个。

    declare @eom table(account varchar(10), dt date); 

with acc_mm AS
(
select account, min(assigned) as min_dt, max(assigned) as max_dt
from #histories
group by account
),

acc_mm1 AS
(
select account,
       dateadd(month, datediff(month, '19991231', min_dt), '19991231') as start_dt,
       dateadd(month, datediff(month, '19991231', max_dt), '19991231') as end_dt
from acc_mm
)

insert into @eom (account, dt) 
select account, dateadd(month, n - 1, start_dt)
from acc_mm1
      join nums.dbo.nums            
           on n - 1 <= datediff(month, start_dt, end_dt); 

select eom.dt, eom.account, a.username
from @eom eom 
     cross apply(select top 1 *
                 from #histories h 
                 where eom.account = h.account
                   and h.assigned <= eom.dt
                 order by h.assigned desc) a
order by eom.account, eom.dt;
    • 4

  3. 三角加入为胜利！

    SELECT account,EndOfMonth,username
FROM (
    SELECT Ends.*, h.*
        ,ROW_NUMBER() OVER (PARTITION BY h.account,Ends.EndOfMonth ORDER BY h.assigned DESC) AS RowNumber
    FROM (
        SELECT [Year],[Month],MAX(DATE) AS EndOfMonth
        FROM #dim
        GROUP BY [Year],[Month]
        ) Ends
    CROSS JOIN (
        SELECT account, MAX(assigned) AS MaxAssigned
        FROM #histories
        GROUP BY account
        ) ac
    JOIN #histories h ON h.account = ac.account
        AND Year(h.assigned) = ends.[Year]
        AND Month(h.assigned) <= ends.[Month] --triangle join for the win!
        AND EndOfMonth < DATEADD(month, 1, Maxassigned)
    ) Results
WHERE RowNumber = 1
ORDER BY account,EndOfMonth;

    结果是：

    account     EndOfMonth  username

ACCOUNT1    2017-01-31  PETER
ACCOUNT1    2017-02-28  PETER
ACCOUNT1    2017-03-31  DAVE
ACCOUNT1    2017-04-30  DAVE
ACCOUNT1    2017-05-31  FRED
ACCOUNT1    2017-06-30  FRED
ACCOUNT1    2017-07-31  FRED
ACCOUNT1    2017-08-31  JAMES

ACCOUNT2    2017-01-31  PHIL
ACCOUNT2    2017-02-28  PHIL
ACCOUNT2    2017-03-31  PHIL
ACCOUNT2    2017-04-30  JAMES
ACCOUNT2    2017-05-31  PHIL

    交互式执行计划在这里。

    I/O 和 TIME 统计信息（在逻辑读取后截断所有零值）：

    (13 row(s) affected)

Table 'Worktable'.  Scan count 3, logical reads 35.
Table 'Workfile'.   Scan count 0, logical reads  0.
Table '#dim'.       Scan count 1, logical reads  4.
Table '#histories'. Scan count 1, logical reads  1.

SQL Server Execution Times:
    CPU time = 0 ms,  elapsed time = 3 ms.

    查询以创建所需的“临时表”并测试我建议的 T-SQL 语句：

    IF OBJECT_ID('tempdb..#histories') IS NOT NULL
    DROP TABLE #histories

CREATE TABLE #histories (
    username VARCHAR(10)
    ,account VARCHAR(10)
    ,assigned DATE
    );

INSERT INTO #histories
VALUES
('PHIL','ACCOUNT1','2017-01-04'),
('PETER','ACCOUNT1','2017-01-15'),
('DAVE','ACCOUNT1','2017-03-04'),
('ANDY','ACCOUNT1','2017-05-06'),
('DAVE','ACCOUNT1','2017-05-07'),
('FRED','ACCOUNT1','2017-05-08'),
('JAMES','ACCOUNT1','2017-08-05'),
('DAVE','ACCOUNT2','2017-01-02'),
('PHIL','ACCOUNT2','2017-01-18'),
('JOSH','ACCOUNT2','2017-04-08'),
('JAMES','ACCOUNT2','2017-04-09'),
('DAVE','ACCOUNT2','2017-05-06'),
('PHIL','ACCOUNT2','2017-05-07');

DECLARE @StartDate DATE = '20170101'
    ,@NumberOfYears INT = 2;

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;

DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

-- this is just a holding table for intermediate calculations:
IF OBJECT_ID('tempdb..#dim') IS NOT NULL
    DROP TABLE #dim

CREATE TABLE #dim (
    [date] DATE PRIMARY KEY
    ,[day] AS DATEPART(DAY, [date])
    ,[month] AS DATEPART(MONTH, [date])
    ,FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0))
    ,[MonthName] AS DATENAME(MONTH, [date])
    ,[week] AS DATEPART(WEEK, [date])
    ,[ISOweek] AS DATEPART(ISO_WEEK, [date])
    ,[DayOfWeek] AS DATEPART(WEEKDAY, [date])
    ,[quarter] AS DATEPART(QUARTER, [date])
    ,[year] AS DATEPART(YEAR, [date])
    ,FirstOfYear AS CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0))
    ,Style112 AS CONVERT(CHAR(8), [date], 112)
    ,Style101 AS CONVERT(CHAR(10), [date], 101)
    );

-- use the catalog views to generate as many rows as we need

INSERT #dim ([date])
SELECT d
FROM (
    SELECT d = DATEADD(DAY, rn - 1, @StartDate)
    FROM (
        SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) rn = ROW_NUMBER() OVER (
                ORDER BY s1.[object_id]
                )
        FROM sys.all_objects AS s1
        CROSS JOIN sys.all_objects AS s2
        -- on my system this would support > 5 million days
        ORDER BY s1.[object_id]
        ) AS x
    ) AS y;

/* The actual SELECT statement to get the results we want! */

SET STATISTICS IO, TIME ON;

SELECT account,EndOfMonth,username
FROM (
    SELECT Ends.*, h.*
        ,ROW_NUMBER() OVER (PARTITION BY h.account,Ends.EndOfMonth ORDER BY h.assigned DESC) AS RowNumber
    FROM (
        SELECT [Year],[Month],MAX(DATE) AS EndOfMonth
        FROM #dim
        GROUP BY [Year],[Month]
        ) Ends
    CROSS JOIN (
        SELECT account, MAX(assigned) AS MaxAssigned
        FROM #histories
        GROUP BY account
        ) ac
    JOIN #histories h ON h.account = ac.account
        AND Year(h.assigned) = ends.[Year]
        AND Month(h.assigned) <= ends.[Month] --triangle join for the win!
        AND EndOfMonth < DATEADD(month, 1, Maxassigned)
    ) Results
WHERE RowNumber = 1
ORDER BY account,EndOfMonth;

SET STATISTICS IO, TIME OFF;

--IF OBJECT_ID('tempdb..#histories') IS NOT NULL DROP TABLE #histories
--IF OBJECT_ID('tempdb..#dim') IS NOT NULL DROP TABLE #dim
    • 2

  4. 这绝不是一个看起来很干净的解决方案，但它似乎提供了您正在寻找的结果（我相信其他人会为您提供漂亮、干净、完全优化的查询）。

    create table #histories
(
    username varchar(10),
    account varchar(10),
    assigned date  
);

insert into #histories 
values 
('PHIL','ACCOUNT1','2017-01-04'),
('PETER','ACCOUNT1','2017-01-15'),
('DAVE','ACCOUNT1','2017-03-04'),
('ANDY','ACCOUNT1','2017-05-06'),
('DAVE','ACCOUNT1','2017-05-07'),
('FRED','ACCOUNT1','2017-05-08'),
('JAMES','ACCOUNT1','2017-08-05'),
('DAVE','ACCOUNT2','2017-01-02'),
('PHIL','ACCOUNT2','2017-01-18'),
('JOSH','ACCOUNT2','2017-04-09'),
('JAMES','ACCOUNT2','2017-04-09'),
('DAVE','ACCOUNT2','2017-05-06'),
('PHIL','ACCOUNT2','2017-05-07') ; 


IF (SELECT OBJECT_ID(N'tempdb..#IncompleteResults')) IS NOT NULL
    DROP TABLE #IncompleteResults;

DECLARE @EOMTable TABLE ( EndOfMonth DATE );
DECLARE @DateToWrite DATE = '2017-01-31';
WHILE @DateToWrite < '2017-10-31'
    BEGIN
        INSERT  INTO @EOMTable
                ( EndOfMonth )
                SELECT  @DateToWrite;

        SELECT  @DateToWrite = EOMONTH(DATEADD(MONTH, 1, @DateToWrite));
    END

    ;
WITH    cteAccountsByMonth
          AS ( SELECT   EndOfMonth ,
                        account
               FROM     @EOMTable e
                        CROSS JOIN ( SELECT DISTINCT
                                            account
                                     FROM   #histories
                                   ) AS h
             ),
        cteHistories
          AS ( SELECT   username ,
                        account ,
                        ROW_NUMBER() OVER ( PARTITION BY ( CAST(DATEPART(YEAR,
                                                              assigned) AS CHAR(4))
                                                           + ( RIGHT('00'
                                                              + CAST(DATEPART(MONTH,
                                                              assigned) AS VARCHAR(10)),
                                                              2) ) ), account ORDER BY assigned DESC ) AS rownum ,
                        CAST(DATEPART(YEAR, assigned) AS CHAR(4)) + RIGHT('00'
                                                              + CAST(DATEPART(MONTH,
                                                              assigned) AS VARCHAR(10)),
                                                              2) AS PartialDate ,
                        assigned ,
                        EOMONTH(assigned) AS EndofMonth
               FROM     #histories
             )
    SELECT  username ,
            e.EndOfMonth ,
            e.account
    INTO #IncompleteResults
    FROM    cteAccountsByMonth e
            LEFT JOIN cteHistories c ON e.EndOfMonth = c.EndofMonth
                                        AND c.account = e.account
                                        AND c.rownum = 1
SELECT  CASE WHEN username IS NULL
             THEN ( SELECT  username
                    FROM    #IncompleteResults i2
                    WHERE   username IS NOT NULL
                            AND i.account = i2.account
                            AND i2.EndOfMonth = ( SELECT    MAX(EndOfMonth)
                                                  FROM      #IncompleteResults i3
                                                  WHERE     i3.EndOfMonth < i.EndOfMonth
                                                            AND i3.account = i.account
                                                            AND i3.username IS NOT NULL
                                                )
                  )
             ELSE username
        END AS username ,
        EndOfMonth ,
        account 
FROM    #IncompleteResults i
ORDER BY account ,
        i.EndOfMonth;
    • 2

  5. 我使用了Aaron Bertrand 的日期维度表，正如您在问题中提到的那样（对于这种情况，这是一个超级方便的表），我编写了以下代码：

    我使用以下代码将EndOfMonth列添加到#dim表中（列之后）：FirstOfMonth

     EndOfMonth as dateadd(s,-1,dateadd(mm, datediff(m,0,[date])+1,0)),

    和解决方案：

    if object_id('tempdb..#temp') is not null drop table #temp
create table #temp (nr int, username varchar(100), account varchar(100), eom date)

;with lastassignedpermonth as
(
    select 
           month(assigned) month
         , account
         , max(assigned) assigned
    from 
           #histories 
    group by month(assigned), account 
)
insert into #temp
select 
       distinct row_number() over (order by d.account, d.eom) nr
     , h.username
     , d.account
     , d.eom
from ( 
        select distinct month, cast(d.endofmonth as date) eom, t.account 
        from #dim d cross apply (select distinct account from #histories) t 
     ) d
            left join lastassignedpermonth l on d.month = l.month and l.assigned <= d.eom and d.account = l.account 
            left join #histories h on h.assigned = l.assigned and h.account = l.account 
where d.eom <=  dateadd(s,-1,dateadd(mm, datediff(m,0,getdate())+1,0)) -- end of current month
order by d.account, eom 

-- This could have been done in one single statement with the lead() function but that is available as of SQL Server 2012
select case when t.username is null then (select username from #temp where nr = previous_username.nr) else t.username end as username, t.account, t.eom 
from #temp as t cross apply ( 
                                select max(nr) nr 
                                from #temp as t1
                                where t1.nr < t.nr and t1.username is not null
                            ) as previous_username

/*
   Note: You get twice JAMES and JOSH for April/ACCOUNT2, because apparently they are both assigned on the same date(2017-04-09)... 
   I guess your data should be cleaned up of overlapping dates.
*/
    • 2

相关问题