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

Откуда: Planet Earth
Сообщений: 107
Добро всем пятничного 13го утра :)

друг подкинул задачку.
вот, решил. Не знаю оптимально ли, может что уже подзабыл :) как можно написать по другому?
/*
С помощью одного запроса вывести таблицу с полями: IP-адрес, браузер, ос, URL с которого

зашел первый раз, URL на который зашел последний раз, кол-во просмотренных уникальных URL-адресов,*/


П.С. а есть ли разница такое реализоввывать на MySQL?

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#loggedIpDetails')) DROP TABLE #loggedIpDetails

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#loggedIPs')) DROP TABLE #loggedIPs

CREATE TABLE #loggedIpDetails
    (
      [date+time] DATETIME ,
      IP numeric(15),
      Ref NVARCHAR(2000) ,
      page NVARCHAR(2000)
    )

INSERT INTO #loggedIpDetails
        ( [date+time] ,
          IP ,
          Ref ,
          page
        )
VALUES  ( '2012-01-13 08:49:16' , -- date+time - datetime
          1 , -- IP - numeric
          N'111' , -- Ref - nvarchar(2000)
          N'111'  -- page - nvarchar(2000)
        ),
		( '2012-01-13 08:49:17' , -- date+time - datetime
          1 , -- IP - numeric
          N'111 fff' , -- Ref - nvarchar(2000)
          N'111'  -- page - nvarchar(2000)
        ),
		( '2012-01-13 08:49:16' , -- date+time - datetime
          1 , -- IP - numeric
          N'111 aaa' , -- Ref - nvarchar(2000)
          N'1112'  -- page - nvarchar(2000)
        ),        
		( '2012-01-13 08:49:18' , -- date+time - datetime
          1 , -- IP - numeric
          N'1112' , -- Ref - nvarchar(2000)
          N'1113'  -- page - nvarchar(2000)
        ),
		( '2012-01-13 08:49:19' , -- date+time - datetime
          1 , -- IP - numeric
          N'1112' , -- Ref - nvarchar(2000)
          N'3333'  -- page - nvarchar(2000)
        ),
		( '2012-01-13 08:49:21' , -- date+time - datetime
          2 , -- IP - numeric
          N'222' , -- Ref - nvarchar(2000)
          N'222'  -- page - nvarchar(2000)
        ),
		( '2012-01-13 08:49:22' , -- date+time - datetime
          2 , -- IP - numeric
          N'222' , -- Ref - nvarchar(2000)
          N'333'  -- page - nvarchar(2000)
        ),
        ( '2012-01-13 08:49:20' , -- date+time - datetime
          3 , -- IP - numeric
          N'333' , -- Ref - nvarchar(2000)
          N'333'  -- page - nvarchar(2000)
        )
CREATE TABLE #loggedIPs
    (
      IP numeric(15) ,
      agent NVARCHAR(2000) ,
      OS NVARCHAR(2000)
    )
INSERT INTO #loggedIPs
        ( IP, agent, OS )
VALUES  ( 1, -- IP - numeric
          N'111a', -- agent - nvarchar(2000)
          N'111os'  -- OS - nvarchar(2000)
          ),
          ( 2, -- IP - numeric
          N'222a', -- agent - nvarchar(2000)
          N'os'  -- OS - nvarchar(2000)
          ),
          ( 3, -- IP - numeric
          N'333a', -- agent - nvarchar(2000)
          N'os'  -- OS - nvarchar(2000)
          )

SELECT  LD.IP ,
        LD.Agent ,
        LD.OS ,
        [In].Ref ,
        [trace].Page ,
        COUNT(L.Page) AS Clicks
