sql - tables - Agregue para cada día a lo largo de series de tiempo, sin usar lógica no equijoin

natural join sql server

Question

Pregunta inicial

Dado el siguiente conjunto de datos emparejado con una tabla de fechas:

MembershipId | ValidFromDate | ValidToDate
==========================================
0001 | 1997-01-01 | 2006-05-09
0002 | 1997-01-01 | 2017-05-12
0003 | 2005-06-02 | 2009-02-07

¿Cuántas Memberships estaban abiertas en un día o una serie temporal de días?

Respuesta inicial

Siguiendo esta pregunta que se hizo here , esta respuesta proporcionó la funcionalidad necesaria:

select d.[Date]
,count(m.MembershipID) as MembershipCount
from DIM.[Date] as d
left join Memberships as m
on(d.[Date] between m.ValidFromDateKey and m.ValidToDateKey)
where d.CalendarYear = 2016
group by d.[Date]
order by d.[Date];

aunque un comentarista comentó que existen otros enfoques cuando la falta de equijo toma demasiado tiempo.

Seguir

Como tal, ¿cómo se vería la única lógica de equijoin para replicar la salida de la consulta anterior?

Progreso hasta ahora

De las respuestas proporcionadas hasta ahora, he encontrado lo siguiente, que supera en rendimiento al hardware con el que estoy trabajando en los 3,2 millones de registros de Membership :

declare @s date = ''20160101'';
declare @e date = getdate();
with s as
(
select d.[Date] as d
,count(s.MembershipID) as s
from dbo.Dates as d
join dbo.Memberships as s
on d.[Date] = s.ValidFromDateKey
group by d.[Date]
)
,e as
(
select d.[Date] as d
,count(e.MembershipID) as e
from dbo.Dates as d
join dbo.Memberships as e
on d.[Date] = e.ValidToDateKey
group by d.[Date]
),c as
(
select isnull(s.d,e.d) as d
,sum(isnull(s.s,0) - isnull(e.e,0)) over (order by isnull(s.d,e.d)) as c
from s
full join e
on s.d = e.d
)
select d.[Date]
,c.c
from dbo.Dates as d
left join c
on d.[Date] = c.d
where d.[Date] between @s and @e
order by d.[Date]
;

Después de eso, para dividir este agregado en grupos constituyentes por día, tengo lo siguiente, que también está funcionando bien:

declare @s date = ''20160101'';
declare @e date = getdate();
with s as
(
select d.[Date] as d
,s.MembershipGrouping as g
,count(s.MembershipID) as s
from dbo.Dates as d
join dbo.Memberships as s
on d.[Date] = s.ValidFromDateKey
group by d.[Date]
,s.MembershipGrouping
)
,e as
(
select d.[Date] as d
,e..MembershipGrouping as g
,count(e.MembershipID) as e
from dbo.Dates as d
join dbo.Memberships as e
on d.[Date] = e.ValidToDateKey
group by d.[Date]
,e.MembershipGrouping
),c as
(
select isnull(s.d,e.d) as d
,isnull(s.g,e.g) as g
,sum(isnull(s.s,0) - isnull(e.e,0)) over (partition by isnull(s.g,e.g) order by isnull(s.d,e.d)) as c
from s
full join e
on s.d = e.d
and s.g = e.g
)
select d.[Date]
,c.g
,c.c
from dbo.Dates as d
left join c
on d.[Date] = c.d
where d.[Date] between @s and @e
order by d.[Date]
,c.g
;

¿Alguien puede mejorar en lo anterior?

Answer 1

Así es como resolvería este problema con equijoin:

