当前和以前的日期

Question

我正在为下面的场景寻找Teradata查询帮助。

我有一个表，包含客户id和trans日期及金额字段，并希望获得所需的输出，包括开始日期和结束日期，以及每个客户在这些日期之间花费的金额。

对于第一个事务的开始日期，我想将其硬编码为2000-01-01。

Custid   Date       Amount    

1        2014-10-11    $100
1        2014-11-01    $50
1        2015-01-05    $20
2        2016-01-01    $40
2        2016-05-01    $60

我要输出如下所示

CustID   Start_Date   End_Date     Amount
1        2000-01-01   2014-10-11   $100
1        2014-10-11   2014-11-01   $50
1        2014-11-01   2015-01-05   $20
2        2000-01-1    2016-01-01   $40
2        2016-01-01   2016-05-01   $60

有谁能帮上忙吗？

Answer 1

您可以使用窗口函数来实现这一点(本质上这与其他RDBMS中的Lag()功能类似)：

SELECT
    CustId,
    COALESCE(MAX("Date") OVER (PARTITION BY CustId ORDER BY "Date" ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), DATE '2000-01-01') AS Start_Date,
    "Date" as End_Date,
    Amount
FROM
    yourtable;

正在使用的例子：

CREATE MULTISET VOLATILE TABLE mytable(
   Custid INTEGER  NOT NULL
  ,"Date"   DATE  NOT NULL
  ,Amount VARCHAR(5) NOT NULL
) PRIMARY INDEX (CustID) ON COMMIT PRESERVE ROWS;

INSERT INTO mytable(Custid,"Date",Amount) VALUES (1,'2014-10-11','$100');
INSERT INTO mytable(Custid,"Date",Amount) VALUES (1,'2014-11-01','$50');
INSERT INTO mytable(Custid,"Date",Amount) VALUES (1,'2015-01-05','$20');
INSERT INTO mytable(Custid,"Date",Amount) VALUES (2,'2016-01-01','$40');
INSERT INTO mytable(Custid,"Date",Amount) VALUES (2,'2016-05-01','$60');

SELECT
    CustId,
    COALESCE(MAX("Date") OVER (PARTITION BY CustId ORDER BY "Date" ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), DATE '2000-01-01') AS Start_Date,
    "Date" as End_Date,
    Amount
FROM
    mytable;

DROP TABLE mytable;

+--------+------------+------------+--------+
| Custid | Start_Date |  End_Date  | Amount |
+--------+------------+------------+--------+
|      1 | 1/1/2000   | 10/11/2014 | $100   |
|      1 | 10/11/2014 | 11/1/2014  | $50    |
|      1 | 11/1/2014  | 1/5/2015   | $20    |
|      2 | 1/1/2000   | 1/1/2016   | $40    |
|      2 | 1/1/2016   | 5/1/2016   | $60    |
+--------+------------+------------+--------+

可以选择在SELECT子句中使用关联子查询：

SELECT 
    CustId,
    COALESCE((SELECT MAX("Date") FROM myTable WHERE mt.CustId = CustID AND "Date" < mt."Date"), DATE '2000-01-01') AS Start_Date,
    "Date" AS End_Date,
    amount AS amount
FROM
    mytable mt;

正在使用的例子：

CREATE MULTISET VOLATILE TABLE mytable(
   Custid INTEGER  NOT NULL
  ,"Date"   DATE  NOT NULL
  ,Amount VARCHAR(5) NOT NULL
) PRIMARY INDEX (CustID) ON COMMIT PRESERVE ROWS;

INSERT INTO mytable(Custid,"Date",Amount) VALUES (1,'2014-10-11','$100');
INSERT INTO mytable(Custid,"Date",Amount) VALUES (1,'2014-11-01','$50');
INSERT INTO mytable(Custid,"Date",Amount) VALUES (1,'2015-01-05','$20');
INSERT INTO mytable(Custid,"Date",Amount) VALUES (2,'2016-01-01','$40');
INSERT INTO mytable(Custid,"Date",Amount) VALUES (2,'2016-05-01','$60');


SELECT 
    CustId,
    COALESCE((SELECT MAX("Date") FROM myTable WHERE mt.CustId = CustID AND "Date" < mt."Date"), DATE '2000-01-01') AS Start_Date,
    "Date" AS End_Date,
    amount AS amount
FROM
    mytable mt;


DROP TABLE mytable;

+--------+------------+------------+--------+
| Custid | Start_Date |  End_Date  | amount |
+--------+------------+------------+--------+
|      2 | 2016-01-01 | 2016-05-01 | $60    |
|      1 | 2014-11-01 | 2015-01-05 | $20    |
|      2 | 2000-01-01 | 2016-01-01 | $40    |
|      1 | 2014-10-11 | 2014-11-01 | $50    |
|      1 | 2000-01-01 | 2014-10-11 | $100   |
+--------+------------+------------+--------+