HowTo: Query

This HowTo is based on this table definition

Synchronous

We want to get all data out of our table. So we have to write a select query in q-sql? No:) You don't need q-sql. Just java. Have a look:

// static table definition
final MyTable table = MyTable.get();

// create select
final Select select = table.select()
	.column(table.sym())
	.column(table.time())
	.column(table.size())
	.column(table.price())
	.build();

// connect to kdb+
final QConnectorSync c = QConnectorFactory.create("localhost", 5011);
c.connect();

// execute select and print the result
final Result result = c.select(select);
for (final TableRow row : table.read(result)) {
	System.out.println(row.get(table.time()));
	System.out.println(row.get(table.sym()));
	System.out.println(row.get(table.price()));
	System.out.println(row.get(table.size()));
}

// close connection
c.disconnect();

Okay. That was easy. But not very sophisiticated:) Go on!

sort data

Now we want our data to be time sorted.

final Select select = table.select()
	.column(table.sym())
	.column(table.time())
	.column(table.size())
	.column(table.price())
	.order(Direction.descending, table.time())
	.build();

limit data

Now we just want to have the last 10 entries.

final Select select = table.select()
	.column(table.sym())
	.column(table.time())
	.column(table.size())
	.column(table.price())
	.order(Direction.descending, table.time())
	.limit(10)
	.build();

Okay. You can also add .start(100) to skip the first 100 entries.

No we want to continue and filter our data.

filter data

Every column provides some filter possibilities. Based on the type you can filter:

Nominal (Symbol)

• filterEqualTo(Column or value)
• filterNotEqualTo(Column or value)
• filterIn(List of values)

Ordinal (All except Symbol)

• filterGreaterThan(Column or value)
• filterGreaterOrEqualThan(Column or value)
• filterSmallerOrEqualThan(Column or value)
• filterSmallerThan(Column or value)

Here we filter just a few symbols:

final Select select = table.select()
	.column(table.sym())
	.column(table.time())
	.column(table.size())
	.column(table.price())
	.filter(table.sym().filterIn(SymbolValue.froms(new String[] {"AAA", "BBB"})))
	.build();

Here we filter sizes greater than 10:

final Select select = table.select()
	.column(table.sym())
	.column(table.time())
	.column(table.size())
	.column(table.price())
	.filter(table.size().filterGreaterOrEqualThan(IntegerValue.from(10)))
	.build();

group data

Every column provides some grouping possibilities. Based on the type you can group:

Nominal (Symbol)

• first()
• last()
• count()

Ordinal (All except Symbol)

• min()
• max()
• avg()
• sum()
• xbar(LongValue)

For example we group by symbol and calculate the average price and the sum of sizes per smbol.

final Select select = table.select()
	.column(table.size().sum())
	.column(table.price().avg())
	.group(table.sym().group())
	.build();

Asynchronous

Not yet supported! Sorry...