CODEWITHSUNDEEP
mysql
jhon dano
jhon dano

Reputation: 658

Retrieve Timediff between current row and next row in subquery

Why I am getting more than 24 hours? I am trying to get the timediff between each row in the sub-query if the timediff is greater than 10 min. then sum the result per day.

My goal is to figure out for each user the total of every brake thats longer than 10 min. and list that among the amount of calls on that particular day?

  SELECT DATE_FORMAT(last_call, '%d, %W') AS DAY
       , COUNT(call_id) AS calls
       , ( SELECT SEC_TO_TIME(SUM((
                      SELECT timestampdiff(SECOND, c.last_call, c2.last_call)
                         FROM calls c2
                         WHERE c2.calling_agent = c.calling_agent
                           AND c2.last_call > c.last_call
                           AND timestampdiff(SECOND, c.last_call, c2.last_call) > 600
                         ORDER BY c2.last_call LIMIT 1
                  )))
             FROM calls AS c
            WHERE EXTRACT(DAY FROM c.last_call) = EXTRACT(DAY FROM calls.last_call)
         ) AS `brakes`
    FROM calls
   WHERE 9 IN (calls.reg_calling_agent)
     AND last_call > DATE_SUB(now() , INTERVAL 12 MONTH)
   GROUP BY EXTRACT(DAY FROM last_call)
   ORDER BY EXTRACT(DAY FROM last_call) DESC

Upvotes: 0

Views: 286

Answers (1)

spencer7593
spencer7593

Reputation: 108400

You're getting more than 24 hours because

1) the row retrieved from c2 could be from a different day. There's no guarantee that the next call (10 minutes after the previous call) isn't the first call made/received by an agent after a week long vacation.

2) that same "gap" of over 10 minutes is going to reported for the last call the agent made/received. And you're also going to get a "gap" between the call the agent made immediately before the one before the gap, and the one before that. That is, there's no provision to made exclude the calls that DID have a subsequent call within 10 minutes. (The subquery is just looking for any subsequent call that is 10 minutes after a call.)

3) you are getting getting an aggregate total (SUM) of all of those gaps in a given day, irregardless of the agent; all the gaps for all agents are being totaled.

4) the outer query is getting a years worth of calls, (for all agents?) but is grouping by day of month (1 through 31). So, you're getting back one row for the 5th of the month, but there will be multiple agents and multiple "days" (Jan 5, Feb 5, March 5, etc.), multiple values of 'brakes', and only one of those values is going to be included in the result,. It's indeterminate which of those row values will be returned. (Other RDBMS's would balk with this construct, a non-aggregate expression in the SELECT list which not included in the GROUP BY, but by default, MySQL allows it.)

--

FOLLOWUP

Q: could you please post the corrected query?

A: I don't have the table schema, or sample data, or a specification, so it's impossible for me to provide a "corrected" query.

For example, it's not at all clear why there's a predicate on reg_calling_agent in the outermost query, but the subqueries don't have any reference to that column, or any other column from the table in the outer query, except for the last_call column. The query to find a subsequent call is relying on the calling_agent column, not reg_calling_agent, but that's being performed for ALL calls in a given day of month.

I can take a shot a query that may be closer to what you are looking for, but there is absolutely no guarantee that this is "correct" in terms of matching the schema, the datatypes, the actual data, or the expected output. A query that returns unexpected results is not an adequate specification.

SELECT a.calling_agent
     , DATE_FORMAT(a.last_call,'%d, %W') AS `day`
     , COUNT(a.call_id) AS `calls`
     , SEC_TO_TIME(
         SUM(
           SELECT IF(TIMESTAMPDIFF(SECOND, a.last_call, c.last_call) > 600
                    ,TIMESTAMPDIFF(SECOND, a.last_call, c.last_call)
                    ,NULL
                  ) AS `gap`
             FROM calls c
            WHERE c.calling_agent = a.calling_agent
              AND c.last_call     > a.last_call
              AND c.last_call     < DATE(a.last_call)+INTERVAL 1 DAY
            ORDER BY c.last_call
            LIMIT 1
         )
       ) AS `breaks`
  FROM calls a
 WHERE a.reg_calling_agent = 9
   AND a.last_call > DATE(NOW()) - INTERVAL 12 MONTH
 GROUP BY a.calling_agent, DATE_FORMAT(a.last_call,'%d, %W')
 ORDER BY a.calling_agent, DATE_FORMAT(a.last_call,'%d, %W') DESC

UNPACKING THE QUERY

I thought I might provide some insight as to the design of this query, what it's intended to do. I retained the FROM and WHERE clauses from the original outer query. I just gave an alias to the calls table, and re-wrote the predicates to a form that I think is simpler, and that I'm more used to using.

For the GROUP BY, I added calling_agent, since it doesn't seem to make sense that we would want to lump all of the agents together. (It's really up to you to decide whether that matches the spec or not.) I did this because calling_agent is NOT referenced in the WHERE clause. (There's an equality predicate on reg_calling_agent, but that's a different column.)

