Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
есть такая таблица:

CREATE TABLE [dbo].[tempRequest](
	[VisitorID] [varchar](40) NULL,
	[timestamp] [datetime] NULL,
	[VisitID] [varchar](50) NULL
) ON [PRIMARY]

данные:
insert into QA1.dbo.tempRequest (VisitorID, timestamp)
select '3213898349' VisitorID, '2007-04-18 01:04:00.000' timestamp union all
select '3213898349' VisitorID, '2007-04-18 01:06:00.000' timestamp union all
select '3213898349' VisitorID, '2007-04-18 01:37:00.000' timestamp union all
select '3218576799' VisitorID, '2007-04-18 01:05:00.000' timestamp union all
select '3218576799' VisitorID, '2007-04-18 01:38:00.000' timestamp union all
select '3218576799' VisitorID, '2007-04-18 01:39:00.000' timestamp union all
select '3218576799' VisitorID, '2007-04-18 01:43:00.000' timestamp union all
select '3218576799' VisitorID, '2007-04-18 01:48:00.000' timestamp union all
select '3218576799' VisitorID, '2007-04-18 02:50:00.000' timestamp union all
select '3218576799' VisitorID, '2007-04-18 02:59:00.000' timestamp 


необходимо генерировать уникальные VisitID из рассчета, что Визит заканчиваетс после того как Визитор неактивен в течении 30 минут
в итоге должен получится примерно такой результат:
VisitorID__________timestamp_______________VisitID
3213898349 2007-04-18 01:04:00.000 698A3298-DA0F-4EA0-90EF-D4CB4EBBA052
3213898349 2007-04-18 01:06:00.000 698A3298-DA0F-4EA0-90EF-D4CB4EBBA052
3213898349 2007-04-18 01:37:00.000 705E6F0A-4CCF-4D71-B104-D42EDA3A2374
3218576799 2007-04-18 01:05:00.000 888D6E49-0B39-48AD-99B7-C4D1FCD5F597
3218576799 2007-04-18 01:38:00.000 338EE510-A294-4F0E-BA38-58900A7F6177
3218576799 2007-04-18 01:39:00.000 338EE510-A294-4F0E-BA38-58900A7F6177
3218576799 2007-04-18 01:43:00.000 338EE510-A294-4F0E-BA38-58900A7F6177
3218576799 2007-04-18 01:48:00.000 338EE510-A294-4F0E-BA38-58900A7F6177
3218576799 2007-04-18 02:50:00.000 7DAEE89E-3D5B-457C-97FD-A3223789EB7A
3218576799 2007-04-18 02:59:00.000 7DAEE89E-3D5B-457C-97FD-A3223789EB7A
26 апр 07, 07:48    [4071648]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74900
автор
необходимо генерировать уникальные VisitID


Генерить в какой момент? В момент вставки?
26 апр 07, 08:29    [4071718]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
нет после вставки
26 апр 07, 08:37    [4071735]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74900
Snark
нет после вставки


Чем обусловлено такое требование? При вставке триггером было бы гараздо проще.
26 апр 07, 08:42    [4071745]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
nergal
Member

Откуда: Екатеринбург
Сообщений: 1015
т.е. работать уже с готовой таблицей?
аля курсоры
26 апр 07, 08:42    [4071747]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
автор

т.е. работать уже с готовой таблицей?
аля курсоры

нет курсоры не хочется, таблица большая- несколько миллионов записей
хочется решить без курсоров и циклов
26 апр 07, 08:46    [4071755]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
nergal
Member

Откуда: Екатеринбург
Сообщений: 1015
а есть какоенибудь дополниетельное условие, например визит только один раз в день?
26 апр 07, 08:50    [4071763]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
nergal

т.е. работать уже с готовой таблицей?
аля курсоры

да таблица будет готова и заполнена данными
нужно только генерировать VisitID
26 апр 07, 08:50    [4071768]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
nergal
а есть какоенибудь дополниетельное условие, например визит только один раз в день?


визитов может быть сколько угодно в день

длительность визита не ограничена, он может продолжаться хоть весь день (и дольше) при условии что визитор проявляет активность по крайней мере один раз в 30 минут

визиты от одного визитора не могут перекрываться
26 апр 07, 08:54    [4071779]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74900
Snark
nergal

т.е. работать уже с готовой таблицей?
аля курсоры

да таблица будет готова и заполнена данными
нужно только генерировать VisitID


Как вариант:

DECLARE
  @NewID uniqueidentifier,
  @VisitorID [varchar](40)

UPDATE
  T
SET
  @NewID = VisitID = CASE WHEN ISNULL(@VisitorID, '') <> T.VisitorID OR DATEDIFF(mi, ISNULL(T2.[timestamp1], '19000101'), T.[timestamp]) >= 30 THEN NEWID() ELSE @NewID END,
  @VisitorID = T.VisitorID 