--data generation
declare @Membership table (MembershipId varchar(10), ValidFromDate date, ValidToDate date)
insert into @Membership values
(''0001'', ''1997-01-01'', ''2006-05-09''),
(''0002'', ''1997-01-01'', ''2017-05-12''),
(''0003'', ''2005-06-02'', ''2009-02-07'')
declare @startDate date, @endDate date
select @startDate = MIN(ValidFromDate), @endDate = max(ValidToDate) from @Membership
--in order to use equijoin I need all days between min date and max date from Membership table (both columns)
;with cte as (
select @startDate [date]
union all
select DATEADD(day, 1, [date]) from cte
where [date] < @endDate
)
--in this query, we will assign value to each day:
--one, if project started on that day
--minus one, if project ended on that day
--then, it''s enough to (cumulative) sum all this values to get how many projects were ongoing on particular day
select [date],
sum(case when [DATE] = ValidFromDate then 1 else 0 end +
case when [DATE] = ValidToDate then -1 else 0 end)
over (order by [date] rows between unbounded preceding and current row)
from cte [c]
left join @Membership [m]
on [c].[date] = [m].ValidFromDate or [c].[date] = [m].ValidToDate
option (maxrecursion 0)

Aquí hay otra solución:

--data generation
declare @Membership table (MembershipId varchar(10), ValidFromDate date, ValidToDate date)
insert into @Membership values
(''0001'', ''1997-01-01'', ''2006-05-09''),
(''0002'', ''1997-01-01'', ''2017-05-12''),
(''0003'', ''2005-06-02'', ''2009-02-07'')
;with cte as (
select CAST(''2016-01-01'' as date) [date]
union all
select DATEADD(day, 1, [date]) from cte
where [date] < ''2016-12-31''
)
select [date],
(select COUNT(*) from @Membership where ValidFromDate < [date]) -
(select COUNT(*) from @Membership where ValidToDate < [date]) [ongoing]
from cte
option (maxrecursion 0)

Answer 2

En primer lugar, su consulta arroja ''1'' como MembershipCount incluso si no existe una membresía activa para la fecha dada.

Debería devolver SUM(CASE WHEN m.MembershipID IS NOT NULL THEN 1 ELSE 0 END) AS MembershipCount .

Para obtener un rendimiento óptimo, cree un índice en Memberships(ValidFromDateKey, ValidToDateKey, MembershipId) y otro en DIM.[Date](CalendarYear, DateKey) .

Una vez hecho esto, la consulta óptima será:

DECLARE @CalendarYear INT = 2000
SELECT dim.DateKey, SUM(CASE WHEN con.MembershipID IS NOT NULL THEN 1 ELSE 0 END) AS MembershipCount
FROM
DIM.[Date] dim
LEFT OUTER JOIN (
SELECT ValidFromDateKey, ValidToDateKey, MembershipID
FROM Memberships
WHERE
ValidFromDateKey <= CONVERT(DATETIME, CONVERT(VARCHAR, @CalendarYear) + ''1231'')
AND ValidToDateKey >= CONVERT(DATETIME, CONVERT(VARCHAR, @CalendarYear) + ''0101'')
) con
ON dim.DateKey BETWEEN con.ValidFromDateKey AND con.ValidToDateKey
WHERE dim.CalendarYear = @CalendarYear
GROUP BY dim.DateKey
ORDER BY dim.DateKey

Ahora, para tu última pregunta, ¿cuál sería la consulta equivalente a equijoin ?

¡ NO HAY MANERA que puedas reescribir esto como un no-equijoin!

Equijoin no implica usar join sintaxis. Equijoin implica usar un predicado equals , cualquiera que sea la sintaxis.

Su consulta produce una comparación de rangos, por lo tanto, equals no se aplica: se requiere un valor between o similar.

Answer 3

Preste atención, creo que @PittsburghDBA tiene razón cuando dice que la consulta actual devuelve un resultado incorrecto.
El último día de membresía no se cuenta, por lo que la suma final es menor de lo que debería ser.
Lo he corregido en esta versión.

Esto debería mejorar un poco tu progreso real:

declare @s date = ''20160101'';
declare @e date = getdate();
with
x as (
select d, sum(c) c
from (
select ValidFromDateKey d, count(MembershipID) c
from Memberships
group by ValidFromDateKey
union all
-- dateadd needed to count last day of membership too!!
select dateadd(dd, 1, ValidToDateKey) d, -count(MembershipID) c
from Memberships
group by ValidToDateKey
)x
group by d
),
c as
(
select d, sum(x.c) over (order by d) as c
from x
)
select d.day, c cnt
from calendar d
left join c on d.day = c.d
where d.day between @s and @e
order by d.day;

