CODEWITHSUNDEEP
mysqlsql
Kaiser Octavius
Kaiser Octavius

Reputation: 157

MYSQL: How can I assign an arithmetic expression to be the value of a field in the SELECT clause without using subqueries?

This is my query:

SELECT DISTINCT id, stat, date, user_id
FROM data AS T1
WHERE 2.0 >= (
         SELECT avg1
         FROM (
            SELECT stat, AVG(value) AS avg1
            FROM data
            WHERE date > SUBTIME(NOW(), MAKETIME(168, 0, 0))
            GROUP BY stat_name) b1
         WHERE stat = T1.stat
         AND id = T1.id)/
         (
         SELECT avg2
         FROM (
            SELECT stat, AVG(value) AS avg2
            FROM cata
            WHERE date > SUBTIME(NOW(), MAKETIME(336, 0, 0))
            AND date <= SUBTIME(NOW(), MAKETIME(168, 0, 0))
            GROUP BY stat) b2
         WHERE stat = T1.stat
         AND id = T1.id)
ORDER BY id;

The idea is that I create a table of stats whose average value this week is more than twice their average value last week.

My problem is this: I want to select a column diff_values that represents MAX(value this week) - MAX(value last week) for each stat, representing the increase over the past week. (Basically, an entry for stat made today is the stat yesterday plus the increase.) I can't think of a way to do it without creating two subqueries similar to those in the WHERE clause, but doing so would make the query execution time really long, since data is a pretty big table.

Is there any way to split the math between the existing subqueries?

Upvotes: 0

Views: 422

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

If I understand your query correctly, then you are trying to calculate the average value of each stat for each user for this week and last week.

If so, the following query does this in a simpler fashion:

select stat, user_id,
       avg(case when date > SUBTIME(NOW(), MAKETIME(168, 0, 0)) then value end) as thisweek,
       avg(case when date > SUBTIME(NOW(), MAKETIME(336, 0, 0)) AND date <= SUBTIME(NOW(), MAKETIME(168, 0, 0)) then value end) as lastweek
from data
group by stat, user_id
having 2.0 >= thisweek / lastweek

It uses conditional aggregation to calculate each of the averages (using your logic for the time -- I haven't changed that). It then uses a having clause for the comparison.

Upvotes: 2

Related Questions