Overview
A q table is essentially a collection of named columns implemented as a dictionary. Consequently, q tables are column-oriented, in contrast to the row-oriented tables in relational databases.
Table Definition
Review of Table as Column Dictionary
q)dc:`name`iq!(`Dent`Beeblebrox`Prefect;98 42 126)
Transpose it with flip to get a table.
q)t:flip `name`iq!(`Dent`Beeblebrox`Prefect;98 42 126)
All tables have type 98h.
q)type t
98h
Table-Definition Syntax
([] *c1*:*L1*; ...; *cn*:*Ln*)
The colons in table-definition syntax are not assignment. They are part of the syntactic sugar and serve as markers separating column names from column values.
q)t:([] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)t~flip `name`iq!(`Dent`Beeblebrox`Prefect;98 42 126)
1b
q)([] c1:1+til 5; c2:5#42)
c1 c2
-----
1 42
2 42
3 42
4 42
5 42
Provided you specify at least one column as a list, atoms will be extended to match.
q)([] c1:`a`b`c; c2:42; c3:98.6)
c1 c2 c3
----------
a 42 98.6
b 42 98.6
c 42 98.6
You cannot define a single-row table using all atoms. You must enlist at least one of the atoms.
q)([] c1:`a; c2:100)
'rank
q)([] enlist `a; c2:100)
c1 c2
------
a 100
q)(flip `c1`c2`c3!(`a`b`c;42;1.1)) ~ ([] c1:`a`b`c;c2:42 42 42; c3:1.1 1.1 1.1)
1b
Table Metadata
The column names of a table can be retrieved as a list of symbols with cols
q)cols t
`name`iq
The function meta applied to a table retrieves its metadata. The result is a keyed table with one record for each column in the original table.
-
The key column
cof the result contains the column names. -
The column
tcontains a symbol denoting the type char of the column. -
The column
fcontains the domains of any foreign key or link columns. -
The column
acontains any attributes associated with the column.q)meta t
c t f a name s iq j
The function tables takes a symbolic namespace and returns a sorted symbol list of the names of tables in that context.
q)t2:([] c1:1 2 3; c2:(1 2; enlist 3; 4 5 6))
q)t:([] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)tables `.
`s#`t`t2
Alternatively, the command \a provides the same result. If no argument is provided, it returns the result for the current context.
Records
Since a table is logically a list of dictionary records, count returns the number of records.
q)count t
3
q)t[1]
name| `Beeblebrox
iq | 42
Since a record dictionary slices across the table display horizontally, this motivates calling the value portion of the record dictionary a table row. The record associates column names with the values in a physical row. To retrieve the naked values of a row – i.e., without the column names – simply apply value to the record dictionary.
q)value t[1]
`Beeblebrox
42
Empty Tables and Schema
q)([] name:(); iq:())
It is good practice to specify the types of all columns in an empty table.
q)([] name:`symbol$(); iq:`int$())
q)([] name:0#`; iq:0#0) ~ ([] name:`symbol$(); iq:`long$())
1b
Basic select and update
Select
q)select from t
name iq
--------------
Dent 98
Beeblebrox 42
Prefect 126
q)select c1:name, c2:iq from t
c1 c2
-----------
Dent 98
Peter 100
Perfect 102
Basic update
q)update iq:iq%100 from t
name iq
------------
Dent 0.98
Peter 1
Perfect 1.02
Primary Keys and Keyed Tables
A keyed table is a dictionary mapping a table of key records to a table of value records. This represents a mapping from each row in a table of (presumably unique) keys to a corresponding row in a table of values
A keyed table is not a table – it is a dictionary and so has type 99h.
q)v:flip `name`iq!(`Dent`Beeblebrox`Prefect;98 42 126)
Now say we want to add a key column eid containing employee identifiers. We begin by placing the identifiers in a separate table.
k:flip (enlist `eid)!enlist 1001 1002 1003
eid
----
1001
1002
1003
Now establish the association between the two tables.
q)kt:k!v
q)kt
eid | name iq
----| --------------
1001| Dent 98
1002| Beeblebrox 42
1003| Prefect 126
Keyed-Table Definition Syntax
This is a generalization of (plain) table definition in which key column(s) are placed between the square brackets and the value columns are after the square brackets.
q)kt:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
eid | name iq
----| --------------
1001| Dent 98
1002| Beeblebrox 42
1003| Prefect 126
Define an empty keyed table
q)ktempty:([eid:()] name:(); iq:())
q)ktempty:([eid:`int$()] `symbol$name:(); iq:`int$())
q)ktempty:([eid:0#0] name:0#`; iq:0#0)
Accessing Records of a Keyed Table
q)kt[(enlist `eid)!enlist 1002] 或 kt[1002]
name| `Beeblebrox
iq | 42
q)kt[1002][`iq] 或 kt2[1002;`iq]
42
Retrieving Multiple Records
q)k2[(enlist 1001;enlist 1002)]
q)kt ([] eid:1001 1002)
q)([] eid:1001 1002)#kt
eid | name iq
----| -------------
1001| Dent 98
1002| Beeblebrox 42
Reverse Lookup
q)kts:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect)
q)kts?([] name:`Prefect`Dent)
eid
----
1003
1001
Components of a Keyed Table
q)kt
eid | name iq
----| --------------
1001| Dent 98
1002| Beeblebrox 42
1003| Prefect 126
q)key kt
eid
----
1001
1002
1003
q)value kt
name iq
--------------
Dent 98
Beeblebrox 42
Prefect 126
q)keys kt
,`eid
q)cols kt
`eid`name`iq
Tables vs. Keyed Tables
It is possible to convert dynamically between a regular table having a column of potential key values and the corresponding keyed table using binary primitive xkey.
q)t:([] eid:1001 1002 1003; name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)`eid xkey t
eid | name iq
----| --------------
1001| Dent 98
1002| Beeblebrox 42
1003| Prefect 126
Conversely, to convert a keyed table to a regular table, use xkey with an empty general list as the left operand.
q)kt:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)kt
_
q)() xkey kt
eid name iq
-------------------
1001 Dent 98
1002 Beeblebrox 42
1003 Prefect 126
Compound Primary Key
q)ktc:([lname:`Dent`Beeblebrox`Prefect; fname:`Arthur`Zaphod`Ford]; iq:98 42 126)
q)ktc
lname fname | iq
-----------------| ---
Dent Arthur| 98
Beeblebrox Zaphod| 42
Prefect Ford | 126
q)ktc[`Dent`Arthur]
iq| 98
q)ktc:([lname:`symbol$();fname:`symbol$()] iq:`int$())
q)ktc:([lname:0#`;fname:0#`] iq:0#0)
Extracting Column Data
q)ktc:([k1:`a`b`c;k2:`x`y`z] v1:`a`b`c; v2:1.1 2.2 3.3)
k1 k2| v1 v2
-----| ------
a x | a 1.1
b y | b 2.2
c z | c 3.3
q)ktc[([] k1:`a`c;k2:`x`z)][`v1`v2]
a c
1.1 3.3
We can simplify using indexing at depth.
q)ktc[([] k1:`a`c;k2:`x`z); `v1`v2]
`a 1.1
`c 3.3
Foreign Keys and Virtual Columns
A foreign key in SQL is a column in one table whose values are members of a primary key column in another table. Foreign keys are the mechanism for establishing relations between tables.
q)kt:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)tdetails:([] eid:`kt$1003 1001 1002 1001 1002 1001; sc:126 36 92 39 98 42)
q)meta tdetails
c | t f a
---| ------
eid| j kt
sc | j
The built-in function fkeys applied to a table (or keyed table) returns a dictionary in which each foreign key column name is mapped to its primary key table name.
q)fkeys tdetails
eid| kt
Resolving a Foreign Key
When you wish to resolve a foreign key – i.e., get the actual values instead of enumerated values – apply value to the enumerated column.
q)meta update value eid from tdetails
c | t f a
---| -----
eid| j
sc | j
Observe that there is no longer an entry in the f column.
Foreign Keys and Relations
q)select eid.name, sc from tdetails
name sc
--------------
Prefect 126
Dent 36
Beeblebrox 92
Dent 39
Beeblebrox 98
Dent 42
There is an implicit left join between tdetails and kt here.
Working with Tables and Keyed Tables
q)t:([] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
q)kt:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
Appending Records
The fundamental way to append a record to a table is to view the table as a list of records and join with ,:. Note that the fields in the record do not need to be in column order.
q)t,:`name`iq!(`W; 26)
q)t,:`iq`name!(200; `Albert)
name iq
--------------
Dent 98
Beeblebrox 42
Prefect 126
W 26
Albert 200
You can also append naked row values with ,: but the fields must be in column order.
q)t,:(`H; 142)
q)t
name iq
--------------
Dent 98
Beeblebrox 42
Prefect 126
W 26
Albert 200
H 142
q)t,:(97;`J)
'type
First and Last Records
q)first t
name| `Dent
iq | 98
q)last t
name| `H
iq | 142
You can retrieve the first or last n records of a table or keyed table using the Take operator #.
q)2#t
name iq
-------------
Dent 98
Beeblebrox 42
q)-3#t
name iq
----------
W 26
Albert 200
H 142
Find ?
The Find operator ? used with a table returns the index of a record – i.e., its row number.
q)t?`name`iq!(`Dent;98)
0
q)t?(`Dent;98)
q)t?((`Dent;98);(`Prefect;126))
0 2
Union with ,
The Join operator , is defined for tables and keyed tables since they both comprise lists of records. It is essentially the same as UNION in SQL.
Tables having exactly the same meta result can be joined to form a table. Since a table is a list of records, the result is obtained by appending the records of the right operand to those of the left.
q)t,`name`iq!(`Slaartibartfast; `123)
name iq
--------------------
Dent 98
Beeblebrox 42
Prefect 126
Slaartibartfast `123
q)t,([] name:1#`W; iq:1#26)
name iq
--------------
Dent 98
Beeblebrox 42
Prefect 126
W 26
Albert 200
H 142
W 26
q)t,t
Two keyed tables with the same meta result can be joined with ,.
Coalesce ^
Coalesce ^ can be used to merge two keyed tables having the same columns.
q)([k:`a`b`c] v:10 0N 30)^([k:`a`b`c] v:100 200 0N)
k| v
-| ---
a| 100
b| 200
c| 30
q)([k:`a`b`c`x] v:10 0N 30 40)^([k:`a`b`c`y]; v:100 200 0N 0N)
k| v
-| ---
a| 100
b| 200
c| 30
x| 40
y|
Column Join
Two tables with the same number of records can be joined sideways with Join Each (,') to create a column join in which the columns are aligned in parallel
q)([] c1:`a`b`c),'([] c2:100 200 300)
c1 c2
------
a 100
b 200
c 300
When the column lists of the tables are not disjoint, the operation on the common columns has upsert semantics because each record is a dictionary.
q)([] c1:`a`b`c; c2:1 2 3),'([] c2:100 200 300)
c1 c2
------
a 100
b 200
c 300
q)([k:1 2 3] v1:10 20 30),'([k:3 4 5] v2:1000 2000 3000)
k| v1 v2
-| -------
1| 10
2| 20
3| 30 1000
4| 2000
5| 3000
Operations on Compound Column Data
q)tm:([] wk:2015.01.01 2015.01.08; rv:(38.92 67.34; 16.99 5.14 128.23 31.69))
q)tm
wk rv
----------------------------------
2015.01.01 38.92 67.34
2015.01.08 16.99 5.14 128.23 31.69
q)select wk, srt:desc each rv, avgr:avg each rv, hi:max each rv from tm
wk srt avgr hi
-------------------------------------------------
2015.01.01 67.34 38.92 53.13 67.34
2015.01.08 128.23 31.69 16.99 5.14 45.5125 128.23
q)select wk, drp:neg 1_'deltas each desc each rv from tm
wk drp
---------------------------
2015.01.01 ,28.42
2015.01.08 96.54 14.7 11.85
Attributes
Sorted `s#
Applying the sorted attribute `s# to a simple list indicates that the items of the list are sorted in ascending order; there is no way to indicate a descending sort.
q)`s#1 2 4 8
`s#1 2 4 8
q)`s#2 1 3 4
's-fail
The sort function asc automatically applies the sorted attribute to its result but til does not.
q)asc 2 1 8 4
`s#1 2 4 8
q)til 5
0 1 2 3 4
Unique `u
Applying the unique attribute `u# to a list indicates that the items of the list are distinct. Knowing that the elements of a list are unique makes distinct the identity function and shortens some operations
q)`u#2 1 4 8
`u#2 1 4 8
q)`u#2 1 4 8 2
'u-fail
Parted `p#
The parted attribute `p# indicates that all common occurrences of any value in a list are adjacent.
q)`p#2 2 2 1 1 4 4 4 4 3 3
`p#2 2 2 1 1 4 4 4 4 3 3
Historical time-series databases for ticker symbols are usually sorted by time within symbol with the parted attribute applied to the (enumerated) symbol column. This makes queries by ticker fast and guarantees that results for a given symbol are returned in time order.
Grouped `g#
The grouped attribute `g# differs from other attributes in that it can be applied to any list. It causes q to create and maintain an index – essentially a hash table. Grouped can be applied to a list when no other assumptions about its structure can be made.
q)`g#1 2 3 2 3 4 3 4 5 2 3 4 5 4 3 5 6
`g#1 2 3 2 3 4 3 4 5 2 3 4 5 4 3 5 6
q)L:`g#100?100
q)L
`g#12 10 1 90 73 90 43 90 84 63 93 54 38 97 88 58 68 45 2 39 64 49 82 40 88 7..
q)L,:1 1 1 1
q)L
`g#12 10 1 90 73 90 43 90 84 63 93 54 38 97 88 58 68 45 2 39 64 49 82 40 88 7..
Applying the grouped attribute to a table column roughly corresponds to placing an index on a column in an RDBMS. As of this writing (Sep 2015), in q3.2 the maximum number of grouped attributes that can be placed on a single table is unlimited.
Remove Attribute `#
The operations `# removes any attribute that may currently be applied.
q)L:`s#til 10
q)L
`s#0 1 2 3 4 5 6 7 8 9
q)`#L
0 1 2 3 4 5 6 7 8 9
作者：Jeffry A. Borror
