时间戳

首页 > 解决方案 > 使用 mysql 总共运行了 N 天

mysql - 使用 mysql 总共运行了 N 天

问题描述

我有一张看起来像这样的桌子： 在此处输入图像描述

我需要的是每天显示过去 5 天（包括
拆分到美国与非美国地理位置）中新 Droppers 的累积（移动）数量。报告列： DataTimstamp - 5 天时间范围的上限 Total - 时间范围内的新 Droppers 数量 Region_US - 新 Droppers 的数量 where country ='US' Region_rest - 新 Droppers 的数量 where country<>'US'< /p>

这是我的代码：

    Create view new_droppers_per_date as Select date,country,count(dropperid) as num_new_customers 
   From(
   Select dropper id,country,min(cast( LoginTimestamp as date))  as date 
   From droppers) as t1 group by date,country
   Select DataTimstamp,region_us,region_rest from(
   (Select  date as DataTimstamp ,sum(num_new_customers) over(oreder by date range between 5 
   preceding and 1 preceding) as total
   From new_droppers_per_date ) as t1 inner join 
   (Select date ,sum(num_new_customers) over(oreder by date range between 5 preceding and preceding) 
   as  region_us
  From new_droppers_per_date where country=”us”) as t2 on t1.date=t2.date inner join 
  (Select date, sum(num_new_customers) over(oreder by date range between 5 preceding and 1 
  preceding) 
  as region_rest 
   From new_droppers_per_date where country <>”us”) as t3 on t2.date=t3.date)

我想知道是否有任何更简单\更智能的方法可以在不使用这么多连接和视图的情况下做到这一点

感谢您的帮助：）

标签： mysqljoinwindow-functionscumulative-sum

解决方案

这是使用窗口函数的一种方法。首先为每个的第一次登录分配一个标志DropperId，然后按天汇总并计算新登录的次数。最后，制作一个包含过去 5 天sum()的框架的窗口。range

select
    LoginDay,
    sum(CntNewDroppers) over(
        order by LoginDay 
        range between interval 5 day preceding and current row
    ) RunningCntNewDroppers
from 
    select
        date(LoginTimestamp) LoginDay,
        sum(rn = 1) CntNewDroppers
    from (
        select 
            LoginTimestamp, 
            row_number() over(partition by DropperId order by LoginTimestamp) rn
        from mytable
    ) t
) t

推荐阅读