java - 要计算 Oracle DB 中股票数据价格列表的移动平均值 - 使用 SQL 还是 Java 更快？

Question

我有一个 Oracle 数据库，其中包含一个包含 Assets 价格数据的表，我想在单独的列中计算该 Assets 价格数据的 10 天移动平均值。

使用 SQL 更快还是应该先将数据加载到 Java Hashmap/ArrayList，然后进行计算并将结果传输回 Oracle DB？

表格如下所示:

| ASSET_ID | PRICE | DATE       | MA    |
-----------------------------------------
| 43       | 33.12 | 2018-09-17 | 33.05 |
| 43       | 34.02 | 2018-09-18 | 33.07 |
| 43       | 30.22 | 2018-09-19 | 33.01 |
| 43       | 31.52 | 2018-09-20 | 32.85 |

Answer 1

使用 AVG( PRICE ) OVER (PARTITION BY asset_id ORDER BY "DATE"RANGE BETWEEN 10 PRECEDING AND 0 FOLLOWING ) 获取移动平均值:

SQL Fiddle

Oracle 11g R2 架构设置:

CREATE TABLE table_name ( ASSET_ID, PRICE, "DATE", MA ) AS
SELECT 43, 33.12, DATE '2018-09-17', CAST( NULL AS NUMBER(8,2) ) FROM DUAL UNION ALL
SELECT 43, 34.02, DATE '2018-09-18', NULL FROM DUAL UNION ALL
SELECT 43, 30.22, DATE '2018-09-19', NULL FROM DUAL UNION ALL
SELECT 43, 31.52, DATE '2018-09-20', NULL FROM DUAL UNION ALL
SELECT 43, 32.52, DATE '2018-09-21', NULL FROM DUAL UNION ALL
SELECT 43, 33.52, DATE '2018-09-22', NULL FROM DUAL UNION ALL
SELECT 43, 34.52, DATE '2018-09-23', NULL FROM DUAL UNION ALL
SELECT 43, 35.52, DATE '2018-09-24', NULL FROM DUAL UNION ALL
SELECT 43, 36.52, DATE '2018-09-25', NULL FROM DUAL UNION ALL
SELECT 43, 37.52, DATE '2018-09-26', NULL FROM DUAL UNION ALL
SELECT 43, 38.52, DATE '2018-09-27', NULL FROM DUAL UNION ALL
SELECT 43, 39.52, DATE '2018-09-28', NULL FROM DUAL UNION ALL
SELECT 43, 40.52, DATE '2018-09-29', NULL FROM DUAL UNION ALL
SELECT 43, 41.52, DATE '2018-09-30', NULL FROM DUAL;

查询 1:

MERGE INTO table_name dst
USING (
  SELECT ROWID rid,
         ROUND(
           AVG( price ) OVER (
             PARTITION BY asset_id
             ORDER BY "DATE"
             RANGE BETWEEN 10 PRECEDING AND 0 FOLLOWING
           ),
           2
         ) AS new_MA
  FROM   table_name
) src
ON ( dst.ROWID = src.rid )
WHEN MATCHED THEN
  UPDATE SET MA = src.new_MA

<强> Results :

14 Rows Updated

查询 2:

SELECT *
FROM   table_name

<强> Results :

| ASSET_ID | PRICE |                 DATE |    MA |
|----------|-------|----------------------|-------|
|       43 | 33.12 | 2018-09-17T00:00:00Z | 33.12 |
|       43 | 34.02 | 2018-09-18T00:00:00Z | 33.57 |
|       43 | 30.22 | 2018-09-19T00:00:00Z | 32.45 |
|       43 | 31.52 | 2018-09-20T00:00:00Z | 32.22 |
|       43 | 32.52 | 2018-09-21T00:00:00Z | 32.28 |
|       43 | 33.52 | 2018-09-22T00:00:00Z | 32.49 |
|       43 | 34.52 | 2018-09-23T00:00:00Z | 32.78 |
|       43 | 35.52 | 2018-09-24T00:00:00Z | 33.12 |
|       43 | 36.52 | 2018-09-25T00:00:00Z |  33.5 |
|       43 | 37.52 | 2018-09-26T00:00:00Z |  33.9 |
|       43 | 38.52 | 2018-09-27T00:00:00Z | 34.32 |
|       43 | 39.52 | 2018-09-28T00:00:00Z |  34.9 |
|       43 | 40.52 | 2018-09-29T00:00:00Z | 35.49 |
|       43 | 41.52 | 2018-09-30T00:00:00Z | 36.52 |