mysql - 如何计算 MySQL 中每周符合条件的行数？

Question

我有一张表，用于存储工单请求，并记录请求日期和解决日期。

我想设计一个查询，以历史显示每周系统中有多少 Unresolved 工单。

问题是，如果我使用请求的日期作为组标准，那么超过一周 Unresolved 工单不会被计算两次。我想确保任何 Unresolved 时间超过我的团队人数的工单，都会根据需要多次贡献其计数。

例如，具有以下数据:

id  requested   resolved
==  ==========  ==========
1   2015-07-01  2015-07-02
2   2015-07-01  NULL
3   2015-07-08  2015-07-10
4   2015-07-08  NULL

第一周 (26) 和第二周 (27) 各有两个请求。每周都有一个已解决和一个 Unresolved 请求，因此查询结果应该显示第一周有 1 个未解决，第二周有 2 个未解决。 (解决日期在未来一周内的项目也算作未解决，但为了简化此示例，我只显示空日期。)

我希望结果显示:

year   week    # unresolved
====   ====    ============
2015   26      1
2015   27      2

我目前的查询:

SELECT
    YEAR(requested) `year`,
    WEEK(requested, 5) `week`,
    COUNT(id) `# unresolved`
FROM
    tickets
WHERE
    WEEK(requested) < WEEK(resolved)
    OR resolved IS NULL
GROUP BY `Year`, `Week`;

每周只显示 1 个 Unresolved 工单:

year  week  # unresolved  
====  ====  ============
2015  26    1               
2015  27    1               

我应该注意什么以适本地修改此查询？

http://sqlfiddle.com/#!9/90782/1/0

编辑:

作为附加信息，这是一个相对简单的查询，当提供任何特定的星期进行检查时:

SELECT
    COUNT(id) `# unresolved`
FROM
    tickets
WHERE
    WEEK(requested) <= WEEK('2015-07-01')
    AND
    (
        WEEK(resolved) > WEEK('2015-07-01')
        OR resolved IS NULL
    );

通过更改输入周，可以获得任何感兴趣周的未解决工单数量。我的目标是创建一个查询，该查询将按周对所有可用数据进行分组，而不是针对单周结果修改此查询。

Answer 1

(注意:我展示的是我的思考过程，但你只需要这个答案中的最后一个查询)

(注2:勾选sqlfiddle here)

我会将请求的工单和解决的工单视为两种不同类型的事件，所以

select requested eventDate, 1 ticketChange from table

会给我每张请求的票数 1 和

select resolved eventDate, -1 ticketChange from table

会为每张已解决的票给我 -1 计数。如果我对这两个查询进行合并，我会得到一个带有 +1 和 -1 的日期列表，用于确定是添加还是解决了工单。所以我可以通过做每周得到一个总 Unresolved 问题

    select year(eventDate) myYear, week(eventDate) myWeek, sum(ticketChange) totTicketChange
    from (select requested eventDate, 1 ticketChange from table union all
          select resolved eventDate, -1 ticketChange from table where resolved is not null)
    group by year(eventDate) asc, week(eventDate) asc

但由于您需要累计总数，因此我将定义一个变量@unresolvedCount 并在我遍历选择行时递增它:

set @unresolvedCount := 0;
select myYear, myWeek, (@unresolvedCount := @unresolvedCount + totTicketChange) unresolved
  from (select year(eventDate) myYear, week(eventDate) myWeek, sum(ticketChange) totTicketChange
          from (select requested eventDate, 1 ticketChange from tickets union all
                select resolved eventDate, -1 ticketChange from tickets where resolved is not null) TicketEvents
 group by year(eventDate) asc, week(eventDate) asc) TicketCummulative

这正是您想要的。我已经用上面提到的 fiddle 检查过它，如果你能找到一个更有效的算法来做你想做的事情，我会感到惊讶。我还建议您单独运行每个内部查询，查看其结果，然后自行解决。这将使您深入了解其工作原理。

如果您只想获得特定时间段(比如当年)的结果，可以根据需要使用三种不同的方法。如果你想只统计这个时间段内请求的工单，也就是说，比如你不想统计去年请求的工单，即使它们仍然没有解决，甚至如果他们今年得到解决，那么您可以将最里面的查询更改为:

select requested eventDate, 1 ticketChange from tickets where requested >= '2015-01-01' union all
select resolved eventDate, -1 ticketChange from tickets where requested >= '2015-01-01' and resolved is not null

如果您想计算在给定时间段内请求或解决的工单，则可以将最里面的查询更改为:

select requested eventDate, 1 ticketChange from tickets where requested >= '2015-01-01' union all
select resolved eventDate, -1 ticketChange from tickets where resolved is not null and resolved >= '2015-01-01'

如果你想计算所有工单，只要它们在当前时间段内被请求或解决，或者如果它们仍然未解决(即使它们已经 3 年了)，那么你将不得不测试日期，在完整查询的最后，让内部查询处理所有工单。

... group by year(eventDate) asc, week(eventDate) asc) TicketCummulative where myYear >= 2015 and myWeek >= 1