0

Schema:

CREATE TABLE purchases (name varchar(20), units SMALLINT, ts int);
INSERT INTO purchases (name, units, ts) 
  VALUES
    ('John', 1, UNIX_TIMESTAMP('2017-01-03 12:00:00')),
    ('John', 1, UNIX_TIMESTAMP('2017-01-04 12:00:00')),
    ('John', 1, UNIX_TIMESTAMP('2018-01-01 12:00:00')),
    ('John', 1, UNIX_TIMESTAMP('2018-01-01 12:00:00')),
    ('Jill', 1, UNIX_TIMESTAMP('2018-01-01 12:01:00')),
    ('Jill', 1, UNIX_TIMESTAMP('2018-01-01 12:01:00')),
    ('Jill', 1, UNIX_TIMESTAMP('2016-02-20 12:01:00')),
    ('Jill', 1, UNIX_TIMESTAMP('2016-03-13 12:01:00')),            
    ('Jack', 1, UNIX_TIMESTAMP('2002-12-08 12:02:00')),
    ('Jean', 1, UNIX_TIMESTAMP('2005-12-11 12:03:00'));

Current SQL query uses 2 passes:

SELECT * FROM 
    (SELECT
        name, SUM(units) as histPurchases
    FROM purchases
    WHERE ts 
        BETWEEN UNIX_TIMESTAMP('1990-01-01 00:00:00')
        AND UNIX_TIMESTAMP('2019-02-01 00:00:00')
    GROUP BY name) as hist
JOIN 
    (SELECT name, SUM(units) as currPurchases # those bought on the 1st of Jan 2018
        FROM purchases
        WHERE ts 
            BETWEEN UNIX_TIMESTAMP('2018-01-01 00:00:00')
            AND UNIX_TIMESTAMP('2018-01-02 00:00:00')
    GROUP BY name) as curr
ON (hist.name = curr.name);

Here's the result:

name    | histPurchases | currPurchases
--------|---------------|------------
Jill    | 4             | 2
John    | 4             | 2

Is there a way to structure the query so that only one pass is made (ideally with the latest version of MySQL)? Here is the SQL fiddle.

geoyws
  • 3,326
  • 3
  • 35
  • 47

0 Answers0