en-USsv-SE

Active Forums

PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 21 Mar 2012 12:18 PM by  HenrikF
SQLug.se challenge 2011
 117 Replies
Sort:
You are not authorized to post a reply.
Page 2 of 6 << < 12345 > >>
Author Messages
Per Scheffer
New Member
New Member
Posts: 7


--
27 Mar 2011 02:30 PM
------------------------------------------------------------------------------
-- SQLug.se Challenge 2011
------------------------------------------------------------------------------
-- Author: Per Scheffer
-- Company: Bizware
-- Edition: 1
-- Version: 1
------------------------------------------------------------------------------
-- Setup
SET NOCOUNT ON
-- Huvudkod
SELECT DISTINCT
pa.PeopleID,
wa.WantedSetID
FROM PeopleAttribute pa
JOIN WantedAttribute wa
ON pa.AttributeID = wa.AttributeID
AND pa.AttributeValue = wa.AttributeValue
WHERE wa.WantedSetID NOT IN (SELECT WantedSetID
FROM WantedAttribute
WHERE AttributeID NOT IN (SELECT AttributeID
FROM PeopleAttribute
WHERE PeopleID = pa.PeopleID))
AND wa.WantedSetID NOT IN (SELECT WantedSetID
FROM WantedAttribute wax
WHERE wax.AttributeID IN (SELECT AttributeID
FROM PeopleAttribute
WHERE PeopleID = pa.PeopleID
AND AttributeValue != wax.AttributeValue))
-- Uppstädning
SET NOCOUNT OFF


Zhong
New Member
New Member
Posts: 11


--
27 Mar 2011 02:35 PM

En CLR lösning (5) här.

I mina tidigare förslag användes WantedAttribute två gånger det känns helt fel och jag har inte lyckats med en SQL som kan göra en enkel SET operation som krävs. CLR anrop med serialization är inte optimal. Det skulle vara mycket intressant att se vilken teknik vinnar.

/Zhong
/**********************************************************************
Accumulator

**********************************************************************/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Collections.Generic;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,
IsInvariantToNulls = false,
IsInvariantToDuplicates = false,
IsInvariantToOrder = false,
MaxByteSize = -1)]
public struct Accumulator : IBinarySerialize
{
List> Set;

public void Init()
{
Set = new List>();
}

public void Accumulate(int id, char val)
{
Set.Add(new KeyValuePair(id, val));
}

public void Merge(Accumulator group)
{
this.Set.AddRange(group.Set);
}

public String Terminate()
{
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair e in Set)
{
sb.Append(e.Key).Append('|').Append(e.Value).Append(';');
}
return sb.ToString();
}

public void Read(BinaryReader r)
{
Init();
while (true)
{
try
{
Set.Add(new KeyValuePair(r.ReadInt32(), r.ReadChar()));
}
catch { return; }
}
}

public void Write(BinaryWriter w)
{
foreach (KeyValuePair e in Set)
{
w.Write(e.Key);
w.Write(e.Value);
}
}
}



/**********************************************************************
Comparer

**********************************************************************/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean Comparer(SqlString wanted, SqlString have)
{
List> ws = GetSet(wanted.Value);
List> hs = GetSet(have.Value);

if (hs.Count < ws.Count) return false;

int i = 0;
for (int j = 0; i < ws.Count && j < hs.Count; j++)
{
if (ws[i].Equals(hs[j])) i++;
}

return (i == ws.Count);
}

static List> GetSet(string s)
{
List> set = new List>();
string[] lst = s.Split(';');

string[] kv;
foreach (string item in lst)
{
kv = item.Split('|');
if (kv.Length == 2)
{
set.Add(new KeyValuePair(Int32.Parse(kv[0]), kv[1][0]));
}
}
return set;
}

};

/*********************************************************

SQL

**********************************************************/

-- Set up
CREATE ASSEMBLY [SqlUg2011]
AUTHORIZATION [dbo]
FROM 'C:\Temp\SqlUg2011.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION [dbo].[Comparer](
@wanted [nvarchar](4000),
@have [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlUg2011].[UserDefinedFunctions].[Comparer]
GO

CREATE AGGREGATE [dbo].[Accumulator] (
@id [int],
@val [nchar](1))
RETURNS[nvarchar](4000)
EXTERNAL NAME [SqlUg2011].[Accumulator]
GO

-- Main
select PeopleID, WantedSetID
from
(
select WantedSetID, dbo.Accumulator(AttributeID, AttributeValue) wanted
from WantedAttribute
group by WantedSetID
) wa join
(
select PeopleID, dbo.Accumulator(AttributeID, AttributeValue) have
from PeopleAttribute
group by PeopleID
) pa on dbo.Comparer(wanted, have) = 1


