MySQL - 单个 SQL 查询的总计数和变化率

Question

我试图在单个 SQL 查询中提取网站点击总数加上每个城市每个客户每天的变化率(增加/减少率)作为百分比，但无法正确获取。

我已经在

创建了一个示例

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

我可以请求帮助吗？

Answer 1

尝试避免内部连接，无论如何它都有错误的条件(t1.hitDate = t2.hitDate 和 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;