Let’s say I have given data as string:
I have two tables, one with the main data and a second table 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-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| +----------+-------+-----------+-------+
Where 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 */ SELECTprice FROMprices WHEREstock_id = '1' ORDERBYdate DESC LIMIT 1 /\* previous */ SELECTprice FROMprices WHEREstock_id = '1' ORDERBYdate DESC LIMIT 1,1
这类组内有序计算需要引用“第 1 条”和“第 2 条”，用 SQL 表达起来非常麻烦。这种情况用 SPL 实现很简单，只需 2 行代码：
| 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) 表示本组记录的第 i 条。
集算器还能用 m(-2)取倒数第 2 条，用 [1] 表示下一条，这种方式可以很容易进行有序计算和跨行计算，可参考：【集算器的序号思维及定位计算】