-- Cleanup
DROP FUNCTION [dbo].[Comparer]
DROP AGGREGATE [dbo].[Accumulator]
DROP ASSEMBLY [SqlUg2011]
GO



Patrik Molin
New Member
New Member
Posts: 2


--
27 Mar 2011 07:12 PM
Patrik Molin här. Representerar Affecto!
Joel Greijer
New Member
New Member
Posts: 4


--
29 Mar 2011 09:01 PM
Här kommer ett försök från mig
Mvh
Joel Greijer, Releye AB




/* Joel Greijer, attempt 1, Setup */


/* Create indexes */


IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PeopleAttribute]') AND name = N'PeopleAttribute1337')
DROP INDEX [PeopleAttribute1337] ON [dbo].[PeopleAttribute] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [PeopleAttribute1337] ON [dbo].[PeopleAttribute]
(
[AttributeID] ASC,
[AttributeValue] ASC
)
INCLUDE ( [PeopleID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO




IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[WantedAttribute]') AND name = N'WantedAttribute1337')
DROP INDEX [WantedAttribute1337] ON [dbo].[WantedAttribute] WITH ( ONLINE = OFF )
GO


CREATE NONCLUSTERED INDEX [WantedAttribute1337] ON [dbo].[WantedAttribute]
(
[WantedSetID] ASC,
[AttributeID] ASC,
[AttributeValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO




/* views */


IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MatchCount]'))
DROP VIEW [dbo].[MatchCount]
GO


create view [dbo].[MatchCount]
as
select wa.WantedSetID,pa.PeopleID,count(*) as Matches
from dbo.WantedAttribute wa
join dbo.PeopleAttribute pa
on wa.AttributeID=pa.AttributeID
and wa.AttributeValue=pa.AttributeValue
group by WantedSetID,PeopleID
GO


IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[WhishCount]'))
DROP VIEW [dbo].[WhishCount]
GO


create view [dbo].[WhishCount]
as
select WantedSetID,Count(*) Whishes
from dbo.WantedAttribute
group by WantedSetID
GO




/* Procedures */


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetMatches') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.GetMatches
GO


create procedure dbo.GetMatches
as


select m.PeopleID,w.WantedSetID
from dbo.WhishCount w
join dbo.MatchCount m
on w.WantedSetID=m.WantedSetID
and w.Whishes<=m.Matches


GO



/* Joel Greijer, attempt 1, Solution */


execute dbo.GetMatches;



go

/* Joel Greijer, attempt 1, Clean-up */

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PeopleAttribute]') AND name = N'PeopleAttribute1337')
DROP INDEX [PeopleAttribute1337] ON [dbo].[PeopleAttribute] WITH ( ONLINE = OFF )
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[WantedAttribute]') AND name = N'WantedAttribute1337')
DROP INDEX [WantedAttribute1337] ON [dbo].[WantedAttribute] WITH ( ONLINE = OFF )
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MatchCount]'))
DROP VIEW [dbo].[MatchCount]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[WhishCount]'))
DROP VIEW [dbo].[WhishCount]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetMatches') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.GetMatches
GO



Per Scheffer
New Member
New Member
Posts: 7


--
30 Mar 2011 07:56 AM
------------------------------------------------------------------------------
-- SQLug.se Challenge 2011
------------------------------------------------------------------------------
-- Author: Per Scheffer
-- Company: Bizware
-- Edition: 2
-- Version: 1
------------------------------------------------------------------------------

-- Setup
SET NOCOUNT ON

-- Huvudkod
SELECT pa.PeopleID,
wa.WantedSetID
FROM PeopleAttribute pa
JOIN WantedAttribute wa
ON pa.AttributeID = wa.AttributeID
AND pa.AttributeValue = wa.AttributeValue
GROUP BY pa.PeopleID,
wa.WantedSetID
HAVING COUNT(*) = (SELECT COUNT(*)
FROM WantedAttribute
WHERE WantedSetID = wa.WantedSetID)

-- Uppstädning
SET NOCOUNT OFF

Per Scheffer
New Member
New Member
Posts: 7


--
30 Mar 2011 07:57 AM
------------------------------------------------------------------------------
-- SQLug.se Challenge 2011
------------------------------------------------------------------------------
-- Author: Per Scheffer
-- Company: Bizware
-- Edition: 2
-- Version: 2
------------------------------------------------------------------------------

-- Setup
SET NOCOUNT ON
CREATE INDEX IX_PeopleAttribute_1 ON PeopleAttribute (AttributeID, AttributeValue, PeopleID)

-- Huvudkod
SELECT pa.PeopleID,
wa.WantedSetID
FROM PeopleAttribute pa
JOIN WantedAttribute wa
ON pa.AttributeID = wa.AttributeID
AND pa.AttributeValue = wa.AttributeValue
GROUP BY pa.PeopleID,
wa.WantedSetID
HAVING COUNT(*) = (SELECT COUNT(*)
FROM WantedAttribute
WHERE WantedSetID = wa.WantedSetID)

-- Uppstädning
DROP INDEX IX_PeopleAttribute_1 ON PeopleAttribute
SET NOCOUNT OFF
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
31 Mar 2011 07:30 PM
Hej Zhong,

Tack för ditt SQL CLR-bidrag. Kan du maila in DLL-filen till info@sqlug.se?
M Höglund
New Member
New Member
Posts: 10


--
02 Apr 2011 01:31 PM
Tävlingsbidrag 1 (Huvudkod)
från Martin Höglund, Bring Citymail

Och ni har ju satt upp unika index i så bra ordning så att vi börjar så här, ingen setup & städning alltså. Det går kanske att finputsa med index sen.

/Martin

/*
ALLA attribut i ett givet sökset finns i ett givet personset
<==>
Det finns INTE NåGOT attribut i ett givet sökset som INTE finns i ett givet personset
*/

select p.PeopleID, ws.WantedSetID
from dbo.People p
cross join (select distinct WantedSetID from dbo.WantedAttribute) ws
where not exists (select *
from dbo.WantedAttribute wa
where wa.WantedSetID = ws.WantedSetID
and not exists (select *
from dbo.PeopleAttribute pa
where pa.PeopleID = p.PeopleID
and pa.AttributeId = wa.AttributeId
and pa.AttributeValue = wa.AttributeValue
)
)


SwePeso
New Member
New Member
Posts: 67


--
04 Apr 2011 08:58 AM
Nya preliminär ställning. Dock saknas en SQLCLR lösning som jag behöver få rätt på först.

**********************************************
* # * Förslag * Företag *
**********************************************
* 1 * Henrik F 1 * *
* 1 * Patrik Molin 1 * Affecto *
* 1 * Zhong 2 * B3IT * *
* 4 * Joel Greijer 1 * Releye AB *
* 4 * Per Scheffer 2a * Bizware *
* 6 * Per Scheffer 2b * Bizware *
* 6 * Zhong 3 * B3IT *
* 8 * Per Scheffer 1 * Bizware *
* 8 * Martin Höglund * Bring Citymail *
* 10 * Zhong 4 * B3IT *
* 11 * Malin D 1 * Affecto *
* 12 * Malin D 2 * Affecto *
* 12 * Zhong 1 * B3IT *
**********************************************



Per Scheffer
New Member
New Member
Posts: 7


--
04 Apr 2011 09:43 AM
Görs testerna likadant varje gång? I den föregående preliminära ställningen hade Zhong 3 och Per Scheffer 1 samma ställning och Zhong 4 gav inte rätt resultat. Räknas tiden i setup-scripten in i de preliminära testerna?
Per Scheffer
New Member
New Member
Posts: 7


--
04 Apr 2011 09:43 AM
Görs testerna likadant varje gång? I den föregående preliminära ställningen hade Zhong 3 och Per Scheffer 1 samma ställning och Zhong 4 gav inte rätt resultat. Räknas tiden i setup-scripten in i de preliminära testerna?
Per Scheffer
New Member
New Member
Posts: 7


--
04 Apr 2011 09:43 AM
Görs testerna likadant varje gång? I den föregående preliminära ställningen hade Zhong 3 och Per Scheffer 1 samma ställning och Zhong 4 gav inte rätt resultat. Räknas tiden i setup-scripten in i de preliminära testerna?
Per Scheffer
New Member
New Member
Posts: 7


--
04 Apr 2011 09:44 AM
Görs testerna likadant varje gång? I den föregående preliminära ställningen hade Zhong 3 och Per Scheffer 1 samma ställning och Zhong 4 gav inte rätt resultat. Räknas tiden i setup-scripten in i de preliminära testerna?
SwePeso
New Member
New Member
Posts: 67


--
04 Apr 2011 09:57 AM
Ja, testerna utförs likadant varje gång.

Att de ligger på samma placering i den preliminära resultatlistan betyder inte att de ger exakt samma resultat.

Det har bildats ett mönster så att vissa lösningar har ungefär samma tider och då ligger de på delad plats än så länge.

Skillnaden kan vara mellan en halv sekund till 2-3 sekunder och då hamnar de i samma grupp, dvs samma placering. Det finns alltså fortfarande utrymme till förbättring på era lösningar!



Anledningen till detta förfarande är att en körning tar ca 25-75 sekunder att genomföra. Det är för tidigt att gå ner på millisekunder ännu då lösningarna är så lika.



Det syns redan nu varifrån ni hämtat er inspiration och vilka böcker ni läst! :-)



