忠诚度与非忠诚度客户数量及销售SQL

Question

我有两个表，一个是事务详细表，如下所示：

TXN_DATE    INDIVIDUAL_ID   TRANSACTION_NUMBER  ITEM_NUM    SLS    ACT_TRANSACTION_ID
1/2/2019          1               12345           326       $10     123366777
1/2/2019          1               12345           112       $20     123366777
1/2/2019          1               12345           554       $30     123366777 
2/3/2019          1               35565           144       $40     655544444 
2/3/2019          1               35565           585       $20     655544444 
4/3/2019          4               88567           987       $20     656697778 
4/3/2019          4               88567           788       $15     656697778 
5/3/2019          4               22456           659       $10     366655544 
5/3/2019          4               22456           781       $60     366655544 
1/23/2019         5               89464           364       $40     647778777
1/23/2019         5               89464           564       $30     647778777
4/3/2019          6               32224           891       $20     987779899   
4/3/2019          6               32224           345       $30     987779899

另一个是事务表，如下所示：

TXN_DATE    INDIVIDUAL_ID   TRANSACTION_NUMBER  SLS Loyalty_Number    ACT_TRANSACTION_ID
1/2/2019         1                12345         $60     987654     123366777
2/3/2019         1                35565         $60     null       655544444
4/3/2019         4                88567         $35     234456     656697778
5/3/2019         4                22456         $70     null       366655544
1/23/2019        5                89464         $70     655498     647778777
4/3/2019         6                32224         $50     556645     987779899

有第三表忠诚度，所以如果客户是忠诚的成员，那么他将在下表。

INDIVIDUAL_ID   Loyalty_number  
1                 987654            
4                 234456            
5                 655498           
6                 556645

我试图找出顾客的数量和他们的销售忠诚度与不忠诚度的关系。下面是我使用的查询：

SELECT GROUPS,
    COUNT(DISTINCT Customer_ID) COUNT_OF_CUSTOMERS,
    SUM(DOLLAR_VALUE_US) DOLLAR_VALUE_US,
    SUM(COUNT_OF_TRANSACTIONS) COUNT_OF_TRANSACTIONS

    FROM (
SELECT DISTINCT
             Customer_ID,
             CASE WHEN COUNT(DISTINCT LOYALTY) = 1 THEN 'ONLY' || ' ' || MAX(LOYALTY)
             ELSE 'CROSS'
             END AS GROUPS,
             SUM (DOLLAR_VALUE_US)             DOLLAR_VALUE_US,
             COUNT (DISTINCT TRANSACTION_NUMBER) COUNT_OF_TRANSACTIONS ---ADDED DISTINCT
        FROM (SELECT A.Customer_ID,
              CASE WHEN CARD_NUMBER IS NULL THEN 'NONLOYALTYTXN'
              WHEN CARD_NUMBER IS NOT NULL THEN 'LOYALTYTXN' END AS LOYALTY,
                     DOLLAR_VALUE_US,
                     TXN_DATE,
                     A.TRANSACTION_NUMBER

                FROM transaction_detail A JOIN transaction B ON A.ACT_TRANSACTION_ID = B.ACT_TRANSACTION_ID)
       WHERE TRUNC (TXN_DATE) BETWEEN '01-JAN-19' AND '31-DEC-19'
       AND Customer_ID IS NOT NULL
    GROUP BY Customer_ID)

    GROUP BY GROUPS;

通过使用这个查询，我得到了三个桶: 1)只忠诚，2)不忠诚，3)交叉

有没有办法找到忠诚和不忠诚的数字？因为有些情况下，客户在结帐时可能没有给出他们的忠诚度(例如，客户1和4)。我不知道如何处理这件事。

Answer 1

如果我推测忠诚与拥有一张卡片有关，那就意味着：

SELECT (CASE WHEN t.Card_Number IS NOT NULL THEN 'Loyal' ELSE 'Disloyal'
        END) as grp,
       COUNT(DISTINCT t.individual_id) as num_customers,
       SUM(sls) as sales
FROM transaction t
GROUP BY (CASE WHEN t.Card_Number IS NOT NULL THEN 'Loyal' ELSE 'Disloyal'
          END)

Answer 2

你只需要第二张桌子就可以了：

select 
count(distinct(INDIVIDUAL_ID)),
sum(case when Card_Number is null then SLS else 0 end) as non_loyalty_sum,
sum(case when Card_Number is not null then SLS else 0 end) as loyalty_sum
from transactions;