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 3 of 3 << < 123
Author Messages
stefan gustafsson
New Member
New Member
Posts: 29


--
28 Jan 2014 07:01 PM
larsove skrev:
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.

Det skulle man ju kunna tro, men så är det i varje fall inte tänkt att fungera.
Anropet till rand(1) sätter ett nytt seed för slumpgeneratorn vilket gör att man får samma sekvens med slumptal vid varje körning.

Prova själv - antal rader i resultatet skall bli samma varje gång.

/SG

rola
New Member
New Member
Posts: 1


--
28 Jan 2014 07:01 PM

Tack för en trevlig tävling!

Här kommer mitt bidrag som hamnade på andraplatsen. Jag kommer att dra lite kort om vad jag har gjort här, men också eventuellt att skriva lite mer på min blogg, www.robinlangell.com för en mera utförlig förklaring. Detta var mitt tredje bidrag, som jag ändå tror blev godkänt, tävlingsledarna får gärna rätta mig om jag har helt fel. Den stora skillnaden mellan version 4 och 5 var främst hashningsfunktionaliteten som gick från en case med 256 when-delar till att bara vara en rad. Lite mer förklaringar kommer efter koden:


/*
TRUNCATE TABLE dbo.SearchGroups
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
*/
/* -====================== Robin Langell, Skandia, v5 ===================- */
; WITH C0 AS (
SELECT
S1.EventTime,
S = split.S,
S1.SearchWords,
RadNrStartEventTime =
case
when DATEDIFF(SECOND, lag(EventTime) over (partition by split.S order by EventTime ), EventTime ) <= 30*60 THEN NULL
ELSE EventTime
END,
RadNrEndEventTime =
case
when DATEDIFF(SECOND, EventTime, lead(EventTime) over (partition by split.S order by EventTime ) ) <= 30*60 THEN NULL
ELSE EventTime
END
FROM dbo.Searches S1
CROSS APPLY (
SELECT S =
ISNULL([1],0)+ ISNULL([2],0)+ ISNULL([3],0)+ ISNULL([4],0)+ ISNULL([5],0)+ ISNULL([6],0)+ ISNULL([7],0)+ ISNULL([8],0)
+ ISNULL([9],0)+ ISNULL([10],0)+ ISNULL([11],0)+ ISNULL([12],0)+ ISNULL([13],0)+ ISNULL([14],0)+ ISNULL([15],0)+ ISNULL([16],0)
+ ISNULL([17],0)+ ISNULL([18],0)+ ISNULL([19],0)+ ISNULL([20],0)+ ISNULL([21],0)+ ISNULL([22],0)+ ISNULL([23],0)+ ISNULL([24],0)
+ ISNULL([25],0)+ ISNULL([26],0)+ ISNULL([27],0)+ ISNULL([28],0)+ ISNULL([29],0)+ ISNULL([30],0)+ ISNULL([31],0)+ ISNULL([32],0)
+ ISNULL([33],0)+ ISNULL([34],0)+ ISNULL([35],0)+ ISNULL([36],0)+ ISNULL([37],0)+ ISNULL([38],0)+ ISNULL([39],0)+ ISNULL([40],0)
+ ISNULL([41],0)+ ISNULL([42],0)+ ISNULL([43],0)+ ISNULL([44],0)+ ISNULL([45],0)+ ISNULL([46],0)+ ISNULL([47],0)+ ISNULL([48],0)
+ ISNULL([49],0)+ ISNULL([50],0)+ ISNULL([51],0)+ ISNULL([52],0)+ ISNULL([53],0)+ ISNULL([54],0)+ ISNULL([55],0)+ ISNULL([56],0)
+ ISNULL([57],0)+ ISNULL([58],0)+ ISNULL([59],0)+ ISNULL([60],0)+ ISNULL([61],0)+ ISNULL([62],0)+ ISNULL([63],0)+ ISNULL([64],0)
+ ISNULL([65],0)+ ISNULL([66],0)+ ISNULL([67],0)+ ISNULL([68],0)+ ISNULL([69],0)+ ISNULL([70],0)+ ISNULL([71],0)+ ISNULL([72],0)
+ ISNULL([73],0)+ ISNULL([74],0)+ ISNULL([75],0)+ ISNULL([76],0)+ ISNULL([77],0)+ ISNULL([78],0)+ ISNULL([79],0)+ ISNULL([80],0)
+ ISNULL([81],0)+ ISNULL([82],0)+ ISNULL([83],0)+ ISNULL([84],0)+ ISNULL([85],0)+ ISNULL([86],0)+ ISNULL([87],0)+ ISNULL([88],0)
+ ISNULL([89],0)+ ISNULL([90],0)+ ISNULL([91],0)+ ISNULL([92],0)+ ISNULL([93],0)+ ISNULL([94],0)+ ISNULL([95],0)+ ISNULL([96],0)
+ ISNULL([97],0)+ ISNULL([98],0)+ ISNULL([99],0)+ ISNULL([100],0)
+ ISNULL([101],0)+ ISNULL([102],0)+ ISNULL([103],0)+ ISNULL([104],0)+ ISNULL([105],0)+ ISNULL([106],0)+ ISNULL([107],0)+ ISNULL([108],0)
+ ISNULL([109],0)+ ISNULL([110],0)+ ISNULL([111],0)+ ISNULL([112],0)+ ISNULL([113],0)+ ISNULL([114],0)+ ISNULL([115],0)+ ISNULL([116],0)
+ ISNULL([117],0)+ ISNULL([118],0)+ ISNULL([119],0)+ ISNULL([120],0)+ ISNULL([121],0)+ ISNULL([122],0)+ ISNULL([123],0)+ ISNULL([124],0)
+ ISNULL([125],0)+ ISNULL([126],0)+ ISNULL([127],0)+ ISNULL([128],0)+ ISNULL([129],0)+ ISNULL([130],0)+ ISNULL([131],0)+ ISNULL([132],0)
+ ISNULL([133],0)+ ISNULL([134],0)+ ISNULL([135],0)+ ISNULL([136],0)+ ISNULL([137],0)+ ISNULL([138],0)+ ISNULL([139],0)+ ISNULL([140],0)
+ ISNULL([141],0)+ ISNULL([142],0)+ ISNULL([143],0)+ ISNULL([144],0)+ ISNULL([145],0)+ ISNULL([146],0)+ ISNULL([147],0)+ ISNULL([148],0)
+ ISNULL([149],0)+ ISNULL([150],0)+ ISNULL([151],0)+ ISNULL([152],0)+ ISNULL([153],0)+ ISNULL([154],0)+ ISNULL([155],0)+ ISNULL([156],0)
+ ISNULL([157],0)+ ISNULL([158],0)+ ISNULL([159],0)+ ISNULL([160],0)+ ISNULL([161],0)+ ISNULL([162],0)+ ISNULL([163],0)+ ISNULL([164],0)
+ ISNULL([165],0)+ ISNULL([166],0)+ ISNULL([167],0)+ ISNULL([168],0)+ ISNULL([169],0)+ ISNULL([170],0)+ ISNULL([171],0)+ ISNULL([172],0)
+ ISNULL([173],0)+ ISNULL([174],0)+ ISNULL([175],0)+ ISNULL([176],0)+ ISNULL([177],0)+ ISNULL([178],0)+ ISNULL([179],0)+ ISNULL([180],0)
+ ISNULL([181],0)+ ISNULL([182],0)+ ISNULL([183],0)+ ISNULL([184],0)+ ISNULL([185],0)+ ISNULL([186],0)+ ISNULL([187],0)+ ISNULL([188],0)
+ ISNULL([189],0)+ ISNULL([190],0)+ ISNULL([191],0)+ ISNULL([192],0)+ ISNULL([193],0)+ ISNULL([194],0)+ ISNULL([195],0)+ ISNULL([196],0)
+ ISNULL([197],0)+ ISNULL([198],0)+ ISNULL([199],0)+ ISNULL([200],0)
FROM
(
SELECT S, RadNr = DENSE_RANK() OVER (ORDER BY N1)
FROM
(
SELECT
N1 = t.N+1 - CHARINDEX(',', REVERSE( SUBSTRING(',' + S1.SearchWords, 1, t.N-1) ) ),
S = (P) / ( (N+1) - (t.N+1 - CHARINDEX(',', REVERSE( SUBSTRING(',' + S1.SearchWords, 1, t.N-1) ) ) )
)
FROM
(
SELECT TOP (ISNULL(DATALENGTH(',' + S1.SearchWords),0))
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
P = POWER( (1.0/CAST( (NULLIF( ASCII( LOWER( SUBSTRING(',' + S1.SearchWords, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 1) ) ) , 44) ) AS DECIMAL(30,24)))+1000.0, 1.01 )
FROM (
SELECT d.N FROM (SELECT N = 1 FROM (SELECT N FROM ( SELECT N FROM (SELECT N = 1 FROM (
SELECT N = 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) a, (
SELECT N = 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) b) a) c ) a ) d,
(SELECT N = 1 FROM (SELECT N FROM ( SELECT N FROM (SELECT N = 1 FROM (
SELECT N = 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) a, (
SELECT N = 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) b) a) c ) b ) e
) a
) t
WHERE
t.N+1 >= t.N+1 - CHARINDEX(',', REVERSE( SUBSTRING(',' + S1.SearchWords, 1, t.N-1) ) )
AND t.N <= ISNULL( NULLIF( CHARINDEX(',', SUBSTRING(',' + S1.SearchWords,t.N+1, 200)), 0) + t.N-1 , LEN( SUBSTRING(',' + S1.SearchWords,t.N+1, 200) ) + t.N )
AND t.P > 0
) P
) C
pivot
(
SUM(S)
for RadNr in (
[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]
)
) p
) split
),
C1 AS (
SELECT
EventTime,
S,
SearchWords,
FirstEventTime = MAX(RadNrStartEventTime) OVER(
PARTITION BY S
ORDER BY EventTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
RadNrEndEventTime
FROM C0
),
C2 AS (
SELECT
StartTime = FirstEventTime,
EndTime = RadNrEndEventTime,
NumberOfSearches = COUNT(FirstEventTime) OVER(
PARTITION BY S, FirstEventTime
ORDER BY FirstEventTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
SearchWords
FROM C1
)
INSERT INTO dbo.SearchGroups
(
[StartTime]
,[EndTime]
,[NumberOfSearches]
,[SearchWords]
)
SELECT
StartTime,
EndTime,
NumberOfSearches,
SearchWords
FROM C2
WHERE EndTime > '1900-01-01'


Jag tänkte mig att varje tecken och plats i sökordet hade betydelse och skapade därför ett unikt värde för varje tecken som jag sedan delade med tecknets plats i varje ord.

Jag använde mig av den inbyggda funktionen ASCII för att ta fram tecknets "grundvärde", men gjorde först en LOWER för att få alla stora och små bokstäver lika. Sedan så lägger jag till 1000 och höjer upp med 1.01. Detta ger ett värde på ca 1070 till 1074 med en massa decimaler.

P = POWER( (1.0/CAST( (NULLIF( ASCII( LOWER( SUBSTRING(',' + S1.SearchWords, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 1) ) ) , 44) ) AS DECIMAL(30,24)))+1000.0, 1.01 )

Varje tecken får då ett värde, och sedan, i nedanstående steg, delar jag alltså med tecknets plats i ordet (S i nedanstående snippet):

SELECT
N1 = t.N+1 - CHARINDEX(',', REVERSE( SUBSTRING(',' + S1.SearchWords, 1, t.N-1) ) ),
S = (P) / ( (N+1) - (t.N+1 - CHARINDEX(',', REVERSE( SUBSTRING(',' + S1.SearchWords, 1, t.N-1) ) ) )

Sen summerade jag upp det i riktig fullösning (PIVOT) som inte krävde lika mycket IO som en sum. Jag summerar då alla teckenvärden (200) och får då en summa som i sin tur blir en unik summa som är lika alla andra sökningar med samma ord oavsett ordföljd. Allt detta gjordes i en cross apply. Nästa del i lösningen byggde på att hitta förhållandet mellan sökorden i minuter. Fanns inget förhållande så visas tiden (tror jag, eller var det tvärtom?). Jag använder dessa i nästa cte.

; WITH C0 AS (
SELECT
S1.EventTime,
S = split.S,
S1.SearchWords,
RadNrStartEventTime =
case
when DATEDIFF(SECOND, lag(EventTime) over (partition by split.S order by EventTime ), EventTime ) <= 30*60 THEN NULL
ELSE EventTime
END,
RadNrEndEventTime =
case
when DATEDIFF(SECOND, EventTime, lead(EventTime) over (partition by split.S order by EventTime ) ) <= 30*60 THEN NULL
ELSE EventTime
END
FROM dbo.Searches S1
CROSS APPLY (

Sen tog jag max på ena firstEventTime för att gruppera ihop det, och sedan en count för att räkna ihop antal rader, där antalet i grupperingen för firstEventTime ökar tills man kommer till endTime. I sista steget sätter jag bara in data som har endTime, och då får man med både firstEventTime och antalet rader.

C1 AS (
SELECT
EventTime,
S,
SearchWords,
FirstEventTime = MAX(RadNrStartEventTime) OVER(
PARTITION BY S
ORDER BY EventTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
RadNrEndEventTime
FROM C0
),
C2 AS (
SELECT
StartTime = FirstEventTime,
EndTime = RadNrEndEventTime,
NumberOfSearches = COUNT(FirstEventTime) OVER(
PARTITION BY S, FirstEventTime
ORDER BY FirstEventTime
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
SearchWords
FROM C1
)
INSERT INTO dbo.SearchGroups
(
[StartTime]
,[EndTime]
,[NumberOfSearches]
,[SearchWords]
)
SELECT
StartTime,
EndTime,
NumberOfSearches,
SearchWords
FROM C2
WHERE EndTime > '1900-01-01'

Jag fick mycket inspiration från Jeff Modens tally table / split string och även lite kod som jag såg att just Itzik hade skrivit.

än en gång Toine, stort grattis! Nu är det bara att tagga om till 2014 års sqlug-tävling!

/Robin
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
28 Jan 2014 08:30 PM

Hej!

Här är testresultat för alla de lösningar som inte fick timeout eller stoppade p g a något fel. IsValid talar om ifall bidraget gav rätt resultat eller inte.

EntryID IsValid LogicalReads LogicalWrites SumReadsAndWrites DurationMicroseconds RowCount AuthorName
45 1 1510 84 1594 729098 12644 Toine Rozemeijer
45 1 1524 84 1608 717079 12644 Toine Rozemeijer
45 1 1525 84 1609 769765 12644 Toine Rozemeijer
46 1 1661 84 1745 872782 12644 Toine Rozemeijer
46 1 1665 84 1749 885522 12644 Toine Rozemeijer
46 1 1672 84 1756 879023 12644 Toine Rozemeijer
80 0 26082 81 26163 1882438 12572 Lars-Ove Johansson
80 0 26082 81 26163 1906315 12572 Lars-Ove Johansson
80 0 26082 81 26163 2093040 12572 Lars-Ove Johansson
61 1 26234 82 26316 3480099 12644 Robin Langell
61 1 26234 82 26316 3491728 12644 Robin Langell
61 1 26234 82 26316 3705357 12644 Robin Langell
52 0 26387 97 26484 3720747 12572 Christian Mosebach
51 0 26387 97 26484 3770208 12572 Christian Mosebach
52 0 26387 97 26484 3798080 12572 Christian Mosebach
51 0 26387 97 26484 3800822 12572 Christian Mosebach
52 0 26387 97 26484 3832065 12572 Christian Mosebach
51 0 26387 98 26485 3868696 12572 Christian Mosebach
16 1 26406 82 26488 12545439 12644 Robin Langell
16 1 26406 82 26488 12641961 12644 Robin Langell
16 1 26406 82 26488 13142563 12644 Robin Langell
6 1 26440 98 26538 1422423 12644 Anders Röding
77 1 26440 98 26538 1519452 12644 Anders Röding
6 1 26440 98 26538 1521381 12644 Anders Röding
77 1 26440 98 26538 1535118 12644 Anders Röding
77 1 26440 98 26538 1581394 12644 Anders Röding
6 1 26440 98 26538 1610766 12644 Anders Röding
33 0 26454 102 26556 1198366 12644 Stefan Gustafsson
13 0 26454 102 26556 1292671 12644 Stefan Gustafsson
33 0 26454 102 26556 1337657 12644 Stefan Gustafsson
13 0 26454 102 26556 1366283 12644 Stefan Gustafsson
13 0 26454 102 26556 1704069 12644 Stefan Gustafsson
33 0 26454 102 26556 1909269 12644 Stefan Gustafsson
44 0 26637 82 26719 21001329 12645 Johan Nilsson
44 0 26637 82 26719 21287687 12645 Johan Nilsson
44 0 26637 82 26719 21345885 12645 Johan Nilsson
20 0 26813 81 26894 785340 12572 Toine Rozemeijer
20 0 26823 81 26904 867793 12572 Toine Rozemeijer
20 0 26827 81 26908 759483 12572 Toine Rozemeijer
47 1 26895 138 27033 1342558 12644 Stefan Gustafsson
47 1 26895 138 27033 1452715 12644 Stefan Gustafsson
47 1 26895 138 27033 1547444 12644 Stefan Gustafsson
14 1 27000 130 27130 1108175 12644 Mikael Eriksson
14 1 27000 130 27130 1242566 12644 Mikael Eriksson
14 1 27000 130 27130 1262117 12644 Mikael Eriksson
70 0 27064 137 27201 2824306 12648 Mikael Eriksson
70 0 27064 137 27201 3109981 12648 Mikael Eriksson
70 0 27064 137 27201 3154132 12648 Mikael Eriksson
68 1 27118 143 27261 1171710 12644 Rolf Mattsson
68 1 27118 143 27261 1187642 12644 Rolf Mattsson
68 1 27118 143 27261 1394909 12644 Rolf Mattsson
41 0 27931 307 28238 1263862 12572 Markus Jägerskogh
41 0 27928 311 28239 1361692 12572 Markus Jägerskogh
41 0 27937 307 28244 1233895 12572 Markus Jägerskogh
76 1 29328 83 29411 1265737 12644 Jeanette Karlsson
76 1 29337 82 29419 1173760 12644 Jeanette Karlsson
76 1 29363 82 29445 1443683 12644 Jeanette Karlsson
60 1 30578 150 30728 1453455 12644 Henrik Linder
60 1 30579 151 30730 1384881 12644 Henrik Linder
60 1 30588 150 30738 1519296 12644 Henrik Linder
56 1 30924 149 31073 1776264 12644 Henrik Linder
56 1 30937 149 31086 1459521 12644 Henrik Linder
56 1 30956 149 31105 1462394 12644 Henrik Linder
11 0 31778 365 32143 2669413 12572 Markus Jägerskogh
11 0 31803 364 32167 2507633 12572 Markus Jägerskogh
11 0 31834 378 32212 5750286 12572 Markus Jägerskogh
65 0 31952 639 32591 2378342 12645 Daniel Hutmacher
65 0 31969 633 32602 2165956 12645 Daniel Hutmacher
65 0 31964 655 32619 2242681 12645 Daniel Hutmacher
48 0 47461 214 47675 2323928 12572 Lars-Ove Johansson
48 0 47461 214 47675 2337683 12572 Lars-Ove Johansson
48 0 47465 214 47679 2401514 12572 Lars-Ove Johansson
32 1 58529 347 58876 2845360 12644 Henrik Linder
32 1 58534 346 58880 2690808 12644 Henrik Linder
32 1 58539 346 58885 2473995 12644 Henrik Linder
64 0 69668 350 70018 20856133 12971 Pär Eriksson
64 0 69655 378 70033 21151457 12971 Pär Eriksson
64 0 69655 384 70039 21372741 12971 Pär Eriksson
42 0 71457 563 72020 7436051 12572 Jeanette Karlsson
42 0 71436 590 72026 7337566 12572 Jeanette Karlsson
42 0 71431 614 72045 7282338 12572 Jeanette Karlsson
54 1 71594 632 72226 7652624 12644 Jeanette Karlsson
54 1 71622 605 72227 7242892 12644 Jeanette Karlsson
54 1 72538 634 73172 7985128 12644 Jeanette Karlsson
2 0 107986 82 108068 1095147 12645 Sergey Klimkevich
2 0 107986 82 108068 1127643 12645 Sergey Klimkevich
2 0 107986 82 108068 1328306 12645 Sergey Klimkevich
31 1 108731 115 108846 1125711 12644 Mikael Eriksson
31 1 108731 115 108846 1149180 12644 Mikael Eriksson
31 1 108731 115 108846 1282650 12644 Mikael Eriksson
78 1 108536 454 108990 112154235 12644 Christian Janssen
78 1 108547 454 109001 115253055 12644 Christian Janssen
67 1 114227 509 114736 1720830 12644 Mattias Nyberg
67 1 114257 511 114768 1650105 12644 Mattias Nyberg
67 1 114268 508 114776 2424112 12644 Mattias Nyberg
26 1 120602 445 121047 116604256 12644 Christian Janssen
26 1 120605 445 121050 111288775 12644 Christian Janssen
15 0 121203 87 121290 1446326 12645 Sergey Klimkevich
15 0 121203 87 121290 1470481 12645 Sergey Klimkevich
15 0 121205 94 121299 1455542 12645 Sergey Klimkevich
36 1 121654 84 121738 1407927 12644 Sergey Klimkevich
36 1 121667 84 121751 1533929 12644 Sergey Klimkevich
36 1 121817 84 121901 1802278 12644 Sergey Klimkevich
29 0 130687 323 131010 2009056 12572 Christian Isaksson
29 0 130691 322 131013 2297196 12572 Christian Isaksson
29 0 130700 322 131022 2678669 12572 Christian Isaksson
43 1 169029 825 169854 36328277 12644 Daniel Hutmacher
43 1 169033 841 169874 37826360 12644 Daniel Hutmacher
43 1 169038 848 169886 37494000 12644 Daniel Hutmacher
72 1 207246 134 207380 2615303 12644 Henrik af Forselles
72 1 207246 134 207380 2953054 12644 Henrik af Forselles
72 1 207247 138 207385 2554322 12644 Henrik af Forselles
37 1 239872 514 240386 20637256 12644 Daniel Hutmacher
37 1 239846 593 240439 20110538 12644 Daniel Hutmacher
37 1 239879 598 240477 20259764 12644 Daniel Hutmacher
79 0 254468 232 254700 1848683 12572 Martin Höglund
79 0 254468 234 254702 2062507 12572 Martin Höglund
79 0 254476 234 254710 1893417 12572 Martin Höglund
23 0 313369 81 313450 2742116 12572 Lars-Ove Johansson
23 0 313369 81 313450 2808262 12572 Lars-Ove Johansson
23 0 313369 81 313450 2864774 12572 Lars-Ove Johansson
8 0 319599 437 320036 4123065 12572 Christian Isaksson
8 0 319612 465 320077 4128958 12572 Christian Isaksson
8 0 319644 462 320106 4153835 12572 Christian Isaksson
30 0 363393 665 364058 4595493 11490 Mattias Nyberg
30 0 363398 668 364066 4491687 11490 Mattias Nyberg
30 0 363405 673 364078 4669557 11490 Mattias Nyberg
53 0 385475 845 386320 21187716 12971 Pär Eriksson
53 0 385475 847 386322 21736703 12971 Pär Eriksson
53 0 385552 835 386387 21006762 12971 Pär Eriksson
50 0 427251 403 427654 2380472 12572 Martin Höglund
50 0 427251 482 427733 2473714 12572 Martin Höglund
50 0 427249 626 427875 2565596 12572 Martin Höglund
69 0 858897 1531 860428 7298452 12571 Per Hederos
69 0 858916 1532 860448 7283597 12571 Per Hederos
69 0 858956 1537 860493 6816291 12571 Per Hederos
19 0 1023094 896 1023990 5174467 12572 Pär Jorwén
19 0 1023104 1062 1024166 5314420 12572 Pär Jorwén
19 0 1023074 1126 1024200 4812519 12572 Pär Jorwén
63 0 3735348 98 3735446 16370411 12644 Jan Nieminen
63 0 3735348 98 3735446 16460164 12644 Jan Nieminen
63 0 3735348 98 3735446 16514656 12644 Jan Nieminen
38 0 5060978 3097 5064075 16549374 12571 Per Hederos
38 0 5060957 3296 5064253 16310872 12571 Per Hederos
7 0 5060951 3451 5064402 16320623 12571 Per Hederos
7 0 5060962 3462 5064424 16114208 12571 Per Hederos
7 0 5060951 3474 5064425 16439487 12571 Per Hederos
38 0 5060966 3460 5064426 16898367 12571 Per Hederos
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
28 Jan 2014 08:34 PM
jojx skrev:
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

Alla som har skickat in en godkänd lösning finns med på listan. Bara bästa bidraget från varje person visas.

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
28 Jan 2014 08:57 PM
Hej,

Vanligaste felen i denna tävlingsomgång var:

1. Att ta datediff på minuter istället för sekunder. Alternativt kan man använda dateadd för att det skall bli en korrekt jämförelse.
2. Att man missade att det kan finnas sökningar vid exakt samma tid som har samma ord, fast i olika ordning.
3. Att använda en dålig hashfunktion som ger kollisioner (t ex CHECKSUM_AGG).

Toine vann tack vare att han gjorde en minimally logged insert. Jag kan rekommendera att läsa den här artikeln:

http://sqlmag.com/t-sql/minimally-l...ed-inserts

Tack för alla tävlingsbidrag och välkomna igen nästa omgång!

MVH
Johan

ToineR
New Member
New Member
Posts: 12


--
28 Jan 2014 10:24 PM
Först vill jag tacka alla för gratulationer (och tålamodet). Också ett stort tack till juryn för att arangera tävlingen.

Här är koden av den vinnande lösningen i tävlingen. Som vanligt har jag inte så mycket kommentar i min kod, men det kommer en förklaring på slutet.
CREATE TABLE #DistinctSearchWords
(
OriginalSearchWords VARCHAR(200) COLLATE DATABASE_DEFAULT NOT NULL
, SortedSearchWords VARCHAR(200) COLLATE DATABASE_DEFAULT NOT NULL
);
INSERT INTO #DistinctSearchWords
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;
INSERT INTO SearchGroups WITH(TABLOCK) (StartTime, EndTime, NumberOfSearches, SearchWords)
SELECT MIN(Grouped.EventTime) AS StartTime
, MAX(Grouped.EventTime) AS EndTime
, COUNT(*) AS NumberOfSearches
, Grouped.SortedSearchWords
FROM
(
SELECT Periods.SortedSearchWords
, Periods.EventTime
, SUM(NewSearchPeriod) OVER (PARTITION BY Periods.SortedSearchWords ORDER BY Periods.EventTime ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PeriodIdentifier
FROM
(
SELECT DSW.SortedSearchWords
, S.EventTime
, CASE WHEN DATEDIFF(ss, LAG(S.EventTime) OVER(PARTITION BY DSW.SortedSearchWords ORDER BY S.EventTime ASC), S.EventTime) <= 1800 THEN 0 ELSE 1 END AS NewSearchPeriod
FROM #DistinctSearchWords AS DSW
JOIN Searches AS S ON DSW.OriginalSearchWords = S.SearchWords
) AS Periods
) AS Grouped
GROUP BY Grouped.SortedSearchWords
, Grouped.PeriodIdentifier;
DROP TABLE #DistinctSearchWords;

Det som jag börjar med är att skapa en temporär tabell som innehåller en rad för varje unika kombination av SearchWords som i input datan.

På grund av att input datan var limiterad på 200 tecken har jag valt för att "joina" input värdet med en fördefinierat lista siffror mellan 1 och 201. Anledningen för att ta en extra siffra är pga input datan inte hade några leading eller trailing seperator. För varje siffra kollar jag sen om det är en seperator och om det är sant så tar jag en substring från nuvarande position till nästa seperator (eller slutet av strängen om det är sista sökordet). Jag lämnar seperator som del av sokordet så att jag inte behöver lägga tillbaka den senare. Sen använder jag STUFF funktionen för att skapa ett sträng med dom sökord i sorterat ordning. STUFF funktionen är ganska användbart om man vill skapa ett XML document och spara i ett fält (eller om man behöver göra visa presentations relaterade grejer i T-SQL koden). Det var faktiskt Tobias Ternström som lärde mig om STUFF funktionen för typ 5 år sen och jag tackar honom för det.

Sen kommer den delen av koden som skapar outputten. Del har delen är inspirerat från förra årets tävling. Vad den gör först är att den med hjälp av LAG funktionen markerar alla rader som startar ett nytt interval. Sen kör jag en running total beräkning vilken skapar en identifiera för varje grupp av sökningar som hör ihop, vilket jag i slutet använder i den sista grupperingen. Värdet som identifierar dom olika grupper ligger bara i GROUP BY clausen och inte i SELECTEN för att den inte skulle visas ut.

Det sista nyckelordet i lösningen är TABLOCK vilket enablar SQL Server att göra ett minimalt loggat operation om alla andra förutsättningar som krävs uppfylls. Det konstiga är att den har lösnigen kunde köra med minimalt loggning på 4 av mina 5 test databaser men inte på den femte. Jag har skrivit en annan lösning (vilket kommer i nästa post) vilken kunde köra minimalt loggat i alla 5 database. Varför en viss statement kan köra minimalt loggat på en av database och inte op den andra när det går att köra minimalt loggat är inte nånting jag har hittat örsaken till än.
ToineR
New Member
New Member
Posts: 12


--
28 Jan 2014 10:26 PM
Här är lösningen som jag kunde köra minimalt loggat i alla mina test databaser (och har EventID 46 i resultatet):

CREATE TABLE #DistinctSearchWords
(
OriginalSearchWords VARCHAR(200) COLLATE DATABASE_DEFAULT NOT NULL
, SortedSearchWords VARCHAR(200) COLLATE DATABASE_DEFAULT NOT NULL
);
INSERT INTO #DistinctSearchWords
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;
WITH NumberedSearches AS (
SELECT DSW.SortedSearchWords
, S.EventTime
, ROW_NUMBER() OVER(PARTITION BY DSW.SortedSearchWords ORDER BY S.EventTime ASC) AS RN
FROM #DistinctSearchWords AS DSW
JOIN Searches AS S ON DSW.OriginalSearchWords = S.SearchWords
), Intervals AS (
SELECT S1.SortedSearchWords
, S1.EventTime
, S1.RN
, CASE
WHEN S1.RN = 1 THEN 1
WHEN S2.EventTime IS NOT NULL AND DATEDIFF(ss, S2.EventTime, S1.EventTime) <= 1800 THEN 0
ELSE 1
END AS New
FROM NumberedSearches AS S1
LEFT JOIN NumberedSearches AS S2 ON S1.SortedSearchWords = S2.SortedSearchWords AND S1.RN - 1 = S2.RN
), Periods AS (
SELECT SortedSearchWords
, EventTime
, SUM(New) OVER(PARTITION BY SortedSearchWords ORDER BY Intervals.RN ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupNr
FROM Intervals
)
INSERT INTO SearchGroups WITH (TABLOCK) (StartTime, EndTime, NumberOfSearches, SearchWords)
SELECT MIN(EventTime)
, MAX(EventTime)
, COUNT(*)
, SortedSearchWords
FROM Periods
GROUP BY SortedSearchWords
, GroupNr;
DROP TABLE #DistinctSearchWords;
Sergey
New Member
New Member
Posts: 16


--
29 Jan 2014 12:07 AM

Stort tack för arrangörerna av tävlingen för riktigt kul och utmanande tävlingsuppgift!
Jag vill även tacka alla som postar sina bidrag.

Insert WITH( TABLOCK ) i vinnande bidrag har skapat en AHA-upplevelse för mig. Tack för den Toine och grattis till segern!

Här nedan bifogar jag min lösning:


INSERT [dbo].[SearchGroups]
( [StartTime] ,
[EndTime] ,
[NumberOfSearches] ,
[SearchWords]
)
SELECT
DATEADD(ss,-secondsSinceFirstSearch,[EventTime]) AS [StartTime]
,[EventTime] AS [EndTime]
,[NumberOfSearches]
,[SearchWords]
FROM (
SELECT
*
,COUNT(1) OVER (PARTITION BY [SortedSearchWords],groupNr ORDER BY [EventTime] ROWS UNBOUNDED PRECEDING) NumberOfSearches
,SUM
(
CASE WHEN isBeginGroup=1 THEN 0 ELSE secondsSinceLastSearch
END
) OVER (PARTITION BY [SortedSearchWords],groupNr ORDER BY [EventTime] ROWS UNBOUNDED PRECEDING) secondsSinceFirstSearch
FROM (
SELECT
*
,SUM(isBeginGroup) OVER (PARTITION BY [SortedSearchWords] ORDER BY [EventTime] ROWS UNBOUNDED PRECEDING) groupNr
FROM (
select
*
, datediff(SECOND, prevEventTime, [EventTime]) as secondsSinceLastSearch
, case
when datediff(SECOND, prevEventTime, [EventTime]) >1800 then 1
when prevEventTime is null then 1
else 0
end as isBeginGroup
, case
when datediff(SECOND, [EventTime], nextEventTime) >1800 then 1
when nextEventTime is null then 1
else 0
end as isEndGroup
from (
select *
,LAG([EventTime],1) OVER (PARTITION BY [SortedSearchWords] ORDER BY [EventTime]) AS prevEventTime
,LEAD([EventTime],1) OVER (PARTITION BY [SortedSearchWords] ORDER BY [EventTime]) AS nextEventTime
from [dbo].[Searches] ss
CROSS APPLY(
select [SortedSearchWords] =
STUFF(
CONVERT(varchar(201),
(SELECT
top (101) ',' + Item
FROM (
--==== DelimitedSplit8K - what an inspiration :) !!!!
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING([SearchWords], l.N1, l.L1)
FROM (
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(',',[SearchWords],s.N1),0)-s.N1,8000) as L1
FROM (
SELECT 1 as N1 UNION ALL
SELECT t.N+1
FROM (
SELECT TOP (ISNULL(DATALENGTH([SearchWords]),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N
FROM (
select E1.N from (values (1),(1),(1),(1),(1),(1)) E1(N)
cross join (select N from (values (1),(1),(1),(1),(1),(1)) E1(N))E2
cross join (select N from (values (1),(1),(1),(1),(1),(1)) E1(N))E3
) E4
) t WHERE SUBSTRING([SearchWords],t.N,1) = ','
) s
) l
)sp
order by Item
FOR XML PATH(''),TYPE
)-- select top 101
) -- convert
,1,1,'')-- STUFF
)ca
) a
) b
)c
)d
WHERE isEndGroup=1
order by [StartTime],[SearchWords]

Testa att köra den med TABLOCK hint vid insert och se vad som händer...

//Sergey

You are not authorized to post a reply.
Page 3 of 3 << < 123