Den snabbate lösningen än så länge (utom tävlan) klockar på 18-20 sekunder medan den långsammaste tickar runt på 420 sekunder.

Peter Abelsson
New Member
New Member
Posts: 3


--
06 Apr 2011 10:43 AM
Hej,

Här kommer mina två bidrag till SQLUG Challenge 2011

Länk till lösningsförslag 1
Länk till lösningsförslag 2

Lösning 2 kräver att clr är enablat på sql-servern.

Företag: HM

Mvh,
Peter Abelsson
Eddan
New Member
New Member
Posts: 11


--
06 Apr 2011 11:08 PM
--Mitt bidrag
;
WITH allMatch AS
(
SELECT peopleId, WantedSetID, count(wa.attributeId) noOfattributes
FROM wantedAttribute wa
INNER JOIN peopleAttribute pa ON wa.attributeID = pa.attributeID
AND wa.attributeValue= pa.attributeValue
GROUP BY peopleID, WantedSetID
),
noOfAttributesTofulFill AS
(
SELECT WantedSetID, Count(attributeID) noOfattributes
FROM wantedAttribute
GROUP BY WantedSetID
)
SELECT allm.peopleId, allm.WantedSetID
FROM allMatch allm
INNER JOiN noOfAttributesTofulFill noa ON noa.WantedSetID=allm.WantedSetID
AND noa.noOfAttributes=allm.noOfAttributes
Eddan
New Member
New Member
Posts: 11


