en-USsv-SE

Active Forums

PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 29 Jan 2014 12:07 AM by  Sergey
SQLug.se Challenge 2013
 47 Replies
Sort:
You are not authorized to post a reply.
Page 2 of 3 << < 123 > >>
Author Messages
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
28 Dec 2013 09:57 AM
ToineR skrev:
När man skapar nya objekt i databasen behöver man då städa bort dom som del av skriptet eller får man lämna kvar dom?

Hej,

Man skall städa bort objekten, inklusive eventuella temp-tabeller.

MVH
Johan

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
28 Dec 2013 10:15 AM
Hej,

Ett par saker till:

Skriv inte USE i era tävlingsbidrag. Vi har en "sandbox"-databas med speciellt namn, vilket innebär att ni INTE får ha några beroenden av databasens namn i era tävlingsbidrag.

Vi har också fått frågor om söktermerna. För att förtydliga så kan de innehålla alfanumeriska tecken (inklusive våra svenska tecken och accenter) samt mellanslag. Collation är Finnish_Swedish_CI_AS. Inga extra mellanslag finns i början eller på slutet.

Vi vill också förtydliga att vi har en time-out när vi provkör bidrag. Det innebär att riktigt långsamma cursor-lösningar och liknande kan bli underkända även om de skulle ha lämnat rätt svar. Tidsgränsen för time-outen bestäms av juryn.

Obs! Att ett bidrag står på resultatlistan är ingen garanti för att det kommer att klara våra finaltester. Resultatlistan är bara preliminär. Bidrag kan falla ifrån och placeringar kan ändras.

Lycka till!

Johan
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
30 Dec 2013 10:46 AM
Hej!

Den preliminära resultatlistan är nu uppdaterad. Detta är den sista uppdateringen innan finaltesterna som kommer att köras i januari.

Lycka till och gott nytt år!

/Johan
Christian J
New Member
New Member
Posts: 9


--
03 Jan 2014 03:18 PM
Tack igen för tävlingen, det är en kul och lärorik uppgift.
Begränsningen med 3 bidrag gör det riktigt tufft och jag välkomnar den.

Nu är det bara vänta på resultatet, ännu mer spännande än att väntas på juldagen :)
Jonas
New Member
New Member
Posts: 13


--
05 Jan 2014 08:33 PM
Hej,

En jättekul tävling. Ska bli spännande att se hur den bästa lösningen ser ut.
De stora problemen tyckte jag var att jämföra strängarna samt grupperingen på "löpande 30 minuters intervall"

Mitt första försök var med en Cursor, vilket löste problemet, men förmodligen inte på bästa sätt.
Därefter fick jag till en variant med temp-table och LAG, men den kom tyvärr inte med på listan. Ser nån vad jag gör fel? Enligt mina tester är resultatet rätt. Nån som vill avslöja sin lösning?


truncate table [dbo].[SearchGroups]

---Funktion för att sortera en sträng med kommaseparerade ord
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create FUNCTION [dbo].[SortString]
(@SearchWords varchar(200))

RETURNS varchar(200)
AS
BEGIN
-- Declare the return variable here
DECLARE @finalString varchar(200)
DECLARE @currentWord varchar(50)
---temptabell för sortering av [SearchWords]
DECLARE @TempWords TABLE
(
Words varchar(200)
)

set @SearchWords = case when right(@SearchWords,1)=',' then @SearchWords else @SearchWords+',' end


-- Add the T-SQL statements to compute the return value here
WHILE( PATINDEX('%,%',@SearchWords)<>0)
BEGIN

SET @currentWord=CAST(SUBSTRING(@SearchWords,0,PATINDEX('%,%',@SearchWords)) AS varchar(50));

INSERT INTO @TempWords (Words) VALUES (@currentWord);

SET @SearchWords=SUBSTRING(@SearchWords,PATINDEX('%,%',@SearchWords)+1,(LEN(@SearchWords)+1)-PATINDEX('%,%',@SearchWords));


END

--uppdaterar [Searches] med sorterad [SearchWords]
SET @finalString='';
SELECT @finalString=@finalString +cast(Words as varchar(200))+',' FROM @TempWords
ORDER BY Words ASC

