php - 根据时间间隔统计mysql表字段

Question

我有订阅表，其中包括与屏幕相关的所有订阅 pf 广告。我想限制用户一次只能在每个屏幕上添加 20 个广告。我完成了每个屏幕的广告计数。但是，问题是，我想考虑日期范围(每个订阅的开始日期和结束日期)。这意味着在特定日期范围内的广告不应超过 20 个。

我已经在 sqlfiddle 中构建了示例模式。 http://sqlfiddle.com/#!2/070cf/1

帮助将不胜感激。谢谢。

Answer 1

您需要检查时间间隔内每一天的情况。这是我强烈建议在应用程序层做的事情，因为 mysql 实际上不是为这种循环构建的。

但为了争论，下面是如何在 sql 中解决它:1.将搜索区间扩大到所有天数2.每天查看状态

第一步:扩大区间

select *, start_date+interval nr.number day as search_date -- search date is the exploded interval
from
(select date('2013-01-13') as start_date, date('2013-02-20') as end_date) search -- the dates we search for
inner join -- joining a numbers table to expand the interval
(select a.nr+b.nr*10 as number from
    (select 1 as nr
    union all select 2
    union all select 3
    union all select 4
    union all select 5
    union all select 6
    union all select 7
    union all select 8
    union all select 9
    union all select 0
    )a
    join
    (select 1 as nr
    union all select 2
    union all select 3
    union all select 4
    union all select 5
    union all select 6
    union all select 7
    union all select 8
    union all select 9
    union all select 0
    )b
    order by 1 asc
) nr
on nr.number<=datediff(end_date, start_date)

现在您有了 start_date、end_date、search_date 列，其中搜索日期包含开始和结束之间的所有日期。我们称此查询为“date_interval”

假设根据您的订阅表，您有一个包含其他 date_start 和 date_end 的表。要计算是否满足每天不超过 20 个广告系列的条件，并找到违规的日子，我们这样加入:

select search_date, count(*) as subscriptions_active from
date_interval as di
inner join 
subscriptions as s 
on di.search_date between s.date_start and s.date_end
group by search_date
having subscriptions_active <20

从我们的搜索间隔中生成包含 20 个或更多事件的日期表。

另一种方法是检查运行总计，这适用于日期时间。假设表订阅有 start_date 和 end_date，查询示例如下。现在很容易检查该表中搜索到的开始日期和结束日期之间是否存在不满足条件的日期时间。

select c.date, c.change_,

(select sum(change_) from   (select start_date as date, change_ from 
        (select start_date , 1 as change_ from 
                (select date('2013-01-13') as start_date, date('2013-02-20') as end_date
                union all
                select date('2013-01-14') as start_date, date('2013-04-25') as end_date
                union all
                select date('2013-03-15') as start_date, date('2013-05-25') as end_date)a
        union
        select end_date, -1 as change_ from 
                (select date('2013-01-13') as start_date, date('2013-02-20') as end_date
                union all
                select date('2013-01-14') as start_date, date('2013-04-25') as end_date
                union all
                select date('2013-03-15') as start_date, date('2013-05-12') as end_date)a
        order by 1 asc)b
    )d where d.date<=c.date) as running_total










 from

    (select start_date as date, change_ from 
        (select start_date , 1 as change_ from 
                (select date('2013-01-13') as start_date, date('2013-02-20') as end_date
                union all
                select date('2013-01-14') as start_date, date('2013-04-25') as end_date
                union all
                select date('2013-03-15') as start_date, date('2013-05-25') as end_date)a
        union
        select end_date, -1 as change_ from 
                (select date('2013-01-13') as start_date, date('2013-02-20') as end_date
                union all
                select date('2013-01-14') as start_date, date('2013-04-25') as end_date
                union all
                select date('2013-03-15') as start_date, date('2013-05-12') as end_date)a
        order by 1 asc)b
    )c