Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitba41dc6

Browse files
committed
Add first/last group aggregates
1 parentcb299ad commitba41dc6

File tree

6 files changed

+512
-57
lines changed

6 files changed

+512
-57
lines changed

‎expected/test.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -398,3 +398,28 @@ select avg((open+close)/2),max(high-low) from stock group by symbol;
398398
10.9200000762939 | 1
399399
(1 row)
400400

401+
create table quote(symbol char(5), ts timestamp, ask_price real, ask_size integer, bid_price real, bid_size integer);
402+
insert into quote values
403+
('AAA', '03-12-2018 10:00', 10.0, 100, 10.1, 202),
404+
('AAA', '03-12-2018 10:01', 11.0, 120, 11.2, 200),
405+
('AAA', '03-12-2018 10:02', 10.4, 110, 10.5, 204),
406+
('AAA', '03-12-2018 10:03', 11.1, 125, 11.2, 201),
407+
('AAA', '03-12-2018 10:04', 11.0, 105, 11.4, 205);
408+
select create_projection('vquote','quote',array['ts','ask_price','ask_size','bid_price','bid_size'],array['symbol'],'ts');
409+
create_projection
410+
-------------------
411+
412+
(1 row)
413+
414+
select vquote_refresh();
415+
vquote_refresh
416+
----------------
417+
5
418+
(1 row)
419+
420+
select first(bid_price,ts),last(ask_size,ts) from vquote group by symbol;
421+
first | last
422+
-------+------
423+
10.1 | 105
424+
(1 row)
425+

‎sql/test.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,3 +58,16 @@ select vstock_refresh();
5858
selectavg((open+close)/2),max(high-low)from stockgroup by symbol;
5959
setvops.auto_substitute_projections=off;
6060
selectavg((open+close)/2),max(high-low)from stockgroup by symbol;
61+
62+
createtablequote(symbolchar(5), tstimestamp, ask_pricereal, ask_sizeinteger, bid_pricereal, bid_sizeinteger);
63+
insert into quotevalues
64+
('AAA','03-12-2018 10:00',10.0,100,10.1,202),
65+
('AAA','03-12-2018 10:01',11.0,120,11.2,200),
66+
('AAA','03-12-2018 10:02',10.4,110,10.5,204),
67+
('AAA','03-12-2018 10:03',11.1,125,11.2,201),
68+
('AAA','03-12-2018 10:04',11.0,105,11.4,205);
69+
70+
select create_projection('vquote','quote',array['ts','ask_price','ask_size','bid_price','bid_size'],array['symbol'],'ts');
71+
select vquote_refresh();
72+
73+
select first(bid_price,ts),last(ask_size,ts)from vquotegroup by symbol;

‎vops--1.0.sql

Lines changed: 215 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -181,6 +181,29 @@ create function betwixt(opd vops_text, low text, high text) returns vops_bool as
181181
createfunctionifnull(opd vops_text, substtext) returns vops_textas'MODULE_PATHNAME','vops_ifnull_text' language C parallel safe immutable strict;
182182
createfunctionifnull(opd vops_text, subst vops_text) returns vops_textas'MODULE_PATHNAME','vops_coalesce_text' language C parallel safe immutable strict;
183183

