oracle 根据后续交易对客户交易进行分组

k3bvogb1 于 7个月前发布在 Oracle

关注(0)|答案(2)|浏览(83)

要求的微小变化
需要Terms SQL查询来创建从第一个事务处理到其后续事务处理的分组。如果后续事务处理是在前一个事务处理之后30天内进行的，则将它们都视为属于同一个组。如果任何事务处理是在前一个事务处理之后30天以上进行的，则断开该组并启动新的事务处理。
数据：
| trans_Id|客户ID|转换日期|
| --|--|--|
| 001 | 1101 |2020-11-02 2020-11-02|
| 002 | 1101 |2020-11-14 2020-11-14|
| 003 | 1101 |2020-11-18 2020-11-18|
| 004 | 1101 |2021-12-04 2021-12-04 2021-12-04|
| 005 | 1101 |2021-01-06 2021-01-06|
| 006 | 1101 |2021-01-08 2021-01-08|
| 007 | 1101 |2021-02-17 2021-02-17 2021-02-17|
| 008 | 1101 |2021-03-01 2021-03-01|
| 009 | 1101 |2021-03-04 2021-03-04|
| 010 | 1102 |2021-03-02 2021-03-02|
| 011 | 1102 |2021-03-08 2021-03-08|
| 012 | 1102 |2021-04-01 2021-04-01 2021-04-01|
| 013 | 1102 |2021-04-02 2021-04-02|
| 014 | 1102 |2021-04-12 2021-04-12 2021-04-12|
| 015 | 1102 |2021-04-29 2021-04-29 2021-04-29|
| 016 | 1102 |2021-06-10 2021-06-10|
| 017 | 1102 |2021-06- 12 2021-06-12 2021-06-12|
预期结果（预期分组如下）。从001到004在其前一个交易的30天内下降。005从其前一个交易已经过去了30天，因此必须开始新的组。
| trans_Id|客户ID|转换日期|组|
| --|--|--|--|
| 001 | 1101 |2020-11-02 2020-11-02| 1 |
| 002 | 1101 |2020-11-14 2020-11-14| 1 |
| 003 | 1101 |2020-11-18 2020-11-18| 1 |
| 004 | 1101 |2021-12-04 2021-12-04 2021-12-04| 1 |
| 005 | 1101 |2021-01-06 2021-01-06| 2 |
| 006 | 1101 |2021-01-08 2021-01-08| 2 |
| 007 | 1101 |2021-02-17 2021-02-17 2021-02-17| 3 |
| 008 | 1101 |2021-03-01 2021-03-01| 3 |
| 009 | 1101 |2021-03-04 2021-03-04| 3 |
| 010 | 1102 |2021-03-02 2021-03-02| 1 |
| 011 | 1102 |2021-03-08 2021-03-08| 1 |
| 012 | 1102 |2021-04-01 2021-04-01 2021-04-01| 2 |
| 013 | 1102 |2021-04-02 2021-04-02| 2 |
| 014 | 1102 |2021-04-12 2021-04-12 2021-04-12| 2 |
| 015 | 1102 |2021-04-29 2021-04-29 2021-04-29| 2 |
| 016 | 1102 |2021-06-10 2021-06-10| 3 |
| 017 | 1102 |2021-06- 12 2021-06-12 2021-06-12| 3 |

oracle

来源：https://stackoverflow.com/questions/77428022/group-customer-transactions-based-on-the-subsequent-transaction

2条答案

按热度按时间

gwo2fgha1#

在Oracle 12中，您可以使用MATCH_RECOGNIZE执行逐行模式匹配：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY customer_id
  ORDER BY trans_date
  MEASURES
    MATCH_NUMBER() AS grp
  ALL ROWS PER MATCH
  PATTERN ( within_30_days+ )
  DEFINE
    within_30_days AS trans_date <= FIRST(trans_date) + INTERVAL '30' DAY
)

字符串
其中，对于样本数据：

CREATE TABLE table_name (trans_id, customer_id, trans_date) AS
  SELECT '001', '1101',  DATE '2020-11-02' FROM DUAL UNION ALL
  SELECT '002', '1101',  DATE '2020-11-14' FROM DUAL UNION ALL
  SELECT '003', '1101',  DATE '2020-11-18' FROM DUAL UNION ALL
  SELECT '004', '1101',  DATE '2021-12-04' FROM DUAL UNION ALL
  SELECT '005', '1101',  DATE '2021-12-05' FROM DUAL UNION ALL
  SELECT '006', '1101',  DATE '2021-12-08' FROM DUAL UNION ALL
  SELECT '007', '1101',  DATE '2021-01-17' FROM DUAL UNION ALL
  SELECT '008', '1101',  DATE '2021-05-01' FROM DUAL UNION ALL
  SELECT '009', '1101',  DATE '2021-05-04' FROM DUAL UNION ALL
  SELECT '010', '1102',  DATE '2021-03-02' FROM DUAL UNION ALL
  SELECT '011', '1102',  DATE '2021-03-08' FROM DUAL UNION ALL
  SELECT '012', '1102',  DATE '2021-04-01' FROM DUAL UNION ALL
  SELECT '013', '1102',  DATE '2021-04-02' FROM DUAL UNION ALL
  SELECT '014', '1102',  DATE '2021-04-12' FROM DUAL UNION ALL
  SELECT '015', '1102',  DATE '2021-04-29' FROM DUAL UNION ALL
  SELECT '016', '1102',  DATE '2021-06-10' FROM DUAL UNION ALL
  SELECT '017', '1102',  DATE '2021-06-12' FROM DUAL;

