项目的目标是检查文本区域字段的总字符数,并将转换率与历史数据进行比较。简单地说:X个人输入300个字符,我想看看以前输入260到340个字符的人的转换率。
以下是一个有效的MySQL查询:
SELECT
COUNT(*) AS total_count,
SUM(converted) AS total_converted,
(SUM(converted) / count(*)) * 100 AS conversion_rate, text_length
FROM my_table WHERE text_length IS NOT NULL
GROUP BY text_length div 40
这适用于较低的数字,但在某一点上(比如超过400个字符),我想把它们放在一起。有没有办法检查"text_length"是否超过某个值,然后只计算它上面的所有值?
此外,如果有人对更好的整体方法有任何建议,我也很乐意尝试。谢谢
使用UNION,您可以执行以下操作:
(SELECT
COUNT(*) AS total_count,
SUM(converted) AS total_converted,
(SUM(converted) / count(*)) * 100 AS conversion_rate, text_length
FROM my_table
WHERE text_length <= 400
GROUP BY text_length div 40)
UNION
(SELECT
COUNT(*) AS total_count,
SUM(converted) AS total_converted,
(SUM(converted) / count(*)) * 100 AS conversion_rate, 401 AS text_length
FROM my_table
WHERE text_length > 400)
请注意,我还删除了不再需要的WHERE text_length IS NOT NULL
。大于400的text_length将被分组为text_length401。
怎么样
GROUP BY (text_length > 400), CAST((text_length / 40) AS INTEGER)
这将使任何具有401+个字符的字符组合成一个单独的组,然后任何400个或更少的字符按40
字符划分进行分组。