cfchart中可变值

Question

最新问题：

Leigh提供的查询工作正常，但由于某种原因，当我替换行和列的名称以将查询从每次单击成本更改为每次注册成本和每个许可证成本时，我将得到与预期不同的值。

备注：我下面列出的结果只适用于每个注册的成本，而不是每个许可证的成本。两者都来自同一张桌子，所以如果一张是固定的，很可能第二张也会跟着做。我还更新了AdReport表，以包括许可证列等。

如我所料

Date    CPR  
1      $31.35   
2      $61.42   
3      $77.85   
4      $78.48   
5      $55.11

我得到了什么

Date    CPR  
1       971.9412  
2       1781.2939  
3       2421.733  
4       2355.4679  
5       1598.164  

查询：

 <cfquery name="costPerRegistration" datasource="#dsn#">
SELECT ab.AdMonth AS Date, 
    CASE WHEN SUM(ar.Conversions) > 0 THEN SUM(ab.AdBudget) / SUM(ar.Conversions)
            ELSE 0
            END AS CPR
FROM AdBudget AS ab INNER JOIN AdReport AS ar
      ON  DATEPART(MONTH, ar.ReportDate) = ab.AdMonth
      AND DATEPART(YEAR, ar.ReportDate)  =  ab.AdYear
      AND ar.AdSourceID = ab.AdSourceID
    WHERE ab.AdYear = '2016' 
    AND ar.AdSourceID != 4
    GROUP BY ab.AdMonth
    ORDER BY ab.AdMonth
</cfquery>

<cfquery name="costPerLic" datasource="#dsn#">
SELECT  ab.AdMonth AS Date, 
    CASE WHEN SUM(al.Licenses) > 0 THEN CAST(SUM(ab.AdBudget)/SUM(al.Licenses) AS smallmoney)
            ELSE 0
            END AS CPL
FROM AdBudget AS ab INNER JOIN AdReport AS al 
      ON  DATEPART(MONTH,al.ReportDate) = ab.AdMonth 
      AND DATEPART(YEAR,al.ReportDate) = ab.AdYear  
      AND al.AdSourceID = ab.AdSourceID
WHERE   ab.AdYear = 2016 
AND     ab.AdSourceID != 4
GROUP BY ab.AdMonth
ORDER BY ab.AdMonth
</cfquery>

代码：

<cfloop index = "i" from = "1" to = "#AdBudget.RecordCount#"> 
    <cfset Clicks.Click[i] = AdBudget.Budgeting/Clicks.Click[i]> 
    <cfset Registrations.Conver[i] = AdBudget.Budgeting/Registrations.Conver[i]>
    <cfset Licenses.License[i] = AdBudget.Budgeting/Licenses.License[i]> 
</cfloop> 

<!--- Bar graph, from Query of Queries --->
<cfchart> 
    <cfchartseries type="curve" 
        seriescolor="##5283DA" 
        serieslabel="Cost per Clicks"
          <cfchartdata item="1" value="#Click#">
    </cfchart>
</cfchart>

数据：

添加示例数据，忽略表中的sourceID和其他ID。

AdBudgetID  AdBudget    AdMonth AdSourceID  AdYear
    1   7663    1   1   2016
    2   20301   2   1   2016
    3   5555    1   2   2016
    4   16442   2   2   2016
    5   1706    1   3   2016
    6   4841    2   3   2016
    7   11384   3   1   2016
    8   23726   3   2   2016
    9   9653    3   3   2016
    13  17557.98    5   1   2016
    14  25685.72    5   2   2016'

AdClickID   AdClicks    AdMonth AdSourceID  AdYear
1   2229    1   1   2016
2   1803    1   2   2016
3   371 1   3   2016
4   4940    2   1   2016
5   5855    2   2   2016
6   673 2   3   2016
7   2374    3   1   2016
8   12913   3   2   2016
9   1400    3   3   2016
13  2374    4   1   2016
14  10272   4   2   2016

   AdReportID   ReportDate  AdSourceID  Clicks  Conversions Demos   Clients    Licenses Onboardings AvgScore
2430    2016-03-27  1   1   1   0   0   0   0   NULL
2431    2016-03-27  2   5   0   0   0   0   0   NULL
2432    2016-03-27  3   1   0   0   0   0   0   NULL
2433    2016-03-27  5   24  0   0   0   0   0   NULL
2434    2016-03-27  6   0   0   0   0   0   0   NULL
2435    2016-03-27  6   0   0   0   0   0   NULL    NULL
2436    2016-03-27  4   0   1   0   0   0   1   NULL
2437    2016-03-26  1   2   0   0   0   0   0   NULL

