首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL -在单个SQL查询中的总数和变化率

问MySQL -在单个SQL查询中的总数和变化率
EN

Stack Overflow用户
提问于 2016-01-27 06:55:34
回答 3查看 958关注 0票数 1

我试图在单个SQL查询中提取网站点击总数加上每个城市每天每个客户端的变化率(增减率),但无法正确执行。

我创建了一个例子

http://www.sqlfiddle.com/#!9/fd279/8

我能请求协助吗?

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2016-01-27 10:41:26

基于百分比的增减

代码语言:javascript
复制
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;
票数 0
EN

Stack Overflow用户

发布于 2016-01-27 07:16:57

试着避免内部连接,它有错误的条件(t1.hitDate = t2.hitDate and t1.hitDate-1 = t2.hitDate不能满足):

代码语言:javascript
复制
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;
票数 1
EN

Stack Overflow用户

发布于 2017-11-16 07:32:46

首先,创建一个类似rownum的唯一数字,然后进行相同的查询并加入rownum列,然后您可以将前一个月作为新列并计算变化率。

代码语言:javascript
复制
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.rownum2
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/35030620

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档