We took a random customer from the AW database named Zachary Hughes (CustomerID 12203) in order to build and test our function. We’ll continue to use our pal Zack in this blog entry also.
We left off with looking at the products suggested to Zack when he’s looking at ProductID 777:
select * from dbo.ufn_GetSuggProducts(777,12203)So it occurred to me that Zack has bought several products in the past. Just because I’m a data nerd, I was wondering what kind of suggested products come up if we were to “feed” those previously-purchased products to our function for Zack?
/*
SuggProdID
-----------
872
934
878
*/
First we have to find out what Zack has purchased. Let’s use a Common Table Expression (CTE) called CustProds to get all the distinct products that Zack has bought, and we’ll JOIN in the Product table so we can see the names of those products.
declare @CustomerID intNow for each of those products, we’ll “feed” that product into our function via a CROSS APPLY operator. And we’ll JOIN in the Product table once again so we can also see the names of those suggested products:
set @CustomerID=12203 --Our pal Zack!
;with CustProds as
(
select distinct ProductID
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID
where CustomerID=@CustomerID
)
select CustProdID=cp.ProductID
,CustProdName=p1.Name
from CustProds cp
join Production.Product p1 on cp.ProductID=p1.ProductID
/*
CustProdID CustProdName
----------- --------------------------
711 Sport-100 Helmet, Blue
712 AWC Logo Cap
713 Long-Sleeve Logo Jersey, S
870 Water Bottle - 30 oz.
871 Mountain Bottle Cage
873 Patch Kit/8 Patches
874 Racing Socks, M
921 Mountain Tire Tube
929 ML Mountain Tire
930 HL Mountain Tire
*/
declare @CustomerID intEach of Zack’s original products produced its own list of 3 suggested products. So, for example, looking at the Racing Socks (ProductID 874) that Zack had purchased, it looks like Customers who bought that item also bought a Mountain Fender Set and/or a Road Tire Tube, and/or a Red Sport Helmet. (Remember that the suggested products that our function produces are items that Zack himself has never purchased before).
set @CustomerID=12203 --Our pal Zack!
;with CustProds as
(
select distinct ProductID
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID
where CustomerID=@CustomerID
)
select CustProdID=cp.ProductID
,CustProdName=p1.Name
,SuggProdID
,SuggProdName=p2.Name
from CustProds cp
join Production.Product p1 on cp.ProductID=p1.ProductID
cross apply dbo.ufn_GetSuggProducts(cp.ProductID,@CustomerID) sp
join Production.Product p2 on sp.SuggProdID=p2.ProductID
/*
CustProdID CustProdName SuggProdID SuggProdName
----------- -------------------------- ----------- -----------------------
711 Sport-100 Helmet, Blue 922 Road Tire Tube
711 Sport-100 Helmet, Blue 923 Touring Tire Tube
711 Sport-100 Helmet, Blue 878 Fender Set - Mountain
712 AWC Logo Cap 707 Sport-100 Helmet, Red
712 AWC Logo Cap 922 Road Tire Tube
712 AWC Logo Cap 878 Fender Set - Mountain
713 Long-Sleeve Logo Jersey, S 922 Road Tire Tube
713 Long-Sleeve Logo Jersey, S 878 Fender Set - Mountain
713 Long-Sleeve Logo Jersey, S 707 Sport-100 Helmet, Red
870 Water Bottle - 30 oz. 707 Sport-100 Helmet, Red
870 Water Bottle - 30 oz. 708 Sport-100 Helmet, Black
870 Water Bottle - 30 oz. 878 Fender Set - Mountain
871 Mountain Bottle Cage 878 Fender Set - Mountain
871 Mountain Bottle Cage 707 Sport-100 Helmet, Red
871 Mountain Bottle Cage 708 Sport-100 Helmet, Black
873 Patch Kit/8 Patches 877 Bike Wash - Dissolver
873 Patch Kit/8 Patches 878 Fender Set - Mountain
873 Patch Kit/8 Patches 707 Sport-100 Helmet, Red
874 Racing Socks, M 878 Fender Set - Mountain
874 Racing Socks, M 922 Road Tire Tube
874 Racing Socks, M 707 Sport-100 Helmet, Red
921 Mountain Tire Tube 708 Sport-100 Helmet, Black
921 Mountain Tire Tube 707 Sport-100 Helmet, Red
921 Mountain Tire Tube 878 Fender Set - Mountain
929 ML Mountain Tire 708 Sport-100 Helmet, Black
929 ML Mountain Tire 707 Sport-100 Helmet, Red
929 ML Mountain Tire 878 Fender Set - Mountain
930 HL Mountain Tire 708 Sport-100 Helmet, Black
930 HL Mountain Tire 707 Sport-100 Helmet, Red
930 HL Mountain Tire 878 Fender Set - Mountain
*/
Note that there are many repeated suggestions here. That makes me think of something. If we take a Customer’s purchasing history, we can come up with a list of (distinct) suggested products based on that and promote them to the customer when he logs into the AW website under a heading “You may be interested in the following products…” We’ve got to let the customer know that he’s sort of “missing out” on all this wonderful stuff that other people have bought but he hasn’t.
So let’s come up with yet another table function that will accept a CustomerID and will come up with some items to promote to the customer based on his purchasing history.
Continuing on with Zack as our
declare @CustomerID intLooks like that Mountain Fender Set is something that Zack really needs, huh?
set @CustomerID=12203 --Our pal Zack!
;with CustProds as
(
select distinct ProductID
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID
where CustomerID=@CustomerID
)
,SuggProds as
(
select SuggProdID
from CustProds cp
cross apply dbo.ufn_GetSuggProducts(cp.ProductID,@CustomerID) sp
)
select SuggProdID
,SuggProdName=p.Name
,NumOccurs=count(*)
from SuggProds sp
join Production.Product p on sp.SuggProdID=p.ProductID
group by SuggProdID
,p.Name
order by count(*) desc
/*
SuggProdID SuggProdName NumOccurs
----------- ----------------------- -----------
878 Fender Set - Mountain 10
707 Sport-100 Helmet, Red 9
708 Sport-100 Helmet, Black 5
922 Road Tire Tube 4
923 Touring Tire Tube 1
877 Bike Wash - Dissolver 1
*/
But notice that the list came up with 2 different Helmets… one red and one black. There are also two kinds of Tire Tubes. We don’t really want our list of promo products to be repetitive like that. In general, I’m guessing that we don’t really want to repeat items of the same Product SubCategory.
Let’s add the Product SubCategory to our query so we can analyze this further:
declare @CustomerID intYep, just as we thought… the two helmets are from SubCategory #31 and the Tire Tubes are from SubCategory #37. I think we should pull out the top product (based on the highest number of occurrences) from each SubCategory.
set @CustomerID=12203 --Our pal Zack!
;with CustProds as
(
select distinct ProductID
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID
where CustomerID=@CustomerID
)
,SuggProds as
(
select SuggProdID
from CustProds cp
cross apply dbo.ufn_GetSuggProducts(cp.ProductID,@CustomerID) sp
)
select SuggProdID
,SuggProdName=p.Name
,SuggSubCatg=p.ProductSubCategoryID
,NumOccurs=count(*)
from SuggProds sp
join Production.Product p on sp.SuggProdID=p.ProductID
group by SuggProdID
,p.Name
,p.ProductSubCategoryID
order by count(*) desc
/*
SuggProdID SuggProdName SuggSubCatg NumOccurs
----------- ----------------------- ----------- -----------
878 Fender Set - Mountain 30 10
707 Sport-100 Helmet, Red 31 9
708 Sport-100 Helmet, Black 31 5
922 Road Tire Tube 37 4
923 Touring Tire Tube 37 1
877 Bike Wash - Dissolver 29 1
*/
We can easily do that using the ROW_NUMBER() function. Let’s just see what happens when we add that window function to our query, doing a PARTITION BY the SubCategoryID and ORDERing BY the Number of Occurrences (which is just COUNT(*)) in descending order:
declare @CustomerID intAll the items that got assigned RowNum=1 are the ones we should present as our Promo Products. So let’s put our main query into its own CTE (which we’ll call SuggSummary), and we’ll pull out all rows with RowNum=1 from that:
set @CustomerID=12203 --Our pal Zack!
;with CustProds as
(
select distinct ProductID
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID
where CustomerID=@CustomerID
)
,SuggProds as
(
select SuggProdID
from CustProds cp
cross apply dbo.ufn_GetSuggProducts(cp.ProductID,@CustomerID) sp
)
select SuggProdID
,SuggProdName=p.Name
,SuggSubCatg=p.ProductSubCategoryID
,NumOccurs=count(*)
,RowNum=row_number() over (partition by p.ProductSubCategoryID
order by count(*) desc)
from SuggProds sp
join Production.Product p on sp.SuggProdID=p.ProductID
group by SuggProdID
,p.Name
,p.ProductSubCategoryID
order by count(*) desc
/*
SuggProdID SuggProdName SuggSubCatg NumOccurs RowNum
----------- ----------------------- ----------- ----------- ------
878 Fender Set - Mountain 30 10 1
707 Sport-100 Helmet, Red 31 9 1
708 Sport-100 Helmet, Black 31 5 2
922 Road Tire Tube 37 4 1
923 Touring Tire Tube 37 1 2
877 Bike Wash - Dissolver 29 1 1
*/
declare @CustomerID intLooks good! We’ve got all of our Promo Products from different SubCategories, so there’s a good variety of items here.
set @CustomerID=12203 --Our pal Zack!
;with CustProds as
(
select distinct ProductID
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID
where CustomerID=@CustomerID
)
,SuggProds as
(
select SuggProdID
from CustProds cp
cross apply dbo.ufn_GetSuggProducts(cp.ProductID,@CustomerID) sp
)
,SuggSummary as
(
select SuggProdID
,SuggProdName=p.Name
,SuggSubCatg=p.ProductSubCategoryID
,NumOccurs=count(*)
,RowNum=row_number() over (partition by p.ProductSubCategoryID
order by count(*) desc)
from SuggProds sp
join Production.Product p on sp.SuggProdID=p.ProductID
group by SuggProdID
,p.Name
,p.ProductSubCategoryID
)
select SuggProdID
,SuggProdName=SuggProdName
,SuggSubCatg
from SuggSummary
where RowNum=1
order by NumOccurs desc
/*
SuggProdID SuggProdName SuggSubCatg
----------- ----------------------- -----------
878 Fender Set - Mountain 30
707 Sport-100 Helmet, Red 31
922 Road Tire Tube 37
877 Bike Wash - Dissolver 29
*/
I think we can go ahead and create our function now. Just so we don’t overwhelm the customer once he signs on to the AW website, we’ll limit our Promo Products to 5 items. So that means adding a TOP (5) to our main query. Since our function is only going to return a single column of the Promo ProductID’s, we don’t need the Product.Name column in our SuggSummary CTE anymore… it was just there so we could see the descriptions as we were building our solution. Here’s the final function:
if object_id('ufn_GetPromoProducts') is not null drop function dbo.ufn_GetPromoProductsSo now let’s test out the function with Zack’s CustomerID:
go
create function dbo.ufn_GetPromoProducts
(
@CustomerID int
)
returns table
as
return
with CustProds as
(
select distinct ProductID
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID
where CustomerID=@CustomerID
)
,SuggProds as
(
select SuggProdID
from CustProds cp
cross apply dbo.ufn_GetSuggProducts(cp.ProductID,@CustomerID) sp
)
,SuggSummary as
(
select SuggProdID
,SuggSubCatg=p.ProductSubCategoryID
,NumOccurs=count(*)
,RowNum=row_number() over (partition by p.ProductSubCategoryID
order by count(*) desc)
from SuggProds sp
join Production.Product p on sp.SuggProdID=p.ProductID
group by SuggProdID
,p.ProductSubCategoryID
)
select top (5) PromoProdID=SuggProdID
from SuggSummary
where RowNum=1
order by NumOccurs desc
select * from dbo.ufn_GetPromoProducts(12203)Yep, those are the ones. And what if we take another customer… any customer… how about Elizabeth Johnson (Customer 11004)? What would we promote to her when she logs on to the AW website?
/*
PromoProdID
-----------
878
707
922
877
*/
select * from dbo.ufn_GetPromoProducts(11004)So we built a pretty sophisticated and complicated function here in a step-by-step manner, building from the ground up. Our new function involves multiple CTEs and multiple JOINs and a CROSS APPLY function call to yet another function that employs many JOINs and WHERE predicates.
/*
PromoProdID
-----------
870
921
711
*/
If we were to expand everything we did into one giant query (converting the CTEs to derived tables), this is what it looks like:
select top (5) PromoProdID=SuggProdIDThat looks pretty intimidating, but the path to getting there was quite easy, taking it one step at a time.
from
(
select SuggProdID
,SuggSubCatg=p.ProductSubCategoryID
,NumOccurs=count(*)
,RowNum=row_number()
over (partition by p.ProductSubCategoryID
order by count(*) desc)
from
(
select SuggProdID
from
(
select distinct ProductID
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod
on soh.SalesOrderID=sod.SalesOrderID
where CustomerID=@CustomerID
) cp
cross apply
(
select top (3) SuggProdID=d2.ProductID
from Sales.SalesOrderDetail d1
join Sales.SalesOrderHeader h1
on d1.SalesOrderID=h1.SalesOrderID
join Sales.Customer c
on h1.CustomerID=c.CustomerID
join Sales.SalesOrderHeader h2
on h1.CustomerID=h2.CustomerID
join Sales.SalesOrderDetail d2
on h2.SalesOrderID=d2.SalesOrderID
join Production.Product p
on d2.ProductID=p.ProductID
join Production.ProductSubCategory sc
on p.ProductSubCategoryID=sc.ProductSubCategoryID
where d1.ProductID=cp.ProductID
and h1.CustomerID<>@CustomerID
and c.CustomerType='I'
and p.ProductSubCategoryID<>(select ProductSubCategoryID
from Production.Product
where ProductID=cp.ProductID)
and sc.ProductCategoryID in (3,4)
and d2.ProductID not in (select ProductID
from Sales.SalesOrderDetail sod
join Sales.SalesOrderHeader soh
on sod.SalesOrderID=soh.SalesOrderID
where CustomerID=@CustomerID)
group by d2.ProductID
having count(distinct h2.SalesOrderID)>5
order by count(distinct h2.SalesOrderID) desc
) ufn_GetSuggProds
) sp
join Production.Product p on sp.SuggProdID=p.ProductID
group by SuggProdID
,p.ProductSubCategoryID
) SuggSummary
where RowNum=1
order by NumOccurs desc
Posts
No comments:
Post a Comment