-- Return the result of the function
RETURN substring(@finalString,1,len(@finalString)-1)

END --End funktion
GO

--Temp tabell för vidare hantering
Select * into #temp from (
Select EventTime,
[dbo].[SortString]([SearchWords]) as SearchWords
---Skapar unik grupp för första sökningen i varje grupp av sökningar (en grupp = en eller flera sökningar på samma ord kombinationer inom 30 min)
,case when ([EventTime]<=Dateadd(n,30,LAG([EventTime]) over (order by [dbo].[SortString]([SearchWords]) asc, [EventTime] asc))
and [dbo].[SortString]([SearchWords])=LAG([dbo].[SortString]([SearchWords])) over (order by [dbo].[SortString]([SearchWords]) asc, [EventTime] asc))
then SearchWords
else convert(varchar(20),[EventTime],120)+SearchWords
end as Grupp
From [dbo].[Searches]
) as T

--Insert av rader till resultattabellen [dbo].[SearchGroups]
INSERT INTO [dbo].[SearchGroups]
([StartTime]
,[EndTime]
,[NumberOfSearches]
,[SearchWords])
select
min([EventTime]) as StarTime
,max([EventTime]) as EndTime
,count(*) as [NumberOfSearches]
,substring(Grupp,20,len(Grupp)) as [SearchWords]
FROM
(
---Sökning som startar en ny grupp av sökningar
select [EventTime]
,Grupp
from #temp
where ISDATE(substring(Grupp,1,19))=1
union all
--Sökningar som är del av en grupp sökningar (men ej första sökningen)
select A.Eventtime
,(select top 1 S.Grupp from #temp as S
where
A.SearchWords=s.SearchWords
and A.EventTime>s.Eventtime
and ISDATE(substring(S.Grupp,1,19))=1
order by s.EventTime desc) as Grupp
from #temp as A
where ISDATE(substring(Grupp,1,19))=0
) As big
group by Grupp

---Se resultat
--select * from [dbo].[SearchGroups]

--Städar
drop table #temp
drop FUNCTION [dbo].[SortString]

Som sagt Tack för en kul tävling!

Joel Greijer
New Member
New Member
Posts: 4


--
05 Jan 2014 09:56 PM
Hej Alla

Kul att få vara med och tävla!

Jag var också inne på Lag eftersom det kändes rätt och rimligt, men blev besviken eftersom inte (iaf den versionen vi kör av) Azure stöder den... arrgggghh.

Jag var därför inne och nosade på cursor-spåret, men tyckte det gav för dåliga resultat eftersom det verkar som den gör en read för varje rad som hämtas, vilket känns overkill. Jag är mycket dålig på cursors så jag kanske har gjort en dålig variant av cursor, kan tilläggas.

För att komma runt problemet med 1 read per rad läste jag in allt i en temptabell med en pålagd identity(1,1) vilken jag kunde göra where between på för att få rimliga reads per batch.
Eftersom tävligen (ursprungligen) gick ut på lägst reads+writes så läste jag i while (500 rader åt gången- vilket i min miljö gav en sådär 5 reads per batch) i en xml-variabel, vilken jag processade i en ytterligare while för att få till score per sökning till en @cache xml, vilken håller alla sökgrupper senaste 30 minuter. Varje iteration flushar äldre entries till searchgroups.

Jag tunade och grejade som en galen häst och den blev förbannat slö men ack så billig read/write-mässigt, vad jag kunde se.

Sagt och gjort, min ful-xml-cache-lösning skickades in och morgonen efter fanns en något uppdaterad variant av "reglerna" där en godtycklig "time-out" ingick i bedömningen :-)

Så jag förstod att risken var stor att jag hade orsakat den och skrev om skiten i flera steg med temptabeller för min version 2. Där jag först började med att bygga upp en söktabell med en NextSearchId

Eftersom CTE-användningen i mina lösningar alltid gav enormt mycket reads, och för att komma undan problemtatiken med detta så gjorde jag först 9 stycken left joins på nextsearchid, vilket eliminerar behovet av cte på alla sökningar där summan NumberOfSearches>=9. Denna gräns 9 är lite godtycklig eftesrom jag inte visste finaldatats täthet, men jag chansade.

