时间戳

首页 > 解决方案 > 要计算 Oracle DB 中股票数据价格列表的移动平均线 - 使用 SQL 还是 Java 更快？

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

问题描述

我有一个 Oracle 数据库，其中包含一个包含资产价格数据的表，我想在单独的列中计算 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 |

标签： javasqloracle

解决方案

用于AVG( PRICE ) OVER ( PARTITION BY asset_id ORDER BY "DATE" RANGE BETWEEN 10 PRECEDING AND 0 FOLLOWING )获得移动平均线：

SQL小提琴

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

结果：

14 Rows Updated

查询 2：

SELECT *
FROM   table_name

结果：

| 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 |

推荐阅读