--
06 Apr 2011 11:08 PM
--Mitt bidrag
;
WITH allMatch AS
(
SELECT peopleId, WantedSetID, count(wa.attributeId) noOfattributes
FROM wantedAttribute wa
INNER JOIN peopleAttribute pa ON wa.attributeID = pa.attributeID
AND wa.attributeValue= pa.attributeValue
GROUP BY peopleID, WantedSetID
),
noOfAttributesTofulFill AS
(
SELECT WantedSetID, Count(attributeID) noOfattributes
FROM wantedAttribute
GROUP BY WantedSetID
)
SELECT allm.peopleId, allm.WantedSetID
FROM allMatch allm
INNER JOiN noOfAttributesTofulFill noa ON noa.WantedSetID=allm.WantedSetID
AND noa.noOfAttributes=allm.noOfAttributes
Eddan
New Member
New Member
Posts: 11


--
06 Apr 2011 11:08 PM
--Mitt bidrag
;
WITH allMatch AS
(
SELECT peopleId, WantedSetID, count(wa.attributeId) noOfattributes
FROM wantedAttribute wa
INNER JOIN peopleAttribute pa ON wa.attributeID = pa.attributeID
AND wa.attributeValue= pa.attributeValue
GROUP BY peopleID, WantedSetID
),
noOfAttributesTofulFill AS
(
SELECT WantedSetID, Count(attributeID) noOfattributes
FROM wantedAttribute
GROUP BY WantedSetID
)
SELECT allm.peopleId, allm.WantedSetID
FROM allMatch allm
INNER JOiN noOfAttributesTofulFill noa ON noa.WantedSetID=allm.WantedSetID
AND noa.noOfAttributes=allm.noOfAttributes
Eddan
New Member
New Member
Posts: 11


--
06 Apr 2011 11:08 PM
--Mitt bidrag
;
WITH allMatch AS
(
SELECT peopleId, WantedSetID, count(wa.attributeId) noOfattributes
FROM wantedAttribute wa
INNER JOIN peopleAttribute pa ON wa.attributeID = pa.attributeID
AND wa.attributeValue= pa.attributeValue
GROUP BY peopleID, WantedSetID
),
noOfAttributesTofulFill AS
(
SELECT WantedSetID, Count(attributeID) noOfattributes
FROM wantedAttribute
GROUP BY WantedSetID
)
SELECT allm.peopleId, allm.WantedSetID
FROM allMatch allm
INNER JOiN noOfAttributesTofulFill noa ON noa.WantedSetID=allm.WantedSetID
AND noa.noOfAttributes=allm.noOfAttributes
Eddan
New Member
New Member
Posts: 11


--
06 Apr 2011 11:08 PM
--Mitt bidrag
;
WITH allMatch AS
(
SELECT peopleId, WantedSetID, count(wa.attributeId) noOfattributes
FROM wantedAttribute wa
INNER JOIN peopleAttribute pa ON wa.attributeID = pa.attributeID
AND wa.attributeValue= pa.attributeValue
GROUP BY peopleID, WantedSetID
),
noOfAttributesTofulFill AS
(
SELECT WantedSetID, Count(attributeID) noOfattributes
FROM wantedAttribute
GROUP BY WantedSetID
)
SELECT allm.peopleId, allm.WantedSetID
FROM allMatch allm
INNER JOiN noOfAttributesTofulFill noa ON noa.WantedSetID=allm.WantedSetID
AND noa.noOfAttributes=allm.noOfAttributes
You are not authorized to post a reply.
Page 2 of 6 << < 12345 > >>