提取minu date和max date的列值(在minu date的24个月内)

dsf9zpds 于 2021-06-24 发布在 Hive

关注(0)|答案(1)|浏览(365)

产品：

+--------------+-----------+------------+------------+
| Product_code | Prod Name |   Status   |    Date    |
+--------------+-----------+------------+------------+
| A            | AA1       | New        | 12/3/2019  |
| A            | AA2       | Expiry     | 7/20/2017  |
| A            | AA1       | Others     | 8/15/2018  |
| A            | AA1       | Others_Loy | 7/10/2019  |
| B            | BB2       | Expiry     | 8/20/2017  |
| B            | BB1       | Others     | 8/15/2019  |
| B            | BB3       | New        | 7/15/2020  |
| C            | CC1       | Others     | 4/27/2020  |
| D            | DD1       | New        | 12/31/2017 |
| D            | DD3       | Expiry     | 2/15/2020  |
+--------------+-----------+------------+------------+

预期结果：最晚日期（最晚日期后24个月内）

+--------------+------------+------------+------------+------------+------------+-------+
| Product_code |  Min_date  | Min_status |  Max_date  | Max_status | count_Prod | Label |
+--------------+------------+------------+------------+------------+------------+-------+
| A            | 7/20/2017  | Expiry     | 7/10/2019  | Others_Loy |          3 |     1 |
| B            | 8/20/2017  | Expiry     | 8/15/2019  | Others     |          2 |     1 |
| C            | 4/27/2020  | Others     | 4/27/2020  | Others     |          1 |     0 |
| D            | 12/31/2017 | New        | 12/31/2017 | New        |          1 |     0 |
+--------------+------------+------------+------------+------------+------------+-------+

我想根据产品代码从最小日期提取记录，然后最大日期需要计算从最小日期到下一个24个月（在24个月的时间段内）的时间，并计算该时间段内的产品数量，如果在24个月的时间段内计数产品超过一个，则标签需要更新。
正在尝试此sql数据库：

sql Hive Database

来源：https://stackoverflow.com/questions/62992948/pull-the-column-value-for-min-date-max-date-within-24-months-min-date

1条答案

按热度按时间

vc6uscn91#

您还没有标记数据库，所以这基本上是伪代码。特别是，不同数据库的日期功能差别很大：

select product_code, 
       min_date, max(case when date = min_date then status end) as min_status,
       max_date, max(case when date = max_date then status end) as max_status,
       sum(case when date >= max_date - interval '2 year' then 1 else 0 end) as count_prod,
       (case when sum(case when date >= max_date - interval '2 year' then 1 else 0 end) > 1
             then 1 else 0
        end) as label
from (select t.*,
             min(date) over (partition by product_code) as min_date,
             max(date) over (partition by product_code) as max_date
      from t
     ) t
group by product_code;

您需要根据正在使用的数据库对此进行调整。

赞(0）回复(0）举报 2021-06-24

我来回答

提取minu date和max date的列值(在minu date的24个月内)

1条答案

相关问题

热门标签

最新问答