postgresql - 在 PostgreSQL 中获取复杂的交叉销售细节

Question

我有一个问题，我需要找出从交叉销售产品中获得的收入的详细信息。例如，我想知道哪些是客户购买的与所选类别的类别组合。以及该客户在所选类别以外的其他类别上花费的金额。

下面是一个示例查询，它将为我提供组合和收入，如果组合计数为 2 个类别，则使用过滤子句我可以获得具有和不具有所选类别的类别的收入，但是当组合中有超过 2 个类别时我需要了解每个类别的费用是多少，我怎样才能得到它？

数据库：PostgreSQL 版本 11

WITH filter_2 as
    (
        SELECT DISTINCT 
                 o.order_number
               , o.client_email
               , COALESCE(category,'') AS category_name
               , SUM(revenue) as revenue
          FROM  
                     orders o
          INNER JOIN distinct_orders dos on dos.client_email=o.client_email
          LEFT JOIN items i ON i.order_id = o.order_id
          LEFT JOIN products p ON p.product_id = i.product_id 
      WHERE  o.state ='Done'
      GROUP BY o.order_number
    , o.client_email, COALESCE(category,'')
    )
    
    ,result_set_1 as
    (
        SELECT 
         f.order_number
        ,f.client_email
        ,string_agg(DISTINCT COALESCE(category,''), ' , ' ORDER BY COALESCE(category,'')) as cat_level_3_name
        ,COUNT(DISTINCT category) as prod_count
       
      ,SUM(revenue) revenue
        FROM filter_2 f
    GROUP BY f.order_number,f.client_email
    )
    SELECT 
    COUNT(DISTINCT order_number) as order_count,
    COUNT(DISTINCT client_email) as customer_count
    ,category
    ,SUM(revenue) as revenue
    FROM result_set_1 r
    WHERE category IS NOT NULL
    GROUP BY category
    ORDER BY order_count DESC

样本输入

Order_number   client_email     category  Revenue
"819214"    "[e-mail]"     "A Tea" 290.00
"819214"    "[e-mail]"     "B Tea" 10.00
"608759"    "[e-mail]"   "A Tea" 15.00
"608759"    "[e-mail]"   "B Tea" 20.00
"608759"    "[e-mail]"   "C Tea" 400.00
"237070"    "[e-mail]"    "A Tea" 60.0
"237070"    "[e-mail]"    "B Tea" 10.0
"508759"    "[e-mail]"   "A Tea" 15.00
"508759"    "[e-mail]"   "B Tea" 25.00
"508759"    "[e-mail]"   "C Tea" 45.00
"578759"    "[e-mail]"    "A Tea" 15.00
"588759"    "[e-mail]"    "A Tea" 15.00
"598759"    "[e-mail]"    "A Tea" 15.00

预期产出

因此，如果我想知道客户与“A Tea”一起购买了哪些所有组合类别，

Combinations         Customer count    Combination count   Revenue Split UP
A Tea                 7                   3 ( Or 7)       A Tee = 45 or (425)
A Tea, B Tea          2                   2               A Tea = 350 B Tea = 20
A Tea, B Tea, C Tea   2                   2               A Tea= 30  B Tea=45   C Tea = 445

标签： postgresql