Calcular visitas totais

Existem muitas perguntas e artigos sobre intervalos de tempo de embalagem. Por exemplo, Packing Intervals de Itzik Ben-Gan.

Você pode empacotar seus intervalos para o usuário determinado. Uma vez compactado, não haverá sobreposições, portanto, você pode simplesmente somar as durações dos intervalos compactados.

Se seus intervalos forem datas sem horas, eu usaria uma Calendartabela. Esta tabela simplesmente contém uma lista de datas para várias décadas. Se você não possui uma tabela Calendário, basta criar uma:

CREATE TABLE [dbo].[Calendar](
    [dt] [date] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED 
(
    [dt] ASC
));

Há muitas maneiras de preencher essa tabela .

Por exemplo, 100 mil linhas (~ 270 anos) de 1900-01-01:

INSERT INTO dbo.Calendar (dt)
SELECT TOP (100000) 
    DATEADD(day, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, '19000101') AS dt
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

Veja também Por que as tabelas de números são "inestimáveis"?

Depois de ter uma Calendarmesa, veja como usá-la.

Cada linha original é unida à Calendartabela para retornar tantas linhas quantas forem as datas entre StartDatee EndDate.

Em seguida, contamos datas distintas, o que remove as datas sobrepostas.

SELECT COUNT(DISTINCT CA.dt) AS TotalCount
FROM
    #Items AS T
    CROSS APPLY
    (
        SELECT dbo.Calendar.dt
        FROM dbo.Calendar
        WHERE
            dbo.Calendar.dt >= T.StartDate
            AND dbo.Calendar.dt <= T.EndDate
    ) AS CA
WHERE T.CustID = 11205
;

Resultado

TotalCount
7

Concordo plenamente que a Numberse a Calendartabela são muito úteis e se esse problema pode ser muito simplificado com uma tabela Calendar.

I'll suggest another solution though (that doesn't need either a calendar table or windowed aggregates - as some of the answers from the linked post by Itzik do). It may not be the most efficient in all cases (or may be the worst in all cases!) but I don't think it harms to test.

It works by first finding start and end dates that do not overlap with other intervals, then puts them in two rows (separately the start and end dates) in order to assign them row numbers and finally matches the 1st start date with the 1st end date, the 2nd with the 2nd, etc.:

WITH 
  start_dates AS
    ( SELECT CustID, StartDate,
             Rn = ROW_NUMBER() OVER (PARTITION BY CustID 
                                     ORDER BY StartDate)
      FROM items AS i
      WHERE NOT EXISTS
            ( SELECT *
              FROM Items AS j
              WHERE j.CustID = i.CustID
                AND j.StartDate < i.StartDate AND i.StartDate <= j.EndDate 
            )
      GROUP BY CustID, StartDate
    ),
  end_dates AS
    ( SELECT CustID, EndDate,
             Rn = ROW_NUMBER() OVER (PARTITION BY CustID 
                                     ORDER BY EndDate) 
      FROM items AS i
      WHERE NOT EXISTS
            ( SELECT *
              FROM Items AS j
              WHERE j.CustID = i.CustID
                AND j.StartDate <= i.EndDate AND i.EndDate < j.EndDate 
            )
      GROUP BY CustID, EndDate
    )
SELECT s.CustID, 
       Result = SUM( DATEDIFF(day, s.StartDate, e.EndDate) + 1 )
FROM start_dates AS s
  JOIN end_dates AS e
    ON  s.CustID = e.CustID
    AND s.Rn = e.Rn 
GROUP BY s.CustID ;

Two indexes, on (CustID, StartDate, EndDate) and on (CustID, EndDate, StartDate) would be useful for improving performance of the query.

An advantage over the Calendar (perhaps the only one) is that it can easily adapted to work with datetime values and counting the length of the "packed intervals" in different precision, larger (weeks, years) or smaller (hours, minutes or seconds, milliseconds, etc) and not only counting dates. A Calendar table of minute or seconds precision would be quite big and (cross) joining it to a big table would be a quite interesting experience but possibly not the most efficient one.

