CFChart产生500错误

Question

我正在尝试创建一个查询查询图表，但无论我尝试什么，我都会收到500个错误。我最终将cfchart标记的范围缩小为问题，但在其中找不到任何语法错误，也找不到任何逻辑错误。

<cfquery name="getData" datasource="#dsn#">
   SELECT 
          ( SELECT Count(UserID) 
            FROM   Users 
            WHERE  StripeCustomerID IS NOT NULL
          ) AS 'Licenses'
          , COUNT(UserID) AS 'Registrations'
          , UserID AS 'User'
  FROM  Users
  WHERE DATEDIFF(hour, FirstContact, DateStamp) <= '12'
  GROUP BY UserID
</cfquery>
<cfquery name="queryOfQuery" dbtype="query">
   SELECT (Licenses/Registrations) AS Percent FROM getData
</cfquery>

<cfchart format="flash"> 
     <cfchartseries type="bar"
        query="queryOfQuery"
        itemcolumn="UserID"
        valuecolumn="Percent"  />
</cfchart>

示例数据

Users Table Sample Data
UserID    StripeCustomerID
35         cus_8LOSlmvEQcfg1g
36         cus_6ixCBe7aU1MJEt
50         NULL
60         cus_6ifLCNmR9rOBCl
69         NULL
70         NULL
72         NULL
73         NULL
74         NULL  

Answer 1

你的语法问题

SELECT (Licenses/Registrations) AS Percent FROM getData

Percent是一个保留关键字，因此您需要添加括号或单引号，以让SQL引擎知道您正在使用保留关键字作为列别名。

SELECT (Licenses/Registrations) AS [Percent] FROM getData

或

SELECT (Licenses/Registrations) AS 'Percent' FROM getData

您可以轻松地在原始数据集中添加百分比列。

SELECT 
    (SELECT Count(UserID) FROM Users WHERE StripeCustomerID IS NOT NULL)     AS     Licenses, 
    COUNT(UserID) AS Registrations,
    (SELECT Count(UserID) FROM Users WHERE StripeCustomerID IS NOT NULL) / COUNT(UserID) AS [Percent],
    UserID AS User
FROM Users
WHERE DATEDIFF(hour, FirstContact, DateStamp) <= '12'
GROUP BY UserID

另外我很好奇你想要什么许可证。你是想从所有的non null UserIds中得到DISTINCT UserId、count还是count ?其余的查询表明，USerId在用户表中重复自己(我觉得很奇怪)…所以 UserId5可能出现了2到3次，所以计数distinct会得到userid 5的1而不添加distinct会得到2或3。

registrations总是1吗?如果是这样，那么UserId是唯一的，您根本不需要在主查询中使用聚合。百分比的答案总是和1/# of Licenses一样.......

根据你的评论，这似乎是你想要的查询。

SELECT
    COUNT(UserId) AS Registrations
    ,SUM(CASE WHEN StripeCustomerId IS NOT NULL THEN 1 ELSE 0 END) AS Licenses
    ,CASE
       WHEN SUM(CASE WHEN StripeCustomerId IS NOT NULL THEN 1 ELSE 0 END) = 0 THEN 0
       ELSE COUNT(UserId) / SUM(CASE WHEN StripeCustomerId IS NOT NULL THEN 1 ELSE 0 END)
    END AS [Percent]
FROM
    Users
WHERE DATEDIFF(hour, FirstContact, DateStamp) <= '12'