型
产出：
| 客户ID|交易日期|GRP|运输ID|
| --|--|--|--|
| 1101 |2020-11-02 00：00：00| 1 | 001 |
| 1101 |2020-11-14 00：00：00| 1 | 002 |
| 1101 |2020-11-18 00：00：00| 1 | 003 |
| 1101 |2021-01-17 00：00：00| 2 | 007 |
| 1101 |2021-05-01 00：00：00| 3 | 008 |
| 1101 |2021-05-04 00：00：00| 3 | 009 |
| 1101 |2021-12-04 00：00：00| 4 | 004 |
| 1101 |2021-12-05 00：00：00| 4 | 005 |
| 1101 |2021-12-08 00：00：00| 4 | 006 |
| 1102 |2021-03-02 00：00：00| 1 | 010 |
| 1102 |2021-03-08 00：00：00| 1 | 011 |
| 1102 |2021-04-01 00：00：00| 1 | 012 |
| 1102 |2021-04-02 00：00：00| 2 | 013 |
| 1102 |2021-04-12 00：00：00| 2 | 014 |
| 1102 |2021-04-29 00：00：00| 2 | 015 |
| 1102 |2021-06-10 00：00：00| 3 | 016 |
| 1102 |2021-06-12 00：00：00| 3 | 017 |
fiddle

赞(0）回复(0）举报 7个月前

h9vpoimq2#

Other Way(ORACLE):
with jobdata as (
    select '001' as trans_Id,   '1101' as customer_id,  to_date('2020-11-02', 'YYYY-MM-DD') as trans_date from dual union all
    select '002' as trans_Id,   '1101' as customer_id,  to_date('2020-11-14', 'YYYY-MM-DD') as trans_date from dual  union all
    select '003' as trans_Id,   '1101' as customer_id,  to_date('2020-11-18', 'YYYY-MM-DD') as trans_date from dual  union all
    select '004' as trans_Id,   '1101' as customer_id,  to_date('2021-12-04', 'YYYY-MM-DD') as trans_date from dual  union all
    select '005' as trans_Id,   '1101' as customer_id,  to_date('2021-12-05', 'YYYY-MM-DD') as trans_date from dual  union all
    select '006' as trans_Id,   '1101' as customer_id,  to_date('2021-12-08', 'YYYY-MM-DD') as trans_date from dual  union all
    select '007' as trans_Id,   '1101' as customer_id,  to_date('2021-01-17', 'YYYY-MM-DD') as trans_date from dual  union all
    select '008' as trans_Id,   '1101' as customer_id,  to_date('2021-05-01', 'YYYY-MM-DD') as trans_date from dual  union all
    select '009' as trans_Id,   '1101' as customer_id,  to_date('2021-05-04', 'YYYY-MM-DD') as trans_date from dual  union all
    select '010' as trans_Id,   '1102' as customer_id,  to_date('2021-03-02', 'YYYY-MM-DD') as trans_date from dual  union all
    select '011' as trans_Id,   '1102' as customer_id,  to_date('2021-03-08', 'YYYY-MM-DD') as trans_date from dual  union all
    select '012' as trans_Id,   '1102' as customer_id,  to_date('2021-04-01', 'YYYY-MM-DD') as trans_date from dual  union all
    select '013' as trans_Id,   '1102' as customer_id,  to_date('2021-04-02', 'YYYY-MM-DD') as trans_date from dual  union all
    select '014' as trans_Id,   '1102' as customer_id,  to_date('2021-04-12', 'YYYY-MM-DD') as trans_date from dual  union all
    select '015' as trans_Id,   '1102' as customer_id,  to_date('2021-04-29', 'YYYY-MM-DD') as trans_date from dual  union all
    select '016' as trans_Id,   '1102' as customer_id,  to_date('2021-06-10', 'YYYY-MM-DD') as trans_date from dual  union all
    select '017' as trans_Id,   '1102' as customer_id,  to_date('2021-06-12', 'YYYY-MM-DD') as trans_date from dual 
)
select a.trans_Id, a.customer_id, a.trans_date 
    , dense_rank() over (partition by a.customer_id order by gap_group) as group_id
from (
    select a.*, min(a.trans_date) over (partition by a.customer_id order by customer_id, trans_date) as min_trans_date
        , a.trans_date - min(a.trans_date) over (partition by a.customer_id order by customer_id, trans_date) as gap_day
        , trunc((a.trans_date - min(a.trans_date) over (partition by a.customer_id order by customer_id, trans_date)) / 30) + 1 as gap_group
    from jobdata a
) a       
;

字符串

赞(0）回复(0）举报 7个月前

我来回答

oracle 根据后续交易对客户交易进行分组

2条答案

相关问题

热门标签

最新问答