我试图找出过去7天的百分比增长,但我有点卡住了。目前,在我创建的SQL查询中,您可以获得最近7天的新帐户总数。但是现在,我如何改进才能以百分比返回结果呢?
这是到目前为止完成的SQL查询。
谢谢
SELECT COUNT(DISTINCT account_type)
FROM account
WHERE date_created > NOW() - INTERVAL 7 DAY
发布于 2018-08-07 17:29:06
您可以创建一个包含两列的临时表,即'old count‘和'new count’。使用从SELECT查询中获得的值填充该表。然后,从临时表中检索这些值以计算百分比差异并删除临时表。
发布于 2018-08-07 17:37:23
你可以尝试用下面的方法计算最近7天的天数,然后计算7天前的所有天数,然后计算百分比
select max(last7days_count) as last7days_count,
max(before7days_count) as before7days_count,
((max(before7days_count)*1.00)/max(last7days_count))*100.00 as percentage from
(
SELECT COUNT(DISTINCT account_type) as last7days_count, 0 as before7days_count
FROM account
WHERE date_created > NOW() - INTERVAL 7 DAY
union all
SELECT 0 as last7days_count COUNT(DISTINCT account_type) as before7days_count
FROM account
WHERE date_created < NOW() - INTERVAL 7 DAY
) as T
发布于 2018-08-07 17:39:21
为了在一个查询中运行所有功能,您可以考虑下一个查询:
SELECT
/* Count for previous period. */
beforeCount,
/* Count for current period. */
afterCount,
/* Simple math, just calculating percentage. */
(beforeCount * 100) / afterCount AS percent
FROM (
SELECT
/* Select count for previous period. */
(
SELECT COUNT(DISTINCT account_type)
FROM account
WHERE date_created BETWEEN NOW() - INTERVAL 14 DAY AND NOW() - INTERVAL 7 DAY
) AS beforeCount,
/* Select count for current period. */
(
SELECT COUNT(DISTINCT account_type)
FROM account
WHERE date_created > NOW() - INTERVAL 7 DAY
) AS afterCount
) AS tmp
https://stackoverflow.com/questions/51723060
复制相似问题