聚合行标签

Question

下面的代码按照账号所属的商店代码列出了账号，并在账号旁边放置了一个标签，如“已过期”、“活动”或“旧”。我希望能够创建一个由每个商店代码的摘要，显示他们有多少每种帐户类型，例如shop_thames =1旧，4活跃和10过期。我将如何实现这一点？

SELECT
shop_code,
CASE WHEN MAX(i_date) < DATEADD(day , - 180 , GETDATE()) THEN 'Expired' WHEN MAX(i_date) > DATEADD(day , - 30 , GETDATE())THEN 'Active' ELSE 'Old' END AS Account_Type,
account_id,
SUM(Revenue) AS Spend

FROM 
[company].[dbo].name

WHERE
Date_Reporting_LY = '2016'
AND  Total_Revenue > 0

GROUP BY
shop_code,
account_id

Answer 1

下面这样的代码应该可以做到：

select  shop_code, account_type, count(*)
  from (SELECT  shop_code,
               CASE WHEN MAX(i_date) < DATEADD(day , - 180 , GETDATE()) THEN 'Expired' WHEN MAX(i_date) > DATEADD(day , - 30 , GETDATE())THEN 'Active' ELSE 'Old' END AS Account_Type
          FROM  [company].[dbo].name    
         WHERE Date_Reporting_LY = '2016'
           AND  Total_Revenue > 0 ) x   
GROUP BY  shop_code, Account_Type

Answer 2

将您的查询主要保持原样，并将其包装在另一个select中：

SELECT shop_code, account_type, count(*)
FROM (
SELECT
shop_code,
CASE WHEN MAX(i_date) < DATEADD(day , - 180 , GETDATE()) THEN 'Expired' WHEN MAX(i_date) > DATEADD(day , - 30 , GETDATE())THEN 'Active' ELSE 'Old' END AS Account_Type,
account_id,
SUM(Revenue) AS Spend

FROM 
[company].[dbo].name

WHERE
Date_Reporting_LY = '2016'
AND  Total_Revenue > 0

GROUP BY
shop_code,
CASE WHEN MAX(i_date) < DATEADD(day , - 180 , GETDATE()) THEN 'Expired' WHEN MAX(i_date) > DATEADD(day , - 30 , GETDATE())THEN 'Active' ELSE 'Old' END
account_id
) x
GROUP BY 
shop_code,
account_type

对原始查询的一个更改- MySQL并不坚持在聚合函数之外按select中的所有元素进行分组，但这样做确实是最佳实践。如果按整个case语句进行分组，则可以避免按看不到的值进行聚合时出现的问题。