FROM
  tempRequest T
  INNER JOIN
  (SELECT
    T1.*,
    (SELECT TOP 1 [timestamp] FROM tempRequest WHERE T1.VisitorID = VisitorID AND  T1.[timestamp] > [timestamp] ORDER BY [timestamp] DESC) timestamp1
  FROM
    tempRequest T1) T2 ON
  T.VisitorID = T2.VisitorID AND
  T.[timestamp] = T2.[timestamp]

VisitorID                                timestamp                                              VisitID                              
---------------------------------------- ------------------------------------------------------ ------------------------------------ 
3213898349                               2007-04-18 01:04:00.000                                DE3B2A61-4E56-4E8E-884A-8C487531BD1E
3213898349                               2007-04-18 01:06:00.000                                DE3B2A61-4E56-4E8E-884A-8C487531BD1E
3213898349                               2007-04-18 01:37:00.000                                7E57671A-8DA8-4834-86D2-D48307F53F3C
3218576799                               2007-04-18 01:05:00.000                                70311978-E678-4FF2-92EC-44AC56797448
3218576799                               2007-04-18 01:38:00.000                                DD88C4E3-D0DB-43D5-9069-11323DAB5EFA
3218576799                               2007-04-18 01:39:00.000                                DD88C4E3-D0DB-43D5-9069-11323DAB5EFA
3218576799                               2007-04-18 01:43:00.000                                DD88C4E3-D0DB-43D5-9069-11323DAB5EFA
3218576799                               2007-04-18 01:48:00.000                                DD88C4E3-D0DB-43D5-9069-11323DAB5EFA
3218576799                               2007-04-18 02:50:00.000                                8E0D2B80-C4D5-40EE-9C62-6DC6E627305E
3218576799                               2007-04-18 02:59:00.000                                8E0D2B80-C4D5-40EE-9C62-6DC6E627305E

(10 row(s) affected)


Но гарантированный порядок может дать только курсор.
26 апр 07, 09:10    [4071823]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
я начал решение этой задачи с рассчета интервала между timestamp:
отсортировал таблицу по VisitorID and timestamp и создал IDENTITY колонку ID
потом сделал self-join по ID с инкрементом 1 - таким образом я сравниваю предыдущий timestamp с текущим,

однако возникает проблема с VisitorID - нужно чтобы рассчет интервала велся для каждого визитора отдельно


select IDENTITY(int, 1, 1) ID, VisitorID, timestamp,  VisitID 
into QA1.dbo.Request
from QA1.dbo.tempRequest
order by VisitorID, timestamp


select a.*, 
isnull(DATEDIFF(mi,b.timestamp, a.timestamp), 0) interval
from QA1.dbo.Request a Left outer join QA1.dbo.Request b 
on a.ID = b.ID+1
order by a.VisitorID, a.timestamp

26 апр 07, 09:17    [4071848]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
nergal
Member

Откуда: Екатеринбург
Сообщений: 1015
DECLARE @d datetime
DECLARE @b varchar(50)
DECLARE @v varchar(40)
SET @d = GetDate() 
SET  @v = ''

SET @b = newid()


SELECT * into #t FROM tempRequest



UPDATE #t 
SET @b = VisitID = CASE WHEN ((datediff(mi,@d,[timestamp])>30)or(visitorID <> @v)) THEN newid() ELSE @b END
,@d = [timestamp]
,@v = visitorID

SELECT * FROM #t

DROP TABLE #t

26 апр 07, 09:30    [4071902]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
nergal
Member

Откуда: Екатеринбург
Сообщений: 1015
хм... не видел ответ pkarklin
26 апр 07, 09:34    [4071919]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
pkarklin

Но гарантированный порядок может дать только курсор.


не очень понял этот комментарий,
гарантированный порядок чего ?
26 апр 07, 09:42    [4071961]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74900
Snark
pkarklin

Но гарантированный порядок может дать только курсор.


не очень понял этот комментарий,
гарантированный порядок чего ?


Приведенный код расчитан, что UPDATE идти упорядочено по VisitorID, [timestamp]. Но сервер не гарантирует такой порядок UPDATEта.
26 апр 07, 09:44    [4071970]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7365
можно и не закладываться на порядок update-а. но вот не факт, что это быстрее курсора будет:
CREATE TABLE #tempRequest( 
        [VisitorID] [varchar](40) NULL, 
        [ts] [datetime] NULL, 
        [VisitID] [varchar](50) NULL 
        , id uniqueidentifier not null default (newid())
) ON [PRIMARY] 


--данные:


