Ответ 1

В предположении, что ваше измерение даты содержит все даты, содержащиеся во всех периодах членства, вы можете использовать что-то вроде следующего.

Соединение - это equi-соединение, поэтому можно использовать хеш-соединение или объединить объединение не только вложенных циклов (которые будут выполнять внутреннее поддерево для каждой внешней строки).

Предполагая индекс на (ValidToDate) include(ValidFromDate) или обратном, можно использовать одиночный поиск против Memberships и одиночный просмотр измерения даты. Ниже приведенное время меньше, чем у меня, чтобы вернуть результаты за год против таблицы с 3,2 миллионами членов и общим активным членством в 1,4 миллиона (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

Демо (использование расширенного периода в качестве календарного года 2016 года не очень интересно с примерами данных)

Ответ 2

Если большинство интервалов действительности вашего членства больше, чем несколько дней, взгляните на ответ Мартина Смита. Вероятно, этот подход будет быстрее.

Когда вы берете таблицу календаря (DIM.[Date]) и левее присоединяете ее к Memberships, вы можете завершить сканирование таблицы Memberships для каждой даты диапазона. Даже если на (ValidFromDate, ValidToDate) есть индекс, это может быть не очень полезно.

Легко повернуть его. Сканируйте таблицу Memberships только один раз, и для каждого членства найдите те даты, которые действительны с помощью CROSS APPLY.

Примеры данных

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';

Запрос 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) на самом деле не требуется, я включаю его во все запросы, когда сравниваю планы выполнения, чтобы быть уверенным, что получаю последний план, когда я играю с запросами.

Когда я посмотрел на план этого запроса, я увидел, что поиск в таблице Calendar.dt использовался только ValidFromDate и ValidToDate, теги @RangeFrom и @RangeTo были перенесены в предикат остатка. Это не идеально. Оптимизатор недостаточно умен, чтобы вычислять максимум две даты (ValidFromDate и @RangeFrom) и использовать эту дату в качестве начальной точки поиска.

Легко помочь оптимизатору:

Запрос 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)
;

В этом запросе поиск оптимален и не читает даты, которые могут быть отброшены позже.

Наконец, вам может не понадобиться сканировать всю таблицу Memberships. Нам нужны только те строки, где данный диапазон дат пересекается с допустимым диапазоном членства.

Запрос 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)
;

Два интервала [a1;a2] и [b1;b2] пересекаются, когда

a2 >= b1 and a1 <= b2

Эти запросы предполагают, что таблица Calendar имеет индекс на dt.

Вы должны попробовать и посмотреть, какие индексы лучше для таблицы Memberships. Для последнего запроса, если таблица довольно большая, скорее всего два отдельных индекса на ValidFromDate и на ValidToDate будут лучше, чем один индекс на (ValidFromDate, ValidToDate).

Вы должны попробовать разные запросы и измерить их производительность на реальном оборудовании с реальными данными. Производительность может зависеть от распределения данных, количества членства в нем, каковы их действительные даты, насколько широким или узким данный диапазон и т.д.

Я рекомендую использовать отличный инструмент под названием SQL Sentry Plan Explorer для анализа и сравнения планов выполнения. Это бесплатно. Он показывает много полезной статистики, например, время выполнения и количество чтений для каждого запроса. Скриншоты, приведенные выше, из этого инструмента.

Ответ 3

Один из подходов состоит в том, чтобы сначала использовать INNER JOIN для поиска набора совпадений и COUNT() для проекта MemberCount GROUPed BY DateKey, затем UNION ALL с тем же набором дат, с 0 на этой проекции для подсчета членов для каждой даты. Последним шагом является SUM() MemberCount этого объединения и GROUP BY DateKey. В соответствии с запросом это позволяет избежать ЛЕВОЙ ВСТУПЛЕНИЯ и НЕ СУЩЕСТВУЕТ. Как отметил другой член, это не равноединение, потому что нам нужно использовать диапазон, но я думаю, что он делает то, что вы намерены.

Это будет обслуживать данные за 1 год с логическими считываниями около 100 килобайт. На обычном ноутбуке с вращающимся диском из холодного кеша он обслуживает 1 месяц в секунду (с правильными значениями).

Вот пример, который создает 3,3 миллиона строк случайной продолжительности. Запрос внизу возвращает данные за один месяц.

--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)

Ответ 4

Вот как я решил бы эту проблему с 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 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)

Здесь другое решение:

--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)

Ответ 5

Обратите внимание, я думаю, что @PittsburghDBA прав, когда говорит, что текущий запрос возвращает неправильный результат.
Последний день членства не учитывается, и поэтому окончательная сумма ниже, чем должна быть.
Я исправил его в этой версии.

Это должно немного улучшить ваш фактический прогресс:

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;

Ответ 6

Прежде всего, ваш запрос дает "1" как MembershipCount, даже если для данной даты не существует активного члена.

Вы должны вернуть SUM(CASE WHEN m.MembershipID IS NOT NULL THEN 1 ELSE 0 END) AS MembershipCount.

Для оптимальной производительности создайте индекс на Memberships(ValidFromDateKey, ValidToDateKey, MembershipId), а другой - на DIM.[Date](CalendarYear, DateKey).

При этом оптимальный запрос должен быть:

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

Теперь, для вашего последнего вопроса, каков будет эквивалентный запрос equijoin.

Существует NO WAY, вы можете переписать это как не равноценное!

Equijoin не подразумевает использование join sintax. Equijoin подразумевает использование предиката equals, независимо от sintax.

В результате запроса получается сравнение диапазона, поэтому equals не применяется: требуется between или подобное.