我试图在单个SQL查询中提取网站点击总数加上每个城市每天每个客户端的变化率(增减率),但无法正确执行。
我创建了一个例子
我能请求协助吗?
发布于 2016-01-27 10:41:26
基于百分比的增减
select
t1.HitDate,
t1.City,
t1.Client,
SUM(t1.NumVisits),
IFNULL(((SUM(t1.NumVisits) - IFNULL((SELECT SUM(t2.NumVisits) FROM PAGE_HITS t2 WHERE t2.HitDate = t1.HitDate-1 AND t2.City = t1.City AND t2.Client = t1.Client), 0)) * 100) / IFNULL((SELECT SUM(t2.NumVisits) FROM PAGE_HITS t2 WHERE t2.HitDate = t1.HitDate-1 AND t2.City = t1.City AND t2.Client = t1.Client), 0),0) as rate_of_change
from
PAGE_HITS t1
WHERE
t1.Client='C'
group by
HitDate,
City
ORDER BY HitDate;发布于 2016-01-27 07:16:57
试着避免内部连接,它有错误的条件(t1.hitDate = t2.hitDate and t1.hitDate-1 = t2.hitDate不能满足):
select
t1.HitDate,
t1.City,
t1.Client,
SUM(t1.NumVisits) - IFNULL((SELECT SUM(t2.NumVisits) FROM PAGE_HITS t2 WHERE t2.HitDate = t1.HitDate-1 AND t2.City = t1.City AND t2.Client = t1.Client), 0) as rate_of_change
from
PAGE_HITS t1
WHERE
t1.Client='C'
group by
HitDate,
City
ORDER BY HitDate;发布于 2017-11-16 07:32:46
首先,创建一个类似rownum的唯一数字,然后进行相同的查询并加入rownum列,然后您可以将前一个月作为新列并计算变化率。
SELECT A.HitDate, A.City, A.sumThis, B.sumFormer, (A.sumThis / B.sumFormer - 1) * 100 as 'ChangingRate'
From (
select @rownum1:=@rownum1 + 1 as 'rownum1', HitDate, City, sum(NumVisits) as 'sumThis'
from page_hits, (Select @rownum1:=0) R
Where client = 'C'
Group by HitDate, City
) A
Left Join (
select @rownum2:=@rownum2 + 1 as 'rownum2', HitDate, City, sum(NumVisits) as 'sumFormer'
from page_hits, (Select @rownum2:=0) R
Where client = 'C'
Group by HitDate, City
) B On A.rownum1 - 1 = B.rownum2https://stackoverflow.com/questions/35030620
复制相似问题