SQL-запрос получает общую стоимость, основанную на разной цене за единицу, количестве в разное время

Следующий запрос (сложный, возможно, медленный, требует оптимизации) работает, проверьте ДЕМО

SELECT tr_sub.cur_tt, tr_sub.item, sum(tr.quantity), sum(tr.quantity*tr.unit_price) 
FROM 
  (SELECT tr1.transaction_time as cur_tt, max(tr2.transaction_time) as prev_tt, tr1.item as item, 
     IF (tr1.unit_price=tr2.unit_price, tr1.unit_price, tr2.unit_price) as t_p 
     FROM transactions tr1 LEFT JOIN transactions tr2 ON 
     tr1.transaction_time>=tr2.transaction_time AND tr1.item=tr2.item
    GROUP BY tr1.item, tr1.transaction_time,  t_p
   ) as tr_sub INNER JOIN transactions tr ON 
   tr_sub.prev_tt=tr.transaction_time 
   AND tr_sub.item=tr.item 
   AND tr_sub.t_p=tr.unit_price
GROUP BY tr_sub.item, tr_sub.cur_tt
ORDER BY tr_sub.cur_tt, tr_sub.item

Сначала вам нужно определить все возможные значения unit_price для конкретного элемента:

SELECT DISTINCT unit_price
FROM t
WHERE item = 'A'

Выход:

unit_price
----------
1
3
2

Также необходимо идентифицировать все возможные transaction_times:

SELECT DISTINCT transaction_time
FROM t
WHERE item = 'A';

Выход:

transaction_time
----------------
2012-05-15
2012-06-10
2012-06-15

Теперь выполните CROSS JOIN между двумя вышеуказанными наборами.

SELECT *
FROM (
   SELECT DISTINCT transaction_time
   FROM t
   WHERE item = 'A') AS times
CROSS JOIN (
   SELECT DISTINCT unit_price
   FROM t
   WHERE item = 'A') AS up
ORDER BY times.transaction_time   

получить:

transaction_time unit_price
----------------------------
2012-05-15       3
2012-05-15       2
2012-05-15       1
2012-06-10       3
2012-06-10       2
2012-06-10       1
2012-06-15       1
2012-06-15       3
2012-06-15       2

Теперь используйте приведенное выше и выполните коррелированный подзапрос, чтобы получить unit_price на transaction_time из элемента 'A':

SELECT transaction_time, unit_price,
       (SELECT quantity
        FROM t
        WHERE t.item = 'A' 
              AND t.unit_price = up.unit_price
              AND t.transaction_time <= times.transaction_time
        ORDER BY transaction_time DESC LIMIT 1) AS quantity
FROM (
   SELECT DISTINCT transaction_time
   FROM t
   WHERE item = 'A') AS times
CROSS JOIN (
   SELECT DISTINCT unit_price
   FROM t
   WHERE item = 'A') AS up
ORDER BY times.transaction_time

Выход:

transaction_time     unit_price  quantity
----------------------------------------
15.05.2012 00:00:00  1           15
15.05.2012 00:00:00  3           15
15.05.2012 00:00:00  2           NULL
10.06.2012 00:00:00  1           15
10.06.2012 00:00:00  3           15
10.06.2012 00:00:00  2           15
15.06.2012 00:00:00  1           15
15.06.2012 00:00:00  3           15
15.06.2012 00:00:00  2           10

Конечный результат - это просто выполнение GROUP BY на указанном выше:

SELECT transaction_time, 
       'A' AS item,
       SUM(quantity) AS quantity,
       SUM(quantity*unit_price) AS subtotal
FROM (           
    SELECT transaction_time, unit_price,
           (SELECT quantity
            FROM t
            WHERE t.item = 'A' 
                  AND t.unit_price = up.unit_price
                  AND t.transaction_time <= times.transaction_time
            ORDER BY transaction_time DESC LIMIT 1) AS quantity
    FROM (
       SELECT DISTINCT transaction_time
       FROM t
       WHERE item = 'A') AS times
    CROSS JOIN (
       SELECT DISTINCT unit_price
       FROM t
       WHERE item = 'A') AS up) AS x
GROUP BY transaction_time

Выход:

transaction_time  item  quantity  subtotal
----------------------------------------------
15.05.2012        A     30        60
10.06.2012        A     45        90
15.06.2012        A     40        80

Демо здесь