I replaced the EXTRACT(DAY FROM ) expression, since that's only returning an integer value between 1 and 31. And it just doesn't seem to make sense to lump together all the "4th day" of all months. I chose to use the expression that's in the SELECT list; because that's the normative pattern... returning the expressions used in the GROUP BY clause in the SELECT list, so the client will be able to distinguish which row in the result belongs to which group identifier.

I also qualified all column references with a table alias, as an aid to the future reader. We're familiar following that pattern in complex queries. It's natural that we extend that same pattern to simpler queries, even when it's not required.

The big change is to the derived breaks column. (I renamed that from 'brakes', because it seems like what this query is doing is finding out when calling_agents weren't making/receiving calls, when workers were "taking a break". (That's entirely a guess on my part.)

There's a SEC_TO_TIME function, all that's doing is reformatting the result.

There's a SUM() aggregate. This is just going to total up the values, for each row in a that's in a "group".

The real "meat" is the correlated subquery. What that does... for each row returned by the outer query (i.e. every row from calls that satisfies the WHERE clause on the outer query)... we are going to run another SELECT. And it's going to look for the very "next" call made/received by the same calling_agent. To do that, the calling_agent on the "next" call needs to match the value from row from the outer query...

            WHERE c.calling_agent = a.calling_agent

Also, the datetime/timestamp of the subsequent "call" needs to be anytime after the datetime/timestamp of the row from the outer query...

              AND c.last_call     > a.last_call

And, we only want to look for calls that are on the same calendar date (year, month, day) as the previous call. (This prevents us from considering a call made four days later as a "subsequent" call.)

              AND c.last_call     < DATE(a.last_call)+INTERVAL 1 DAY

And, out of all those potential subsequent calls, we only want the first one, so we order them by datetime/timestamp, and then take just the first one.

            ORDER BY c.last_call
            LIMIT 1

If we don't get a row, the subquery will return a NULL. If we do get a row, the next thing we want to do is check if the datetime/timestamp on this call is more than 10 minutes after the previous call. We use the same TIMESTAMPDIFF expression from the original query, to derive the number of seconds between the calls, and we compare that to 10 minutes. If the gap is greater than 10 minutes, we consider this as a "break", and we return the difference as number of seconds. Otherwise, we just return a NULL, as if we hadn't found a "next" row.

                  IF(TIMESTAMPDIFF(SECOND, a.last_call, c.last_call) > 600
                    ,TIMESTAMPDIFF(SECOND, a.last_call, c.last_call)
                    ,NULL
                  ) AS `gap`

That's MySQL-specific shorthand for the ANSI-standard form:

                 CASE
                   WHEN TIMESTAMPDIFF(SECOND, a.last_call, c.last_call) > 600
                   THEN TIMESTAMPDIFF(SECOND, a.last_call, c.last_call)
                   ELSE NULL
                 END AS `gap`

(NOTE: the ELSE NULL could be omitted, that would be functionally equivalent because NULL is the default when ELSE is omitted. I include it here for completeness, and for comparison to the MySQL IF() function.)

Finally, we include all of the expressions in the GROUP BY clause in the SELECT list. (This isn't required, but it's the usual pattern. If those expressions are omitted, there should be a pretty obvious reason why they are omitted. For example, if the outer query had an equality predicate on calling_agent, e.g.

    AND a.calling_agent = 86

Then we'd know that any row returned by the query would have a value of 86 returned for calling_agent, so we could omit the expression from the SELECT list. But if we omit an equality predicate, or change it so that more than one calling_agent could be returned, something like:

    AND (a.calling_agent = 86 OR a.calling_agent = 99)

then without calling_agent in the SELECT list, we won't be able to tell which rows are for which calling_agent. If we're going to the bother of doing a GROUP BY on the expression, we usually want to include the expression in the SELECT list; that's the normal pattern.

Upvotes: 1

Related Questions