How to flexibly use "select"?

Community Support
1

Hi, 

I’m having a problem using “select”, hope I could get some suggestions here. 

========================================== what I have done so far =============================

my code :


price_series: select price by utc_datetime,sym from trade where sym in (ESH6;efFESXH6), utc_datetime within (T0;T1)

the result :


utc_datetime sym | price

--------------------------------------| ------

2016.02.23D14:00:00.023522675 ESH6 | 193675

2016.02.23D14:00:00.045145057 ESH6 | 193700

2016.02.23D14:00:00.321368969 ESH6 | 193675

2016.02.23D14:00:00.565350502 ESH6 | 193700

2016.02.23D14:00:00.588827878 ESH6 | 193700

2016.02.23D14:00:00.862156420 efFESXH6| 29260

2016.02.23D14:00:00.923543870 efFESXH6| 29260

2016.02.23D14:00:01.190791741 ESH6 | 193675

2016.02.23D14:00:02.281757704 ESH6 | 193675

2016.02.23D14:00:02.351077832 efFESXH6| 29260


============================================ my question =================================


However, I want ESH6 and efFESXH6 to be displayed in separate columns, meanwhile the whole table is sorted by utc_datetime.


this is the desired result:

utc_datetime sym1 sym2 price

-------------------------------------------------------

2016.02.23D14:00:00.023522675 ESH6          193675

2016.02.23D14:00:00.045145057 ESH6          193700

2016.02.23D14:00:00.321368969 ESH6          193675

2016.02.23D14:00:00.565350502 ESH6          193700

2016.02.23D14:00:00.588827878 ESH6          193700

2016.02.23D14:00:00.862156420 efFESXH6 29260 

2016.02.23D14:00:00.923543870      efFESXH6 29260 

2016.02.23D14:00:01.190791741 ESH6      193675

2016.02.23D14:00:02.281757704 ESH6          193675

2016.02.23D14:00:02.351077832      efFESXH6 29260 


As I formatted this table manually, I’m not sure which columns should be keyed. Could you please help me with it? Any suggestions are appreciated!! 

Thanks,

Xinyu

2

Hi Xinyu,

The following uses two select statements to generate a table for each sym, then combines them using a union join. Finally the data is re-sorted by ascending time order.

time xasc (select utc_datetime,sym1:sym,price from trade where sym=ESH6, utc_datetime within (T0;T1)) uj select utc_datetime,sym2:sym,price from trade where sym=`efFESXH6, utc_datetime within (T0;T1)

Hope that helps,

Liam,

AquaQ Analytics

3

Hi Xinyu,

For a more general solution (more than two different syms) something like this may be helpful:

Using the following table t as an example:
q)s:qwer
q)n:100
q)t:`time xasc (sym:n?s;time:n?.z.T;price:n?1.)
q)t
sym time         price

e   00:00:17.080 0.7880561
e   00:07:41.168 0.699044
e   00:08:12.640 0.7875153
w   00:10:12.557 0.01668426
w   00:21:04.412 0.9677446
..

Set time range
q)T0:00:00
q)T1:01:00

q)fills {[x;y]![x;((=;`sym;enlist y);(within;`time;(enlist;`T0;`T1)));0b;enlist[y]!enlist`price]}/[t;exec distinct sym from t]
sym time         price      e         w          r         q

e   00:00:17.080 0.7880561  0.7880561
e   00:07:41.168 0.699044   0.699044
e   00:08:12.640 0.7875153  0.7875153
w   00:10:12.557 0.01668426 0.7875153 0.01668426
w   00:21:04.412 0.9677446  0.7875153 0.9677446
e   00:22:32.095 0.7263287  0.7263287 0.9677446
r   00:40:25.890 0.4799172  0.7263287 0.9677446  0.4799172
r   00:41:09.207 0.4414907  0.7263287 0.9677446  0.4414907
q   01:03:46.241 0.01221208 0.7263287 0.9677446  0.4414907
e   01:07:33.288 0.3135821  0.7263287 0.9677446  0.4414907
e   01:08:32.831 0.7019831  0.7263287 0.9677446  0.4414907
r   01:33:47.975 0.5420167  0.7263287 0.9677446  0.4414907
r   01:34:06.379 0.6150236  0.7263287 0.9677446  0.4414907
w   01:47:52.218 0.5000071  0.7263287 0.9677446  0.4414907
r   02:00:27.990 0.5046331  0.7263287 0.9677446  0.4414907
q   02:03:19.927 0.7269904  0.7263287 0.9677446  0.4414907
q   02:21:36.661 0.7632325  0.7263287 0.9677446  0.4414907
e   03:06:48.079 0.8392881  0.7263287 0.9677446  0.4414907
w   03:09:34.285 0.691249   0.7263287 0.9677446  0.4414907
e   03:10:35.039 0.4244823  0.7263287 0.9677446  0.4414907
e   03:13:58.871 0.3664924  0.7263287 0.9677446  0.4414907
q   03:14:54.508 0.3460797  0.7263287 0.9677446  0.4414907
r   03:20:42.011 0.3611978  0.7263287 0.9677446  0.4414907
q   03:21:39.927 0.4837422  0.7263287 0.9677446  0.4414907
q   03:23:35.323 0.1461295  0.7263287 0.9677446  0.4414907
..

This returns a column of prevailing prices for each sym in your table, which may be more useful depending on what you’re trying to achieve.

Hope this helps,
Cathal,
AquaQ Analytics

4

Hi Liam,

That’s what I want, thanks a lot for your kind reply!

Best,

Xinyu

5

Hi Cathal,

I really appreciate your help! It also solved my next problem!   I was thinking about how to write this into a function and make it suitable for more inputs. This general solution is extremely helpful, thanks!

Best

Xinyu