Teradata SQL如何传输“按日期"到“日期范围"? [英] Teradata SQL how to transfer "by date" to by "date range"?

问题描述

我有6亿行，如下表1所示.在Teradata SQL中，如何将按日期"转换为日期范围"?

I have 600 Million rows as table 1 below. In Teradata SQL how to transfer "by date" to by "date range"?

+-----------+-------+------------+----------+
| ProductID | Store | Trans_Date | Cost_Amt |
+-----------+-------+------------+----------+
|     20202 |  2320 | 2018-01-02 |  $9.23   |
|     20202 |  2320 | 2018-01-03 |  $9.23   |
|     20202 |  2320 | 2018-01-04 |  $9.23   |
|     20202 |  2320 | 2018-01-05 |  $9.38   |
|     20202 |  2320 | 2018-01-06 |  $9.38   |
|     20202 |  2320 | 2018-01-07 |  $9.38   |
|     20202 |  2320 | 2018-01-08 |  $9.23   |
|     20202 |  2320 | 2018-01-09 |  $9.23   |
|     20202 |  2320 | 2018-01-10 |  $9.23   |
+-----------+-------+------------+----------+

所需的输出:

+-----------+-------+------------+------------+----------+
| ProductID | Store | Start Date |  End Date  | Cost_Amt |
+-----------+-------+------------+------------+----------+
|     20202 |  2320 | 2018-01-02 | 2018-01-04 |  $9.23   |
|     20202 |  2320 | 2018-01-05 | 2018-01-07 |  $9.38   |
|     20202 |  2320 | 2018-01-08 | 2018-01-10 |  $9.23   |
+-----------+-------+------------+------------+----------+

推荐答案

我将示例扩展为:

CREATE TABLE bigtable(
   ProductID  INTEGER 
  ,Store      INTEGER 
  ,Trans_Date DATE 
  ,Cost_Amt   VARCHAR(10)
);
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-02','$9.23');
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-03','$9.23');
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-04','$9.23');
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-05','$9.38');
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-06','$9.38');
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-07','$9.38');
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-08','$9.23');
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-09','$9.23');
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-10','$9.23');
INSERT INTO bigtable(ProductID,Store,Trans_Date,Cost_Amt) VALUES (20202,2320,'2018-01-11','$9.38');

，此查询用于显示派生表:

and this query is used to display the derived table:

select
    *
    , row_number() over(partition by ProductID,Store           order by Trans_Date) rn1
    , row_number() over(partition by ProductID,Store,Cost_Amt  order by Trans_Date) rn2
    , row_number() over(partition by ProductID,Store           order by Trans_Date)
    - row_number() over(partition by ProductID,Store,Cost_Amt  order by Trans_Date) grp
from bigtable
order by ProductID,Store,Trans_Date
;

计算出我们稍后需要的"grp"值:

which calculates a "grp" value we need later:

|    | ProductID | Store |     Trans_Date      | Cost_Amt | rn1 | rn2 | grp |
|----|-----------|-------|---------------------|----------|-----|-----|-----|
|  1 |     20202 |  2320 | 02.01.2018 00:00:00 | $9.23    |   1 |   1 |   0 |
|  2 |     20202 |  2320 | 03.01.2018 00:00:00 | $9.23    |   2 |   2 |   0 |
|  3 |     20202 |  2320 | 04.01.2018 00:00:00 | $9.23    |   3 |   3 |   0 |
|  4 |     20202 |  2320 | 05.01.2018 00:00:00 | $9.38    |   4 |   1 |   3 |
|  5 |     20202 |  2320 | 06.01.2018 00:00:00 | $9.38    |   5 |   2 |   3 |
|  6 |     20202 |  2320 | 07.01.2018 00:00:00 | $9.38    |   6 |   3 |   3 |
|  7 |     20202 |  2320 | 08.01.2018 00:00:00 | $9.23    |   7 |   4 |   3 |
|  8 |     20202 |  2320 | 09.01.2018 00:00:00 | $9.23    |   8 |   5 |   3 |
|  9 |     20202 |  2320 | 10.01.2018 00:00:00 | $9.23    |   9 |   6 |   3 |
| 10 |     20202 |  2320 | 11.01.2018 00:00:00 | $9.38    |  10 |   4 |   6 |

现在可以计算日期范围:

and the date ranges are now calculated:

select
      ProductID
    , Store
    , Cost_Amt
    , grp
    , min(Trans_Date) start_date
    , max(Trans_Date) end_date
from (
    select
        *
        , row_number() over(partition by ProductID,Store           order by Trans_Date)
        - row_number() over(partition by ProductID,Store,Cost_Amt  order by Trans_Date) grp
    from bigtable
    ) d
group by
      ProductID
    , Store
    , Cost_Amt
    , grp
;

其结果是:

|    | ProductID | Store | Cost_Amt | grp |  (No column name)   |  (No column name)   |
|----|-----------|-------|----------|-----|---------------------|---------------------|
|  1 |     20202 |  2320 | $9.23    |   0 | 02.01.2018 00:00:00 | 04.01.2018 00:00:00 |
|  2 |     20202 |  2320 | $9.23    |   3 | 08.01.2018 00:00:00 | 10.01.2018 00:00:00 |
|  3 |     20202 |  2320 | $9.38    |   3 | 05.01.2018 00:00:00 | 07.01.2018 00:00:00 |
|  4 |     20202 |  2320 | $9.38    |   6 | 11.01.2018 00:00:00 | 11.01.2018 00:00:00 |

另请参阅: http://rextester.com/PJRU91378

这篇关于Teradata SQL如何传输“按日期"到“日期范围"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！