SQL-like Operations with KDB+ and Q

Introduction

While KDB+ and Q have their own distinct syntax, many operations mirror those found in SQL, making it a familiar ground for those with a SQL background. This chapter explores the Q equivalents of common SQL constructs, providing a bridge between the two worlds.

Creating and Manipulating Tables

KDB+ tables are similar to SQL tables, but with a more concise syntax.

Code snippet

// Create a table
trades:([] time:`timestamp$til 10:00:01; symbol:`AAPL`GOOG`IBM; price:100+til 10; volume:1000*til 10)

// Insert a new row
append trades ([] time:10:00:01; symbol:`MSFT; price:120; volume:800)

// Update a row
update price:110 from trades where symbol=`AAPL and time = 10:00:00

// Delete a row
delete from trades where symbol=`IBM

Selecting Data

Q's select statement is analogous to SQL's SELECT clause.

Code snippet

// Select all columns
select from trades

// Select specific columns
select time, price from trades

// Where clause
select from trades where price > 105

// Order by
select from trades order by price desc

// Group by
select avg price by symbol from trades

Joining Tables

KDB+ offers flexible join operations:

Code snippet

// Create another table
quotes:([] time:`timestamp$til 10:00:01; symbol:`AAPL`GOOG`IBM; open:95+til 10)

// Inner join
joinedTable: aj[`time`symbol; trades; quotes]

// Left outer join
leftJoin: lj[`time`symbol; trades; quotes]

// Right outer join
rightJoin: rj[`time`symbol; trades; quotes]

Aggregate Functions

Q provides a rich set of aggregate functions:

Code snippet

// Basic aggregations
sum price from trades
avg price from trades
min price from trades
max price from trades
count price from trades

// Grouped aggregations
sum price by symbol from trades

Subqueries

While KDB+ doesn't have explicit subqueries like SQL, it can achieve similar results using functional programming or table joins:

Code snippet

// Find symbols with average price greater than 105
avgPriceBySymbol: avg price by symbol from trades
symbolsWithHighAvg: select from avgPriceBySymbol where avg > 105

Window Functions

KDB+ supports window functions through its moving_avg and related functions:

Code snippet

// Calculate moving average of price
mavg: moving_avg[price; 3] // Calculate 3-period moving average

Common Table Expressions (CTEs)

While KDB+ doesn't have direct CTE support, intermediate results can be stored in variables:

Code snippet

// Calculate average price by symbol
avgPriceBySymbol: avg price by symbol from trades

// Use the result in another calculation
select from avgPriceBySymbol where avg > 100

Performance Considerations

• Indexing: Create indexes on frequently queried columns for optimal performance.

• Vectorized operations: Utilize vector operations whenever possible.

• Data types: Choose appropriate data types to avoid unnecessary conversions.

• Query optimization: Analyze query execution plans to identify performance bottlenecks.

Summary

While KDB+ and Q have their own unique syntax, the core concepts of data manipulation and query are similar to SQL. By understanding these equivalences, SQL users can quickly adapt to the KDB+ world and leverage its powerful capabilities for high-performance data processing.