我有表 [visit]。当 order_number 为 null 时，我需要获取按 user_id 分组的行，总和为 visit_duration_seconds，例如，对于用户 [2875636]，我将得到：61+151+33+13。每一行应包括其前面的行的总和。
另请参阅下面的预期结果中的结果列
user_id starttime visit_duration_seconds order_number 2875636 2013-01-16 18:03:50 61 2875636 2013-01-16 18:08:18 151 2875636 2013-01-16 18:15:43 33 2875636 2013-01-16 18:16:37 13 2875636 2013-01-16 18:18:01 2011 10177888 2875636 2013-01-16 18:24:35 1172 10177884 2875636 2013-01-16 18:32:03 4731 2875636 2013-01-16 18:33:27 407 2875636 2013-01-16 18:37:29 74 2875636 2013-01-16 18:48:55 80 2875636 2013-01-16 19:05:00 1955 2875636 2013-01-16 19:14:12 326 2875636 2013-01-16 19:23:39 972 2875636 2013-01-16 19:33:05 5440 2875636 2013-01-16 19:35:48 43 2875636 2013-01-16 19:41:10 66 2875636 2013-01-16 19:42:03 100 2875636 2013-01-16 19:42:12 2414 10177940 2875636 2013-01-16 19:49:05 432 10177925 2875636 2013-01-16 19:50:19 183 2875636 2013-01-16 19:52:46 2061 2875636 2013-01-16 19:52:53 400 2875636 2013-01-16 20:00:47 338 2875636 2013-01-16 20:08:58 216 2875636 2013-01-16 20:14:21 58 2875636 2013-01-16 20:14:26 196 2875636 2013-01-16 20:19:14 2189 2875636 2013-01-16 20:21:29 424 2875636 2013-01-16 20:24:42 999 2875636 2013-01-16 21:01:39 1810 2875636 2013-01-16 21:02:54 525 2875636 2013-01-16 21:10:06 27 2875636 2013-01-16 21:12:08 282 2875636 2013-01-16 21:51:02 6 2875636 2013-01-16 22:18:34 173 2875636 2013-01-16 23:02:58 318 2875636 2013-01-16 23:45:37 207 3018868 2013-01-16 16:01:45 18 3018868 2013-01-16 16:16:45 39 3018868 2013-01-16 16:22:55 656 3018868 2013-01-16 16:25:54 1852 3018868 2013-01-16 16:29:23 688 3018868 2013-01-16 16:47:26 2258 10177846 3018868 2013-01-16 16:57:41 572 3018868 2013-01-16 17:06:47 1431 3018868 2013-01-16 17:18:32 29 3018868 2013-01-16 17:21:57 45 3018868 2013-01-16 17:29:23 16 3018868 2013-01-16 17:36:47 490
预期成果
user_id starttime visit_duration_seconds order_number RESULT
2875636 2013-01-16 18:03:50 61 61
2875636 2013-01-16 18:08:18 151 212
2875636 2013-01-16 18:15:43 33 245
2875636 2013-01-16 18:16:37 13 258
2875636 2013-01-16 18:18:01 2011 10177888 0
2875636 2013-01-16 18:24:35 1172 10177884 0
2875636 2013-01-16 18:32:03 4731 4731
2875636 2013-01-16 18:33:27 407 5138
2875636 2013-01-16 18:37:29 74 5212
2875636 2013-01-16 18:48:55 80 ...
2875636 2013-01-16 19:05:00 1955 ...
2875636 2013-01-16 19:14:12 326 ...
2875636 2013-01-16 19:23:39 972
2875636 2013-01-16 19:33:05 5440
2875636 2013-01-16 19:35:48 43
2875636 2013-01-16 19:41:10 66
2875636 2013-01-16 19:42:03 100
2875636 2013-01-16 19:42:12 2414 10177940
2875636 2013-01-16 19:49:05 432 10177925
2875636 2013-01-16 19:50:19 183
2875636 2013-01-16 19:52:46 2061
2875636 2013-01-16 19:52:53 400
2875636 2013-01-16 20:00:47 338
2875636 2013-01-16 20:08:58 216
2875636 2013-01-16 20:14:21 58
2875636 2013-01-16 20:14:26 196
2875636 2013-01-16 20:19:14 2189
2875636 2013-01-16 20:21:29 424
2875636 2013-01-16 20:24:42 999
2875636 2013-01-16 21:01:39 1810
2875636 2013-01-16 21:02:54 525
2875636 2013-01-16 21:10:06 27
2875636 2013-01-16 21:12:08 282
2875636 2013-01-16 21:51:02 6
2875636 2013-01-16 22:18:34 173
2875636 2013-01-16 23:02:58 318
2875636 2013-01-16 23:45:37 207
3018868 2013-01-16 16:01:45 18
3018868 2013-01-16 16:16:45 39
3018868 2013-01-16 16:22:55 656
3018868 2013-01-16 16:25:54 1852
3018868 2013-01-16 16:29:23 688
3018868 2013-01-16 16:47:26 2258 10177846
3018868 2013-01-16 16:57:41 572
3018868 2013-01-16 17:06:47 1431
3018868 2013-01-16 17:18:32 29
3018868 2013-01-16 17:21:57 45
3018868 2013-01-16 17:29:23 16
3018868 2013-01-16 17:36:47 490
使用示例数据创建表：
SQL> create table mytable (user_id,starttime,visit_duration_seconds,order_number)
2 as
3 select 2875636, to_date('2013-01-16 18:03:50','yyyy-mm-dd hh24:mi:ss'), 61, null from dual union all
4 select 2875636, to_date('2013-01-16 18:08:18','yyyy-mm-dd hh24:mi:ss'), 151, null from dual union all
5 select 2875636, to_date('2013-01-16 18:15:43','yyyy-mm-dd hh24:mi:ss'), 33, null from dual union all
6 select 2875636, to_date('2013-01-16 18:16:37','yyyy-mm-dd hh24:mi:ss'), 13, null from dual union all
7 select 2875636, to_date('2013-01-16 18:18:01','yyyy-mm-dd hh24:mi:ss'), 2011,10177888 from dual union all
8 select 2875636, to_date('2013-01-16 18:24:35','yyyy-mm-dd hh24:mi:ss'), 1172,10177884 from dual union all
9 select 2875636, to_date('2013-01-16 18:32:03','yyyy-mm-dd hh24:mi:ss'), 4731, null from dual union all
10 select 2875636, to_date('2013-01-16 18:33:27','yyyy-mm-dd hh24:mi:ss'), 407, null from dual union all
11 select 2875636, to_date('2013-01-16 18:37:29','yyyy-mm-dd hh24:mi:ss'), 74, null from dual union all
12 select 2875636, to_date('2013-01-16 18:48:55','yyyy-mm-dd hh24:mi:ss'), 80, null from dual union all
13 select 2875636, to_date('2013-01-16 19:05:00','yyyy-mm-dd hh24:mi:ss'), 1955, null from dual union all
14 select 2875636, to_date('2013-01-16 19:14:12','yyyy-mm-dd hh24:mi:ss'), 326, null from dual union all
15 select 2875636, to_date('2013-01-16 19:23:39','yyyy-mm-dd hh24:mi:ss'), 972, null from dual union all
16 select 2875636, to_date('2013-01-16 19:33:05','yyyy-mm-dd hh24:mi:ss'), 5440, null from dual union all
17 select 2875636, to_date('2013-01-16 19:35:48','yyyy-mm-dd hh24:mi:ss'), 43, null from dual union all
18 select 2875636, to_date('2013-01-16 19:41:10','yyyy-mm-dd hh24:mi:ss'), 66, null from dual union all
19 select 2875636, to_date('2013-01-16 19:42:03','yyyy-mm-dd hh24:mi:ss'), 100, null from dual union all
20 select 2875636, to_date('2013-01-16 19:42:12','yyyy-mm-dd hh24:mi:ss'), 2414,10177940 from dual union all
21 select 2875636, to_date('2013-01-16 19:49:05','yyyy-mm-dd hh24:mi:ss'), 432,10177925 from dual union all
22 select 2875636, to_date('2013-01-16 19:50:19','yyyy-mm-dd hh24:mi:ss'), 183, null from dual union all
23 select 2875636, to_date('2013-01-16 19:52:46','yyyy-mm-dd hh24:mi:ss'), 2061, null from dual union all
24 select 2875636, to_date('2013-01-16 19:52:53','yyyy-mm-dd hh24:mi:ss'), 400, null from dual union all
25 select 2875636, to_date('2013-01-16 20:00:47','yyyy-mm-dd hh24:mi:ss'), 338, null from dual union all
26 select 2875636, to_date('2013-01-16 20:08:58','yyyy-mm-dd hh24:mi:ss'), 216, null from dual union all
27 select 2875636, to_date('2013-01-16 20:14:21','yyyy-mm-dd hh24:mi:ss'), 58, null from dual union all
28 select 2875636, to_date('2013-01-16 20:14:26','yyyy-mm-dd hh24:mi:ss'), 196, null from dual union all
29 select 2875636, to_date('2013-01-16 20:19:14','yyyy-mm-dd hh24:mi:ss'), 2189, null from dual union all
30 select 2875636, to_date('2013-01-16 20:21:29','yyyy-mm-dd hh24:mi:ss'), 424, null from dual union all
31 select 2875636, to_date('2013-01-16 20:24:42','yyyy-mm-dd hh24:mi:ss'), 999, null from dual union all
32 select 2875636, to_date('2013-01-16 21:01:39','yyyy-mm-dd hh24:mi:ss'), 1810, null from dual union all
33 select 2875636, to_date('2013-01-16 21:02:54','yyyy-mm-dd hh24:mi:ss'), 525, null from dual union all
34 select 2875636, to_date('2013-01-16 21:10:06','yyyy-mm-dd hh24:mi:ss'), 27, null from dual union all
35 select 2875636, to_date('2013-01-16 21:12:08','yyyy-mm-dd hh24:mi:ss'), 282, null from dual union all
36 select 2875636, to_date('2013-01-16 21:51:02','yyyy-mm-dd hh24:mi:ss'), 6, null from dual union all
37 select 2875636, to_date('2013-01-16 22:18:34','yyyy-mm-dd hh24:mi:ss'), 173, null from dual union all
38 select 2875636, to_date('2013-01-16 23:02:58','yyyy-mm-dd hh24:mi:ss'), 318, null from dual union all
39 select 2875636, to_date('2013-01-16 23:45:37','yyyy-mm-dd hh24:mi:ss'), 207, null from dual union all
40 select 3018868, to_date('2013-01-16 16:01:45','yyyy-mm-dd hh24:mi:ss'), 18, null from dual union all
41 select 3018868, to_date('2013-01-16 16:16:45','yyyy-mm-dd hh24:mi:ss'), 39, null from dual union all
42 select 3018868, to_date('2013-01-16 16:22:55','yyyy-mm-dd hh24:mi:ss'), 656, null from dual union all
43 select 3018868, to_date('2013-01-16 16:25:54','yyyy-mm-dd hh24:mi:ss'), 1852, null from dual union all
44 select 3018868, to_date('2013-01-16 16:29:23','yyyy-mm-dd hh24:mi:ss'), 688, null from dual union all
45 select 3018868, to_date('2013-01-16 16:47:26','yyyy-mm-dd hh24:mi:ss'), 2258,10177846 from dual union all
46 select 3018868, to_date('2013-01-16 16:57:41','yyyy-mm-dd hh24:mi:ss'), 572, null from dual union all
47 select 3018868, to_date('2013-01-16 17:06:47','yyyy-mm-dd hh24:mi:ss'), 1431, null from dual union all
48 select 3018868, to_date('2013-01-16 17:18:32','yyyy-mm-dd hh24:mi:ss'), 29, null from dual union all
49 select 3018868, to_date('2013-01-16 17:21:57','yyyy-mm-dd hh24:mi:ss'), 45, null from dual union all
50 select 3018868, to_date('2013-01-16 17:29:23','yyyy-mm-dd hh24:mi:ss'), 16, null from dual union all
51 select 3018868, to_date('2013-01-16 17:36:47','yyyy-mm-dd hh24:mi:ss'), 490, null from dual
52 /
Table created.
使用tabibitosan的查询：
SQL> with tabibitosan as
2 ( select user_id
3 , starttime
4 , visit_duration_seconds
5 , order_number
6 , row_number() over (partition by user_id order by starttime)
7 - row_number() over (partition by user_id, order_number order by starttime) grp
8 from mytable
9 )
10 select user_id
11 , starttime
12 , visit_duration_seconds
13 , order_number
14 , case
15 when order_number is null then
16 sum(visit_duration_seconds) over (partition by user_id,grp order by starttime)
17 end result
18 from tabibitosan
19 order by user_id
20 , starttime
21 /
USER_ID STARTTIME VISIT_DURATION_SECONDS ORDER_NUMBER RESULT
---------- ------------------- ---------------------- ------------ ----------
2875636 16-01-2013 18:03:50 61 61
2875636 16-01-2013 18:08:18 151 212
2875636 16-01-2013 18:15:43 33 245
2875636 16-01-2013 18:16:37 13 258
2875636 16-01-2013 18:18:01 2011 10177888
2875636 16-01-2013 18:24:35 1172 10177884
2875636 16-01-2013 18:32:03 4731 4731
2875636 16-01-2013 18:33:27 407 5138
2875636 16-01-2013 18:37:29 74 5212
2875636 16-01-2013 18:48:55 80 5292
2875636 16-01-2013 19:05:00 1955 7247
2875636 16-01-2013 19:14:12 326 7573
2875636 16-01-2013 19:23:39 972 8545
2875636 16-01-2013 19:33:05 5440 13985
2875636 16-01-2013 19:35:48 43 14028
2875636 16-01-2013 19:41:10 66 14094
2875636 16-01-2013 19:42:03 100 14194
2875636 16-01-2013 19:42:12 2414 10177940
2875636 16-01-2013 19:49:05 432 10177925
2875636 16-01-2013 19:50:19 183 2194
2875636 16-01-2013 19:52:46 2061 4255
2875636 16-01-2013 19:52:53 400 4655
2875636 16-01-2013 20:00:47 338 4993
2875636 16-01-2013 20:08:58 216 5209
2875636 16-01-2013 20:14:21 58 5267
2875636 16-01-2013 20:14:26 196 5463
2875636 16-01-2013 20:19:14 2189 7652
2875636 16-01-2013 20:21:29 424 8076
2875636 16-01-2013 20:24:42 999 9075
2875636 16-01-2013 21:01:39 1810 10885
2875636 16-01-2013 21:02:54 525 11410
2875636 16-01-2013 21:10:06 27 11437
2875636 16-01-2013 21:12:08 282 11719
2875636 16-01-2013 21:51:02 6 11725
2875636 16-01-2013 22:18:34 173 11898
2875636 16-01-2013 23:02:58 318 12216
2875636 16-01-2013 23:45:37 207 12423
3018868 16-01-2013 16:01:45 18 18
3018868 16-01-2013 16:16:45 39 57
3018868 16-01-2013 16:22:55 656 713
3018868 16-01-2013 16:25:54 1852 2565
3018868 16-01-2013 16:29:23 688 3253
3018868 16-01-2013 16:47:26 2258 10177846
3018868 16-01-2013 16:57:41 572 572
3018868 16-01-2013 17:06:47 1431 2003
3018868 16-01-2013 17:18:32 29 2032
3018868 16-01-2013 17:21:57 45 2077
3018868 16-01-2013 17:29:23 16 2093
3018868 16-01-2013 17:36:47 490 2583
49 rows selected.
问候
抢。
SELECT user_id
,starttime
,visit_duration_seconds
,order_number
,CASE WHEN order_number is NULL THEN SUM (visit_duration_seconds)
OVER (PARTITION BY user_id ORDER BY starttime ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ELSE NULL END result
FROM tab;
我已根据开始时间按升序对行进行排序
这是方法，您可以根据需要放置一些过滤器！
SELECT A.user_id, A.starttime, A.visit_duration_seconds, A.order_number,
case when a.order_number is NULL then sum(b.visit_duration_seconds) else null end
from tablename A, tablename B
where B.user_id=A.user_id
and B.starttime <= A.starttime
group by A.user_id, A.starttime, A.visit_duration_seconds, A.order_number
order by A.user_id, A.starttime
参见 Sqlfiddle。
您可以使用分析：
select user_id, visit_duration_seconds, order_number,
case when order_number is not null then null
else sum(case when order_number is null then visit_duration_seconds end)
over (partition by user_id order by starttime)
end rolling_total
from data;
USER_ID VISIT_DURATION_SECONDS ORDER_NUMBER ROLLING_TOTAL
---------- ---------------------- ------------ -------------
2875636 61 61
2875636 151 212
2875636 33 245
2875636 13 258
2875636 2011 10177888
2875636 1172 10177884
2875636 4731 4989
[...]