Ja, jag glömde nästan, för att jämföra sökningar använde jag mig av splitstring_moden från diskussioner här: http://www.sqlperformance.com/2012/...it-strings
i vilken jag sedan checksum'ade och aggade till en SentenceChecksum, enligt exempel

insert #searches (EventTime,SearchWords,SentenceChecksum)
select EventTime,SearchWords,checksum_agg(binary_checksum(lower(w.item))) as SentenceChecksum
from dbo.Searches s
cross apply dbo.SplitStrings_moden(s.searchwords,',') w
group by EventTime,SearchWords
order by EventTime,SearchWords


Jag använde någon dmv för jämförelse av mina olika versioner vilket kanske inte varit det besta, borde läst lite bättre på forumet/vetat mer om extended events :-)

Snart får vi veta resultatet,
Tack för en skojig uppgift!

//Joel

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
06 Jan 2014 04:56 PM
Hej!

Tack för alla tävlingsbidrag. Det har varit riktigt roligt att se så många tävlande och kreativa lösningar!

Ni kommer att få ge er till tåls ett tag till för resultaten. Vi kommer inte att släppa dem förrän till årsmötet den 27:e januari. Där meddelar vi resultaten under lite mer högtidliga former.

God fortsättning!

MVH
Johan
Christian J
New Member
New Member
Posts: 9


--
06 Jan 2014 10:04 PM
Jag satt också först på cursor, men sedan kom jo den risken att diskvalificeras och då hade jag ett försök kvar :)

Ja, det var en del bedömningar man fick göra och rätt så tuffa när man bara har tre skott. Jag sysslade en del med det här:

  1. Stöds lag() ? Jag byggde en Azure databas för att testa, men det var jo inte det som man kör på utan "en Windows Azure VM Small (1 kärna) med SQL Server 2012 SP1 Web Edition." dvs lag() stöds i den versionen.
  2. Kan man använda checksum() för att gruppera searchwords? Inte rakt av, se: http://connect.microsoft.com/SQL/fe...kID=513376
  3. Räcker smallint till grupperingen av unika searchwords? Jag vågade inte begränsa detta till 32k, men det gick att använda 64k om man tänker efter och det kanske är en rimlig gräns, men väldigt riskant. Jag körde safe med sista skottet som sagt.

I det stora hela är rätt få osäkerheter med tankte på komplexitet av en sådan uppgift. I "real-life" scenario finns mycket mer man måste definera innan man kan gå i produktion.
Joel Greijer
New Member
New Member
Posts: 4


--
07 Jan 2014 07:41 AM
Aj aj , Det var trist för mig att jag missuppfattat miljön. Får hoppas att inte vinnande lösning involverade lag().
Nu kan det ju vara så att mitt fall blir checksum() ändå :-)
/Joel



ToineR
New Member
New Member
Posts: 12


--
07 Jan 2014 01:48 PM
JAhlen wrote:
Ni kommer att få ge er till tåls ett tag till för resultaten. Vi kommer inte att släppa dem förrän till årsmötet den 27:e januari. Där meddelar vi resultaten under lite mer högtidliga former.

Spännande. Jag hoppas att ingen har skrivit bättre kod än mig men man vet ju aldrig :).

Christian wrote:
Jag satt också först på cursor, men sedan kom jo den risken att diskvalificeras och då hade jag ett försök kvar :)

Jag hade en cursor lösning som gick ganska snabbt, men problemet var att cursor definitionen gör en nested loop join istället för en merge eller hash join. Dom läsningar som cursor gjorde var inte med i outputen av statistics io, men var med i extended events. Så då la jag cursor spåret på is.

Själv har jag skickat i lösningar båda med och utan LAG() pga att LAG() tog mycket fler reads på en av mina test databaser än en lösning baserat på ROWNUMBER().

Henrik Linder
New Member
New Member
Posts: 8


--
08 Jan 2014 08:50 PM
Tack till arrangörerna för nedlagd möda! Mycket rolig tävling, som jag i slutändan lade lite mer tid än planerat på. :)

