search

at15 / reika

[WIP] A DSL for query and benchmark TSDB
MIT License
1 stars 0 forks source link

[ref] kx/q #12

Closed at15 closed 6 years ago

at15 commented 6 years ago

Typo

at15 commented 6 years ago

APL read from right to left

Pick 6 lottery number

x[⍋x←6?40]
at15 commented 6 years ago

http://code.kx.com/q/tutorials/in-memory-queries/ data generation has a html escape &, might make a PR later to https://github.com/KxSystems/docs/blob/master/docs/tutorials/in-memory-queries.md

/ Generate some random computer statistics (cpu usage only)
/ You can modify n (number of unique computers), timerange (how long the data is for)
/ freq (how often a computer publishes a statistic) and calls (the number of logged calls)
n:1000; timerange:5D; freq:0D00:01; calls:3000;
depts:`finance`packing`logistics`management`hoopjumping`trading`telesales; startcpu:(til n)!25+n?20; fcn:n*fc:`long$timerange%freq;

computer:([]time:(-0D00:00:10 + fcn?0D00:00:20)+fcn#(.z.p - timerange)+freq*til fc; id:raze fc#'key startcpu)
computer:update `g#id from `time xasc update cpu:{100>3|startcpu[first x]+sums(count x)?-2 -1 -1 0 0 1 1 2}[id] by id from computer

/ And generate some random logged calls
calls:([] time:(.z.p - timerange)+asc calls?timerange; id:calls?key startcpu; severity:calls?1 2 3)

/ create a lookup table of computer information
computerlookup:([id:key startcpu] dept:n?depts; os:n?`win7`win8`osx`vista)

tables[] shows all the table being created

q) tables
k){."\\a ",$$[^x;`;x]}

q) tables[]!
![`s#`calls`computer`computerlookup]

q) count each value each tables[]
3000 7200000 1000

q) tables[]! count each value each tables[]
calls         | 3000
computer      | 7200000
computerlookup| 1000

ad-hoc join

q)calls lj computerlookup
time                          id  severity dept        os   
------------------------------------------------------------
2017.11.14D01:50:39.028941608 990 1        hoopjumping win7 
2017.11.14D01:50:42.241758621 33  2        telesales   win8 
2017.11.14D01:50:42.694582558 843 1        management  win7 
2017.11.14D01:52:02.383347667 16  2        management  win7 
2017.11.14D01:54:52.882221705 776 3        packing     vista
2017.11.14D01:57:15.965935654 529 3        management  vista
2017.11.14D02:01:03.514993108 766 1        hoopjumping win7
at15 commented 6 years ago

Time joins

q)aj[`id`time;calls;computer]                                                                                                                                                       
time                          id  severity cpu
----------------------------------------------
2014.05.09D12:28:29.436601608 990 1        36 
2014.05.09D12:28:32.649418621 33  2        28 
2014.05.09D12:28:33.102242558 843 1        37 
2014.05.09D12:29:52.791007667 16  2        41 
2014.05.09D12:32:43.289881705 776 3        29 
2014.05.09D12:35:06.373595654 529 3        24 
2014.05.09D12:38:53.922653108 766 1        31 
q) wj[-0D00:10 0D00:02+\:calls.time;`id`time; calls; (update `p#id from `id xasc  computer;(max;`cpu);(avg;`cpu))] 
time                          id  severity cpu cpu
--------------------------------------------------
2017.11.14D01:50:39.028941608 990 1        1   1  
2017.11.14D01:50:42.241758621 33  2        1   1  
2017.11.14D01:50:42.694582558 843 1        1   1  
2017.11.14D01:52:02.383347667 16  2        1   1  
2017.11.14D01:54:52.882221705 776 3        1   1  
2017.11.14D01:57:15.965935654 529 3        1   1  
2017.11.14D02:01:03.514993108 766 1        1   1
at15 commented 6 years ago

Randomness and probability http://code.kx.com/q/tutorials/q-by-examples/

q)A:5?1.;A                 /5 random floats from 0..1
0.03505812 0.7834427 0.7999031 0.9046515 0.2232866

q)B:10?2;B                 /coin toss
1 1 1 0 1 0 1 1 0 0

q)B1:10?0b;B1              /with booleans
11110010101b

q)C:-3?3;C                 /deal 3 unique cards out of 3
1 0 2

q)(min;max)@\:A            /min and max over the list
0.03505812 0.9046515

q)B?0                      /first zero
3

q)avg C~/:1_10000{-3?3}\() /method monte carlo
0.1643836

q)reciprocal f 3           /exact probability of 3 cards in given order
0.1666667
at15 commented 6 years ago 
q)item:`nut                 / atom (singleton)

q)items:`nut`bolt`cam`cog   / list
q)sales: 6 8 0 3            / list
q)prices: 10 20 15 20       / list

q)(items;sales;prices)      / list of lists
nut bolt cam cog
6   8    0   3
10  20   15  20

q)dict:`items`sales`prices!(items;sales;prices) / dictionary
q)dict
items | nut bolt cam cog
sales | 6   8    0   3
prices| 10  20   15  20

q)tab:([]items;sales;prices)                   / table
q)tab
items sales prices
------------------
nut   6     10
bolt  8     20
cam   0     15
cog   3     20

Note that a table is a flip (transpose) of a dictionary (I think it's just display in this way? If you store table in columnar format, it's just using dictionary, where each key is column name and each value is a list of table cells in this column)

q)flip dict
items sales prices
------------------
nut   6     10
bolt  8     20
cam   0     15
cog   3     20
at15 commented 6 years ago

closing all reference issue for now ....