Aggregate
SQL - 如果匹配,則在聚合數據上添加列
我有一個大致如下所示的訂單表
|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