kdb+ 4.0 64位个人版

显示全部楼层 · 2012-5-21 10:19:41

1在https://anaconda.org/kx/kdb/files 下载安装文件，比如https://anaconda.org/kx/kdb/4.0/download/win-64/kdb-4.0-20211104.tar.bz22解压缩，运行\q\Scripts中的q.bat
3输入用户名和电子邮件，需要联网，在邮件中点击链接激活，就会产生kc.lic(邮件中也有一个附件）
4如果计算机的核数大于16，需要限制，例如start /affinity 16 d:\q\q\w64\q.exe

千问 · 2012-5-21 10:19:41

读入csv的效率
"ftp://ftp.nyse.com/Historical Data Samples/DAILY TAQ/EQY_US_ALL_TRADE_200006.zip"

SYMBOL|DATE|TIME|PRICE|SIZE|G127|CORR|COND|EX|TSEQ
A|20000601|9:35:22|74.4375|76100|40|0||N|816385
A|20000601|9:35:23|74.4375|100|0|0||M|0
A|20000601|9:35:23|74.4375|200|0|0||M|0
q)\t t:("SDTFIIIICI";enlist"|") 0: `:d:/nyx/CT_200006.csv
51630
q)meta t
c | t f a
------| -----
SYMBOL| s
DATE| d
TIME| t
PRICE | f
SIZE| i
G127| i
CORR| i
COND| i
EX| c
TSEQ| i

千问 · 2012-5-21 10:19:41

显示前10行
q)10#t
SYMBOL DATE TIME PRICE SIZEG127 CORR COND EX TSEQ
---------------------------------------------------------------------
A2000.06.01 09:35:22.000 74.4375 76100 40 0 N816385
A2000.06.01 09:35:23.000 74.4375 100 00 M0
A2000.06.01 09:35:23.000 74.4375 200 00 M0
A2000.06.01 09:35:23.000 74.4375 100 00 M0
A2000.06.01 09:35:23.000 74.4375 200 00 M0
A2000.06.01 09:35:23.000 74.4375 100 00 M0
A2000.06.01 09:35:23.000 74.4375 500 00 M0
A2000.06.01 09:35:23.000 74.4375 100 00 M0
A2000.06.01 09:35:23.000 74.4375 100000 M0
A2000.06.01 09:35:23.000 74.4375 400 00 M0
查询最大值最小值
q)select n:count PRICE,time1:first TIME,time2:last TIME,price1:min PRICE,price2:max PRICE from t
ntime1time2price1 price2
------------------------------------------------
65252260 09:35:22.000 17:11:34.000 060800
q)\t select n:count PRICE,time1:first TIME,time2:last TIME,price1:min PRICE,price2:max PRICE from t
146

千问 · 2012-5-21 10:19:41

本帖最后由〇〇于 2021-11-29 16:35 编辑
分组汇总
q)select n:count PRICE,time1:first TIME,time2:last TIME,price1:min PRICE,price2:max PRICE by SYMBOL from t
SYMBOL| ntime1time2price1price2
------| -------------------------------------------------
A | 102460 09:35:22.000 18:07:04.000 20.3125 83.75
AA| 3337909:35:43.000 18:07:04.000 27.87562.125
AAABB | 11413:52:04.000 12:21:49.000 5.875 7
AABC| 35 15:21:47.000 16:45:08.000 6.125 6.75
AAC | 70 10:03:18.000 16:10:43.000 7 8.125
AACB| 65 09:42:34.000 11:33:29.000 10.37513.25
AACE| 64812:45:27.000 15:48:57.000 11.516.25
AAE | 14912:49:46.000 14:33:43.000 54.75 68
AAG | 66 12:40:31.000 10:36:11.000 1717.8125
AAGP| 51909:41:55.000 15:39:32.000 3.125 5.4375
AAGPRT| 66 09:32:12.000 15:53:46.000 21.75 22.875
AAII| 1192 09:26:10.000 17:30:11.000 6.259.75
AAIR| 5841 09:29:33.000 16:45:09.000 3.46875 5
AALA| 63 13:48:57.000 14:17:01.000 1.752.375
AAM | 51209:36:58.000 15:33:12.000 0.81250.9375
AAME| 86 10:00:22.000 15:26:40.000 2.625 3.375
AANB| 13410:54:42.000 16:45:08.000 7.43759.25
AAON| 1641 09:20:00.000 15:25:35.000 6.125 27.875
AAPL| 192091 08:01:03.000 18:28:37.000 0 103.9375
AAPR| 12 11:52:19.000 10:10:50.000 51.12553
..
q)\t select n:count PRICE,time1:first TIME,time2:last TIME,price1:min PRICE,price2:max PRICE by SYMBOL from t
1116

千问 · 2012-5-21 10:19:41

对应的duckdb测试，读入的是parquet文件，没有可比性，t也是内存表，可比
D:\duckdb>duckdb1125
v0.3.2-dev173 a73d33c14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D create table t as select * from 'd:/ct_stdz.parquet';
Error: IO Error: No files found that match the pattern "d:/ct_stdz.parquet"
D .timer on
D create table t as select * from 'd:/nyx/ct_stdz.parquet';
Run Time: real 24.836 user 51.230728 sys 5.444435
D describe t;
┌────────┬───────────┬──────┬─────┬─────────┬───────┐
│ Field│ Type│ Null │ Key │ Default │ Extra │
├────────┼───────────┼──────┼─────┼─────────┼───────┤
│ SYMBOL │ VARCHAR │ YES│ │ │ │
│ date │ TIMESTAMP │ YES│ │ │ │
│ time │ TIMESTAMP │ YES│ │ │ │
│ PRICE│ DOUBLE│ YES│ │ │ │
│ SIZE │ INTEGER │ YES│ │ │ │
│ G127 │ INTEGER │ YES│ │ │ │
│ CORR │ INTEGER │ YES│ │ │ │
│ COND │ VARCHAR │ YES│ │ │ │
│ EX │ VARCHAR │ YES│ │ │ │
│ TSEQ │ INTEGER │ YES│ │ │ │
└────────┴───────────┴──────┴─────┴─────────┴───────┘
Run Time: real 0.027 user 0.000000 sys 0.000000
D select symbol,count(*)n,max(time),min(time),max(price),min(price) from t group by symbol order by symbol limit 20;
┌────────┬────────┬─────────────────────┬─────────────────────┬─────
┬────────────┐
│ SYMBOL │ n│max(time)│min(time)│ max(price) │ min(price) │
├────────┼────────┼─────────────────────┼─────────────────────┼─────
┼────────────┤
│ A│ 102460 │ 2000-06-30 18:07:04 │ 2000-06-01 09:35:22 │ 83.75│ 20.3125│
│ AA │ 33379│ 2000-06-30 18:07:04 │ 2000-06-01 09:35:43 │ 62.125 │ 27.875 │
│ AAABB│ 114│ 2000-06-30 12:21:49 │ 2000-06-01 13:52:04 │ 7.0│ 5.875│
│ AABC │ 35 │ 2000-06-30 16:45:08 │ 2000-06-01 15:21:47 │ 6.75 │ 6.125│
│ AAC│ 70 │ 2000-06-30 16:10:43 │ 2000-06-29 10:03:18 │ 8.125│ 7.0│
│ AACB │ 65 │ 2000-06-30 11:33:29 │ 2000-06-01 09:42:34 │ 13.25│ 10.375 │
│ AACE │ 648│ 2000-06-30 15:48:57 │ 2000-06-01 12:45:27 │ 16.25│ 11.5 │
│ AAE│ 149│ 2000-06-30 14:33:43 │ 2000-06-02 12:49:46 │ 68.0 │ 54.75│
│ AAG│ 66 │ 2000-06-16 10:36:11 │ 2000-06-01 12:40:31 │ 17.8125│ 17.0 │
│ AAGP │ 519│ 2000-06-30 15:39:32 │ 2000-06-01 09:41:55 │ 5.4375 │ 3.125│
│ AAGPRT │ 66 │ 2000-06-30 15:53:46 │ 2000-06-01 09:32:12 │ 22.875 │ 21.75│
│ AAII │ 1192 │ 2000-06-30 17:30:11 │ 2000-06-01 09:26:10 │ 9.75 │ 6.25 │
│ AAIR │ 5841 │ 2000-06-30 16:45:09 │ 2000-06-01 09:29:33 │ 5.0│ 3.46875│
│ AALA │ 63 │ 2000-06-28 14:17:01 │ 2000-06-01 13:48:57 │ 2.375│ 1.75 │
│ AAM│ 512│ 2000-06-30 15:33:12 │ 2000-06-01 09:36:58 │ 0.9375 │ 0.8125 │
│ AAME │ 86 │ 2000-06-29 15:26:40 │ 2000-06-01 10:00:22 │ 3.375│ 2.625│
│ AANB │ 134│ 2000-06-30 16:45:08 │ 2000-06-01 10:54:42 │ 9.25 │ 7.4375 │
│ AAON │ 1641 │ 2000-06-30 15:25:35 │ 2000-06-01 09:20:00 │ 27.875 │ 6.125│
│ AAPL │ 192091 │ 2000-06-30 18:28:37 │ 2000-06-01 08:01:03 │ 103.9375 │ 0.0│
│ AAPR │ 12 │ 2000-06-23 10:10:50 │ 2000-06-05 11:52:19 │ 53.0 │ 51.125 │
└────────┴────────┴─────────────────────┴─────────────────────┴─────
┴────────────┘
Run Time: real 0.740 user 9.859263 sys 0.031200

千问 · 2012-5-21 10:19:41

32 位版
http://www.itpub.net/thread-2058222-1-1.html

千问 · 2012-5-21 10:19:41

sum 1和sum 列含义不同
q)select s:sum 1 from t
s
-
1
q)select s:sum CORR from t
s
-------
1505552
q)

千问 · 2012-5-21 10:19:41

q)select sum PRICE by SYMBOL from t
SYMBOL| PRICE
------| -------------
A | 7416381
AA| 1246970
AAABB | 714.375
AABC| 226.0625
AAC | 541.3359
AACB| 775.9844
AACE| 8608.375
AAE | 9300.414
AAG | 1151.031
AAGP| 2260.234
AAGPRT| 1473
AAII| 9030.406
AAIR| 24536.52
AALA| 127.2813
AAM | 446.9375
AAME| 254.8438
AANB| 1109.844
AAON| 41624.55
AAPL| 1.484211e+007
AAPR| 633.125
..
q)10#select sum PRICE by SYMBOL from t
SYMBOL| PRICE
------| --------
A | 7416381
AA| 1246970
AAABB | 714.375
AABC| 226.0625
AAC | 541.3359
AACB| 775.9844
AACE| 8608.375
AAE | 9300.414
AAG | 1151.031
AAGP| 2260.234
q)\t 10#select sum PRICE by SYMBOL from t
505