Reputation: 1373

Complex SQL query optimization suggestions please

enter code hereI have a table with structure like this:

| ID (bigint) | APPID (Bigint)| USAGE_START_TIME (datetime) | SESSION_TIME (bigint) |  USERID (bigint)   |
-----------------------------------------------------------------------------------------------------------
|  1          |        1      |         2013-05-03 04:42:55 |       400             |       12           |
|  2          |        1      |         2013-05-12 06:22:45 |       200             |       12           |
|  3          |        2      |         2013-06-12 08:44:24 |       350             |       12           |
|  4          |        2      |         2013-06-24 04:20:56 |         2             |       12           |
|  5          |        3      |         2013-06-26 08:20:26 |         4             |       12           |
|  6          |        4      |         2013-09-12 05:48:27 |        50             |       12           |

Now if put is userid, i want to get the total (sum of) session_time for each month (of all app), month wise for the last 6 months.

If an app is used more than once in a month (based on usage_start_time), only the latest session_time should included in the sum for that app.

For the above example, the result should be like this (if present month is october, and input is userid=13):

| MONTH       | TOTAL_SESSION_TIME | 
------------------------------------
|  10         |        0           |            
|   9         |       50           |         
|   8         |        0           |         
|   7         |        0           |         
|   6         |        6           | 
|   5         |      200           |

Here the month represents month in numeric form (for ex: 10 represents october).

Presently i'm using separate queries for each month. for ex:

SELECT 
COALESCE(SUM(failcount),0) AS TOTAL_SESSION_TIME , MONTH(CURRENT_DATE) AS MONTH 
FROM appstime 
WHERE MONTH(`USAGE_START_TIME`) = MONTH(CURRENT_DATE) AND userid=12

But this doesn't give me expected results.

Also, i want to know if i can do this with a single query instead of query for each month. I'm using PHP+Mysql.

Please check the sql fiddle here: http://sqlfiddle.com/#!2/4eaf2

Thank you,
Sash

Upvotes: 0

Answers (5)

Kickstart

Reputation: 21513

Not tested, but something like this:-

SELECT ForMonths.aMonth, IFNULL(SUM(SESSION_TIME), 0)
FROM
(
    SELECT MONTH(DATE_ADD(NOW(), INTERVAL aInt MONTH)) AS aMonth
    FROM
    (
        SELECT 0 AS aInt UNION SELECT -1 UNION SELECT -2 UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
    ) aCnt
) ForMonths
LEFT OUTER JOIN 
(
    SELECT a.USERID, a.APPID, aMonth, SESSION_TIME
    FROM appstime a
    INNER JOIN
    (
        SELECT USERID, APPID, MONTH(USAGE_START_TIME) AS aMonth, MAX(USAGE_START_TIME) AS USAGE_START_TIME
        FROM appstime
        GROUP BY USERID, APPID, aMonth
    ) b
    ON a.USERID = b.USERID
    AND a.USAGE_START_TIME = b.USAGE_START_TIME
    AND a.APPID = b.APPID
  WHERE a.USERID = 12
) ForDetails
ON ForMonths.aMonth = ForDetails.aMonth
GROUP BY ForMonths.aMonth

Could be simplified, but one subquery to get each of the last 6 months, and join that against another subquery to get the latest amount for each month for a user.

EDIT - To use year and month:-

SELECT ForMonths.aMonth, IFNULL(SUM(SESSION_TIME), 0)
FROM
(
    SELECT EXTRACT(YEAR_MONTH FROM DATE_ADD(NOW(), INTERVAL aInt MONTH)) AS aMonth
    FROM
    (
        SELECT 0 AS aInt UNION SELECT -1 UNION SELECT -2 UNION SELECT -3 UNION SELECT -4 UNION SELECT -5
    ) aCnt
) ForMonths
LEFT OUTER JOIN 
(
    SELECT a.USERID, a.APPID, aMonth, SESSION_TIME
    FROM appstime a
    INNER JOIN
    (
        SELECT USERID, APPID, EXTRACT(YEAR_MONTH FROM USAGE_START_TIME) AS aMonth, MAX(USAGE_START_TIME) AS USAGE_START_TIME
        FROM appstime
        GROUP BY USERID, APPID, aMonth
    ) b
    ON a.USERID = b.USERID
    AND a.USAGE_START_TIME = b.USAGE_START_TIME
    AND a.APPID = b.APPID
  WHERE a.USERID = 12
) ForDetails
ON ForMonths.aMonth = ForDetails.aMonth
GROUP BY ForMonths.aMonth
ORDER BY ForMonths.aMonth

Upvotes: 1

DRapp

Reputation: 48139

Although I was having problems getting the 6 months records, I would have done on my other machine but don't have MySQL on current machine. I scraped that part from Kickstart (for his credit there).

Anyhow, I've started with an inner-most "PreQuery" (alias PQ) by starting with the 6 months ago and current user of 12. To ensure I was not just getting 6 months from today (2nd of month), or even if ran on the 26th of the month, but wanted the FIRST of 6 months ago, I did some date-math by going to the last day of 6 months ago, then adding 1 day to be first of following month. So that will create