Det var knepigt att hålla nere I/O, men jag fick till en lösning där jag via en vy gör insert direkt i måltabell från källtabell. (Dock finns en subselect kvar som jag gärna skulle bli av med, men familjesituationen tillät inte ytterligare optimering)
Fick också till en funktion som sorterar söksträngarna utan någon I/O, vilket jag är nöjd över.

För den intresserade har jag lagt upp bidraget i sin helhet här: http://www.henriklinder.com/Bidrag3.sql

även jag labbade lite med cursors, men det gick inget vidare, så jag övergav det spåret efter ett tag.


jojx: jag gissar att den lösningen du skickade kommer att ge fel svar om ISDATE() råkar ge true på själv söksträngen (utan att jag har analyserat din kod i detalj...).
stefan gustafsson
New Member
New Member
Posts: 29


--
09 Jan 2014 09:29 AM
Mycket intressant tävling. Att uppgiften var att optimera antal logiska reads gav en extra twist.

Tyvärr kom jag på min bästa lösning dagen efter sista inlämningstid men jag tycker själv att den blev ganska snygg :-)

Jag gör exakt en läsning av datan i söktabellen och klarar mig sedan med bara två sorteringar. Om man har stora mängder data (så att man inte kan sortera allt i minnet) så tror jag att denna lösning är hyfsat optimal när det gäller antal reads.

Synd bara att jag inte fick med denna lösning i tävlingen...

/SG

-- reset
use [SQLUGChallenge2013]
if object_id('dbo.GetCanonicSearch4') is not null drop function dbo.GetCanonicSearch4
truncate table dbo.SearchGroups
go
-- Stefan Gustafsson, Acando
-- v5
--
-- Sortera sökorden inom varje sträng med hjälp av en funktion som inte ger några reads
--
-- Skapa ett intervall för varje sökning som är 30 minuter långt
-- Varje intervall har ett startevent och ett slutevent (30 minuter senare)
-- Startevent har EventType = +1
-- Slutevent har EventType = -1
-- Skapa en running sum av EventType, använd detta för att identifiera första och sista eventet inom varje session
--
-- Resultatet blir att vi läser dbo.Searches exakt en gång och vi gör bara två sorteringar
-- Sorteringar ger upphov till reads om datan inte får plats i minnet
create function [dbo].[GetCanonicSearch4](@sw varchar(200)) returns varchar(200)
as
begin
-- Insertion sort
-- for i = 1 to len(a)
-- t = a(i)
-- j = 1
-- while j <= len(b)
-- if t < b(j) break
-- j++
-- end while
-- insert t at j
-- next i
declare @b varchar(200) = ''
declare @a varchar(200) = reverse(@sw)
declare @i int = 1
while @i <= datalength(@a) begin
declare @i2 int = charindex(',', @a, @i)
if @i2 = 0
set @i2 = datalength(@a) + 1
declare @t varchar(200) = reverse(substring(@a, @i, @i2-@i))
declare @j int = 1
while @j <= datalength(@b) begin
declare @j2 int = charindex(',', @b, @j)
if @j2 = 0
set @j2 = datalength(@b) + 1
if @t < substring(@b, @j, @j2-@j)
break
set @j = @j2 + 1
end
-- Sätt in @t innan @j
if @j <= datalength(@b)
set @b = substring(@b, 1, @j-1) + @t + ',' + substring(@b, @j, 200)
else if datalength(@b) > 0
set @b = @b + ',' + @t
else
set @b = @t
set @i = @i2 + 1
end
return @b
end
GO
;with
s1 as (
-- Sortera orden i varje söksträng
select
EventTime,
SearchWords = dbo.GetCanonicSearch4(SearchWords)
from dbo.Searches
)
, cte1 as (
-- Använd cross join för att skapa två rader per sökning - en rad för StartTime och en
-- rad för när sessionen slutar (30 min efter EventTime)
select
EventTime = case when EventType = 1 then EventTime else dateadd(minute, 30, EventTime) end,
SearchWords,
EventType
from s1
cross join (values (1), (-1)) u(EventType)
)
, cte2 as (
-- Running sum och radnummer
select
*,
s = sum(EventType) over (partition by SearchWords order by EventTime, EventType desc rows between unbounded preceding and current row),
rn = row_number() over (partition by SearchWords order by EventTime, EventType desc)
from cte1
)
, cte3 as (
-- Filtrera fram första och sista eventet i varje session
select *
from cte2
where (EventType = 1 and s = 1) or (EventType = -1 and s = 0)
)
, cte4 as (
-- Hämta data från startevent till slutevent med hjälp av LAG
select
*,
prev_rn = LAG(rn, 1) over (partition by SearchWords order by EventTime, EventType desc),
prev_dt = LAG(EventTime, 1) over (partition by SearchWords order by EventTime, EventType desc)
from cte3
)
, cte5 as (
-- filtrera fram en rad per grupp och beräkna resultat
select
StartTime = prev_dt,
EndTime = dateadd(minute, -30, EventTime),
NumberOfSearches = (rn+1-prev_rn)/2,
SearchWords = SearchWords
from cte4
where EventType = -1 -- slutevent
)
insert into dbo.SearchGroups (StartTime, EndTime, NumberOfSearches, SearchWords)
select StartTime, EndTime, NumberOfSearches, SearchWords
from cte5
option (force order)
-- städa
drop function dbo.GetCanonicSearch4
go

