oracle 按产品组合时的情况

oxcyiej7 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(84)

我的table上有很多产品。我希望能够分析有多少这些产品一起使用。我想展示这些产品中最常用的，我创建了一个表如下;我的table;

Customer_id  Product Count(product)     Product Amount  Total Amount per Customer
1               A      3                         10               20
1               B      3                          5               20
1               C      3                          5               20
2               D      2                         20               30
2               E      2                         10               30 
3               A      2                          5               35
3               F      2                         30               35
4               B      1                         40               40
5               D      2                         5                10  
5               E      2                         5                10
6               A      2                         3                6
6               F      2                         3                6

我想要的结果;

COUNT( DISTINCT CUSTOMER_ID)    SUM(TOTAL AMOUNT)  PRODUCT_COUNT  PRODUCT_BUNDLE
           1                       20                 3                A&B&C
           2                       40                 2                 D&E
           2                       41                 2                 A&F
           1                       10                 1                  B

oracle

来源：https://stackoverflow.com/questions/77156091/case-when-combination-by-product

1条答案

按热度按时间

xmjla07d1#

聚合两次：

SELECT COUNT(customer_id),
       product_count,
       SUM(total_amount),
       product_bundle
FROM   (
  SELECT customer_id,
         LISTAGG(product, '&') WITHIN GROUP (ORDER BY product) AS product_bundle,
         COUNT(product) AS product_count,
         SUM(product_amount) AS total_amount
  FROM   table_name
  GROUP BY customer_id
)
GROUP BY product_count, product_bundle

其中，对于样本数据：

create table table_name (Customer_id, Product, Product_Amount) AS
  SELECT 1, 'A', 10 FROM DUAL UNION ALL
  SELECT 1, 'B',  5 FROM DUAL UNION ALL
  SELECT 1, 'C',  5 FROM DUAL UNION ALL
  SELECT 2, 'D', 20 FROM DUAL UNION ALL
  SELECT 2, 'E', 10 FROM DUAL UNION ALL
  SELECT 3, 'A',  5 FROM DUAL UNION ALL
  SELECT 3, 'F', 30 FROM DUAL UNION ALL
  SELECT 4, 'B', 40 FROM DUAL UNION ALL
  SELECT 5, 'D',  5 FROM DUAL UNION ALL
  SELECT 5, 'E',  5 FROM DUAL UNION ALL
  SELECT 6, 'A',  3 FROM DUAL UNION ALL
  SELECT 6, 'F',  3 FROM DUAL;

输出：
| 用户名（CUSTOMER_ID）|产品中心|合计（TOTAL_AMOUNT）|产品_捆绑包|
| --|--|--|--|
| 1 | 3 | 20 |A&B&C|
| 2 | 2 | 40 |D&E|
| 2 | 2 | 41 |A&F|
| 1 | 1 | 40 |B|
如果你只需要bundle，那么在内部查询中使用HAVING COUNT(product) > 1（或者在外部查询中使用WHERE product_count > 1）。
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 按产品组合时的情况

1条答案

相关问题

热门标签

最新问答