insert into #tempRequest (VisitorID, ts) 
select '3213898349' VisitorID, '2007-04-18 01:04:00.000' union all 
select '3213898349' VisitorID, '2007-04-18 01:06:00.000' union all 
select '3213898349' VisitorID, '2007-04-18 01:37:00.000' union all 
select '3218576799' VisitorID, '2007-04-18 01:05:00.000' union all 
select '3218576799' VisitorID, '2007-04-18 01:38:00.000' union all 
select '3218576799' VisitorID, '2007-04-18 01:39:00.000' union all 
select '3218576799' VisitorID, '2007-04-18 01:43:00.000' union all 
select '3218576799' VisitorID, '2007-04-18 01:48:00.000' union all 
select '3218576799' VisitorID, '2007-04-18 02:50:00.000' union all 
select '3218576799' VisitorID, '2007-04-18 02:59:00.000'

update #tempRequest
set VisitID = t.VisitID
from #tempRequest
  inner join 
  (select
     VisitorID
     , ts
     , (select top 1 id from #tempRequest t2 where t1.VisitorID = t2.VisitorID and t2.ts <= t1.ts and 
        datediff(mi, isnull((select max(ts) from #tempRequest t3 where t2.VisitorID = t3.VisitorID and t3.ts < t2.ts),
'19000101'), t2.ts) > 30
        order by ts desc) VisitID
   from #tempRequest t1
  ) t on
  #tempRequest.VisitorID = t.VisitorID and #tempRequest.ts = t.ts
  
select VisitorID, ts, VisitID from #tempRequest order by VisitorID, ts

Posted via ActualForum NNTP Server 1.4

26 апр 07, 09:46    [4071980]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
pkarklin
Snark
pkarklin

Но гарантированный порядок может дать только курсор.


не очень понял этот комментарий,
гарантированный порядок чего ?


Приведенный код расчитан, что UPDATE идти упорядочено по VisitorID, [timestamp]. Но сервер не гарантирует такой порядок UPDATEта.


а если отсортировать и сделать кластерный индекс на на VisitorID и timestamp ?
26 апр 07, 09:48    [4071988]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
nergal
Member

Откуда: Екатеринбург
Сообщений: 1015
2 pkarklin
хм... тогда вместо каждой перменной можно в upadate написать select, который выберет
нужный параметр ?

п.з. скорость думаю жестко упадет
26 апр 07, 09:50    [4071999]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
nergal
Member

Откуда: Екатеринбург
Сообщений: 1015
2 daw
лучше скрипт автора
select IDENTITY(int, 1, 1) ID, VisitorID, timestamp,  VisitID 
into QA1.dbo.Request
from QA1.dbo.tempRequest
order by VisitorID, timestamp


select a.*, 
isnull(DATEDIFF(mi,b.timestamp, a.timestamp), 0) interval
from QA1.dbo.Request a Left outer join QA1.dbo.Request b 
on a.ID = b.ID+1
order by a.VisitorID, a.timestamp

только тут условие нужно добавить
26 апр 07, 09:52    [4072011]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74900
nergal
2 pkarklin
хм... тогда вместо каждой перменной можно в upadate написать select, который выберет
нужный параметр ?

п.з. скорость думаю жестко упадет


Ну, в принципе, это вариант и привел daw. Но, его быстродействие, IMHO, будет не фонтан. Уж лучше курсор.
26 апр 07, 09:52    [4072016]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
nergal
Member

Откуда: Екатеринбург
Сообщений: 1015
pkarklin

Ну, в принципе, это вариант и привел daw. Но, его быстродействие, IMHO, будет не фонтан. Уж лучше курсор.

Увидел, за время пока пишу ответы, появляются новые )))
кстати в фак
тоже расчитывается, что апдейт будет идти в определенном порядке.
26 апр 07, 09:57    [4072035]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
pkarklin
nergal
2 pkarklin
хм... тогда вместо каждой перменной можно в upadate написать select, который выберет
нужный параметр ?

п.з. скорость думаю жестко упадет


Ну, в принципе, это вариант и привел daw. Но, его быстродействие, IMHO, будет не фонтан. Уж лучше курсор.


а если курсором то на каждую запись в таблице прийдется делать один update ?
вы считаете что это может оказаться быстрее всех приведеных решений ?
26 апр 07, 10:01    [4072057]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74900
Snark
а если курсором то на каждую запись в таблице прийдется делать один update ?
вы считаете что это может оказаться быстрее всех приведеных решений ?


Я предполагаю, ибо несколько корреляционных подзапросов прыти UPDATEту не добавять. естественно, что все это надо проверять.
26 апр 07, 10:04    [4072088]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
Snark
Member

Откуда: LA
Сообщений: 111
pkarklin
Snark
а если курсором то на каждую запись в таблице прийдется делать один update ?
вы считаете что это может оказаться быстрее всех приведеных решений ?


Я предполагаю, ибо несколько корреляционных подзапросов прыти UPDATEту не добавять. естественно, что все это надо проверять.


а как бы Вы написали с курсором ?
26 апр 07, 10:06    [4072105]     Ответить | Цитировать Сообщить модератору
 Re: как рассчитать визиты  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74900
Snark
а как бы Вы написали с курсором ?


Сами не хотите, для начала, попробовать? ;)
26 апр 07, 10:07    [4072113]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить