mysql - 计算特定时间段的净推荐值

Question

我需要转换数据，因此它进入我的 BI 软件中。我在互联网上的某个地方找到了以下查询来计算净推荐值(NPS)，但是有一个问题 - 它计算所有数据并忽略 WHERE 语句，我在其中尝试指定年份和月份。这意味着，它需要所有年份的所有行并输出它们的计算结果。

SELECT NPS_Deployment_Type
     , CAST(((SUM(CASE WHEN NPSRating BETWEEN 9 AND 10 THEN 1 ELSE 0 END) * 1.0 -
SUM(CASE WHEN NPSRating BETWEEN 0 AND 6 THEN 1 ELSE 0 END)) / COUNT(*) * 100) AS signed) NPS
     , count(*) as 'SR'
  from g360_relationship_survey_extract 
 where year(Survey_Response_Created_Date) = '2017' 
  AND month(Survey_Response_Created_Date) >= '10' 
 GROUP 
    BY NPS_Deployment_Type

注:净推荐值系统是客户体验调查，提出评分范围为 0 到 10(含)的问题。计算公式为:((回复10或9的人数)/所有回复-(回复0-6的人数)/所有回复) * 100

Answer 1

一些事情。

首先，使用

 WHERE Survey_Response_Created_Date >= '2017-10-01'
   AND Survey_Response_Created_Date <  '2017-11-01'

获取 2017 年 10 月的所有回复。这可以利用您列上的索引。

其次，为了确保您理解数据，请尝试首先创建详细子查询，然后对其进行汇总。例如，首先获取详细信息(IF 比 CASE 更容易使用):

           SELECT Survey_Response_Created_Date,
                  NPS_Deployment_Type,
                  IF(NPSRating BETWEEN 0 AND 10, 1,0) AS total,
                  IF(NPSRating BETWEEN 9 AND 10, 1,0) AS positive,
                  IF(NPSRating BETWEEN 0 AND  6, 1,0) AS negative
             FROM table

然后在外部查询中总结它们

  SELECT (100 * SUM(positive) - SUM(negative))/SUM(total) AS nps,
         SUM(total) AS responses,
         LAST_DAY(Survey_Response_Created_Date) month_ending,
         NPS_Deployment_Type
    FROM (
           SELECT Survey_Response_Created_Date,
                  NPS_Deployment_Type,
                  IF(NPSRating BETWEEN 0 AND 10, 1,0) AS total,
                  IF(NPSRating BETWEEN 9 AND 10, 1,0) AS positive,
                  IF(NPSRating BETWEEN 0 AND  6, 1,0) AS negative
             FROM table
         ) detail
   WHERE Survey_Response_Created_Date >= '2017-10-01'
     AND Survey_Response_Created_Date <  '2017-11-01'
   GROUP BY LAST_DAY(Survey_Response_Created_Date, NPS_Deployment_Type

这样做的优点是，在尝试汇总您的个人调查回复之前，可以轻松验证它们。