分组后比较组内数据

原创于 2022-08-22 08:54:19 发布 · 296 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#分组 #有序计算

JAVA计算专栏收录该内容

363 篇文章

订阅专栏

本文探讨了一种SQL问题，即如何在分组后计算组内数据的差值，特别是获取最新价格与前一个价格的差值。当股票的价格历史记录不足两条时，结果应为NULL。虽然SQL实现此操作复杂，但使用SPL语言可以简化为两行代码，利用其序号思维和定位计算功能实现高效处理。

【问题】

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] 表示下一条，这种方式可以很容易进行有序计算和跨行计算，可参考：【集算器的序号思维及定位计算】