1

I'm trying to calculate purchase/profit growth rate in different time periods, This is the query i've come up with (View in SQLFiddle):

Schema:

CREATE TABLE `profit_log` (
  `market_id` smallint(6) NOT NULL,
  `total_purchase` bigint(20) NOT NULL,
  `total_profit` bigint(20) NOT NULL,
  `company_profit` bigint(20) NOT NULL,
  `date` date NOT NULL,
  `tid` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Query:

SELECT a.`market_id`,
    SUM(a.`total_purchase`) as acc_purchase,
    SUM(a.`total_profit`) as acc_profit,
    SUM(a.`company_profit`) as acc_company_profit,
    ((b.`total_purchase` - c.`total_purchase`)/ c.`total_purchase`) * 100 as growth,
    ((d.`total_purchase` - e.`total_purchase`)/ e.`total_purchase`) * 100 as growth_week,
    ((f.`total_purchase` - g.`total_purchase`)/ g.`total_purchase`) * 100 as growth_month,
    ((d.`total_profit` - e.`total_profit`)/ e.`total_profit`) * 100 as growth_week_profit,
    ((f.`total_profit` - g.`total_profit`)/ g.`total_profit`) * 100 as growth_month_profit
FROM `profit_log` as a
JOIN `profit_log` as b on a.`market_id` = b.`market_id`
    AND b.`date` = '2016-05-05'
JOIN `profit_log` as c on a.`market_id` = c.`market_id`
    AND c.`date` = '2016-05-01'
JOIN `profit_log` as d on a.`market_id` = d.`market_id`
    AND d.`date` >= ('2016-05-05' - INTERVAL 7 DAY) AND d.`date` < ('2016-05-05' + INTERVAL 1 DAY)
JOIN `profit_log` as e on a.`market_id` = e.`market_id`
    AND e.`date` >= ('2016-05-05' - INTERVAL 14 DAY) AND e.`date` < ('2016-05-05' - INTERVAL 7 DAY)
JOIN `profit_log` as f on a.`market_id` = f.`market_id`
    AND f.`date` >= ('2016-05-05' - INTERVAL 1 MONTH) and f.`date` < ('2016-05-05' + INTERVAL 1 DAY)
JOIN `profit_log` as g on a.`market_id` = g.`market_id`
    AND g.`date` >= ('2016-05-05' - INTERVAL 2 MONTH) AND g.`date` < ('2016-05-05' - INTERVAL 1 MONTH)
WHERE a.`date` >= '2016-05-01'
AND a.`date` < ('2016-05-05' + INTERVAL 1 DAY)
AND a.`market_id` IN (0,20)
GROUP BY a.`market_id`

The Strange thing is after all this JOINs, SUMs (i.e SUM(a.total_purchase)) return unbelievably large numbers, it seems that values are accumulated over all this joins.

Can you please explain why is that happening (maybe i don't understand JOIN behavior), and help me to rewrite this query the proper way (6 self joins doesn't look right).

Thanks.

  • 2
    I've no idea how you should rewrite your query because it's unclear what the results should be. (You are using non-standard GROUP BY behaviour here, which doesn't help matters.) However, what's happening is each subset (or at least more than one of them) contains the same market_id more than once. When joining on market_id, you simply get a cross-product: each duplicate ID of one subset is matched by each duplicate ID of the other. The more subsets have duplicate ID entries, the more your results are skewed. – Andriy M May 8 '16 at 3:01
  • 2
    You should use separate queries for different time spans. If you need to return all in one query you can just join these queries together on market_id - select ... from (select market_id, sum() from a group by market_id) a_sum join (selet market_id, sum() from b group by market_id) b_sum using(market_id) ... – jkavalik May 8 '16 at 5:16
1

Another way you can do this is by summing each criterion by a CASE statement:

SELECT market_id,
       SUM(CASE WHEN a = 1 THEN total_purchase END) AS acc_purchase,
       SUM(CASE WHEN a = 1 THEN total_profit END) AS acc_profit,
       SUM(CASE WHEN a = 1 THEN company_profit END) AS acc_company_profit,
       ((SUM(CASE WHEN b = 1 THEN total_purchase END) - SUM(CASE WHEN c = 1 THEN total_purchase END)) / SUM(CASE WHEN c = 1 THEN total_purchase END)) * 100 AS growth,
       ((SUM(CASE WHEN p = 'd' THEN total_purchase END) - SUM(CASE WHEN p = 'e' THEN total_purchase END)) / SUM(CASE WHEN p = 'e' THEN total_purchase END)) * 100 AS growth_week,
       ((SUM(CASE WHEN p = 'f' THEN total_purchase END) - SUM(CASE WHEN p = 'g' THEN total_purchase END)) / SUM(CASE WHEN p = 'g' THEN total_purchase END)) * 100 AS growth_month,
       ((SUM(CASE WHEN p = 'd' THEN total_profit END) - SUM(CASE WHEN p = 'e' THEN total_profit END)) / SUM(CASE WHEN p = 'e' THEN total_profit END)) * 100 AS growth_week_profit,
       ((SUM(CASE WHEN p = 'f' THEN total_profit END) - SUM(CASE WHEN p = 'g' THEN total_profit END)) / SUM(CASE WHEN p = 'g' THEN total_profit END)) * 100 AS growth_month_profit
FROM (
    SELECT profit_log.*,
           CASE WHEN date >= @f THEN 1 END AS a,
           CASE WHEN date = @d THEN 1 END AS b,
           CASE WHEN date = @f THEN 1 END AS c,
           CASE WHEN date >= @d - INTERVAL 7 DAY THEN 'd'
                WHEN date >= @d - INTERVAL 14 DAY THEN 'e'
                WHEN date >= @d - INTERVAL 1 MONTH THEN 'f'
                WHEN date >= @d - INTERVAL 2 MONTH THEN 'g' END AS p
    FROM (SELECT @d := DATE'2016-05-05', @f := @d - INTERVAL DAY(@d)-1 DAY) AS params,
         profit_log
    WHERE date BETWEEN @d - INTERVAL 2 MONTH AND @d
          AND market_id IN (0, 20)
  ) a
GROUP BY market_id

In the case above, @d is the anchor date, and @f is the first day of the month in @d. The calculated columns a, b and c denote entries valid (value 1) since the start of the month, for the anchor day only, and for the first day of the month only, respectively. The calculated column p has values d, e, f and g, for last 7 days, last 14 days, last month, and last two months, respectively. Notice that values a, b, and c can overlap with each other and with d, e, and f (thus needing their individual flags), but values d, e, f and g never overlap, so are folded in a single column.

With these values (performed primarily for readability of the outer SELECT statement), all the values are SUMs of values conforming to some criteria (either a, b, c, d, e, f, or g). If none of the values overlapped, the query could be simplified significantly.

  • 1
    Really great answer, Although it needs some tuning to get the numbers correct as i tried it. Thanks – Nevercom May 9 '16 at 7:16
3

Explanation

First all the JOINing is done. This inflates the number of rows. Then you do a GROUP BY and SUM. Hence, the "unbelievably large numbers".

Solution 1

A solution is usually to do the GROUP BY and SUM over just one table. If that can't be done, then find the ids for that one table, dedup them (via DISTINCT or GROUP BY), then do the grouping and summing.

Give this a try...

  1. Rip out all mention of tables other than a. See if the grouping and summing works correctly.

  2. Make that a subquery and put it as a 'table' in with the rest of the tables:

    SELECT ... FROM ( SELECT ... SUM... FROM profit_log WHERE ... GROUP BY ... ) AS a JOIN b ... -- as you have the rest

Solution 2

Another approach is to use subqueries for the other stuff...

SELECT a.`market_id`,
    SUM(a.`total_purchase`) as acc_purchase,
    SUM(a.`total_profit`) as acc_profit,
    SUM(a.`company_profit`) as acc_company_profit,
    ( SELECT ((b.`total_purchase` - c.`total_purchase`)/ c.`total_purchase`)
        FROM `profit_log` as b on a.`market_id` = b.`market_id`
    ) * 100 as growth,
    ...
FROM `profit_log` as a
WHERE a.`date` >= '2016-05-01'
  AND a.`date` < ('2016-05-05' + INTERVAL 1 DAY)
  AND a.`market_id` IN (0,20)
GROUP BY a.`market_id`

Need indexes

InnoDB tables really need a PRIMARY KEY.

Also you need this; it should increase performance significantly: INDEX(market_id, date)

  • I've tried using subqueries as you mentioned in Solution 2, but it makes the query really complex and a bit unreadable. Can you please explain the PRIMARY KEY part, does it improve performance even if i never use or query that PK field ? – Nevercom May 9 '16 at 7:14
  • 1
    @Nevercom Each InnoDB table has a primary key, always. Thats the way innodb is designed. So when you do not supply one yourself, it has to invent one. It can use some othe unique non null index if available but in the worst case it creates a hidden autoincrement-variant which is not very useful and not very performant. – jkavalik May 9 '16 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.