FROM    #loggedIPs LD 
		INNER JOIN (SELECT DISTINCT IP,page from #loggedIpDetails) L ON LD.IP = L.IP -- для подсчёта только уникальных
        INNER JOIN ( SELECT IP ,
                            MIN([date+time]) AS FirstLink ,
                            MAX([date+Time]) AS LastTrace
                     FROM   #loggedIpDetails
                     GROUP BY IP
                   ) AS [Link] ON LD.IP = Link.IP  -- для связки к джойнам по минимальному посещению и максимальному
--далее джойн с селектами, чтобы отбросить совпадающие по времени записи.
--мне кажется или задача не полностью сформулирована?
        INNER JOIN (SELECT ip,[date+time],MIN(ref) AS ref from #loggedIpDetails GROUP BY ip,[date+time]) AS [In] ON LD.IP = [In].IP
                                    AND Link.FirstLink = [In].[date+time] 
        INNER JOIN (SELECT ip,[date+time],MIN(page) AS Page from #loggedIpDetails GROUP BY ip,[date+time]) AS [trace] ON LD.IP = [trace].IP
                                       AND Link.LastTrace = [trace].[date+time]
GROUP BY LD.IP, LD.Agent, LD.OS, [In].Ref, [trace].Page 



DROP TABLE #loggedIpDetails
DROP TABLE #loggedIPs
13 янв 12, 13:28    [11898876]     Ответить | Цитировать Сообщить модератору
 Re: Задачка  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Newber
Не знаю оптимально ли, может что уже подзабыл :) как можно написать по другому?

Если версия сервака >=2005, то воспользовавшись оконными функциями можно снизить кол-во сканов #loggedIpDetails, ну и от лишних группировок избавиться.
13 янв 12, 13:36    [11898968]     Ответить | Цитировать Сообщить модератору
 Re: Задачка  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Вариант:
select
  LD.IP,
  LD.Agent,
  LD.OS,
  First = (select top 1 Ref+'/'+Page from #loggedIpDetails LL where LL.IP = LD.IP order by LL.[date+time] asc),
  Last  = (select top 1 Ref+'/'+Page from #loggedIpDetails LL where LL.IP = LD.IP order by LL.[date+time] desc),
  Clics = COUNT(distinct L.Page)
from
  #loggedIPs LD left join #loggedIpDetails L on LD.IP = L.IP
group by
  LD.IP,
  LD.Agent,
  LD.OS
13 янв 12, 13:49    [11899135]     Ответить | Цитировать Сообщить модератору
 Re: Задачка  [new]
Newber
Member

Откуда: Planet Earth
Сообщений: 107
kDnZP
Newber
Не знаю оптимально ли, может что уже подзабыл :) как можно написать по другому?

Если версия сервака >=2005, то воспользовавшись оконными функциями можно снизить кол-во сканов #loggedIpDetails, ну и от лишних группировок избавиться.


пока что получилось только вот это :)
SELECT  L.IP ,
        L.Agent ,
        L.OS ,
	[In].Ref ,
        [trace].Page ,
        COUNT(DISTINCT LD.Page) AS Clicks
FROM    #loggedIPs L
		inner JOIN #loggedIpDetails LD ON LD.IP = L.IP -- для подсчёта только уникальных
        INNER JOIN (SELECT ip,ref, ROW_NUMBER() over(PARTITION BY ip ORDER BY [date+time] ASC,ref) AS RN from #loggedIpDetails) AS [In] ON L.IP = [In].IP
                                    AND [In].RN =1
        INNER JOIN (SELECT ip,page, ROW_NUMBER() over(PARTITION BY ip ORDER BY [date+time] desc,page) AS RN from #loggedIpDetails ) AS [trace] ON L.IP = [trace].IP
                                       AND [trace].RN =1
GROUP BY L.IP, L.Agent, L.OS, [In].Ref, [trace].Page 



А этот вариант хорош!
тоже думал сначала написать через селект топ 1 для выборки первой и последней, но почему-то подумал что иннер джойн будет шустрей работать.
Спасибо.
и этот вариант работает и на mysql
+ считает нулевые тоже
Prolog
Вариант:
select
  LD.IP,
  LD.Agent,
  LD.OS,
  First = (select top 1 Ref+'/'+Page from #loggedIpDetails LL where LL.IP = LD.IP order by LL.[date+time] asc),
  Last  = (select top 1 Ref+'/'+Page from #loggedIpDetails LL where LL.IP = LD.IP order by LL.[date+time] desc),
  Clics = COUNT(distinct L.Page)
from
  #loggedIPs LD left join #loggedIpDetails L on LD.IP = L.IP
group by
  LD.IP,
  LD.Agent,
  LD.OS
13 янв 12, 14:31    [11899602]     Ответить | Цитировать Сообщить модератору
 Re: Задачка  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Newber, где-то так, планы сами поглядите:
SELECT  L.IP,
        L.Agent,
        L.OS,
        t.Ref,
        t.Page,
        t.Clicks
FROM    ( SELECT    t.IP,
                    MAX(t.frn) Ref,
                    MAX(t.lrn) Page,
                    MAX(t.dr) Clicks
          FROM      ( SELECT    LD.IP,
                                CASE WHEN ROW_NUMBER() OVER ( PARTITION BY LD.IP ORDER BY LD.[date+time], LD.ref ) = 1 THEN LD.ref
                                END frn,
                                CASE WHEN ROW_NUMBER() OVER ( PARTITION BY LD.IP ORDER BY LD.[date+time] DESC, LD.ref DESC ) = 1 THEN LD.Page
                                END lrn,
                                DENSE_RANK() OVER ( PARTITION BY LD.IP ORDER BY LD.page ) dr
                      FROM      #loggedIpDetails LD
                    ) t
          GROUP BY  t.IP
        ) t
JOIN    #loggedIPs L
ON      t.IP = L.IP
13 янв 12, 15:18    [11900056]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить