如何在T-SQL语句中找到Apriori关联?
数据库:
Transaction ProductID
1 1000
2 1000
2 1001
3 1000
3 1002
4 1000
4 1001
5 1003
上表中,如何用T-SQL语句找到这个结果?
+-------------+-----------+-----------------+
| PRODUCTID1 | PRODUCTID2| SUPPORT |
+-------------+-----------+-----------------+
| 1000 | 1001 | 2 |
+-------------+-----------+-----------------+
| 1000 | 1002 | 1 |
+-------------+-----------+-----------------+
| 1000 | 1003 | 0 |
+-------------+-----------+-----------------+
| 1001 | 1002 | 0 |
+-------------+-----------+-----------------+
| 1001 | 1003 | 0 |
+-------------+-----------+-----------------+
| 1002 | 1003 | 0 |,
+-------------+-----------+-----------------+
测试表:
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');
提前致谢。
Database:
Transaction ProductID
1 1000
2 1000
2 1001
3 1000
3 1002
4 1000
4 1001
5 1003
In the above table, how to find this result with a T-SQL statement?
+-------------+-----------+-----------------+
| PRODUCTID1 | PRODUCTID2| SUPPORT |
+-------------+-----------+-----------------+
| 1000 | 1001 | 2 |
+-------------+-----------+-----------------+
| 1000 | 1002 | 1 |
+-------------+-----------+-----------------+
| 1000 | 1003 | 0 |
+-------------+-----------+-----------------+
| 1001 | 1002 | 0 |
+-------------+-----------+-----------------+
| 1001 | 1003 | 0 |
+-------------+-----------+-----------------+
| 1002 | 1003 | 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');
Thanks in advance.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
你能推断出零吗?即我们可以说没有列出的任何内容都是零吗?如果是这样:
输出:
Can you infer the zeros? i.e. can we just say that anything not listed is zero? if so:
With output: