php - 如何按日期分组，但在select语句中使用日期作为条件？

Question

我真的不确定如何给这个问题起标题，对此深表歉意..

我有一个网站，用户可以在其中“观看”产品。当他们开始观看产品时，一行会插入到 product_tracking 中，如下所示:

+-----------------------+------------+------+-----+---------+----------------+
| Field                 | Type       | Null | Key | Default | Extra          |
+-----------------------+------------+------+-----+---------+----------------+
| id                    | bigint(11) | NO   | PRI | NULL    | auto_increment |
| u_id                  | int(11)    | NO   | MUL | NULL    |                |
| p_id                  | bigint(11) | NO   | MUL | NULL    |                |
| date_started_tracking | datetime   | NO   |     | NULL    |                |
| date_stopped_tracking | datetime   | YES  |     | NULL    |                |
+-----------------------+------------+------+-----+---------+----------------+

用户开始观看产品的日期时间在插入行时插入到 date_started_tracking 中，date_stopped_tracking 默认为 null。当用户停止观看产品时，date_stopped_tracking 会更新为当前日期时间。

我想在我的网站上有一个图表，显示随着时间的推移被跟踪的产品数量。像这样:

# tracks
|                       __/
|               ___    /      
|              /   \__/     
|             /       
|            /
|       ____/
|      /
|  ___/
| /
|/___________________________ date (grouped by day, week, or month)

我不知道如何编写查询来检索制作图表所需的信息。

困难在于我想按天、周或月对行进行分组，但仅当 date_started_tracking 是<=分组日期和 date_stopped_tracking 是 null 或 > 分组日期(即选择显示在该日期被跟踪的产品的行)。

换句话说，对于每一天、每一周或每一月(取决于所需的粒度)，应该返回用户当时正在观看的产品数量。

---

我的解决方案尝试

我想出的唯一想法是创建一个日期表，然后编写一个查询，例如:

SELECT WEEK(d.date) as week, 
       (SELECT COUNT(pt.id) 
        FROM product_tracking pt
        WHERE pt.date_started_tracking <= d.date AND (pt.date_stopped_tracking IS NULL OR pt.date_stopped_tracking > d.date)
        ) as numWatches
FROM dates d
GROUP BY week
ORDER BY week ASC

我认为这可行，但它需要创建一个日期表，这看起来有点迟钝。

有没有更好的办法？

Answer 1

您的方法可行，但无法很好地扩展。另一种方法是使用累积总和的变量来跟踪每天的跟踪。这看起来像:

select dte, sum(inc) as net,
       (@sum := @sum + sum(inc)) as total_for_day
from ((select date_start_tracking as dte, count(*) as inc
       from product_tracking
       group by date_start_tracking
      ) union all
      (select date_stopped_tracking, - count(*)
       from product_tracking
       where date_stopped_tracking is not null
       group by date_stopped_tracking
      )
     ) pt cross join
     (select @sum := 0) vars
group by dte
order by dte;

这是解决方案的概要。对于您的数据，您可能需要比停止日期多一个 - 取决于当天是否计算产品。

然后您可以将此查询用作子查询以聚合您喜欢的任何时间段。