(thanks to Vladimir Baranov): It is rather difficult to have a proper comparison of performance, because performance of different methods would likely depend on the data distribution. 1) how long are the intervals - the shorter the intervals, the better Calendar table would perform, because long intervals would produce a lot of intermediate rows 2) how often intervals overlap - mostly non-overlapping intervals vs. most intervals covering the same range. I think performance of Itzik's solution depends on that. There could be other ways to skew the data and it's hard to tell how efficiency of the various methods would be affected.

Essa primeira consulta cria diferentes intervalos de Data de início e Data de término sem sobreposições.

Observação:

• Seu sample( id=0) está misturado com um sample do Ypercube ( id=1)
• Essa solução pode não ser bem dimensionada com uma grande quantidade de dados para cada id ou um grande número de id. Isso tem a vantagem de não exigir uma tabela numérica. Com um grande conjunto de dados, uma tabela de números provavelmente fornecerá melhores desempenhos.

Consulta:

SELECT DISTINCT its.id
    , Start_Date = its.Start_Date 
    , End_Date = COALESCE(DATEADD(day, -1, itmax.End_Date), CASE WHEN itmin.Start_Date > its.End_Date THEN itmin.Start_Date ELSE its.End_Date END)
    --, x1=itmax.End_Date, x2=itmin.Start_Date, x3=its.End_Date
FROM @Items its
OUTER APPLY (
    SELECT Start_Date = MAX(End_Date) FROM @Items std
    WHERE std.Item_ID <> its.Item_ID AND std.Start_Date < its.Start_Date AND std.End_Date > its.Start_Date
) itmin
OUTER APPLY (
    SELECT End_Date = MIN(Start_Date) FROM @Items std
    WHERE std.Item_ID <> its.Item_ID+1000 AND std.Start_Date > its.Start_Date AND std.Start_Date < its.End_Date
) itmax;

Resultado:

id  | Start_Date                    | End_Date                      
0   | 2015-01-23 00:00:00.0000000   | 2015-01-23 00:00:00.0000000   => 1
0   | 2015-01-24 00:00:00.0000000   | 2015-01-27 00:00:00.0000000   => 4
0   | 2015-01-29 00:00:00.0000000   | 2015-01-30 00:00:00.0000000   => 2
1   | 2016-01-20 00:00:00.0000000   | 2016-01-22 00:00:00.0000000   => 3
1   | 2016-01-23 00:00:00.0000000   | 2016-01-24 00:00:00.0000000   => 2
1   | 2016-01-25 00:00:00.0000000   | 2016-01-29 00:00:00.0000000   => 5

Se você usar esta Data de início e Data de término com DATEDIFF:

SELECT DATEDIFF(day
    , its.Start_Date 
    , End_Date = COALESCE(DATEADD(day, -1, itmax.End_Date), CASE WHEN itmin.Start_Date > its.End_Date THEN itmin.Start_Date ELSE its.End_Date END)
) + 1
...

A saída (com duplicatas) é:

• 1, 4 e 2 para id 0 (sua amostra => SUM=7)
• 3, 2 e 5 para id 1 (amostra Ypercube => SUM=10)

Você só precisa juntar tudo com um SUMe GROUP BY:

SELECT id 
    , Days = SUM(
        DATEDIFF(day, Start_Date, End_Date)+1
    )
FROM (
    SELECT DISTINCT its.id
         , Start_Date = its.Start_Date 
        , End_Date = COALESCE(DATEADD(day, -1, itmax.End_Date), CASE WHEN itmin.Start_Date > its.End_Date THEN itmin.Start_Date ELSE its.End_Date END)
    FROM @Items its
    OUTER APPLY (
        SELECT Start_Date = MAX(End_Date) FROM @Items std
        WHERE std.Item_ID <> its.Item_ID AND std.Start_Date < its.Start_Date AND std.End_Date > its.Start_Date
    ) itmin
    OUTER APPLY (
        SELECT End_Date = MIN(Start_Date) FROM @Items std
        WHERE std.Item_ID <> its.Item_ID AND std.Start_Date > its.Start_Date AND std.Start_Date < its.End_Date
    ) itmax
) as d
GROUP BY id;

