Find 3 item association rules in MS SQL

Question

0.00/5 (No votes)

See more:

Database:

Transaction  ProductID
 1              1000
 2              1000
 2              1001
 3              1000
 3              1002
 4              1000
 4              1001
 5              1003

and L2 table:

PRODUCTID1  PRODUCTID2
   1000       1001
   1000       1002

I want to self join L2.
ex; first row{1000,1001} and second row {1000,1002}-->result {1000,1001,1002}

how to find this result with a T-SQL statement? And find support(Count) value from my Transaction table? I mean {1000,1001,1002} count which sold together.
not: it's just sample database. i've a big database for this.

+-------------+-----------+-----------------+---------+
| PRODUCTID1  | PRODUCTID2|   PRODUCTID3    | SUPPORT |
+-------------+-----------+-----------------+---------+
|      1000   |   1001    |       1002      |    0    |
+-------------+-----------+-----------------+---------+

Test Table:

SQL

create table transactions(
       ORDERID    INT,
       PRODUCTID INT
    );

    insert into transactions(ORDERID, PRODUCTID)
    values ('1', '1000')
          ,('2', '1000')
          ,('2', '1001')
          ,('3', '1000')
          ,('3', '1002')
          ,('4', '1000')
          ,('4', '1001'),
           ('5', '1003');
    CREATE TABLE L2
       (PRODUCTID1 INT,
        PRODUCTID2 INT)
    INSERT INTO L2 (PRODUCTID1,PRODUCTID2)
    VALUES (1000,1001),(1000,1002)

Thanks in advance.

Posted 5-May-11 10:03am

wide20

Updated 6-May-11 0:15am

Costica U

v2

Add a Solution

Comments

NuttingCDEF 5-May-11 16:10pm

Am I losing the plot? Or did I see a question pretty much identical to this earlier today?

wide20 5-May-11 16:34pm

i asked it again, because nobody answer it.

NuttingCDEF 5-May-11 16:43pm

Gentle hint - patience is a virtue . . .

Costica U 6-May-11 6:17am

You should show some work done by yourself, and maybe someone can help you. Don't expect somebody to write the complete solution for you.

Steven.Pinto2000 7-May-11 1:05am

i don't think the example which you have give is proper and i don't get you why do you need a self join there cant be any self join in this case

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2011-05-24T07:39:00

Do you want to count products for each transaction or you want to pivot products?

I have create A_TEST database on MS SQL server 2005 Express Edition, which contain 'Transaction' table with some values.

SQL

USE [A_TEST];

DECLARE @sqry NVARCHAR(2000)
DECLARE @mqry NVARCHAR(2000)
DECLARE @fqry NVARCHAR(2000)
DECLARE @cols NVARCHAR(2000)

--select all ProductsId
SET @cols = STUFF(( SELECT DISTINCT '],[' + CONVERT(NVARCHAR, [ProductId])
					FROM [dbo].[Transactions]
					ORDER BY '],[' + CONVERT(NVARCHAR, [ProductId]) 
					FOR XML PATH('')), 1, 2, '') + ']'

--count products for each order/transaction
SET @sqry = 'SELECT [OrderId], COUNT([OrderId]) AS [CountOfProducts] ' +
			'FROM [dbo].[Transactions] ' +
			'GROUP BY [OrderId]'
EXECUTE (@sqry)

--count products for each order/transaction
SET @sqry = 'SELECT [OrderId], [ProductId], COUNT([OrderId]) AS [CountOfProducts] ' +
			'FROM [dbo].[Transactions] ' +
			'GROUP BY [OrderId], [ProductId]'
EXECUTE (@sqry)

--pivot products for each orderid/transaction
SET @mqry = 'SELECT [OrderId], ' + @cols + ' ' +
			'FROM (' + @sqry + ') AS DT ' + 
			'PIVOT (COUNT(DT.[CountOfProducts]) FOR DT.[ProductId] IN (' + @cols + ')) AS PT ' + 
			'ORDER BY PT.[OrderId]'
EXECUTE (@mqry)

--pivot and sum of products in order (total)
SET @cols = STUFF((SELECT DISTINCT ']+S.[' + CONVERT(NVARCHAR, [ProductId])
                    FROM [dbo].[Transactions]
                    ORDER BY ']+S.[' + CONVERT(NVARCHAR, [ProductId])
            FOR XML PATH('')), 1, 2, '') + ']';
SET @fqry = 'SELECT S.*, (' + @cols + ') AS [SumOfProductsInOrder] ' +
            'FROM (' + @mqry + ') AS S '
EXECUTE(@fqry)

Here are the results for each EXECUTE(query) command:
1) global count of products
OrderID | CountOfProducts
------------------------------
1 | 1
2 | 2
3 | 2
4 | 2
5 | 4

2) count of products for each ProductId
OrderID | ProductId | CountOfProducts
--------------------------------------------------
1 | 1000 | 1
2 | 1000 | 1
3 | 1000 | 1
4 | 1000 | 1
5 | 1000 | 1
2 | 1001 | 1
4 | 1001 | 1
5 | 1001 | 1
3 | 1002 | 1
5 | 1002 | 1
5 | 1003 | 1

3) pivot and count of ProductId for each OrderId
OrderID | 1000 | 1001 | 1002 | 1003
--------------------------------------------------------
1 | 1 | 0 | 0 | 0
2 | 1 | 1 | 0 | 0
3 | 1 | 0 | 1 | 0
4 | 1 | 1 | 0 | 0
5 | 1 | 1 | 1 | 1

4) total
OrderID | 1000 | 1001 | 1002 | 1003 | SumOfProductsInOrder
-------------------------------------------------------------------------
1 | 1 | 0 | 0 | 0 | 1
2 | 1 | 1 | 0 | 0 | 2
3 | 1 | 0 | 1 | 0 | 2
4 | 1 | 1 | 0 | 0 | 2
5 | 1 | 1 | 1 | 1 | 4