3列表，对我来说太难了

Question

我是一个非常新的SQL用户，我有一个包含3列的大型数据库：公司名称、日期和下个月的公司股票回报。如何有效地将月度数据转换为年度数据？我试着将每个月与接下来的12个月连接起来，这样我就可以将12列相乘，但这太低效了。所有日期都是一个月的最后一天。如有任何帮助，我们将不胜感激！

这是表格的样子：

Company Name              date          return
apple               11/30/2012            1.05
apple               12/31/2012             .97
apple                1/31/2013            1.01
apple                2/28/2013            1.04
ford                11/30/2012            1.05
ford                12/31/2012             .97
ford                 1/31/2013            1.01
BP                   6/30/2012             .95

我希望收益是未来12个月的收益加在一起。这能有效地完成吗？

Answer 1

这样的东西应该能在中工作

SELECT 
      CompanyName, 
      YEAR(date), 
      EXP(SUM(LOG(return))) As AnnualReturn
FROM  yourTable
GROUP BY CompanyName, YEAR(date)
ORDER BY CompanyName, YEAR(date)

YEAR(..)函数等内容的细节因特定的DBMS产品而异，但这是基本方法。

Answer 2

如果使用ORACLE，则可以使用以下方法：

SQL Fiddle

没有PRODUCT聚合函数，但您可以编写自定义聚合函数。。。

Oracle 11g R2架构设置:

CREATE TYPE ProductAggregationType as OBJECT
(
  product NUMBER,
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ProductAggregationType) 
    RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ProductAggregationType, 
    value IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN ProductAggregationType, 
    RETURNValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT ProductAggregationType, 
    ctx2 IN ProductAggregationType) RETURN NUMBER
);
/
CREATE OR REPLACE type body ProductAggregationType is 
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ProductAggregationType) 
RETURN NUMBER IS
BEGIN
  sctx := ProductAggregationType(1);
  RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ProductAggregationType, value IN NUMBER) RETURN NUMBER is
BEGIN
  self.product := self.product * value;
  RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN ProductAggregationType, 
    returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER is
BEGIN
  returnValue := self.product;
  RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT ProductAggregationType, ctx2 IN ProductAggregationType) RETURN NUMBER is
BEGIN
  self.product := self.product * ctx2.product;
  RETURN ODCIConst.Success;
END;
END;
/
CREATE FUNCTION PRODUCT (input NUMBER) RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING ProductAggregationType;
/
CREATE TABLE data ( names, dates, "return" ) AS
          SELECT 'A', TO_DATE( '31-01-2021', 'DD-MM-YYYY' ), 1.05 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '28-02-2021', 'DD-MM-YYYY' ), 0.97 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-03-2021', 'DD-MM-YYYY' ), 1.01 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '30-04-2021', 'DD-MM-YYYY' ), 1.04 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-05-2021', 'DD-MM-YYYY' ), 1.05 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '30-06-2021', 'DD-MM-YYYY' ), 0.97 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-07-2021', 'DD-MM-YYYY' ), 1.01 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-08-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '30-09-2021', 'DD-MM-YYYY' ), 1.03 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-10-2021', 'DD-MM-YYYY' ), 0.90 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '30-11-2021', 'DD-MM-YYYY' ), 1.00 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-12-2021', 'DD-MM-YYYY' ), 1.12 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-01-2022', 'DD-MM-YYYY' ), 0.90 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '28-02-2022', 'DD-MM-YYYY' ), 1.10 FROM DUAL
UNION ALL SELECT 'A', TO_DATE( '31-03-2022', 'DD-MM-YYYY' ), 1.00 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-01-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '28-02-2021', 'DD-MM-YYYY' ), 0.98 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-03-2021', 'DD-MM-YYYY' ), 1.03 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '30-04-2021', 'DD-MM-YYYY' ), 1.01 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-05-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '30-06-2021', 'DD-MM-YYYY' ), 0.96 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-07-2021', 'DD-MM-YYYY' ), 1.05 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-08-2021', 'DD-MM-YYYY' ), 1.01 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '30-09-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-10-2021', 'DD-MM-YYYY' ), 0.97 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '30-11-2021', 'DD-MM-YYYY' ), 1.02 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-12-2021', 'DD-MM-YYYY' ), 1.08 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-01-2022', 'DD-MM-YYYY' ), 0.95 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '28-02-2022', 'DD-MM-YYYY' ), 1.05 FROM DUAL
UNION ALL SELECT 'B', TO_DATE( '31-03-2022', 'DD-MM-YYYY' ), 1.00 FROM DUAL
/

查询1：

SELECT names,
       EXTRACT( YEAR FROM dates ) AS year,
       PRODUCT( "return" ) AS total_return
FROM   data
GROUP BY names, EXTRACT( YEAR FROM Dates )
ORDER BY year

结果：

| NAMES | YEAR |   TOTAL_RETURN |
|-------|------|----------------|
|     A | 2021 | 1.165456536894 |
|     B | 2021 | 1.176962855452 |
|     A | 2022 |           0.99 |
|     B | 2022 |         0.9975 |

查询2：

或者你可以用对数的幂来计算乘积：

SELECT names,
       EXTRACT( YEAR FROM dates ) AS year,
       EXP(SUM(LN( "return" ))) AS total_return
FROM   data
GROUP BY names, EXTRACT( YEAR FROM Dates )
ORDER BY year

结果：

| NAMES | YEAR |   TOTAL_RETURN |
|-------|------|----------------|
|     A | 2021 | 1.165456536894 |
|     B | 2021 | 1.176962855452 |
|     A | 2022 |           0.99 |
|     B | 2022 |         0.9975 |