分组后比较组内数据
【问题】
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 */
SELECT price
FROM prices
WHEREstock_id = '1'
ORDERBYdate DESC
LIMIT 1
/\* previous */
SELECT price
FROM prices
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] 表示下一条，这种方式可以很容易进行有序计算和跨行计算，可参考：【集算器的序号思维及定位计算】