ToineR
New Member
New Member
Posts: 12


--
09 Jan 2014 09:28 PM
Vad roligt att alla lösningar publicerat hittills är så olikt varandra. Hittills finns det 3 olika sätt för att få fram ett värde som man kan grupperar dom olika sökord på.

Själv har jag löst det på ett annat sätt vilket inte använder nån UDF:
SELECT S.SearchWords
, Ordered.SearchWords
FROM
(
SELECT DISTINCT Searchwords
FROM Searches
) AS S
CROSS APPLY
(
SELECT STUFF
(
(
SELECT SUBSTRING(TMP.SearchWords, Start, Length)
FROM (VALUES(',' + S.SearchWords)) AS TMP(SearchWords)
CROSS APPLY (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201)) AS A(Nr)
CROSS APPLY
(
SELECT A.Nr AS Start
, CASE CHARINDEX(',', TMP.SearchWords, A.Nr + 1)
WHEN 0 THEN LEN(TMP.SearchWords) - (A.Nr - 1)
ELSE CHARINDEX(',', TMP.SearchWords, A.Nr + 1) - A.Nr
END AS Length
) AS Interval
WHERE SUBSTRING(TMP.SearchWords, A.Nr, 1) = ','
ORDER BY SUBSTRING(TMP.SearchWords, Start, Length) ASC
FOR XML PATH('')
)
, 1
, 1
, ''
) AS SearchWords
) AS Ordered;

Tidigare använda jag mig av CTE metoden för att skapa Numbers tabellen, men den tog mer CPU så då blev det en inline variant istället.

Jag har valt att inte posta hela min lösning än, men det kommer jag att göra efter att tävlingsresultatet är ute så spänningen om vem som vinner är kvar till årsmötet.
Jonas
New Member
New Member
Posts: 13


--
28 Jan 2014 11:18 AM
Hej igen,

Jag ser att resultatet har presenterats på tävlingssidan. är det alla godkända lösningar som är med? Jag saknar mitt bidrag långt ner på listan.

Det hade även varit roligt att få se den vinande lösningen!

ha de gott
/Jonas
Henrik Linder
New Member
New Member
Posts: 8


--
28 Jan 2014 11:36 AM
Grattis Toine!
Nu är jag väldigt nyfiken på din lösning, och du skulle ju enligt tidigare utfästelse offentliggöra den när tävligen var avgjord. :)

Det skulle också vara intressant att ta del av det testdata som användes vid finaltesterna. Går det att ordna?
ToineR
New Member
New Member
Posts: 12


--
28 Jan 2014 02:48 PM
Tackar! :)

Jag kommer att lägga upp hela bidraget ikväll och sen funderar jag på att publisera sättet hur jag har skapat mitt testdata nån annan gång på ett blogg eller liknande. Det finns ganska mycket information om hur man optimerar för cpu och delvis i/o, men jag har hittat ganska lite information om hur man skapar bra testdata.
LarsOve
New Member
New Member
Posts: 6