Oct 2 - 6 months = Apr 2... Then moved to the last day of Apr 30, then add 1 day to get to May 1. So, now May1 to Oct current where Oct is the 6th month if 1 day to the entire month. If you want to get from April 1 to current, then go back 7 months.

So from that, I'm getting each app, user, per month and the max instance per that month. I'm including the user in case you wanted ALL users and not just one in the future.

Once those MAX() per app/user/month are queried, I then join again to the sessions again, but ONLY for those that matched.

Finally being applied via a left-join to the outer list of months gives you the rest. Again, if you wanted all users, you could just add the user to the outer query and group by clause.

SELECT
      AllMonths.aMonth,
      COALESCE( SUM( Ap2.Session_Time ), 0 ) as Total_Session_Time
   from
      ( SELECT MONTH(DATE_ADD(NOW(), INTERVAL aInt MONTH)) AS aMonth
           FROM
            (
                SELECT 0 AS aInt 
                    UNION SELECT -1 
                    UNION SELECT -2 
                    UNION SELECT -3 
                    UNION SELECT -4 
                    UNION SELECT -5
            ) aCnt ) AllMonths
      LEFT JOIN
         ( select
                 appstime.AppID,
                 appstime.UserID,
                 MONTH( appstime.Usage_Start_Time ) as PerMonth,
                 MAX( appstime.Usage_Start_Time ) as ThisTime
              from 
                 ( select @StartDate := last_day( now() - interval 6 month ) + interval 1 day ) sqlvars,
                 appstime
              where
                     appstime.Usage_Start_Time >= @StartDate
                 AND appstime.UserID = 12
              group by
                 appstime.AppID,
                 appstime.UserID,
                 MONTH( appstime.Usage_Start_Time ) ) PQ
         ON AllMonths.aMonth = PQ.PerMonth
         LEFT JOIN appstime Ap2
             ON PQ.AppID = Ap2.AppID
            AND PQ.UserID = Ap2.UserID
            AND PQ.ThisTime = Ap2.Usage_Start_Time
   group by         
      AllMonths.aMonth

Upvotes: 1

alpham8

Reputation: 1362

If I understood your question right, then this should be your query (IBM DB2 Syntax for current date):

SELECT MONTH(USAGE_START_TIME) AS MONTH, SESSION_TIME as TOTAL_SESSION_TIME
FROM appstime
WHERE (MONTH(CURRENT DATE) - MONTH(USAGE_START_TIME)) BETWEEN 0 AND 6
GROUP BY MONTH(USAGE_START_TIME)
HAVING SUM(SESSION_TIME)

This counts all session times per month from last 6 months.

Cheers.

Edit: This gets all app calls from the same app in the month. For getting the last call from the same app id you will need a stored procedure for doing this. 6 variables for each month, which checks in the select loop, if the date is larger than the last from the same month. I recommend you to use the equivalent DAYS(current date) function for MySQL to compare the dates since the year 0 in days.

Upvotes: 0

Anthony

Reputation: 37045

Are you wanting GROUP BY

SELECT 
SUM(COALESCE(SESSION_TIME,0)) AS TOTAL_SESSION_TIME , 
MONTH(USAGE_START_TIME) AS THE_MONTH 
FROM appstime 
WHERE userid=13 AND 
DATE(SESSION_TIME) > DATE_SUB(NOW(), INTERVAL 6 MONTH)
GROUP BY THE_MONTH

UPDATE:

This will limit the results to the max date per appid per month:

SELECT 
SUM(COALESCE(session_time,0)) AS TOTAL_SESSION_TIME , 
MONTH(usage_start_time) AS THE_MONTH 
FROM appstime 
INNER JOIN
    (
    SELECT appid , MAX(usage_start_time) AS max_app_date
    FROM appstime
    GROUP BY appid
    ) grouped_apps ON 
      grouped_apps.appid = appstime.appid AND
      grouped_apps.max_app_date = appstime.usage_start_time
WHERE userid=12 
GROUP BY THE_MONTH

Upvotes: 1

Xophmeister

Reputation: 9211

You need to use aggregation functions (i.e., group by):

select   userid,
         month(usage_start_time) as month,
         sum(session_time) as total_session_time
from     appstime
group by userid,
         month(usage_start_time);

EDIT To only show the most recent session_time in any month, by userid and appid. Note that calling this field total_session_time is therefore probably not such a good idea!

select    latest.userid,
          latest.appid,
          month(latest.usage_start_time) as month,
          latest.session_time as latest_session_time

from      appstime as latest

left join appstime as later
on        later.userid           = latest.userid
and       later.appid            = latest.appid
and       later.usage_start_time > latest.usage_start_time

where     later.id is null;

Upvotes: 0

Complex SQL query optimization suggestions please

Answers (5)

UPDATE:

Related Questions