Tutorials/Tables II/Solutions

From Kx Wiki
Jump to: navigation, search

1. Create a table cities with columns city and state, keyed to city.

q)show cities:([city:`Boston`Lowell`NewYork`Nyack`Montauk`Rochester];state:`MA`M
A`NY`NY`NY`NY)
city     | state
---------| -----
Boston   | MA
Lowell   | MA
NewYork  | NY
Nyack    | NY
Montauk  | NY
Rochester| NY

2. Create an unkeyed table sales with columns city and sale.

As an alternative to typing in the raw content of sales, see if you can construct it using rand and the city column of cities.

Start with the cities:

q)count c:exec city from cities          // how many cities?
6
q)rand each 20#count c                   // 20 random picks from 6
3 4 4 2 4 2 2 2 2 0 5 5 2 5 3 0 2 1 0 1
q)c rand each 20#count c                 // use them to index c
`Boston`Nyack`Nyack`Rochester`Boston`NewYork`Montauk`Nyack`Montauk`Boston`Mon..

Putting it together:

q)show c:exec city from cities
`Boston`Lowell`NewYork`Nyack`Montauk`Rochester
q)show sales:([]city:c rand each 20#count c;sale:rand each 20#1000)
city      sale
--------------
Rochester 55
Lowell    92
Boston    142
Boston    858
NewYork   273
Lowell    622
NewYork   985
Nyack     945
Montauk   592
NewYork   523
Nyack     776
Montauk   264
Boston    298
Montauk   606
NewYork   275
Boston    396
NewYork   11
Boston    1
Nyack     256
NewYork   988

3. Join the tables to show the state for each sale. Your result will be an unkeyed table with columns city, sale, and state.

q)sales ij cities
city      sale state
--------------------
Rochester 55   NY
Lowell    92   MA
Boston    142  MA
Boston    858  MA
NewYork   273  NY
Lowell    622  MA
NewYork   985  NY
Nyack     945  NY
Montauk   592  NY
NewYork   523  NY
Nyack     776  NY
Montauk   264  NY
Boston    298  MA
Montauk   606  NY
NewYork   275  NY
Boston    396  MA
NewYork   11   NY
Boston    1    MA
Nyack     256  NY
NewYork   988  NY

4. Report the total sales, by state. Your result will be a keyed table with columns state and sale, keyed to state, with a row for each unique state in the result from (3).

Two steps to this. First group the sales by state. Then sum those sales.

q)select sale by state from sales ij cities
state| sale
-----| -------------------------------------------------
MA   | 92 142 858 622 298 396 1
NY   | 55 273 985 945 592 523 776 264 606 275 11 256 988
q)select sum sale by state from sales ij cities
state| sale
-----| ----
MA   | 2409
NY   | 6549
Personal tools
Namespaces
Variants
Actions
Navigation
Print/export
Toolbox