Answer 4

Si la mayoría de los intervalos de validez de su membresía son más largos que unos pocos días, eche un vistazo a la respuesta de Martin Smith. Es probable que ese enfoque sea más rápido.

Cuando toma la tabla de calendario ( DIM.[Date] ) y la deja unida a las Memberships , puede terminar escaneando la tabla de Memberships para cada fecha del rango. Incluso si hay un índice en (ValidFromDate, ValidToDate) , puede que no sea muy útil.

Es fácil darle la vuelta. Escanee la tabla de Memberships solo una vez y para cada membresía encuentre las fechas que son válidas usando CROSS APPLY .

Data de muestra

DECLARE @T TABLE (MembershipId int, ValidFromDate date, ValidToDate date);
INSERT INTO @T VALUES
(1, ''1997-01-01'', ''2006-05-09''),
(2, ''1997-01-01'', ''2017-05-12''),
(3, ''2005-06-02'', ''2009-02-07'');
DECLARE @RangeFrom date = ''2006-01-01'';
DECLARE @RangeTo date = ''2006-12-31'';

Consulta 1

SELECT
CA.dt
,COUNT(*) AS MembershipCount
FROM
@T AS Memberships
CROSS APPLY
(
SELECT dbo.Calendar.dt
FROM dbo.Calendar
WHERE
dbo.Calendar.dt >= Memberships.ValidFromDate
AND dbo.Calendar.dt <= Memberships.ValidToDate
AND dbo.Calendar.dt >= @RangeFrom
AND dbo.Calendar.dt <= @RangeTo
) AS CA
GROUP BY
CA.dt
ORDER BY
CA.dt
OPTION(RECOMPILE);

OPTION(RECOMPILE) no es realmente necesaria, la OPTION(RECOMPILE) en todas las consultas cuando comparo los planes de ejecución para asegurarme de que recibo el último plan cuando juego con las consultas.

Cuando miré el plan de esta consulta, vi que la búsqueda en la tabla Calendar.dt estaba usando solo ValidFromDate y ValidToDate , el @RangeFrom y @RangeTo fueron empujados al predicado del residuo. No es ideal. El optimizador no es lo suficientemente inteligente como para calcular el máximo de dos fechas ( ValidFromDate y @RangeFrom ) y utilizar esa fecha como punto de partida de la búsqueda.

Es fácil ayudar al optimizador:

Consulta 2

SELECT
CA.dt
,COUNT(*) AS MembershipCount
FROM
@T AS Memberships
CROSS APPLY
(
SELECT dbo.Calendar.dt
FROM dbo.Calendar
WHERE
dbo.Calendar.dt >=
CASE WHEN Memberships.ValidFromDate > @RangeFrom
THEN Memberships.ValidFromDate
ELSE @RangeFrom END
AND dbo.Calendar.dt <=
CASE WHEN Memberships.ValidToDate < @RangeTo
THEN Memberships.ValidToDate
ELSE @RangeTo END
) AS CA
GROUP BY
CA.dt
ORDER BY
CA.dt
OPTION(RECOMPILE)
;

En esta consulta, la búsqueda es óptima y no lee las fechas que se pueden descartar más adelante.

Finalmente, es posible que no necesite escanear toda la tabla de Memberships . Solo necesitamos aquellas filas donde el rango de fechas dado se interseca con el rango válido de la membresía.

Consulta 3

SELECT
CA.dt
,COUNT(*) AS MembershipCount
FROM
@T AS Memberships
CROSS APPLY
(
SELECT dbo.Calendar.dt
FROM dbo.Calendar
WHERE
dbo.Calendar.dt >=
CASE WHEN Memberships.ValidFromDate > @RangeFrom
THEN Memberships.ValidFromDate
ELSE @RangeFrom END
AND dbo.Calendar.dt <=
CASE WHEN Memberships.ValidToDate < @RangeTo
THEN Memberships.ValidToDate
ELSE @RangeTo END
) AS CA
WHERE
Memberships.ValidToDate >= @RangeFrom
AND Memberships.ValidFromDate <= @RangeTo
GROUP BY
CA.dt
ORDER BY
CA.dt
OPTION(RECOMPILE)
;

