在MS SQL中找到3个项目关联规则 [英] Find 3 item association rules in MS SQL

问题描述

数据库:

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

和L2表:

and L2 table:

PRODUCTID1  PRODUCTID2
   1000       1001
   1000       1002

我想自己加入L2.
前任;第一行{1000,1001}和第二行{1000,1002}-结果{1000,1001,1002}

如何用T-SQL语句找到此结果?并从我的交易表中找到support(Count)值?我的意思是一起售出的{1000,1001,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:

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.

推荐答案

您要为每笔交易计算产品还是要对产品进行支配?

我在MS SQL Server 2005 Express Edition上创建了A_TEST数据库，其中包含带有某些值的事务"表.

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.

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)

以下是每个EXECUTE(query)命令的结果:
1)全球产品数量
OrderID | CountOfProducts
------------------------------
1 | 1
2 | 2
3 | 2
4 | 2
5 | 4

2)每个ProductId的产品计数
OrderID |产品编号| 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)每个OrderId的ProductId的枢轴和计数
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)总计
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

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

这篇关于在MS SQL中找到3个项目关联规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！