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)Copy the code

Transpose it with flip to get a table.  

q)t:flip `name`iq! (`Dent`Beeblebrox`Prefect; 98 42 126)Copy the code

All tables have type 98h.

q)type t
98h
Copy the code

Table-Definition Syntax

([] *c1*:*L1*; . ; *cn*:*Ln*)Copy the code

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 42Copy the code

Provided you specify at least one column as a list, atoms will be extended to match.

q)([] c1:`a`b`c; c2:42; C :98.6) c1 c2 c3 ---------- a 42 98.6b 42 98.6c 42 98.6Copy the code

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. C1: (1.1)) ~ ([] ` a ` ` b c; c2:42 42 42; C3:1.1, 1.1, 1.1) 1 bCopy the code

Table Metadata

The column names of a table can be retrieved as a list of symbols with cols

q)cols t
`name`iq
Copy the code

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 c of the result contains the column names.

  • The column t contains a symbol denoting the type char of the column.

  • The column f contains the domains of any foreign key or link columns.

  • The column a contains 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
Copy the code

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
Copy the code

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 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
Copy the code

Empty Tables and Schema

q)([] name:(); iq:())
Copy the code

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
Copy the code

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
Copy the code

Basic update

Q) Update IQ: IQ %100 from t name IQ ------------ Dent 0.98 Peter 1 Perfect 1.02Copy the code

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)Copy the code

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 1003Copy the code

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 126Copy the code

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
Copy the code

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)
Copy the code

Accessing Records of a Keyed Table

[(q) kt enlist ` eid! Enlist 1002] or kt [1002] name | ` Beeblebrox IQ | 42 q) kt [1002] [` IQ] or kt2 [1002; ` IQ] 42Copy the code

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
Copy the code

Reverse Lookup

q)kts:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect) q)kts? ([] name:`Prefect`Dent) eid ---- 1003 1001Copy the code

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
Copy the code

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
Copy the code

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
Copy the code

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)
Copy the code

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 | c 3.3 2.2 c z q) KTC [([] k1: ` a ` c; k2: ` ` z x)] [` v1 ` v2] a c 1.1 3.3Copy the code

We can simplify using indexing at depth.

[([] k1: 'a' c;k2: 'x' z)Copy the code

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    
Copy the code

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
Copy the code

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
Copy the code

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
Copy the code

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)
Copy the code

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 200Copy the code

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) 'typeCopy the code

First and Last Records

q)first t
name| `Dent
iq  | 98
q)last t
name| `H
iq  | 142
Copy the code

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
Copy the code

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; 0 2 126))Copy the code

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,tCopy the code

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|    
Copy the code

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
Copy the code

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
Copy the code

Operations on Compound Column Data

Tm: (q) [] wk: a 2015.01.01 2015.01.08; The rv: (38.92 67.34; 16.99 5.14 128.23 31.69) q) tm wk rv a -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 2015.01.01 2015.01.08 16.99 67.34 38.92 5.14 Q)select wk, SRT :desc each rv, avgr:avg each rv, Hi: Max each rv from tm wk SRT avgr a 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: 1 _ 'neg deltas each desc each rv from tm wk DRP a -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 2015.01.01, 28.42 2015.01.08 96.54 14.7 11.85Copy the code

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
Copy the code

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
Copy the code

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
Copy the code

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
Copy the code

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 To 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..Copy the code

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
Copy the code

Reference: code.kx.com/q4m3/8\_Tab…

By Jeffry A. Borror