Dos intervalos [a1;a2] y [b1;b2] intersecan cuando

a2 >= b1 and a1 <= b2

Estas consultas asumen que la tabla de Calendar tiene un índice en dt .

Debe intentar y ver qué índices son mejores para la tabla Memberships . Para la última consulta, si la tabla es bastante grande, lo más probable es que dos índices separados en ValidFromDate y en ValidToDate sean mejores que un índice en (ValidFromDate, ValidToDate) .

Debe probar diferentes consultas y medir su rendimiento en el hardware real con datos reales. El rendimiento puede depender de la distribución de datos, cuántas membresías hay, cuáles son sus fechas válidas, qué tan ancho o estrecho es el rango dado, etc.

Recomiendo usar una gran herramienta llamada SQL Sentry Plan Explorer para analizar y comparar planes de ejecución. Es gratis. Muestra una gran cantidad de estadísticas útiles, como el tiempo de ejecución y el número de lecturas para cada consulta. Las capturas de pantalla de arriba son de esta herramienta.

Answer 5

Suponiendo que su dimensión de fecha contenga todas las fechas contenidas en todos los períodos de membresía, puede usar algo como lo siguiente.

La unión es una unión equi, por lo que puede usar la combinación hash o la combinación de combinación no solo los bucles anidados (que ejecutarán el subárbol interno una vez por cada fila externa).

Suponiendo que el índice en (ValidToDate) include(ValidFromDate) o revierta, se puede usar una búsqueda simple en Memberships y un solo escaneo de la dimensión de la fecha. A continuación, tengo un tiempo transcurrido de menos de un segundo para que pueda devolver los resultados durante un año en una tabla con 3,2 millones de miembros y una membresía activa general de 1,4 millones ( script )

DECLARE @StartDate DATE = ''2016-01-01'',
@EndDate DATE = ''2016-12-31'';
WITH MD
AS (SELECT Date,
SUM(Adj) AS MemberDelta
FROM Memberships
CROSS APPLY (VALUES ( ValidFromDate, +1),
--Membership count decremented day after the ValidToDate
(DATEADD(DAY, 1, ValidToDate), -1) ) V(Date, Adj)
WHERE
--Members already expired before the time range of interest can be ignored
ValidToDate >= @StartDate
AND
--Members whose membership starts after the time range of interest can be ignored
ValidFromDate <= @EndDate
GROUP BY Date),
MC
AS (SELECT DD.DateKey,
SUM(MemberDelta) OVER (ORDER BY DD.DateKey ROWS UNBOUNDED PRECEDING) AS CountOfNonIgnoredMembers
FROM DIM_DATE DD
LEFT JOIN MD
ON MD.Date = DD.DateKey)
SELECT DateKey,
CountOfNonIgnoredMembers AS MembershipCount
FROM MC
WHERE DateKey BETWEEN @StartDate AND @EndDate
ORDER BY DateKey

Demo (usa el período extendido ya que el año calendario de 2016 no es muy interesante con los datos de ejemplo)

Answer 6

Un enfoque es usar primero un INNER JOIN para encontrar el conjunto de coincidencias y COUNT () para proyectar MemberCount GROUPed BY DateKey, luego UNION ALL con el mismo conjunto de fechas, con un 0 en esa proyección para el conteo de miembros para cada fecha . El último paso es SUMAR () el MemberCount de esta unión, y GROUP BY DateKey. Como se solicitó, esto evita UNIRSE IZQUIERDO y NO EXISTE Como señaló otro miembro, esto no es una combinación equitativa, porque necesitamos usar un rango, pero creo que hace lo que usted pretende.

Esto servirá hasta 1 año de datos con alrededor de 100k lecturas lógicas. En una computadora portátil ordinaria con un disco giratorio, del caché en frío, sirve 1 mes en menos de un segundo (con los conteos correctos).

Aquí hay un ejemplo que crea 3.3 millones de filas de duración aleatoria. La consulta en la parte inferior devuelve un mes de datos.

