我有以下表字段:
Invitations (user_id, type, created_at, completed_at)
我目前可以通过运行以下查询和手动计算来获得上周的邀请会话速率。
SELECT *
FROM invitations
WHERE created_at >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND created_at < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
AND user_id != 1
AND type = 'email'
ORDER BY completed_at, created_at
有没有可能用SQL输出更多的报告...返回的东西:
LAST WEEK | Total Invitations | Invitations Completed | % Conversion
| 100 | 50 | 50%
TWO WEEKS | Total Invitations | Invitations Completed | % Conversion
| 100 | 60 | 60%
这样的事情可以用SQL实现吗?或者我需要用应用程序逻辑来创建吗?
发布于 2018-07-26 06:56:45
也许您想使用count()
进行聚合并执行一个UNION ALL
。
SELECT 'LAST WEEK' `Period`,
count(created_at) `Total Invitations`,
count(completed_at) `Invitations completed`,
concat(count(completed_at) / count(created_at) * 100, '%') `% Conversion`
FROM invitations
WHERE created_at >= curdate() - INTERVAL dayofweek(curdate()) + 6 DAY
AND created_at < curdate() - INTERVAL dayofweek(curdate()) - 1 DAY
AND user_id <> 1
AND type = 'email'
UNION ALL
SELECT 'TWO WEEKS' `Period`,
count(created_at) `Total Invitations`,
count(completed_at) `Invitations completed`,
concat(count(completed_at) / count(created_at) * 100, '%') `% Conversion`
FROM invitations
WHERE created_at >= curdate() - INTERVAL dayofweek(curdate()) + 13 DAY
AND created_at < curdate() - INTERVAL dayofweek(curdate()) - 1 DAY
AND user_id <> 1
AND type = 'email';
在completed_at
不为null的情况下,count(completed_at)
只计算行数。当且仅当邀请未完成时,我假定completed_at
为null。count(created_at)
是模拟工作的。但我假设该列中没有空值(如果有空值,这些行将不会与WHERE
子句中的条件匹配,因此它们甚至不是计数的候选者)。UNION ALL
只统一了两个结果集(没有消除重复项,因为至少Period
不同)。
https://stackoverflow.com/questions/51528558
复制相似问题