Resultado:

id  Days
0   7
1   10

Dados usados ​​com 2 IDs diferentes:

INSERT INTO @Items
    (id, Item_ID, Start_Date, End_Date)
VALUES 
    (0, 20009, '2015-01-23', '2015-01-26'),
    (0, 20010, '2015-01-24', '2015-01-24'),
    (0, 20011, '2015-01-23', '2015-01-26'),
    (0, 20012, '2015-01-23', '2015-01-27'),
    (0, 20013, '2015-01-23', '2015-01-27'),
    (0, 20014, '2015-01-29', '2015-01-30'),

    (1, 20009, '2016-01-20', '2016-01-24'),
    (1, 20010, '2016-01-23', '2016-01-26'),
    (1, 20011, '2016-01-25', '2016-01-29')

Acho que isso seria direto com uma tabela de calendário, por exemplo, algo assim:

SELECT i.CustID, COUNT( DISTINCT c.calendarDate ) days
FROM #Items i
    INNER JOIN calendar.main c ON c.calendarDate Between i.StartDate And i.EndDate
GROUP BY i.CustID

Equipamento de teste

USE tempdb
GO

-- Cutdown calendar script
IF OBJECT_ID('dbo.calendar') IS NULL
BEGIN

    CREATE TABLE dbo.calendar (
        calendarId      INT IDENTITY(1,1) NOT NULL,
        calendarDate    DATE NOT NULL,

        CONSTRAINT PK_calendar__main PRIMARY KEY ( calendarDate ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
        CONSTRAINT UK_calendar__main UNIQUE NONCLUSTERED ( calendarId ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
END
GO


-- Populate calendar table once only
IF NOT EXISTS ( SELECT * FROM dbo.calendar )
BEGIN

    -- Populate calendar table
    WITH cte AS
    (
    SELECT 0 x
    UNION ALL
    SELECT x + 1
    FROM cte
    WHERE x < 11323 -- Do from year 1 Jan 2000 until 31 Dec 2030 (extend if required)
    )
    INSERT INTO dbo.calendar ( calendarDate )
    SELECT
        calendarDate
    FROM
        (
        SELECT 
            DATEADD( day, x, '1 Jan 2010' ) calendarDate,
            DATEADD( month, -7, DATEADD( day, x, '1 Jan 2010' ) ) academicDate
        FROM cte
        ) x
    WHERE calendarDate < '1 Jan 2031'
    OPTION ( MAXRECURSION 0 )

    ALTER INDEX ALL ON dbo.calendar REBUILD

END
GO





IF OBJECT_ID('tempdb..Items') IS NOT NULL DROP TABLE Items
GO

CREATE TABLE dbo.Items
    (
    CustID INT NOT NULL,
    ItemID INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,

    INDEX _cdx_Items CLUSTERED ( CustID, StartDate, EndDate )
    )
GO

INSERT INTO Items ( CustID, ItemID, StartDate, EndDate )
SELECT 11205, 20009, '2015-01-23',  '2015-01-26'  
UNION ALL 
SELECT 11205, 20010, '2015-01-24',  '2015-01-24'    
UNION ALL  
SELECT 11205, 20011, '2015-01-23',  '2015-01-26' 
UNION ALL  
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'  
UNION ALL  
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'   
UNION ALL  
SELECT 11205, 20012, '2015-01-28',  '2015-01-29'
GO


-- Scale up : )
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO Items ( CustID, ItemID, StartDate, EndDate )
SELECT 11206 + rn % 999, 20012 + rn, DATEADD( day, rn % 333, '1 Jan 2015' ), DATEADD( day, ( rn % 333 ) + rn % 7, '1 Jan 2015' )
FROM cte
GO
--:exit



-- My query: Pros: simple, one copy of items, easy to understand and maintain.  Scales well to 1 million + rows.
-- Cons: requires calendar table.  Others?
SELECT i.CustID, COUNT( DISTINCT c.calendarDate ) days
FROM dbo.Items i
    INNER JOIN dbo.calendar c ON c.calendarDate Between i.StartDate And i.EndDate
GROUP BY i.CustID
--ORDER BY i.CustID
GO


-- Vladimir query: Pros: Effectively same as above
-- Cons: I wouldn't use CROSS APPLY where it's not necessary.  Fortunately optimizer simplifies avoiding RBAR (I think).
-- Point of style maybe, but in terms of queries being self-documenting I prefer number 1.
SELECT T.CustID, COUNT( DISTINCT CA.calendarDate ) AS TotalCount
FROM
    Items AS T
    CROSS APPLY
    (
        SELECT c.calendarDate
        FROM dbo.calendar c
        WHERE
            c.calendarDate >= T.StartDate
            AND c.calendarDate <= T.EndDate
    ) AS CA
GROUP BY T.CustID
--ORDER BY T.CustID
--WHERE T.CustID = 11205
GO


/*  WARNING!! This is commented out as it can't compete in the scale test.  Will finish at scale 100, 1,000, 10,000, eventually.  I got 38 mins for 10,0000.  Pegs CPU.  

-- Julian:  Pros; does not require calendar table.
-- Cons: over-complicated (eg versus Query 1 in terms of number of lines of code, clauses etc); three copies of dbo.Items table (we have already shown
-- this query is possible with one); does not scale (even at 100,000 rows query ran for 38 minutes on my test rig versus sub-second for first two queries).  <<-- this is serious.
-- Indexing could help.
SELECT DISTINCT
    CustID,
     StartDate = CASE WHEN itmin.StartDate < its.StartDate THEN itmin.StartDate ELSE its.StartDate END
    , EndDate = CASE WHEN itmax.EndDate > its.EndDate THEN itmax.EndDate ELSE its.EndDate END
FROM Items its
OUTER APPLY (
    SELECT StartDate = MIN(StartDate) FROM Items std
    WHERE std.ItemID <> its.ItemID AND (
        (std.StartDate <= its.StartDate AND std.EndDate >= its.StartDate)
        OR (std.StartDate >= its.StartDate AND std.StartDate <= its.EndDate)
    )
) itmin
OUTER APPLY (
    SELECT EndDate = MAX(EndDate) FROM Items std
    WHERE std.ItemID <> its.ItemID AND (
        (std.EndDate >= its.StartDate AND std.EndDate <= its.EndDate)
        OR (std.StartDate <= its.EndDate AND std.EndDate >= its.EndDate)
    )
) itmax
GO
*/

-- ypercube:  Pros; does not require calendar table.
-- Cons: over-complicated (eg versus Query 1 in terms of number of lines of code, clauses etc); four copies of dbo.Items table (we have already shown
-- this query is possible with one); does not scale well; at 1,000,000 rows query ran for 2:20 minutes on my test rig versus sub-second for first two queries.
WITH 
  start_dates AS
    ( SELECT CustID, StartDate,
             Rn = ROW_NUMBER() OVER (PARTITION BY CustID 
                                     ORDER BY StartDate)
      FROM items AS i
      WHERE NOT EXISTS
            ( SELECT *
              FROM Items AS j
              WHERE j.CustID = i.CustID
                AND j.StartDate < i.StartDate AND i.StartDate <= j.EndDate 
            )
      GROUP BY CustID, StartDate
    ),
  end_dates AS
    ( SELECT CustID, EndDate,
             Rn = ROW_NUMBER() OVER (PARTITION BY CustID 
                                     ORDER BY EndDate) 
      FROM items AS i
      WHERE NOT EXISTS
            ( SELECT *
              FROM Items AS j
              WHERE j.CustID = i.CustID
                AND j.StartDate <= i.EndDate AND i.EndDate < j.EndDate 
            )
      GROUP BY CustID, EndDate
    )
SELECT s.CustID, 
       Result = SUM( DATEDIFF(day, s.StartDate, e.EndDate) + 1 )
FROM start_dates AS s
  JOIN end_dates AS e
    ON  s.CustID = e.CustID
    AND s.Rn = e.Rn 
GROUP BY s.CustID ;