--Stay quiet for a moment
SET NOCOUNT ON
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
--Clean up if re-running
DROP TABLE IF EXISTS DIM_DATE
DROP TABLE IF EXISTS FACT_MEMBER
--Date dimension
CREATE TABLE DIM_DATE
(
DateKey DATE NOT NULL
)
--Membership fact
CREATE TABLE FACT_MEMBER
(
MembershipId INT NOT NULL
, ValidFromDateKey DATE NOT NULL
, ValidToDateKey DATE NOT NULL
)
--Populate Date dimension from 2001 through end of 2018
DECLARE @startDate DATE = ''2001-01-01''
DECLARE @endDate DATE = ''2018-12-31''
;WITH CTE_DATE AS
(
SELECT @startDate AS DateKey
UNION ALL
SELECT
DATEADD(DAY, 1, DateKey)
FROM
CTE_DATE AS D
WHERE
D.DateKey < @endDate
)
INSERT INTO
DIM_DATE
(
DateKey
)
SELECT
D.DateKey
FROM
CTE_DATE AS D
OPTION (MAXRECURSION 32767)
--Populate Membership fact with members having a random membership length from 1 to 36 months
;WITH CTE_DATE AS
(
SELECT @startDate AS DateKey
UNION ALL
SELECT
DATEADD(DAY, 1, DateKey)
FROM
CTE_DATE AS D
WHERE
D.DateKey < @endDate
)
,CTE_MEMBER AS
(
SELECT 1 AS MembershipId
UNION ALL
SELECT MembershipId + 1 FROM CTE_MEMBER WHERE MembershipId < 500
)
,
CTE_MEMBERSHIP
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY NEWID()) AS MembershipId
, D.DateKey AS ValidFromDateKey
FROM
CTE_DATE AS D
CROSS JOIN CTE_MEMBER AS M
)
INSERT INTO
FACT_MEMBER
(
MembershipId
, ValidFromDateKey
, ValidToDateKey
)
SELECT
M.MembershipId
, M.ValidFromDateKey
, DATEADD(MONTH, FLOOR(RAND(CHECKSUM(NEWID())) * (36-1)+1), M.ValidFromDateKey) AS ValidToDateKey
FROM
CTE_MEMBERSHIP AS M
OPTION (MAXRECURSION 32767)
--Add clustered Primary Key to Date dimension
ALTER TABLE DIM_DATE ADD CONSTRAINT PK_DATE PRIMARY KEY CLUSTERED
(
DateKey ASC
)
--Index
--(Optimize in your spare time)
DROP INDEX IF EXISTS SK_FACT_MEMBER ON FACT_MEMBER
CREATE CLUSTERED INDEX SK_FACT_MEMBER ON FACT_MEMBER
(
ValidFromDateKey ASC
, ValidToDateKey ASC
, MembershipId ASC
)
RETURN
--Start test
--Emit stats
SET STATISTICS IO ON
SET STATISTICS TIME ON
--Establish range of dates
DECLARE
@rangeStartDate DATE = ''2010-01-01''
, @rangeEndDate DATE = ''2010-01-31''
--UNION the count of members for a specific date range with the "zero" set for the same range, and SUM() the counts
;WITH CTE_MEMBER
AS
(
SELECT
D.DateKey
, COUNT(*) AS MembershipCount
FROM
DIM_DATE AS D
INNER JOIN FACT_MEMBER AS M ON
M.ValidFromDateKey <= @rangeEndDate
AND M.ValidToDateKey >= @rangeStartDate
AND D.DateKey BETWEEN M.ValidFromDateKey AND M.ValidToDateKey
WHERE
D.DateKey BETWEEN @rangeStartDate AND @rangeEndDate
GROUP BY
D.DateKey
UNION ALL
SELECT
D.DateKey
, 0 AS MembershipCount
FROM
DIM_DATE AS D
WHERE
D.DateKey BETWEEN @rangeStartDate AND @rangeEndDate
)
SELECT
M.DateKey
, SUM(M.MembershipCount) AS MembershipCount
FROM
CTE_MEMBER AS M
GROUP BY
M.DateKey
ORDER BY
M.DateKey ASC
OPTION (RECOMPILE, MAXDOP 1)