--
28 Jan 2014 05:22 PM
Grattis till alla på topp 3 och tack för en rolig tävling!

Olyckligtvis blev mina bidrag underkända vid de slutgiltiga testerna. Hade varit intressant att se vad jag och även de andra som försvann från listan stupade på.

Detta var mitt sista bidrag.




SET NOCOUNT ON

-- We need a number table with 200 numbers, one for each possible
-- location of a ',' in the SearchWords field.
;WITH Numbers(N) AS
(
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40
UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44 UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50
UNION ALL SELECT 51 UNION ALL SELECT 52 UNION ALL SELECT 53 UNION ALL SELECT 54 UNION ALL SELECT 55 UNION ALL SELECT 56 UNION ALL SELECT 57 UNION ALL SELECT 58 UNION ALL SELECT 59 UNION ALL SELECT 60
UNION ALL SELECT 61 UNION ALL SELECT 62 UNION ALL SELECT 63 UNION ALL SELECT 64 UNION ALL SELECT 65 UNION ALL SELECT 66 UNION ALL SELECT 67 UNION ALL SELECT 68 UNION ALL SELECT 69 UNION ALL SELECT 70
UNION ALL SELECT 71 UNION ALL SELECT 72 UNION ALL SELECT 73 UNION ALL SELECT 74 UNION ALL SELECT 75 UNION ALL SELECT 76 UNION ALL SELECT 77 UNION ALL SELECT 78 UNION ALL SELECT 79 UNION ALL SELECT 80
UNION ALL SELECT 81 UNION ALL SELECT 82 UNION ALL SELECT 83 UNION ALL SELECT 84 UNION ALL SELECT 85 UNION ALL SELECT 86 UNION ALL SELECT 87 UNION ALL SELECT 88 UNION ALL SELECT 89 UNION ALL SELECT 90
UNION ALL SELECT 91 UNION ALL SELECT 92 UNION ALL SELECT 93 UNION ALL SELECT 94 UNION ALL SELECT 95 UNION ALL SELECT 96 UNION ALL SELECT 97 UNION ALL SELECT 98 UNION ALL SELECT 99 UNION ALL SELECT 100
UNION ALL SELECT 101 UNION ALL SELECT 102 UNION ALL SELECT 103 UNION ALL SELECT 104 UNION ALL SELECT 105 UNION ALL SELECT 106 UNION ALL SELECT 107 UNION ALL SELECT 108 UNION ALL SELECT 109 UNION ALL SELECT 110
UNION ALL SELECT 111 UNION ALL SELECT 112 UNION ALL SELECT 113 UNION ALL SELECT 114 UNION ALL SELECT 115 UNION ALL SELECT 116 UNION ALL SELECT 117 UNION ALL SELECT 118 UNION ALL SELECT 119 UNION ALL SELECT 120
UNION ALL SELECT 121 UNION ALL SELECT 122 UNION ALL SELECT 123 UNION ALL SELECT 124 UNION ALL SELECT 125 UNION ALL SELECT 126 UNION ALL SELECT 127 UNION ALL SELECT 128 UNION ALL SELECT 129 UNION ALL SELECT 130
UNION ALL SELECT 131 UNION ALL SELECT 132 UNION ALL SELECT 133 UNION ALL SELECT 134 UNION ALL SELECT 135 UNION ALL SELECT 136 UNION ALL SELECT 137 UNION ALL SELECT 138 UNION ALL SELECT 139 UNION ALL SELECT 140
UNION ALL SELECT 141 UNION ALL SELECT 142 UNION ALL SELECT 143 UNION ALL SELECT 144 UNION ALL SELECT 145 UNION ALL SELECT 146 UNION ALL SELECT 147 UNION ALL SELECT 148 UNION ALL SELECT 149 UNION ALL SELECT 150
UNION ALL SELECT 151 UNION ALL SELECT 152 UNION ALL SELECT 153 UNION ALL SELECT 154 UNION ALL SELECT 155 UNION ALL SELECT 156 UNION ALL SELECT 157 UNION ALL SELECT 158 UNION ALL SELECT 159 UNION ALL SELECT 160
UNION ALL SELECT 161 UNION ALL SELECT 162 UNION ALL SELECT 163 UNION ALL SELECT 164 UNION ALL SELECT 165 UNION ALL SELECT 166 UNION ALL SELECT 167 UNION ALL SELECT 168 UNION ALL SELECT 169 UNION ALL SELECT 170
UNION ALL SELECT 171 UNION ALL SELECT 172 UNION ALL SELECT 173 UNION ALL SELECT 174 UNION ALL SELECT 175 UNION ALL SELECT 176 UNION ALL SELECT 177 UNION ALL SELECT 178 UNION ALL SELECT 179 UNION ALL SELECT 180
UNION ALL SELECT 181 UNION ALL SELECT 182 UNION ALL SELECT 183 UNION ALL SELECT 184 UNION ALL SELECT 185 UNION ALL SELECT 186 UNION ALL SELECT 187 UNION ALL SELECT 188 UNION ALL SELECT 189 UNION ALL SELECT 190
UNION ALL SELECT 191 UNION ALL SELECT 192 UNION ALL SELECT 193 UNION ALL SELECT 194 UNION ALL SELECT 195 UNION ALL SELECT 196 UNION ALL SELECT 197 UNION ALL SELECT 198 UNION ALL SELECT 199 UNION ALL SELECT 200
)

