时间戳

首页 > 解决方案 > 列的总和和行之间的列表差异

sql - 列的总和和行之间的列表差异

问题描述

我正在尝试根据下表中的 SELL_ID 分组来获取行之间的差异，

table1 -（表格格式由GitHub提供）

+---------+---------+----------+----------+------------------+---------+
| seq_ID  | REQ_ID  | CALL_ID  | SELL_ID  |     REGION       |  COUNT  |
+---------+---------+----------+----------+------------------+---------+
|    1    |    123  | C001     | S1       | AGL              |  510563 |
|    2    |    123  | C001     | S1       | USL              |  122967 |
|    3    |    123  | C001     | S1       | VALIC            |  614106 |
|    4    |    123  | C001     | S2       | Inforce          | 1247636 |
|    5    |    123  | C001     | S2       | NB               |       0 |
|    6    |    123  | C001     | S3       | Seriatim Summary | 1247636 |
+---------+---------+----------+----------+------------------+---------+

我试图得到如下结果，

表2 -

+---------+---------+----------+----------+-------+
| seq_ID  | REQ_ID  | CALL_ID  | Summary  | COUNT |
+---------+---------+----------+----------+-------+
|    1    |    123  | C001     | S1_vs_S2 |     0 |
|    2    |    123  | C001     | S2_vs_S3 |     0 |
|    3    |    123  | C001     | S3_vs_s1 |     0 |
+---------+---------+----------+----------+-------+

(sum(count) from table1 where sell_id='S1')S1_vs_S2 是和之间的区别(sum(count) from table1 where sell_id='S2')

下面是我正在使用的代码，但无法获取结果，

INSERT INTO table2 (SEQ_ID, REQ_ID,call_id,summary,count) 
SELECT min(seq_id) seq_id
     , req_id
     , call_id
     , S1_vs_S2
     ,((SELECT sum(c2) FROM TABLE_STG_CTRL WHERE source='S1')-
        SELECT sum(c2) FROM TABLE_STG_CTRL WHERE source='S2'))
FROM table1
GROUP BY req_ID, Ctrl_ID, c1, source 
ORDER BY SEQ_ID ;

标签： sqloracle

解决方案

这是做你想做的吗？

select req_id, call_id, sell_id,
       lead(sell_id) over (partition by req_id, call_id order by seq_id) as next_sell_id,
       (cnt -
        lead(cnt) over (partition by req_id, call_id order by seq_id)
       ) as diff
from (select req_id, call_id, sell_id, sum(count) as cnt, min(seq_id) as seq_id
      from t
      group by req_id, call_id, sell_id
     ) t

推荐阅读