对不起，表配置，不知道如何使它整洁。另外，我们有更多的转换(注册)和许可证，而这些转换和许可没有显示在示例数据中，只是碰巧的是，第一行~10行的数字很低。

Answer 1

如何设置图表以正确绘制图表

我会从这个问题开始，而不是提问。从一个小的硬编码图表开始，这是很容易调整的，以便准确地确定如何构造标签来生成所需的图表。最后的分组将决定如何聚合查询数据。

假设你想将2016年1月至5月的“每次点击成本”和“每次注册成本”作为单独的系列来绘制。创建这样一个硬编码示例：

<cfchart> 
    <cfchartseries type="curve" serieslabel="Cost per Clicks">
       <cfchartdata item="1" value="15">
       <cfchartdata item="2" value="50">
       <cfchartdata item="3" value="47">
       <cfchartdata item="4" value="32">
       <cfchartdata item="5" value="65">
    </cfchartseries>
    <cfchartseries type="curve" serieslabel="Cost per Registration">
       <cfchartdata item="1" value="45.52">
       <cfchartdata item="2" value="17.68">
       <cfchartdata item="3" value="28.50">
       <cfchartdata item="4" value="78.62">
       <cfchartdata item="5" value="42.50">
    </cfchartseries>
</cfchart>

生成以下图表：

​

​

查看cfchartdata标记，表明需要两个查询:一个按月包含“每次单击成本”，另一个按月包含“每个注册成本”。

若要计算每次单击的成本，请在月份、年份和源id列上加入AdBudget和AdClick。按月将结果分组，并将预算总额除以单击总数：

SQLFiddle示例：

<cfquery name="costPerClick" ....>
SELECT   ab.AdMonth
         , CASE WHEN SUM(ac.AdClicks) > 0 THEN SUM(ab.AdBudget) / SUM(ac.AdClicks)
                ELSE 0
           END AS CostPerClick
FROM    AdBudget ab LEFT JOIN AdClick ac 
          ON  ac.AdMonth = ab.AdMonth 
          AND ac.AdYear = ab.AdYear  
          AND ac.AdSourceID = ab.AdSourceID
WHERE   ab.AdYear = 2016 
AND     ab.AdSourceID <> 4
GROUP BY ab.AdMonth
ORDER BY ab.AdMonth
</cfquery>

然后简单地循环查询以生成cfchartseries：

<cfchart> 
    <cfchartseries type="curve" serieslabel="Cost per Clicks">
       <cfoutput query="costPerClick">
          <cfchartdata item="#costPerClick.AdMonth#" 
               value="#DecimalFormat(costPerClick.Amount)#">
       </cfoutput>
    </cfchartseries>
</cfchart>

使用上面的提示，您应该能够遵循相同的过程来生成另外两个查询和系列。

更新：

结果我误解了AdReport的结构。由于它包含每个月/年/源it组合的多个记录，因此首先需要按月计算总转换。然后将结果加入到AdBudget中。因此，“每次注册成本”查询需要如下所示：

SELECT  ab.AdMonth 
        , CASE WHEN SUM(ar.Conversions) > 0 THEN SUM(ab.AdBudget) / SUM(ar.Conversions)
               ELSE 0
          END AS CPR
FROM    AdBudget ab LEFT JOIN 
        (
            /* Calculate total conversions per month */
            SELECT AdSourceID
                    , DATEPART(MONTH, ReportDate) AS AdMonth
                    , DATEPART(YEAR, ReportDate) AS AdYear
                    , SUM(Conversions) AS Conversions
            FROM   AdReport
            --- first of desired year (2016) to first of next year (exclusive)
            WHERE  ReportDate >= '2016-01-01'
            AND    ReportDate < '2017-01-01'
            AND    AdSourceID <> 4
            GROUP BY AdSourceID
                     , DATEPART(MONTH, ReportDate)
                     ,  DATEPART(YEAR, ReportDate)
        ) 
        ar  ON  ar.AdMonth = ab.AdMonth 
            AND ar.AdYear = ab.AdYear  
            AND ar.AdSourceID = ab.AdSourceID           
GROUP BY ab.AdMonth
ORDER BY ab.AdMonth