-- To be able to compare the searchwords we will first sort them
-- internally.
,SortedSearches AS
(
SELECT
EventTime

-- Sub-query that splits, sorts and joins the words in the
-- SearchWords column.
,CAST((
SELECT STUFF((SELECT
',' + Word AS W
FROM
(
SELECT
SUBSTRING(Searches.SearchWords, s.StartIndex, ISNULL(NULLIF(CHARINDEX(',',Searches.SearchWords,s.StartIndex),0)-s.StartIndex, 200 )) AS Word
FROM
(
SELECT 1 AS StartIndex

UNION ALL

SELECT
t.N+1 AS StartIndex
FROM
(
SELECT TOP (DATALENGTH(Searches.SearchWords))
N
FROM
Numbers
) AS t
WHERE
SUBSTRING(Searches.SearchWords,t.N,1) = ','
) AS s
) AS Words
WHERE
NOT Searches.EventTime IS NULL -- Makes the sub-query non-foldable.
ORDER BY
Word

-- VARCHAR(201) is needed to fit the extra leading ',' before this is removed.
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(201)'),1,1,'') AS SortedSearchWords

) AS VARCHAR(200)) AS SortedSearchWords

FROM
dbo.Searches
)

-- Get the time for the previous and next occurence of each searchword.
-- Use these times to check if each row is the start or end of a
-- searchgroup, ie that there are not more than 30 minutes since the
-- previous and to the next.
,SearchGroupStartsAndEndFlags AS
(
SELECT
EventTime
,SortedSearchWords
,ROW_NUMBER() OVER (PARTITION BY SortedSearchWords ORDER BY EventTime) AS RowNumber
,CAST(CASE WHEN DATEDIFF(n, LAG(EventTime) OVER (PARTITION BY SortedSearchWords ORDER BY EventTime), EventTime) <= 30 THEN 0 END AS BIT) AS NullIfSearchGroupStart
,CAST(CASE WHEN DATEDIFF(n, EventTime, LEAD(EventTime) OVER (PARTITION BY SortedSearchWords ORDER BY EventTime)) <= 30 THEN 0 END AS BIT) AS NullIfSearchGroupEnd
FROM
SortedSearches
)

-- Get the searchgroup start and end time, for the start and end rows.
-- Use the difference of the start and end number to get the number of
-- searches.
,SearchGroupsStartAndEndRows AS
(
SELECT
EventTime AS StartTime
,CASE WHEN NullIfSearchGroupEnd IS NULL THEN EventTime ELSE LEAD(EventTime) OVER (PARTITION BY SortedSearchWords ORDER BY EventTime) END AS EndTime
,CASE WHEN NullIfSearchGroupEnd IS NULL THEN 1 ELSE (LEAD(RowNumber) OVER (PARTITION BY SortedSearchWords ORDER BY EventTime) - RowNumber) + 1 END AS NumberOfSearches
,SortedSearchWords
,NullIfSearchGroupStart
FROM
SearchGroupStartsAndEndFlags
WHERE
NullIfSearchGroupStart IS NULL OR NullIfSearchGroupEnd IS NULL
)

-- Insert the searchgroups, use the start row to get one row for each group.
INSERT INTO SearchGroups (StartTime, EndTime, NumberOfSearches, SearchWords)
SELECT
StartTime
,EndTime
,NumberOfSearches
,SortedSearchWords
FROM
SearchGroupsStartAndEndRows
WHERE
NullIfSearchGroupStart IS NULL


SET NOCOUNT OFF
stefan gustafsson
New Member
New Member
Posts: 29


--
28 Jan 2014 05:45 PM
Här är mitt script för att generera testdata:

use SQLUGChallenge2013
set nocount on
truncate table dbo.searches
-- Parametrar
declare @pRows int = 10000 -- Antal rader att generera
declare @pStartTime datetime = '20130101' -- Tid för första event
declare @pMaxWords int = 10 -- Maximalt antal ord i varje söksträng
declare @pTotalWords int = 12 -- Antal ord att välja bland
declare @pMaxDiff int = 10 -- Maximalt antal sekunder mellan events
select top (@pTotalWords) name
into #words
from (
select distinct name from sys.all_columns
) t1
order by name
begin tran
-- Variabler
declare @dummy float = rand(1) -- för repeterbarhet
declare @i int = @pRows
declare @et datetime = @pStartTime
declare @sw varchar(200)
while @i > 0 begin
set @sw = stuff(cast((
select top (cast(@pMaxWords*rand() as int)+1) ','+name from #words order by newid()
for xml path('')
) as varchar(200)),1,1,'')
begin try
insert into dbo.searches (EventTime, SearchWords) values (@et, @sw)
end try
begin catch
set @i = @i + 1
end catch
set @et = dateadd(second, cast(rand()*@pMaxDiff as int), @et)
set @i = @i - 1
end
commit
drop table #words
select count(*) from dbo.searches

Enligt mina (godkända) lösningar skall svaret med data genererad av ovanstående script innehålla 7514 rader.

larsove: Din lösning ger 7491 rader.

Exakt vad som går fel vet jag inte.

/SG

stefan gustafsson
New Member
New Member
Posts: 29


--
28 Jan 2014 06:14 PM
-- Get the time for the previous and next occurence of each searchword.
-- Use these times to check if each row is the start or end of a
-- searchgroup, ie that there are not more than 30 minutes since the
-- previous and to the next.
,SearchGroupStartsAndEndFlags AS
(
SELECT
EventTime
,SortedSearchWords
,ROW_NUMBER() OVER (PARTITION BY SortedSearchWords ORDER BY EventTime) AS RowNumber
,CAST(CASE WHEN DATEDIFF(n, LAG(EventTime) OVER (PARTITION BY SortedSearchWords ORDER BY EventTime), EventTime) <= 30 THEN 0 END AS BIT) AS NullIfSearchGroupStart
,CAST(CASE WHEN DATEDIFF(n, EventTime, LEAD(EventTime) OVER (PARTITION BY SortedSearchWords ORDER BY EventTime)) <= 30 THEN 0 END AS BIT) AS NullIfSearchGroupEnd
FROM
SortedSearches
)

Felet är att du använder datediff(n, ...) som bara räknar hela minuter. EventTime har upplösningen sekunder.

Din lösning fungerarar bra om man ändrar till datediff(s, ...) <= 30*60

/SG

LarsOve
New Member
New Member
Posts: 6


--
28 Jan 2014 06:34 PM
Ajdå. Otur att jag inte blev underkänd i de tidigare testerna, då hade jag kollat noggrannare.

Tack för svaret Stefang. Gissar att antal resultatrader från ditt testskript kan skilja sig något beroende på vilka värden som det slumpat fram.
You are not authorized to post a reply.
Page 2 of 3 << < 123 > >>