sql - 按可互换使用的值对分组

Question

所以，基本上我想计算唯一 ID 对集的数量。这是交互式演示。

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=aa5e732b8e980358a84fa255e83114b5

数据:

<表类="s-表"><头>发件人接收者交易ID<正文>1234567811234567845678123455678123495678987612987656787

期望的输出:

<表类="s-表"><头>发件人接收者交易数量<正文>123456784567898762

或者:

<表类="s-表"><头>发件人接收者交易数量<正文>567812344987656782

Answer 1

使用LEAST 和GREATEST:

SELECT LEAST(sender, receiver) AS sender,
       GREATEST(sender, receiver) AS receiver,
       COUNT(*) AS transaction_count
FROM   table_name
GROUP BY
       LEAST(sender, receiver),
       GREATEST(sender, receiver)

其中，对于您的示例数据:

CREATE TABLE table_name (SENDER, RECEIVER, TRANSACTION_ID) AS
SELECT 1234, 5678,  1 FROM DUAL UNION ALL
SELECT 1234, 5678,  4 FROM DUAL UNION ALL
SELECT 5678, 1234,  5 FROM DUAL UNION ALL
SELECT 5678, 1234,  9 FROM DUAL UNION ALL
SELECT 5678, 9876, 12 FROM DUAL UNION ALL
SELECT 9876, 5678,  7 FROM DUAL;

输出:

SENDER	RECEIVER	TRANSACTION_COUNT
5678	9876	2
1234	5678	4

db<> fiddle here