184+
createfunctionvops_text_first_accumulate(state internal, val vops_text, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
185+
createfunctionvops_text_first_final(state internal) returnstextas'MODULE_PATHNAME','vops_first_final' language C parallel safe strict;
186+
createfunctionvops_first_combine(internal,internal) returns internalas'MODULE_PATHNAME' language C parallel safe;
187+
CREATEAGGREGATEfirst(vops_text,vops_timestamp) (
188+
SFUNC= vops_text_first_accumulate,
189+
STYPE= internal,
190+
SSPACE=24,
191+
FINALFUNC= vops_text_first_final,
192+
COMBINEFUNC= vops_first_combine,
193+
PARALLEL= SAFE
194+
);
195+
196+
createfunctionvops_text_last_accumulate(state internal, val vops_text, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
197+
createfunctionvops_last_combine(internal,internal) returns internalas'MODULE_PATHNAME' language C parallel safe;
198+
CREATEAGGREGATElast(vops_text,vops_timestamp) (
199+
SFUNC= vops_text_last_accumulate,
200+
STYPE= internal,
201+
SSPACE=24,
202+
FINALFUNC= vops_text_first_final,
203+
COMBINEFUNC= vops_last_combine,
204+
PARALLEL= SAFE
205+
);
206+
184207
createfunctionfirst(tile vops_text) returnstextas'MODULE_PATHNAME','vops_text_first' language C parallel safe immutable strict;
185208
createfunctionlast(tile vops_text) returnstextas'MODULE_PATHNAME','vops_text_last' language C parallel safe immutable strict;
186209
createfunctionlow(tile vops_text) returnstextas'MODULE_PATHNAME','vops_text_low' language C parallel safe immutable strict;
@@ -518,6 +541,27 @@ CREATE AGGREGATE mcount(vops_char) (
518541
PARALLEL= SAFE
519542
);
520543

544+
createfunctionvops_char_first_accumulate(state internal, val vops_char, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
545+
createfunctionvops_char_first_final(state internal) returns"char"as'MODULE_PATHNAME','vops_first_final' language C parallel safe;
546+
CREATEAGGREGATEfirst(vops_char,vops_timestamp) (
547+
SFUNC= vops_char_first_accumulate,
548+
STYPE= internal,
549+
SSPACE=24,
550+
FINALFUNC= vops_char_first_final,
551+
COMBINEFUNC= vops_first_combine,
552+
PARALLEL= SAFE
553+
);
554+
555+
createfunctionvops_char_last_accumulate(state internal, val vops_char, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
556+
CREATEAGGREGATElast(vops_char,vops_timestamp) (
557+
SFUNC= vops_char_last_accumulate,
558+
STYPE= internal,
559+
SSPACE=24,
560+
FINALFUNC= vops_char_first_final,
561+
COMBINEFUNC= vops_last_combine,
562+
PARALLEL= SAFE
563+
);
564+
521565
createfunctionfirst(tile vops_char) returns"char"as'MODULE_PATHNAME','vops_char_first' language C parallel safe immutable strict;
522566
createfunctionlast(tile vops_char) returns"char"as'MODULE_PATHNAME','vops_char_last' language C parallel safe immutable strict;
523567
createfunctionlow(tile vops_char) returns"char"as'MODULE_PATHNAME','vops_char_low' language C parallel safe immutable strict;
@@ -837,6 +881,27 @@ CREATE AGGREGATE mcount(vops_int2) (
837881
PARALLEL= SAFE
838882
);
839883

884+
createfunctionvops_int2_first_accumulate(state internal, val vops_int2, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
885+
createfunctionvops_int2_first_final(state internal) returns int2as'MODULE_PATHNAME','vops_first_final' language C parallel safe;
886+
CREATEAGGREGATEfirst(vops_int2,vops_timestamp) (
887+
SFUNC= vops_int2_first_accumulate,
888+
STYPE= internal,
889+
SSPACE=24,
890+
FINALFUNC= vops_int2_first_final,
891+
COMBINEFUNC= vops_first_combine,
892+
PARALLEL= SAFE
893+
);
894+
895+
createfunctionvops_int2_last_accumulate(state internal, val vops_int2, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
896+
CREATEAGGREGATElast(vops_int2,vops_timestamp) (
897+
SFUNC= vops_int2_last_accumulate,
898+
STYPE= internal,
899+
SSPACE=24,
900+
FINALFUNC= vops_int2_first_final,
901+
COMBINEFUNC= vops_last_combine,
902+
PARALLEL= SAFE
903+
);
904+
840905
createfunctionfirst(tile vops_int2) returns int2as'MODULE_PATHNAME','vops_int2_first' language C parallel safe immutable strict;
841906
createfunctionlast(tile vops_int2) returns int2as'MODULE_PATHNAME','vops_int2_last' language C parallel safe immutable strict;
842907
createfunctionlow(tile vops_int2) returns int2as'MODULE_PATHNAME','vops_int2_low' language C parallel safe immutable strict;
@@ -1156,6 +1221,27 @@ CREATE AGGREGATE mcount(vops_int4) (
11561221
PARALLEL= SAFE
11571222
);
11581223

1224+
createfunctionvops_int4_first_accumulate(state internal, val vops_int4, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
1225+
createfunctionvops_int4_first_final(state internal) returns int4as'MODULE_PATHNAME','vops_first_final' language C parallel safe;
1226+
CREATEAGGREGATEfirst(vops_int4,vops_timestamp) (
1227+
SFUNC= vops_int4_first_accumulate,
1228+
STYPE= internal,
1229+
SSPACE=24,
1230+
FINALFUNC= vops_int4_first_final,
1231+
COMBINEFUNC= vops_first_combine,
1232+
PARALLEL= SAFE
1233+
);
1234+
1235+
createfunctionvops_int4_last_accumulate(state internal, val vops_int4, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
1236+
CREATEAGGREGATElast(vops_int4,vops_timestamp) (
1237+
SFUNC= vops_int4_last_accumulate,
1238+
STYPE= internal,
1239+
SSPACE=24,
1240+
FINALFUNC= vops_int4_first_final,
1241+
COMBINEFUNC= vops_last_combine,
1242+
PARALLEL= SAFE
1243+
);
1244+
11591245
createfunctionfirst(tile vops_int4) returns int4as'MODULE_PATHNAME','vops_int4_first' language C parallel safe immutable strict;
11601246
createfunctionlast(tile vops_int4) returns int4as'MODULE_PATHNAME','vops_int4_last' language C parallel safe immutable strict;
11611247
createfunctionlow(tile vops_int4) returns int4as'MODULE_PATHNAME','vops_int4_low' language C parallel safe immutable strict;
@@ -1470,6 +1556,27 @@ CREATE AGGREGATE mcount(vops_date) (
14701556
PARALLEL= SAFE
14711557
);
14721558

1559+
createfunctionvops_date_first_accumulate(state internal, val vops_date, ts vops_timestamp) returns internalas'MODULE_PATHNAME','vops_int4_first_accumulate' language C parallel safe;
1560+
createfunctionvops_date_first_final(state internal) returnsdateas'MODULE_PATHNAME','vops_first_final' language C parallel safe;
1561+
CREATEAGGREGATEfirst(vops_date,vops_timestamp) (
1562+
SFUNC= vops_date_first_accumulate,
1563+
STYPE= internal,
1564+
SSPACE=24,
1565+
FINALFUNC= vops_date_first_final,
1566+
COMBINEFUNC= vops_first_combine,
1567+
PARALLEL= SAFE
1568+
);
1569+
1570+
createfunctionvops_date_last_accumulate(state internal, val vops_date, ts vops_timestamp) returns internalas'MODULE_PATHNAME','vops_int4_last_accumulate' language C parallel safe;
1571+
CREATEAGGREGATElast(vops_date,vops_timestamp) (
1572+
SFUNC= vops_date_last_accumulate,
1573+
STYPE= internal,
1574+
SSPACE=24,
1575+
FINALFUNC= vops_date_first_final,
1576+
COMBINEFUNC= vops_last_combine,
1577+
PARALLEL= SAFE
1578+
);
1579+
14731580
createfunctionfirst(tile vops_date) returnsdateas'MODULE_PATHNAME','vops_int4_first' language C parallel safe immutable strict;
14741581
createfunctionlast(tile vops_date) returnsdateas'MODULE_PATHNAME','vops_int4_last' language C parallel safe immutable strict;
14751582
createfunctionlow(tile vops_date) returnsdateas'MODULE_PATHNAME','vops_int4_low' language C parallel safe immutable strict;
@@ -2120,6 +2227,27 @@ CREATE AGGREGATE mcount(vops_interval) (
21202227
PARALLEL= SAFE
21212228
);
21222229

2230+
createfunctionvops_interval_first_accumulate(state internal, val vops_interval, ts vops_timestamp) returns internalas'MODULE_PATHNAME','vops_int8_first_accumulate' language C parallel safe;
2231+
createfunctionvops_interval_first_final(state internal) returns int8as'MODULE_PATHNAME','vops_first_final' language C parallel safe;
2232+
CREATEAGGREGATEfirst(vops_interval,vops_timestamp) (
2233+
SFUNC= vops_interval_first_accumulate,
2234+
STYPE= internal,
2235+
SSPACE=24,
2236+
FINALFUNC= vops_interval_first_final,
2237+
COMBINEFUNC= vops_first_combine,
2238+
PARALLEL= SAFE
2239+
);
2240+
2241+
createfunctionvops_interval_last_accumulate(state internal, val vops_interval, ts vops_timestamp) returns internalas'MODULE_PATHNAME','vops_int8_last_accumulate' language C parallel safe;
2242+
CREATEAGGREGATElast(vops_interval,vops_timestamp) (
2243+
SFUNC= vops_interval_last_accumulate,
2244+
STYPE= internal,
2245+
SSPACE=24,
2246+
FINALFUNC= vops_interval_first_final,
2247+
COMBINEFUNC= vops_last_combine,
2248+
PARALLEL= SAFE
2249+
);
2250+
21232251
createfunctionfirst(tile vops_interval) returns deltatimeas'MODULE_PATHNAME','vops_int8_first' language C parallel safe immutable strict;
21242252
createfunctionlast(tile vops_interval) returns deltatimeas'MODULE_PATHNAME','vops_int8_last' language C parallel safe immutable strict;
21252253
createfunctionlow(tile vops_interval) returns deltatimeas'MODULE_PATHNAME','vops_int8_low' language C parallel safe immutable strict;
@@ -2440,6 +2568,27 @@ CREATE AGGREGATE mcount(vops_int8) (
24402568
PARALLEL= SAFE
24412569
);
24422570

2571+
createfunctionvops_int8_first_accumulate(state internal, val vops_int8, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
2572+
createfunctionvops_int8_first_final(state internal) returns int8as'MODULE_PATHNAME','vops_first_final' language C parallel safe;
2573+
CREATEAGGREGATEfirst(vops_int8,vops_timestamp) (
2574+
SFUNC= vops_int8_first_accumulate,
2575+
STYPE= internal,
2576+
SSPACE=24,
2577+
FINALFUNC= vops_int8_first_final,
2578+
COMBINEFUNC= vops_first_combine,
2579+
PARALLEL= SAFE
2580+
);
2581+
2582+
createfunctionvops_int8_last_accumulate(state internal, val vops_int8, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
2583+
CREATEAGGREGATElast(vops_int8,vops_timestamp) (
2584+
SFUNC= vops_int8_last_accumulate,
2585+
STYPE= internal,
2586+
SSPACE=24,
2587+
FINALFUNC= vops_int8_first_final,
2588+
COMBINEFUNC= vops_last_combine,
2589+
PARALLEL= SAFE
2590+
);
2591+
24432592
createfunctionfirst(tile vops_int8) returns int8as'MODULE_PATHNAME','vops_int8_first' language C parallel safe immutable strict;
24442593
createfunctionlast(tile vops_int8) returns int8as'MODULE_PATHNAME','vops_int8_last' language C parallel safe immutable strict;
24452594
createfunctionlow(tile vops_int8) returns int8as'MODULE_PATHNAME','vops_int8_low' language C parallel safe immutable strict;
@@ -2742,6 +2891,28 @@ CREATE AGGREGATE mcount(vops_float4) (
27422891
PARALLEL= SAFE
27432892
);
27442893

2894+
createfunctionvops_float4_first_accumulate(state internal, val vops_float4, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
2895+
createfunctionvops_float4_first_final(state internal) returns float4as'MODULE_PATHNAME','vops_first_final' language C parallel safe;
2896+
CREATEAGGREGATEfirst(vops_float4,vops_timestamp) (
2897+
SFUNC= vops_float4_first_accumulate,
2898+
STYPE= internal,
2899+
SSPACE=24,
2900+
FINALFUNC= vops_float4_first_final,
2901+
COMBINEFUNC= vops_first_combine,
2902+
PARALLEL= SAFE
2903+
);
2904+
2905+
createfunctionvops_float4_last_accumulate(state internal, val vops_float4, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
2906+
CREATEAGGREGATElast(vops_float4,vops_timestamp) (
2907+
SFUNC= vops_float4_last_accumulate,
2908+
STYPE= internal,
2909+
SSPACE=24,
2910+
FINALFUNC= vops_float4_first_final,
2911+
COMBINEFUNC= vops_last_combine,
2912+
PARALLEL= SAFE
2913+
);
2914+
2915+
27452916
createfunctionfirst(tile vops_float4) returns float4as'MODULE_PATHNAME','vops_float4_first' language C parallel safe immutable strict;
27462917
createfunctionlast(tile vops_float4) returns float4as'MODULE_PATHNAME','vops_float4_last' language C parallel safe immutable strict;
27472918
createfunctionlow(tile vops_float4) returns float4as'MODULE_PATHNAME','vops_float4_low' language C parallel safe immutable strict;
@@ -3045,6 +3216,27 @@ CREATE AGGREGATE mcount(vops_float8) (
30453216
PARALLEL= SAFE
30463217
);
30473218

3219+
createfunctionvops_float8_first_accumulate(state internal, val vops_float8, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
3220+
createfunctionvops_float8_first_final(state internal) returns float8as'MODULE_PATHNAME','vops_first_final' language C parallel safe;
3221+
CREATEAGGREGATEfirst(vops_float8,vops_timestamp) (
3222+
SFUNC= vops_float8_first_accumulate,
3223+
STYPE= internal,
3224+
SSPACE=24,
3225+
FINALFUNC= vops_float8_first_final,
3226+
COMBINEFUNC= vops_first_combine,
3227+
PARALLEL= SAFE
3228+
);
3229+
3230+
createfunctionvops_float8_last_accumulate(state internal, val vops_float8, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
3231+
CREATEAGGREGATElast(vops_float8,vops_timestamp) (
3232+
SFUNC= vops_float8_last_accumulate,
3233+
STYPE= internal,
3234+
SSPACE=24,
3235+
FINALFUNC= vops_float8_first_final,
3236+
COMBINEFUNC= vops_last_combine,
3237+
PARALLEL= SAFE
3238+
);
3239+
30483240
createfunctionfirst(tile vops_float8) returns float8as'MODULE_PATHNAME','vops_float8_first' language C parallel safe immutable strict;
30493241
createfunctionlast(tile vops_float8) returns float8as'MODULE_PATHNAME','vops_float8_last' language C parallel safe immutable strict;
30503242
createfunctionlow(tile vops_float8) returns float8as'MODULE_PATHNAME','vops_float8_low' language C parallel safe immutable strict;
@@ -3084,6 +3276,29 @@ CREATE AGGREGATE mcount(*) (
30843276
PARALLEL= SAFE
30853277
);
30863278

3279+
createfunctionvops_bool_first_accumulate(state internal, val vops_bool, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
3280+
createfunctionvops_bool_first_final(state internal) returns boolas'MODULE_PATHNAME','vops_first_final' language C parallel safe;
3281+
CREATEAGGREGATEfirst(vops_bool,vops_timestamp) (
3282+
SFUNC= vops_bool_first_accumulate,
3283+
STYPE= internal,
3284+
SSPACE=24,
3285+
FINALFUNC= vops_bool_first_final,
3286+
COMBINEFUNC= vops_first_combine,
3287+
PARALLEL= SAFE
3288+
);
3289+
3290+
createfunctionvops_bool_last_accumulate(state internal, val vops_bool, ts vops_timestamp) returns internalas'MODULE_PATHNAME' language C parallel safe;
3291+
CREATEAGGREGATElast(vops_bool,vops_timestamp) (
3292+
SFUNC= vops_bool_last_accumulate,
3293+
STYPE= internal,
3294+
SSPACE=24,
3295+
FINALFUNC= vops_bool_first_final,
3296+
COMBINEFUNC= vops_last_combine,
3297+
PARALLEL= SAFE
3298+
);
3299+
3300+
createfunctionfirst(tile vops_bool) returns boolas'MODULE_PATHNAME','vops_bool_first' language C parallel safe immutable strict;
3301+
createfunctionlast(tile vops_bool) returns boolas'MODULE_PATHNAME','vops_bool_last' language C parallel safe immutable strict;
30873302

30883303
-- Generic functions
30893304

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp