Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сумма записей которые идут по порядку  [new]
ITW
Member

Откуда:
Сообщений: 52
Здравствуйте мастера.
например: есть таблица в которой сохраняется дата заказа клиента

задача состоит в том, чтобы показать активность юзера в системе по неделям, тоесть
нужно вывести количество недель которые идут попорядку в которых он был "активным".
например если он делал заказы каждую неделю в течении года потом был перерив две недели потом он опять в течении двух недель делал заказы то результат должен быть
52,
2

пример:
DECLARE @result TABLE (UserID int, StoredDate DATETIME)
INSERT INTO @result ( UserID, StoredDate )
SELECT 12, '20110602'
UNION ALL
SELECT 12, '20110607'
UNION ALL
SELECT 12, '20110610'
UNION ALL
SELECT 12, '20110617'
UNION ALL
SELECT 12, '20110624'
UNION ALL
SELECT 12, '20110702'
UNION ALL
SELECT 12, '20110714'
UNION ALL
SELECT 12, '20110721'
UNION ALL
SELECT 12, '20110728'
UNION ALL
SELECT 12, '20110815'

результат
5
3
1

Заранее спасибо!
8 авг 11, 15:47    [11086576]     Ответить | Цитировать Сообщить модератору
 Re: Сумма записей которые идут по порядку  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
ITW, как заказывали:

SELECT COUNT(gr) FROM (
SELECT UserID, wk-ROW_NUMBER() OVER (ORDER BY UserID, wk) gr FROM (
SELECT DISTINCT UserID, DATEPART(wk,StoredDate) wk
FROM @result
) t
) t
GROUP BY gr
8 авг 11, 15:57    [11086683]     Ответить | Цитировать Сообщить модератору
 Re: Сумма записей которые идут по порядку  [new]
ITW
Member

Откуда:
Сообщений: 52
kDnZP,

Спасибо! запрос работает но не совсем правильно как хотелось бы!
если активность длилась с декабря 2010 по февраль 2011 то будет разрыв на две части так как номера недели идут (49,50,51,52,1,2,3,4,5) вместо 9 будет 4,5
8 авг 11, 16:14    [11086808]     Ответить | Цитировать Сообщить модератору
 Re: Сумма записей которые идут по порядку  [new]
aleks2
Guest
DECLARE @result TABLE (UserID int, StoredDate DATETIME)
INSERT INTO @result ( UserID, StoredDate )
SELECT 12, '20110602'
UNION ALL
SELECT 12, '20110607'
UNION ALL
SELECT 12, '20110610'
UNION ALL
SELECT 12, '20110617'
UNION ALL
SELECT 12, '20110624'
UNION ALL
SELECT 12, '20110702'
UNION ALL
SELECT 12, '20110714'
UNION ALL
SELECT 12, '20110721'
UNION ALL
SELECT 12, '20110728'
UNION ALL
SELECT 12, '20110815'

;with
--начала интервалов непрерывности
	  b as (select T.*, row_number() over(PARTITION by T.UserID ORDER BY T.StoredDate)n FROM @result T LEFT OUTER JOIN @result P on T.UserID=P.UserID AND P.StoredDate<T.StoredDate AND P.StoredDate>DATEADD(day, -8, T.StoredDate) WHERE P.UserID is null)
--концы интервалов непрерывности
	, e as (select T.*, row_number() over(PARTITION by T.UserID ORDER BY T.StoredDate)n  FROM @result T LEFT OUTER JOIN @result P on T.UserID=P.UserID AND P.StoredDate>T.StoredDate AND P.StoredDate<DATEADD(day, +8, T.StoredDate) WHERE P.UserID is null)
-- ну... выровнять даты на начало недель - это уж сами
select b.UserID, DATEDIFF(week, b.StoredDate, e.StoredDate)+1 weekcount FROM b inner join e ON b.UserID=e.UserID AND b.n=e.n
8 авг 11, 16:44    [11087055]     Ответить | Цитировать Сообщить модератору
 Re: Сумма записей которые идут по порядку  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
ITW
kDnZP,

Спасибо! запрос работает но не совсем правильно как хотелось бы!
если активность длилась с декабря 2010 по февраль 2011 то будет разрыв на две части так как номера недели идут (49,50,51,52,1,2,3,4,5) вместо 9 будет 4,5


SELECT COUNT(gr) FROM (
SELECT UserID, wk-ROW_NUMBER() OVER (ORDER BY UserID, wk) gr FROM (
SELECT DISTINCT UserID, DATEDIFF(wk,StoredDate,'99991231') wk
FROM @result
) t
) t
GROUP BY gr
8 авг 11, 16:57    [11087127]     Ответить | Цитировать Сообщить модератору
 Re: Сумма записей которые идут по порядку  [new]
ITW
Member

Откуда:
Сообщений: 52
aleks2,

большое спасибо! то что доктор прописал :)
8 авг 11, 16:58    [11087134]     Ответить | Цитировать Сообщить модератору
 Re: Сумма записей которые идут по порядку  [new]
ITW
Member

Откуда:
Сообщений: 52
kDnZP,

Ваууу! Запрос простой до безобразия ))) спасибо
8 авг 11, 17:04    [11087178]     Ответить | Цитировать Сообщить модератору
 Re: Сумма записей которые идут по порядку  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
ITW, тока неправильный)))))))). Вот так должно быть правильнее:
SELECT COUNT(gr)
FROM (
SELECT UserID
, wk-ROW_NUMBER() OVER (ORDER BY UserID, wk) gr
, ROW_NUMBER() OVER (ORDER BY UserID, wk DESC) rn
FROM (
SELECT DISTINCT UserID, DATEDIFF(wk,StoredDate,'99991231') wk
FROM @result
) t
) t
GROUP BY UserID, gr
ORDER BY MAX(rn)
8 авг 11, 17:08    [11087198]     Ответить | Цитировать Сообщить модератору
 Re: Сумма записей которые идут по порядку  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Тестировал на таком наборе данных (только переход недель зависит от настроек сервера, обратить внимание):
DECLARE @result TABLE (UserID int, StoredDate DATETIME)
INSERT INTO @result ( UserID, StoredDate )
SELECT 12, '20110602'
UNION ALL
SELECT 12, '20110607'
UNION ALL
SELECT 12, '20110610'
UNION ALL
SELECT 12, '20110617'
UNION ALL
SELECT 12, '20110624'
UNION ALL
SELECT 12, '20110702'
UNION ALL
SELECT 12, '20110714'
UNION ALL
SELECT 12, '20110721'
UNION ALL
SELECT 12, '20110728'
UNION ALL
SELECT 12, '20110815'
UNION ALL
SELECT 12, '20111228'
UNION ALL
SELECT 12, '20120101'
UNION ALL
SELECT 12, '20120108'
UNION ALL
SELECT 12, '20120112'
UNION ALL
SELECT 12, '20121001'
8 авг 11, 17:14    [11087247]     Ответить | Цитировать Сообщить модератору
 Re: Сумма записей которые идут по порядку  [new]
ITW
Member

Откуда:
Сообщений: 52
kDnZP,

по тех. заданию порядок активности был не столь важен но все равно спасибо!
8 авг 11, 17:17    [11087270]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить