试试这个查询。为了得到可靠的结果，聚集中的Order子句是必不可少的。

with transactions as(
select client_id,transaction_id
   ,string_agg( (item_id ||'('||cast(quantity as varchar) || ')') ,',' order by item_id) itemlist
from orders
group by client_id,transaction_id
)
select * 
  ,row_number()over(partition by itemlist order by client_id,transaction_id) rn
  ,count(*)over(partition by itemlist) cnt
from transactions
order by itemlist

cnt〉1的行具有相同的行（具有相同数量的相同项目）。
使用测试数据，为transaction_id1001 -1004和1005-1006找到2个相同的组（不是图片中的绿色）
结果是
| 客户端ID|事务ID|项目表|rn|碳纳米管|
| --------------|--------------|--------------|--------------|--------------|
| CL5555|一千零五|111（1）、222（2）|1|二|
| CL6666|一千零六|111（1）、222（2）|二|二|
| CL1111|一千零一|111（1）、222（2）、333（1）|1|二|
| CL4444|一千零四|111（1）、222（2）、333（1）|二|二|
| CL3333|一千零三|111（1）、222（2）、333（1）、444（1）|1|1|
| CL2222|一千零二|111（2）、222（1）、333（1）|1|1|
| CL6666|一千零七|333（1）|1|1|
测试数据

INSERT INTO orders (client_id,item_id,transaction_id,quantity)
values 
        ('CL1111','111','1001',1),
        ('CL1111','222','1001',2),
        ('CL1111','333','1001',1),
        ('CL2222','111','1002',2),
        ('CL2222','222','1002',1),
        ('CL2222','333','1002',1),
        ('CL3333','111','1003',1),
        ('CL3333','222','1003',2),
        ('CL3333','333','1003',1),
        ('CL3333','444','1003',1),
        ('CL4444','111','1004',1),
        ('CL4444','222','1004',2),
        ('CL4444','333','1004',1),
        ('CL5555','111','1005',1),
        ('CL5555','222','1005',2),
        ('CL6666','111','1006',1),
        ('CL6666','222','1006',2),
        ('CL6666','333','1007',1)
;

您的示例代码不包括数量！下面是一个代码，它会产生与表中包含的相同订单一样多的行：

sql = """
    with orders_mod as
    (
        select distinct
                "transaction_id",
                string_agg("item_id" || ' ' || "quantity"::text, ',') over (partition by "client_id") as "list_of_ordered_item_ids"
        from    orders
    )
    select distinct
            string_agg("transaction_id", ',') over (partition by "list_of_ordered_item_ids") as "list_of_the_same_orders",
            "list_of_ordered_item_ids"
    from    orders_mod
    ;
"""

df_SQL = pd.read_sql(sql, con)
df_SQL

请检查此查询，它在dbfiddle中返回正确的行

with agg as (
  SELECT client_id, 
         array_agg(array[item_id]) ai, array_agg(array[quantity]) aq
  from orders group by client_id 
  having count(distinct transaction_id) = 1 )
select client_id, ai items, aq quantities 
from (
  select client_id, ai , aq , count(1) over (partition by ai, aq) cnt
  from agg ) c
where cnt > 1

我不知道客户端有多个事务，但在您的示例中，客户端CL 5555和CL 6666不匹配，即使它们有相同的事务1005和1006。因此，这里只比较具有一个不同事务的客户端。

select
     transaction_id
from
     (
     select
        transaction_id,
        ai,
        aq,
        count(1) over (partition by ai, aq) cnt
     from
          (
          select
               transaction_id,
               array_agg(array[item_id]) ai,
               array_agg(array[quantity]) aq
          from orders
          group by transaction_id
          ) a
     ) a
where cnt > 1