分组后比较组内前两条记录
【问题】
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-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 |
+———-+——-+———–+——-+
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
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
【回答】
这类组内有序计算需要引用“第 1 条”和“第 2 条”，用 SQL 实现起来非常麻烦。这种情况用集算器实现很简单，只需 2 行代码：
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) 表示获取本组记录的第 i 条。集算器还能用 m(-2) 取倒数第 2 条，这种方式可以很容易进行有序计算和跨行计算。
