Aggregate

SQL - 如果匹配,則在聚合數據上添加列

  • December 21, 2016

我有一個大致如下所示的訂單表

|Customer id |Date          | revGBP |orderID  |GiftOrder    |
---------------------------------------------------------------------
|1           |1-9-2014      |350     |1        | gift        |
---------------------------------------------------------------------
|2           |1-6-2014      |250     |2        | notGift     |
---------------------------------------------------------------------
|3           |1-9-2014      |200     |3        | gift        |
---------------------------------------------------------------------
|3           |4-9-2014      |200     |4        | notgift     |
---------------------------------------------------------------------
|1           |9-9-2014      |200     |5        | notgift     |
---------------------------------------------------------------------
|2           |1-9-2014      |200     |6        | notgift     |

我想使用聚合函式進行查詢

Select
customer_id,
MAX(Date) AS LastOrderDate,
MIN(Date) AS FirstOrderDate,
COUNT(orderID) AS CountOfOrders,
ROUND(SUM(revGBP),2) AS TotalCustSpend,
FROM
Workings.om
Group By customer_email

這可行,但是我想另外添加客戶是否曾經進行過禮品交易,因此如果客戶 1 的訂單中包含 GiftOrder 中的單詞 Gift,請將其添加為列,否則聲明 notGift。

這可能嗎?

您可以使用以下任一方式添加禮物數量:

SELECT ..., 
   COUNT(CASE WHEN GiftOrder='gift' THEN 1 END) AS number_of_gifts

或類似的(僅適用於 MySQL):

SELECT ..., 
   SUM(GiftOrder='gift') AS number_of_gifts

如果您想要一個簡單(是/否)的問題,如果客戶曾經做過一件或多件禮物,而根本沒有,那麼:

SELECT ..., 
   CASE WHEN COUNT(CASE WHEN GiftOrder='gift' THEN 1 END) > 0 
       THEN 'Gift'
       ELSE 'Not Gift' 
   END AS gifts

引用自:https://dba.stackexchange.com/questions/78899