Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1314151617

Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle) Expand / Collapse
Author
Message
Posted Friday, June 13, 2014 11:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
Ed Wagner (6/13/2014)
As always, an excellent article, Jeff. Then again, we've come to expect nothing less.


Thanks, Ed. Better than the article, though, look at the great discussions going on. Lotsa good people with good ideas. That's why I love this place. Ya just gotta love this community!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1580659
Posted Friday, June 13, 2014 4:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Do we have a readymade 10 million/1million row generator for this problem?
Post #1580746
Posted Friday, June 13, 2014 4:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Now that we have new analytical functions, just wanted to jump in on the new windowed functions fun :)

; WITH CTE AS
(
SELECT P.CustomerID ,P.ProductCode
, CODE = CASE WHEN ( P.ProductCode = 'B'
AND LAG (P.ProductCode, 1, 0) OVER (PARTITION BY P.CustomerID ORDER BY P.ProductCode) = 'A'
AND LEAD (P.ProductCode, 1, 0) OVER (PARTITION BY P.CustomerID ORDER BY P.ProductCode) <> 'C' ) THEN 1
ELSE 0
END
FROM #Purchase P
WHERE P.ProductCode in ('A','B', 'C')
GROUP BY P.CustomerID ,P.ProductCode
)
SELECT CustomerID
FROM CTE

Post #1580750
Posted Friday, June 13, 2014 4:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
ColdCoffee (6/13/2014)
Now that we have new analytical functions, just wanted to jump in on the new windowed functions fun :)

; WITH CTE AS
(
SELECT P.CustomerID ,P.ProductCode
, CODE = CASE WHEN ( P.ProductCode = 'B'
AND LAG (P.ProductCode, 1, 0) OVER (PARTITION BY P.CustomerID ORDER BY P.ProductCode) = 'A'
AND LEAD (P.ProductCode, 1, 0) OVER (PARTITION BY P.CustomerID ORDER BY P.ProductCode) <> 'C' ) THEN 1
ELSE 0
END
FROM #Purchase P
WHERE P.ProductCode in ('A','B', 'C')
GROUP BY P.CustomerID ,P.ProductCode
)
SELECT CustomerID
FROM CTE



What's the performance look like compared to the other methods?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1580755
Posted Friday, June 13, 2014 4:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
ColdCoffee (6/13/2014)
Do we have a readymade 10 million/1million row generator for this problem?


Heh... apparently, you didn't read the article where is says...

The code above isn't adequate for performance testing. For those that want to explore and compare solutions of their own, I've attached code to build a million row test table at the bottom of this article in the "Resources" link.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1580757
Posted Monday, June 16, 2014 8:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 2,329, Visits: 2,662
Thanks, Jeff, for this great article.

I have one small editorial comment.

In the section Filtering for "2" Products, the query is the one below:

--===== Find Customers that bought either "A" OR "B"
-- and count the DISTINCT number of products each bought.
-- The filter in the WHERE clause causes an error here.
SELECT CustomerID,
FROM #Purchase
WHERE ProductCode IN ('A','B')
AND COUNT(DISTINCT ProductCode) = 2
GROUP BY CustomerID
;

The error is supposed to be Msg 147, Level 15, State 1, Line 1.... But the error returned when the code above is run is:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'FROM'.

Because, I think, of the comma after CustomerID in the SELECT clause.

Not a huge deal, but given how precise your articles and comments are, I think it will help those following the steps.

Thanks as always for such detailed and informative work!!

-webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1582008
Posted Tuesday, June 17, 2014 2:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Jeff Moden (6/13/2014)
ColdCoffee (6/13/2014)
Do we have a readymade 10 million/1million row generator for this problem?


Heh... apparently, you didn't read the article where is says...

The code above isn't adequate for performance testing. For those that want to explore and compare solutions of their own, I've attached code to build a million row test table at the bottom of this article in the "Resources" link.



I read the article when ti was first published. after that, i was following the discussion
When i ran the code, the logical reads were less but the elapsed time was higher than mister.magoo's code. i dint do a extensive testing
Post #1582601
« Prev Topic | Next Topic »

Add to briefcase «««1314151617

Permissions Expand / Collapse