试图找到购买品牌1，只有品牌2的客户以及刚刚购买品牌1和2的客户

Question

我正在尝试在每个品牌中找到独特的客户，并试图找到哪些客户购买了两个品牌。

以下是我正在提起的查询：

WITH GROUPS AS (   
    SELECT 
     individual_id
    ,CASE WHEN COUNT(DISTINCT FML) = 1 THEN 'only '|| MAX(FML)
              ELSE 'cross'
              END AS GROUPS
    FROM DM_OWNER.transaction_detail_mv A JOIN sl_d1fml B ON A.SKU = B.SKU
    GROUP BY 
    individual_id
    )
    SELECT  
    g.GROUPS
    ,COUNT(DISTINCT t.individual_id) AS countIndv
    ,SUM(t.dollar_value_us)
    ,COUNT(t.transaction_number)
    ,SUM(t.quantity)
    FROM  DM_OWNER.transaction_detail_mv t
    JOIN GROUPS g
    ON t.individual_id = g.individual_id
    JOIN sl_d1fml C ON T.SKU = C.SKU
    WHERE BRAND_ORG_CODE = 'HT'
    AND t.is_merch = 1
    AND t.Line_Item_Amt_Type_Cd = 'S'
    AND TRUNC (t.TXN_DATE) between '01-JAN-18' AND '31-JAN-18'
    GROUP BY g.GROUPS;

我只是想知道查询是否正确，如果有2个以上的FML

Answer 1

first 获取个人的所有品牌。

在较旧的数据库中，我使用了wmsys.wm_concat，但这似乎已经过时了，被listagg

取代

这是一个在您的dbfiddle中工作的查询。您可以添加到它上以获取所需的其余列。

select individual_id, 
rtrim( regexp_replace((listagg(brand_org_code , '-') 
WITHIN GROUP (ORDER BY brand_org_code)), 
           '([^-]*)(-1)+($|-)', 
           '13'),
         '-') as brands, 
sum(dollar_value_us) dollar_sum
from transaction_detail_mv 
group by individual_id

第二个将其用作子问题。

select brands, sum(dollar_sum)
from (select individual_id, 
rtrim( regexp_replace((listagg(brand_org_code , '-') 
WITHIN GROUP (ORDER BY brand_org_code)), 
           '([^-]*)(-1)+($|-)', 
           '13'),
         '-') as brands, 
sum(dollar_value_us) dollar_sum
from transaction_detail_mv 
group by individual_id) table1
group by brands