【Question】
I have given data as strings. There are two tables, one with the main data and the other with historical values.

 

Tablestocks:

+———-+——-+———–+

| stock_id | symbol| name |

+———-+——-+———–+

| 1 | AAPL | Apple |

| 2 | GOOG | Google |

| 3 | MSFT | Microsoft |

+———-+——-+———–+

 

Tableprices:

+———-+——-+———————+

| stock_id | price | date |

+———-+——-+———————+

| 1 | 0.05 | 2015-02-24 01:00:00 |

| 2 | 2.20 | 2015-02-24 01:00:00 |

| 1 | 0.50 | 2015-02-23 23:00:00 |

| 2 | 1.90 | 2015-02-23 23:00:00 |

| 3 | 2.10 | 2015-02-23 23:00:00 |

| 1 | 1.00 | 2015-02-23 19:00:00 |

| 2 | 1.00 | 2015-02-23 19:00:00 |

+———-+——-+———————+

 

I need a query that returns:

+———-+——-+———–+——-+

| stock_id | symbol| name | diff |

+———-+——-+———–+——-+

| 1 | AAPL | Apple | -0.45 |

| 2 | GOOG | Google | 0.30 |

| 3 | MSFT | Microsoft | NULL |

+———-+——-+———–+——-+

diff is the result of subtracting from the newest price of a stock the previous price. If one or less prices are present for a particular stock I should get NULL.

I have the following queries that return the last price and the previous price but I don’t know how to join everything

/* last */

SELECT price

FROM prices

WHERE stock_id = ‘1’

ORDER BY date DESC

LIMIT 1

/* previous */

SELECT price

FROM prices

WHERE stock_id = ‘1’

ORDER BY date DESC

LIMIT 1,1

 

【Answer】
The intra-group order-based calculations need to reference the first record and the second record. It’s complicated to do this in SQL. Yet only a two-liner script is enough in SPL (Structured Process Language):

	A
1	$select s.stock_id   stock_id,s.symbol symbol,s.name name,p.price price,p.date date from stocks   s,price p where s.stock_id=p.stock_id order by p.date desc
2	=A1.group(stock_id;symbol,name,if(p2=~.m(2).price,~.m(1).price-p2):diff)

A2: ~.m(i) gets the i^th record of a group. SPL uses m(-2) to get the second last record. These syntax makes it easy to perform order-based calculations and cross-row calculations.