【Question】

I have two tables containing string data. One is the main table and the other contains 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-2401:00:00|

 

| 2| 2.20| 2015-02-2401:00:00|

 

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

 

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

 

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

 

| 1| 1.00| 2015-02-2319:00:00|

 

| 2| 1.00| 2015-02-2319: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 one. 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

 

WHEREstock_id = '1'

 

ORDERBYdate DESC

 

LIMIT 1

 

/\* previous */

 

SELECT price

 

FROM prices

 

WHEREstock_id = '1'

 

ORDERBYdate DESC

 

LIMIT 1,1

 

【Answer】

You question is an order-based one. It involves reference of “the first” and “the second”, which is hard to code in SQL. We use SPL (Structured Process Language) to do it. It needs a two-liner only:

	A
1	$(db1)select s.stock_id   stock_id,s.symbol symbol,s.name name,p.price price,p.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)

 

~.m(i) represents the i^th record in the current group.

SPL supports getting the second-to-last record using m(-2) and the next record with [1]. This syntax makes it easy to write an order-based or inter-row calculation.