cassandra 2.x cql 5 語法學習(五)

學習 cql 進階的 query 語法
1. create table use clustering column

cqlsh:demo>  


CREATE TABLE timeline (
  day text,
  hour int,
  min int,
  sec int,
  value text,
  PRIMARY KEY (day, hour, min, sec)
);

2. insert example data

cqlsh:demo>  

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 3, 43, 12, 'event1');

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 3, 52, 58, 'event2');

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 4, 37, 01, 'event3');

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 4, 37, 01, 'event3');

cqlsh:demo>
INSERT INTO timeline (day, hour , min , sec , value ) VALUES ('12 Jan 2014', 6, 00, 34, 'event4');

cqlsh:demo>
SELECT * FROM timeline;


 day         | hour | min | sec | value
-------------+------+-----+-----+--------
 12 Jan 2014 |    3 |  43 |  12 | event1
 12 Jan 2014 |    3 |  52 |  58 | event2
 12 Jan 2014 |    4 |  37 |   1 | event3
 12 Jan 2014 |    6 |   0 |  34 | event4

(4 rows)

3. Slicing over partition rows

cqlsh:demo> 

SELECT * FROM timeline WHERE day='12 Jan 2014'
AND (hour, min) >= (3, 50)
AND (hour, min, sec) <= (4, 37, 30);

 day         | hour | min | sec | value
-------------+------+-----+-----+--------
 12 Jan 2014 |    3 |  52 |  58 | event2
 12 Jan 2014 |    4 |  37 |   1 | event3

(2 rows)

4. 查詢該筆資料的寫入的時間

cqlsh:demo>

select writetime(value) from timeline where hour = 6 allow filtering;

 writetime_value
------------------
 1408501327276126

(1 rows)