Developer
Get started with KDB-X
Download the free Community Edition from the KX Developer Center and start building with a unified platform for time-series, vector, and AI data.
ポイント
- Familiar Interface – SQL syntax can be used directly in KDB-X, lowering the barrier to entry for users coming from traditional database systems.
- Interoperability – You can query, transform, and aggregate q tables using standard SQL commands such as SELECT, WHERE, and GROUP BY.
- Multiple Execution Options – Queries can be run inline using s) or via functions such as .s.e, .s.sp, .s.sq, and .s.sx for parameterized or reusable queries.
- Integration with q – SQL can call and interact with q functions, enabling powerful hybrid workflows that combine SQL readability with q’s advanced data manipulation capabilities.
- Extensibility – SQL support in KDB-X continues to evolve, offering a foundation that may eventually be modularized for more flexible deployment.
This tutorial walks through how to use SQL directly inside KDB-X to query, transform, and analyze q tables — offering a familiar relational interface while tapping into the power and efficiency of the KDB-X engine.
Using SQL in KDB-X
SQL provides a familiar relational interface for querying and managing data in KDB-X, allowing users to leverage standard SQL syntax within the q environment.
In this tutorial, we will walk through some examples of how to use SQL to interact with and manipulate q objects, and then show how to utilise the power of q functionality through SQL. You can also explore the entire notebook on GitHub.
Load the dataset
To begin with, we will load a dataset into a q table.
//Run the initialisation function so that you can use s) or .s.e straight away
.s.init[]
// The \z system command sets the format for date parsing
\z 1
//load the data
fvprices:("SSSDSF"; enlist ",") 0: `$":/src/wholesaleproduceprices.csv"
Now the dataset has been loaded, we can inspect the first row of the table to give us an idea of what the table looks like
first fvprices;
category| `fruit
item | `apples
variety | `bramleys_seedling
date | 2025.10.13
unit | `kg
price | 1.27 The data we’re using is a simple table giving information on average wholesale prices of selected home-grown horticultural produce in England and Wales. It is updated every fortnight. Source: https://www.gov.uk/government/statistical-data-sets/wholesale-fruit-and-vegetable-prices-weekly-average.
Using SQL to interrogate a q table
In KDB-X, we have the ability to query the above dataset using both q and SQL.
This is particularly useful if you have a q object that you need to interrogate, but your skillset is more suited to SQL than the q language.
Common query types in SQL
There are different options to query using SQL within a q session using variations of more q-like and more SQL-like syntax.
The first option is to use s) and then standard SQL code.
To return all rows of the table:
s)SELECT * FROM fvprices
category item variety date unit price
---------------------------------------------------------------------------
fruit apples bramleys_seedling 2025.10.13 kg 1.27
fruit apples coxs_orange_group 2025.10.13 kg 1.22
fruit apples egremont_russet 2025.10.13 kg 1.46
fruit apples braeburn 2025.10.13 kg 1.38
fruit apples gala 2025.10.13 kg 1.23
fruit blackberries blackberries 2025.10.13 kg 15.35
fruit blueberries blueberries 2025.10.13 kg 12.19
fruit pears conference 2025.10.13 kg 1.22
fruit pears doyenne_du_comice 2025.10.13 kg 1.17
fruit plums all_other 2025.10.13 kg 1.52
fruit raspberries raspberries 2025.10.13 kg 13.16
fruit strawberries strawberries 2025.10.13 kg 4.08
vegetable beans dwarf_french_or_kidney 2025.10.13 kg 1.87
vegetable beans runner_climbing 2025.10.13 kg 3.92
vegetable beetroot beetroot 2025.10.13 kg 0.7
vegetable brussels_sprouts brussels_sprouts 2025.10.13 kg 1 However, it is not necessary capitalise key words like SELECT and FROM:
s)select * from fvprices where category = 'vegetable'
category item variety date unit price
---------------------------------------------------------------------------
vegetable beans dwarf_french_or_kidney 2025.10.13 kg 1.87
vegetable beans runner_climbing 2025.10.13 kg 3.92
vegetable beetroot beetroot 2025.10.13 kg 0.7
vegetable brussels_sprouts brussels_sprouts 2025.10.13 kg 1
vegetable pak_choi pak_choi 2025.10.13 kg 3.5
vegetable curly_kale curly_kale 2025.10.13 kg 4.19
vegetable cabbage red 2025.10.13 kg 0.54
vegetable cabbage savoy 2025.10.13 head 0.64
vegetable spring_greens prepacked 2025.10.13 kg 1.39
vegetable cabbage summer_autumn_pointed 2025.10.13 kg 0.79
vegetable cabbage white 2025.10.13 kg 0.52
vegetable cabbage round_green_other 2025.10.13 head 0.62
.. Another way to query using SQL is to wrap your SQL code within quotation marks using .s.e:
.s.e"SELECT category, COUNT(item) AS count_per_category FROM fvprices GROUP BY category"
category count_per_category
------------------------------
cut_flowers 423
fruit 3521
pot_plants 49
vegetable 12673 Common SQL aggregations and calculations are supported in KDB-X. For example, to find the average price of each type of item:
.s.e"SELECT category,item,unit,avg(price) AS avg_price FROM fvprices GROUP BY category,item ORDER BY avg_price DESC"
category item unit avg_price
-----------------------------------------------
vegetable asparagus kg 10.35726
vegetable watercress kg 9.374348
fruit raspberries kg 8.969415
fruit blueberries kg 8.801304
fruit blackberries kg 8.368394
fruit currants kg 8.165982
fruit gooseberries kg 6.566883
fruit cherries kg 5.314222
vegetable rocket kg 5.295729
vegetable mixed_babyleaf_salad kg 5.292558 Defining functions
Functions can be used in a number of different ways using both SQL and q.
You can define a function to return a table in SQL and then query the result of that function:
x:.s.e"SELECT item, variety, unit, avg(price) as avg_price FROM fvprices WHERE price>15.00 GROUP BY variety";
.s.e"select item, avg_price from x";
item avg_price
----------------------
cherries 16.08
asparagus 17.20381
currants 17.16
blackberries 16.342
gooseberries 16.7075
raspberries 16.57
currants 15.82 Using parameters
To parameterize your function, you can use .s.sp. The function expects a list of parameters so in the case where you only need one parameter, you can use enlist.
.s.sp["select item,variety, price from fvprices where category=$1"](enlist `fruit);
item variety price
-------------------------------------
apples bramleys_seedling 1.27
apples coxs_orange_group 1.22
apples egremont_russet 1.46
apples braeburn 1.38
apples gala 1.23
blackberries blackberries 15.35
blueberries blueberries 12.19
pears conference 1.22
pears doyenne_du_comice 1.17
plums all_other 1.52
raspberries raspberries 13.16
strawberries strawberries 4.08
..
.s.sp["select item,variety, price from fvprices where category=$1 and price<$2"](`fruit;2.00);
item variety price
-------------------------------
apples bramleys_seedling 1.27
apples coxs_orange_group 1.22
apples egremont_russet 1.46
apples braeburn 1.38
apples gala 1.23
pears conference 1.22
pears doyenne_du_comice 1.17
plums all_other 1.52
apples bramleys_seedling 1.35
apples coxs_orange_group 1.36
.. To define a function that you would like to use repeatedly with different parameters, you can use .s.sq to define the function, giving null values for the parameters:
query:.s.sq["select * from fvprices where item=$1 and price<$2"](`;0n);
And then use .s.sx to call that function with whichever parameters you need:
.s.sx[query](`apples;0.6)
category item variety date unit price
--------------------------------------------------------
fruit apples coxs_orange_group 2023.09.08 kg 0.5
fruit apples other_late_season 2022.08.12 kg 0.58
fruit apples braeburn 2021.09.24 kg 0.53
fruit apples braeburn 2021.07.02 kg 0.5
fruit apples other_mid_season 2019.10.11 kg 0.59
fruit apples braeburn 2019.08.23 kg 0.55
fruit apples other_late_season 2019.01.18 kg 0.57
fruit apples other_late_season 2019.01.11 kg 0.54
fruit apples other_late_season 2018.12.14 kg 0.58
fruit apples other_mid_season 2018.11.23 kg 0.32
fruit apples other_mid_season 2018.11.02 kg 0.37
fruit apples other_mid_season 2018.10.26 kg 0.37
fruit apples other_early_season 2018.10.12 kg 0.44
fruit apples other_early_season 2018.10.05 kg 0.52
fruit apples other_early_season 2018.09.21 kg 0.53
.s.sx[query](`beans;1.4)
category item variety date unit price
-----------------------------------------------------
vegetable beans broad 2020.10.23 kg 1.35
vegetable beans broad 2020.10.16 kg 1.28
vegetable beans broad 2020.10.09 kg 1.33
vegetable beans broad 2020.09.25 kg 1.34
vegetable beans broad 2020.09.18 kg 1.34
vegetable beans broad 2019.11.01 kg 1.12
vegetable beans broad 2019.10.25 kg 1.15
vegetable beans broad 2019.10.18 kg 1.27
vegetable beans broad 2019.10.11 kg 1.26
vegetable beans runner_climbing 2019.10.11 kg 1.08
vegetable beans broad 2019.09.20 kg 1.3
vegetable beans runner_climbing 2019.09.13 kg 1.34
vegetable beans broad 2019.09.06 kg 1.36
.. Integrate with q
Sometimes, it may be easier to use a function that exists in the q language.
In the code below, we are looking at an example of creating a pivot table – something that is relatively straightforward and simple to do in just a few lines of q code.
pivotprices:{[]
//Remove the "." from the column names (dates) because SQL does not like dots in column names
pp:update date:`$"_" sv '"." vs' string[date] from select variety, price, date from fvprices;
//Pull out the date column into distinct values that will become individual columns in the pivot table
D:asc exec distinct date from pp;
//Create the dictionary mappings
B: exec D#(date!price) by variety:variety from pp;
//Add the remaining columns back into the table
A:select variety, category,item,unit from fvprices;
A lj B
}; We can then call that q function from within the SQL code
s)select * from qt('{pivotprices[]}[]')
Conclusion
By supporting SQL queries directly in the q environment, KDB-X empowers analysts and developers to leverage existing SQL expertise while taking advantage of kdb+’s efficiency and expressiveness.
To learn more about KDB-X modules, visit KDB-X Module Management.
If you enjoyed this blog and would like to explore other examples, you can visit our GitHub repository. You can also begin your journey with KDB-X by signing up for the KDB-X Community Edition Public Preview.
Get started with KDB-X
Download the free Community Edition from the KX Developer Center and start building with a unified platform for time-series, vector, and AI data.
