Блог

Контекст представляет собой таблицу транзакций с датой и учетной записью пользователя. Эта таблица содержит около миллиарда строк.

 dOperationValueDate  sUserAccount                                  
-------------------  ----------------------------------------------
2016-03-05           00000000001                                   
2016-03-06           00000000002                                   
2016-03-07           00000000003                                   
2016-03-08           00000000004                                   
2016-03-09           00000000005                                   
2016-04-05           00000000002                                   
2016-10-05           00000000001                                   
2016-10-06           00000000001                                   
2016-10-06           00000000005                                   
  

Я хотел бы найти данные в моей таблице с этими критериями :

• По крайней мере, одна транзакция до 6 месяцев назад (например, TOP 1 *)
• Нет транзакции в течение 6 месяцев

В моем примере результатами будут учетные записи 2, 3, 4.

Я начал с ЛЕВОГО ВНЕШНЕГО СОЕДИНЕНИЯ, чтобы удалить все идентификаторы пользователя с транзакцией с 6 месяцев. Но время обработки просто ужасно: уже 4 часа.

 SELECT b.sUserAccount FROM
(SELECT sUserAccount FROM T_Operations WITH (readuncommitted) WHERE dOperationValueDate < DATEADD(month, -6, DATEADD(month, DATEDIFF(month, 0, GETUTCDATE()), 0)) GROUP BY sUserAccount) b -- all operations before 6 months ago
LEFT JOIN
(SELECT sUserAccount FROM T_Operations WITH (readuncommitted) WHERE dOperationValueDate >= DATEADD(month, -6, DATEADD(month, DATEDIFF(month, 0, GETUTCDATE()), 0)) GROUP BY sUserAccount) c -- all operations since 6 months
ON b.sUserAccount = c.sUserAccount
WHERE c.sUserBankAccount IS NULL) d -- remove all customers who have operations before 6 months ago and since 6 months / keep only customers who have operations beofre 6 months ago only
  

Я думаю, что решение состоит в том, чтобы найти только одну операцию в запросе b, и sql останавливается, когда находит одну строку. Основная проблема заключается только в том, что у пользователя не было транзакции до 6 месяцев назад, но для остальных все будет хорошо.

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

Я читал о ПЕРЕКРЕСТНОМ ПРИМЕНЕНИИ, но я не уверен, как это работает.

Основной проблемой здесь является время обработки. Я должен выполнить «быстрый» запрос (менее 1 часа).

Я думаю, вы должны иметь возможность просто использовать NOT EXISTS here .

 SELECT  b.sUserAccount
FROM    T_Operations b WITH (READUNCOMMITTED)
WHERE   b.dOperationValueDate < DATEADD(month,-6,DATEADD(month,DATEDIFF(month,0,GETUTCDATE()),0))
        AND NOT EXISTS ( SELECT 1
                         FROM   T_Operations WITH (READUNCOMMITTED)
                         WHERE  sUserAccount = b.sUserAccount
                                AND dOperationValueDate >= DATEADD(month,-6,DATEADD(month,DATEDIFF(month,0,GETUTCDATE()),0)) )
GROUP BY b.sUserAccount -- all operations before 6 months ago
  

или, на самом деле, вы могли бы просто использовать GROUP BY с НАЛИЧИЕМ

 SELECT  sUserAccount
FROM    T_Operations WITH (READUNCOMMITTED)
GROUP BY sUserAccount
HAVING  MAX(dOperationValueDate) < DATEADD(month,-6,DATEADD(month,DATEDIFF(month,0,GETUTCDATE()),0))
  

в качестве примечания.. DATEADD(month,-6,DATEADD(month,DATEDIFF(month,0,GETUTCDATE()),0)) вернет 2016-04-01

если вам нужна текущая дата, минус шесть месяцев, вы можете использовать DATEADD(month,-6,CAST(GETUTCDATE() AS DATE)) или DATEADD(month,-6,DATEADD(day,DATEDIFF(day,0,GETUTCDATE()),0)

 datatime @dt = DATEADD(month, -6, DATEADD(month, DATEDIFF(month, 0, GETUTCDATE()), 0));
SELECT sUserAccount 
FROM T_Operations WITH (readuncommitted) 
WHERE dOperationValueDate < @dt
EXCEPT
SELECT sUserAccount 
FROM T_Operations WITH (readuncommitted) 
WHERE dOperationValueDate >= @dt;
  

Иметь индекс в dOperationValueDate