· 7 years ago · Feb 20, 2019, 12:18 PM
1### First Try
2CREATE KEYSPACE IF NOT EXISTS mykeyspace WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
3USE mykeyspace;
4 CREATE TABLE heartrate (
5 pet_chip_id uuid,
6 time timestamp,
7 heart_rate int,
8PRIMARY KEY (pet_chip_id, time));
9
10INSERT INTO heartrate(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:01:00', 100);
11INSERT INTO heartrate(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:02:00', 103);
12INSERT INTO heartrate(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:03:00', 130);
13INSERT INTO heartrate(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:04:00', 131);
14INSERT INTO heartrate(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:05:00', 111);
15INSERT INTO heartrate(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:06:00', 99);
16INSERT INTO heartrate(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-05 07:04:00', 100);
17INSERT INTO heartrate(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-05 07:05:00', 99);
18INSERT INTO heartrate(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-05 07:06:00', 100);
19
20SELECT * from heartrate WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23;
21SELECT * from heartrate WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 AND time>='2019-03-04 07:03:00' AND time <= '2019-03-05 07:05:00';
22
23
24### Second
25CREATE TABLE heartrate2 (
26 pet_chip_id uuid,
27 date text,
28 time timestamp,
29 heart_rate int,
30 PRIMARY KEY ((pet_chip_id, date), time));
31
32INSERT INTO heartrate2(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04', '2019-03-04 07:01:00', 100);
33INSERT INTO heartrate2(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04', '2019-03-04 07:02:00', 103);
34INSERT INTO heartrate2(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04', '2019-03-04 07:03:00', 130);
35INSERT INTO heartrate2(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04', '2019-03-04 07:04:00', 131);
36INSERT INTO heartrate2(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04', '2019-03-04 07:05:00', 111);
37INSERT INTO heartrate2(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04', '2019-03-04 07:06:00', 99);
38INSERT INTO heartrate2(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-05', '2019-03-05 07:04:00', 100);
39INSERT INTO heartrate2(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-05', '2019-03-05 07:05:00', 99);
40INSERT INTO heartrate2(pet_chip_id, date, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-05', '2019-03-05 07:06:00', 100);
41
42SELECT * from heartrate2 WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 AND time>='2019-03-04 07:03:00' AND time <= '2019-03-05 07:05:00';
43> InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
44
45* Full Scan
46SELECT * from heartrate2 WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 AND time>='2019-03-04 07:03:00' AND time <= '2019-03-05 07:05:00' ALLOW FILTERING;
47
48* WHERE on Date
49SELECT * from heartrate2 WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 AND date = '2019-03-04' AND time>='2019-03-04 07:03:00' AND time <= '2019-03-05 07:05:00';
50SELECT * from heartrate2 WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 AND date = '2019-03-05' AND time>='2019-03-04 07:03:00' AND time <= '2019-03-05 07:05:00';
51
52* IN
53SELECT * from heartrate2 WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 AND date IN('2019-03-05', '2019-03-04') AND time>='2019-03-04 07:03:00' AND time <= '2019-03-05 07:05:00';