A delicious dilemma
-
sqldriver (2/2/2015)
Ah, yeah, it's not much more rows than this. I guess the point of the secondary relationships is 'route choosing' of sorts. Like, if you start with BarX, these are your next possible choices (scotch in common) and then these are your next choices (secondary scotch in common) and then these are your next choices (tertiary scotch in common) and so on, until either all possible unique choices (bars) are exhausted, or all are exhausted and un-matched choices (bars) are displayed. If you picture that as a single list column that allows someone to choose a 'search path', it may make more sense.Thanks
Normally one would expect route selection to be determined by bar proximity rather than stock choices.
Unless of course you have a truck that can carry only two or three scotch brands at a time.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Proximity on a small scale, but on a larger scale you'd need to choose connecting paths. Or even on a not very large scale; think about a subway system. Certain routes go to certain stops with certain connecting lines. That's probably the more apt analogy.
The point of showing unique, earliest occurring here, would be to show the first station you could switch lines, for example.
Thanks
-
sqldriver (2/2/2015)
Eirikur Eiriksson (2/2/2015)
Quick thought, the problem itself is straightforward, the issue is more the schema, normalize it and the solution is easy.😎
That was one of the first things I did. There were about a dozen other columns in this load set that I had to do stuff with, but are irrelevant to this.
I posted the data up like this because I thought it might make it a bit easier for you to consume, and because it made it a lot easier for me to mask 😉
Anyway, I messed a bit with, after something like below, making a table of bars, and of scotch, and then a bunch of 'satellite' scotch per bar tables, all with unique IDs. I just got nowhere with this one.
SELECT Bar, Scotch, Item as ScotchNorm
INTO #WobblyNorm
FROM #Wobbly AS w
CROSS APPLY master.dbo.DelimitedSplit8K(Scotch, ';') AS dsk
SELECT *
FROM #WobblyNorm AS wn
As I said earlier, normalize the data and this problem becomes elementary, see the code for some hints on normalization.
😎
USE tempdb;GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#Wobbly') IS NOT NULL DROP TABLE #Wobbly;
CREATE TABLE #Wobbly (
Bar VARCHAR(5),
Scotch VARCHAR(50)
)
INSERT #Wobbly
( Bar, Scotch )
SELECT 'Bar1','Laphroaig;Lagavulin;Macallan' UNION ALL
SELECT 'Bar2','Talisker;Bowmore' UNION ALL
SELECT 'Bar3','Ardbeg;Oban' UNION ALL
SELECT 'Bar4','Speyburn;Dewars' UNION ALL
SELECT 'Bar5','Glenmorangie;Dalmore' UNION ALL
SELECT 'Bar6','Dalmore' UNION ALL
SELECT 'Bar7','Glenlivet;Talisker;Balvenie;Glengoyne;Springbank' UNION ALL
SELECT 'Bar8','Glenmorangie;Bruichladdich' UNION ALL
SELECT 'Bar9','Laphroaig;Dewars;HighlandPark;Speyburn' UNION ALL
SELECT 'Bar10','Laphroaig;Ardbeg;DunBheagan;' UNION ALL
SELECT 'Bar11','Dalmore;Talisker' UNION ALL
SELECT 'Bar12','Talisker;Oban;Tomintoul;Kilchomin' UNION ALL
SELECT 'Bar13','Bruichladdich;Glenfiddich' UNION ALL
SELECT 'Bar14','AnCnoc;Bruichladdich;Dalwhinnie;Tomintoul' UNION ALL
SELECT 'Bar15','Dalmore;Teachers' UNION ALL
SELECT 'Bar16','OldPulteney;Arberlour;Macallan' UNION ALL
SELECT 'Bar17','Glenmorangie;CompassBox' UNION ALL
SELECT 'Bar18','Longrow;Glenrothes;Dewars' UNION ALL
SELECT 'Bar19','Tomintoul' UNION ALL
SELECT 'Bar20','Glenmorangie;Bunnahabhain' UNION ALL
SELECT 'Bar21','Dalmore;Tomintoul'
IF OBJECT_ID(N'dbo.TBL_BAR') IS NOT NULL
BEGIN
ALTER TABLE dbo.TBL_BAR_WHISKY DROP CONSTRAINT FK_DBO_TBL_BAR_WHISKY_BAR_ID_DBO_TBL_BAR_BAR_ID;
DROP TABLE dbo.TBL_BAR;
END
CREATE TABLE dbo.TBL_BAR
(
BAR_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_BAR_BAR_ID PRIMARY KEY CLUSTERED
,BAR_NAME VARCHAR(50) NOT NULL CONSTRAINT UNQ_DBO_TBL_BAR_BAR_NAME UNIQUE
);
IF OBJECT_ID(N'dbo.TBL_WHISKY') IS NOT NULL
BEGIN
ALTER TABLE dbo.TBL_BAR_WHISKY DROP CONSTRAINT FK_DBO_TBL_BAR_WHISKY_WHISKY_ID_DBO_TBL_WHISKY_WHISKY_ID;
DROP TABLE dbo.TBL_WHISKY;
END
CREATE TABLE dbo.TBL_WHISKY
(
WHISKY_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_WHISKY_WHISKY_ID PRIMARY KEY CLUSTERED
,WHISKY_NAME VARCHAR(50) NOT NULL CONSTRAINT UNQ_DBO_TBL_WHISKY_WHISKY_NAME UNIQUE
);
IF OBJECT_ID(N'dbo.TBL_BAR_WHISKY') IS NOT NULL DROP TABLE dbo.TBL_BAR_WHISKY;
CREATE TABLE dbo.TBL_BAR_WHISKY
(
BAR_ID INT NOT NULL
,WHISKY_ID INT NOT NULL
,CONSTRAINT PK_DBO_TBL_BAR_WHISKY_BAR_ID_WHISKY_ID PRIMARY KEY CLUSTERED (BAR_ID,WHISKY_ID)
,CONSTRAINT FK_DBO_TBL_BAR_WHISKY_BAR_ID_DBO_TBL_BAR_BAR_ID FOREIGN KEY (BAR_ID) REFERENCES dbo.TBL_BAR(BAR_ID)
,CONSTRAINT FK_DBO_TBL_BAR_WHISKY_WHISKY_ID_DBO_TBL_WHISKY_WHISKY_ID FOREIGN KEY (WHISKY_ID) REFERENCES dbo.TBL_WHISKY(WHISKY_ID)
);
INSERT INTO dbo.TBL_BAR(BAR_NAME)
SELECT
W.Bar
FROM #Wobbly W;
INSERT INTO dbo.TBL_WHISKY(WHISKY_NAME)
SELECT DISTINCT
X.Item
FROM #Wobbly W
CROSS APPLY dbo.DelimitedSplitL8K(W.Scotch,CHAR(59)) AS X
WHERE LEN(X.Item) > 0;
INSERT INTO dbo.TBL_BAR_WHISKY (BAR_ID,WHISKY_ID)
SELECT
BAR.BAR_ID
,WHISKY.WHISKY_ID
FROM dbo.TBL_BAR BAR
INNER JOIN #Wobbly W
ON BAR.BAR_NAME = W.Bar
CROSS APPLY dbo.DelimitedSplitL8K(W.Scotch,CHAR(59)) AS X
INNER JOIN dbo.TBL_WHISKY WHISKY
ON X.Item = WHISKY.WHISKY_NAME;
SELECT
BAR.BAR_NAME
,WHISKY.WHISKY_NAME
FROM dbo.TBL_BAR BAR
INNER JOIN dbo.TBL_BAR_WHISKY BW
ON BAR.BAR_ID = BW.BAR_ID
INNER JOIN dbo.TBL_WHISKY WHISKY
ON BW.WHISKY_ID = WHISKY.WHISKY_ID
;
SELECT
BW.BAR_ID
,BW.WHISKY_ID
,COUNT(BW.BAR_ID) OVER
(
PARTITION BY BW.WHISKY_ID
) AS CNT_BAR_WHISKY
,COUNT(BW.WHISKY_ID) OVER
(
PARTITION BY BW.BAR_ID
) AS CNT_WHISKY_BAR
FROM dbo.TBL_BAR_WHISKY BW;
Query example
SELECTBW.BAR_ID
,BW.WHISKY_ID
,BAR.BAR_NAME
,COUNT(BW.WHISKY_ID) OVER
(
PARTITION BY BW.BAR_ID
) AS CNT_WHISKY_BAR
,STUFF((SELECT
CONCAT(CHAR(44),W.WHISKY_NAME)
FROM dbo.TBL_WHISKY W
INNER JOIN dbo.TBL_BAR_WHISKY WB
ON W.WHISKY_ID = WB.WHISKY_ID
WHERE WB.BAR_ID = BW.BAR_ID
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(500)'),1,1,'') AS WHISKY_LIST
,WHISKY.WHISKY_NAME
,COUNT(BW.BAR_ID) OVER
(
PARTITION BY BW.WHISKY_ID
) AS CNT_BAR_WHISKY
,STUFF((SELECT
CONCAT(CHAR(44),B.BAR_NAME)
FROM dbo.TBL_BAR B
INNER JOIN dbo.TBL_BAR_WHISKY WB
ON B.BAR_ID = WB.BAR_ID
WHERE WB.WHISKY_ID = BW.WHISKY_ID
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(500)'),1,1,'') AS BAR_LIST
FROM dbo.TBL_BAR BAR
INNER JOIN dbo.TBL_BAR_WHISKY BW
ON BAR.BAR_ID = BW.BAR_ID
INNER JOIN dbo.TBL_WHISKY WHISKY
ON BW.WHISKY_ID = WHISKY.WHISKY_ID;
Results
BAR_ID WHISKY_ID BAR_NAME CNT_WHISKY_BAR WHISKY_LIST WHISKY_NAME CNT_BAR_WHISKY BAR_LIST----------- ----------- ---------- -------------- -------------------------------------------------------- ------------------ -------------- -----------------------------
14 1 Bar14 4 AnCnoc,Bruichladdich,Dalwhinnie,Tomintoul AnCnoc 1 Bar14
16 2 Bar16 3 Arberlour,Macallan,OldPulteney Arberlour 1 Bar16
10 3 Bar10 3 Ardbeg,DunBheagan,Laphroaig Ardbeg 2 Bar3,Bar10
3 3 Bar3 2 Ardbeg,Oban Ardbeg 2 Bar3,Bar10
7 4 Bar7 5 Balvenie,Glengoyne,Glenlivet,Springbank,Talisker Balvenie 1 Bar7
2 5 Bar2 2 Bowmore,Talisker Bowmore 1 Bar2
8 6 Bar8 2 Bruichladdich,Glenmorangie Bruichladdich 3 Bar8,Bar13,Bar14
13 6 Bar13 2 Bruichladdich,Glenfiddich Bruichladdich 3 Bar8,Bar13,Bar14
14 6 Bar14 4 AnCnoc,Bruichladdich,Dalwhinnie,Tomintoul Bruichladdich 3 Bar8,Bar13,Bar14
20 7 Bar20 2 Bunnahabhain,Glenmorangie Bunnahabhain 1 Bar20
17 8 Bar17 2 CompassBox,Glenmorangie CompassBox 1 Bar17
15 9 Bar15 2 Dalmore,Teachers Dalmore 5 Bar5,Bar6,Bar11,Bar15,Bar21
21 9 Bar21 2 Dalmore,Tomintoul Dalmore 5 Bar5,Bar6,Bar11,Bar15,Bar21
11 9 Bar11 2 Dalmore,Talisker Dalmore 5 Bar5,Bar6,Bar11,Bar15,Bar21
5 9 Bar5 2 Dalmore,Glenmorangie Dalmore 5 Bar5,Bar6,Bar11,Bar15,Bar21
6 9 Bar6 1 Dalmore Dalmore 5 Bar5,Bar6,Bar11,Bar15,Bar21
14 10 Bar14 4 AnCnoc,Bruichladdich,Dalwhinnie,Tomintoul Dalwhinnie 1 Bar14
9 11 Bar9 4 Dewars,HighlandPark,Laphroaig,Speyburn Dewars 3 Bar4,Bar9,Bar18
4 11 Bar4 2 Dewars,Speyburn Dewars 3 Bar4,Bar9,Bar18
18 11 Bar18 3 Dewars,Glenrothes,Longrow Dewars 3 Bar4,Bar9,Bar18
10 12 Bar10 3 Ardbeg,DunBheagan,Laphroaig DunBheagan 1 Bar10
13 13 Bar13 2 Bruichladdich,Glenfiddich Glenfiddich 1 Bar13
7 14 Bar7 5 Balvenie,Glengoyne,Glenlivet,Springbank,Talisker Glengoyne 1 Bar7
7 15 Bar7 5 Balvenie,Glengoyne,Glenlivet,Springbank,Talisker Glenlivet 1 Bar7
8 16 Bar8 2 Bruichladdich,Glenmorangie Glenmorangie 4 Bar5,Bar8,Bar17,Bar20
5 16 Bar5 2 Dalmore,Glenmorangie Glenmorangie 4 Bar5,Bar8,Bar17,Bar20
20 16 Bar20 2 Bunnahabhain,Glenmorangie Glenmorangie 4 Bar5,Bar8,Bar17,Bar20
17 16 Bar17 2 CompassBox,Glenmorangie Glenmorangie 4 Bar5,Bar8,Bar17,Bar20
18 17 Bar18 3 Dewars,Glenrothes,Longrow Glenrothes 1 Bar18
9 18 Bar9 4 Dewars,HighlandPark,Laphroaig,Speyburn HighlandPark 1 Bar9
12 19 Bar12 4 Kilchomin,Oban,Talisker,Tomintoul Kilchomin 1 Bar12
1 20 Bar1 3 Lagavulin,Laphroaig,Macallan Lagavulin 1 Bar1
1 21 Bar1 3 Lagavulin,Laphroaig,Macallan Laphroaig 3 Bar1,Bar9,Bar10
10 21 Bar10 3 Ardbeg,DunBheagan,Laphroaig Laphroaig 3 Bar1,Bar9,Bar10
9 21 Bar9 4 Dewars,HighlandPark,Laphroaig,Speyburn Laphroaig 3 Bar1,Bar9,Bar10
18 22 Bar18 3 Dewars,Glenrothes,Longrow Longrow 1 Bar18
16 23 Bar16 3 Arberlour,Macallan,OldPulteney Macallan 2 Bar1,Bar16
1 23 Bar1 3 Lagavulin,Laphroaig,Macallan Macallan 2 Bar1,Bar16
3 24 Bar3 2 Ardbeg,Oban Oban 2 Bar3,Bar12
12 24 Bar12 4 Kilchomin,Oban,Talisker,Tomintoul Oban 2 Bar3,Bar12
16 25 Bar16 3 Arberlour,Macallan,OldPulteney OldPulteney 1 Bar16
9 26 Bar9 4 Dewars,HighlandPark,Laphroaig,Speyburn Speyburn 2 Bar4,Bar9
4 26 Bar4 2 Dewars,Speyburn Speyburn 2 Bar4,Bar9
7 27 Bar7 5 Balvenie,Glengoyne,Glenlivet,Springbank,Talisker Springbank 1 Bar7
7 28 Bar7 5 Balvenie,Glengoyne,Glenlivet,Springbank,Talisker Talisker 4 Bar2,Bar7,Bar11,Bar12
2 28 Bar2 2 Bowmore,Talisker Talisker 4 Bar2,Bar7,Bar11,Bar12
11 28 Bar11 2 Dalmore,Talisker Talisker 4 Bar2,Bar7,Bar11,Bar12
12 28 Bar12 4 Kilchomin,Oban,Talisker,Tomintoul Talisker 4 Bar2,Bar7,Bar11,Bar12
15 29 Bar15 2 Dalmore,Teachers Teachers 1 Bar15
19 30 Bar19 1 Tomintoul Tomintoul 4 Bar12,Bar14,Bar19,Bar21
21 30 Bar21 2 Dalmore,Tomintoul Tomintoul 4 Bar12,Bar14,Bar19,Bar21
12 30 Bar12 4 Kilchomin,Oban,Talisker,Tomintoul Tomintoul 4 Bar12,Bar14,Bar19,Bar21
14 30 Bar14 4 AnCnoc,Bruichladdich,Dalwhinnie,Tomintoul Tomintoul 4 Bar12,Bar14,Bar19,Bar21
Edit: added query sample
-
Hi Eirikur,
I feel a lot better about how I set things up now. That's about the identical schema that I made after the loading files were staged, with the exception of a wider table that contained all the values, and a horrible EAV table of just the codes :pinch:
The thing I couldn't figure out, regardless of the schema and normalization, was how to query to present data the way end users were requesting. What I found awkward was creating a hierarchy where anything could be the top node, and some top nodes would result in multiple parents (scotches), and then traversing it without hitting the same bar more than once. What Nevyn nailed that I didn't, was not making the rCTE first the first CTE in the chain. If you see a better way of getting there, I'm wide open to suggestions (and always impressed with your solutions, but you know that already!).
Thanks
-
Hi Joe,
I did take steps to normalize it. The data as I presented it is how I received it in a load file. Not making excuses, but I didn't want the way I normalized it to restrict anyone's solution. I presented it as I received it because I am able to duplicate whatever schema suits the best solution. If that ends up being 1NF, then good, because I already have it normalized that way.
Also, I did read a couple articles of yours that I thought might be relevant HERE[/url], and citing the pilot example HERE[/url]. There was another article of yours that I recalled, about finding workers with certain skills that match up to job postings, but I couldn't find it off the bat.
While I did find the Relational Division article helpful and informative, I wasn't sure how to best apply it as I need it, in what I've started calling a KOTH hierarchy situation. Like a King of The Hill match, any team can be on top, and the hierarchy has to be built based on that, recursively. Does that clarify?
Thanks
-
Cool, thanks Joe. I'll see what I can come up with based on this.
-
If this is Romley's division (I'm sorry but I did not recognize it as such, which of course doesn't mean that it is not), Peter Larsson proposes a faster method in the comments posted near the bottom of this article:
High Performance Relational Division in SQL Server[/url]
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
I think that is the first time a DML has made me salivate;
I'm off home now to Select * from Bar
:sick:
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply