SQL

SELECT Stream

SELECT로 Stream 데이터를 조회

테스트 데이터 입력: Redis 명령으로 입력

온도 temp temperature
습도 humid humidity
압력 pres pressure
진동 vib vibration

Example

명령>	xadd sensor_1 * temp 10 humid 20 pres 30 vib 40
명령>	xadd sensor_1 * temp 11 humid 25 pres 28 vib 50
명령>	xadd sensor_1 * temp 12 humid 30 pres 26 vib 60
명령>	xadd sensor_1 * temp 13 humid 35 pres 24 vib 20
명령>	xadd sensor_1 * temp 14 humid 40 pres 22 vib 30
명령>	xadd sensor_2 * temp 124.7 humid 46.8 pres 150.4 flag AA1
명령>	xadd sensor_2 * temp 123.4 humid 50.4 pres 148.6 flag AA2
명령>	xadd sensor_2 * temp 119.8 humid 48.1 pres 149.2 flag BB1
명령>	xadd sensor_2 * temp 128.1 humid 51.2 flag BB2
명령>	xadd sensor_2 * temp 117.4 humid 46.7 flag BB3
명령>	xadd sensor_3 * temp 10
명령>	xadd sensor_3 * temp 50
명령>	xadd sensor_3 * temp 30
명령>	xadd sensor_3 * temp 20
명령>	xadd sensor_3 * temp 40
명령>	xadd mys1 * temp 10
명령>	xadd mys1 * temp 50
명령>	xadd mys1 * temp 30

SQL Insert 문으로 입력

Example

명령>	insert into stream values('sensor_1', '*', 'temp', 10, 'humid', '20', 'pres', '30', 'vib', '40');
명령>	insert into stream values('sensor_1', '*', 'temp', '11', 'humid', '25', 'pres', '28', 'vib', '50');
명령>	insert into stream values('sensor_1', '*', 'temp', '12', 'humid', '30', 'pres', '26', 'vib', '60');
명령>	insert into stream values('sensor_1', '*', 'temp', '13', 'humid', '35', 'pres', '24', 'vib', '20');
명령>	insert into stream values('sensor_1', '*', 'temp', '14', 'humid', '40', 'pres', '22', 'vib', '30');
명령>	insert into stream values('sensor_2', '*', 'temp', '124.7', 'humid', '46.8', 'pres', '150.4', 'flag', 'AA1');
명령>	insert into stream values('sensor_2', '*', 'temp', '123.4', 'humid', '50.4', 'pres', '148.6', 'flag', 'AA2');
명령>	insert into stream values('sensor_2', '*', 'temp', '119.8', 'humid', '48.1', 'pres', '149.2', 'flag', 'BB1');
명령>	insert into stream values('sensor_2', '*', 'temp', '128.1', 'humid', '51.2', 'flag', 'BB2');
명령>	insert into stream values('sensor_2', '*', 'temp', '117.4', 'humid', '46.7', 'flag', 'BB3');
명령>	insert into stream values('sensor_3', '*', 'temp', '10');
명령>	insert into stream values('sensor_3', '*', 'temp', '50');
명령>	insert into stream values('sensor_3', '*', 'temp', '30');
명령>	insert into stream values('sensor_3', '*', 'temp', '20');
명령>	insert into stream values('sensor_3', '*', 'temp', '40');
명령>	insert into stream values('mys1', '*', 'temp', '10');
명령>	insert into stream values('mys1', '*', 'temp', '50');
명령>	insert into stream values('mys1', '*', 'temp', '30');

SELECT

SELECT * : 포함된 모든 필드를 조회합니다.
SELECT key, id, field1, field2 : 조회할 필드를 지정합니다.
SELECT field1 name1 or field1 AS name1 --> alias(별명) 사용 가능

FROM

FROM stream.*
FROM stream.mystream*
FROM stream.mystream1

SELECT * FROM STREAM.MYSTREAM*

Stream에서 키와 필드를 조회한다.

Example

명령>	*select from stream.sensor_1;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355128-0\|10\|20\|30\|40 2) sensor_1\|1610347355132-0\|11\|25\|28\|50 3) sensor_1\|1610347355135-0\|12\|30\|26\|60 4) sensor_1\|1610347355138-0\|13\|35\|24\|20 5) sensor_1\|1610347355141-0\|14\|40\|22\|30
명령>	*select from stream.sensor;* opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib\|flag 1) sensor_1\|1610347355128-0\|10\|20\|30\|40\|(nil) 2) sensor_1\|1610347355132-0\|11\|25\|28\|50\|(nil) 중간 생략 15) sensor_3\|1610347355174-0\|40\|(nil)\|(nil)\|(nil)\|(nil)
명령>	*select from stream.my;* opcode
결과>	0) key\|id\|temp 1) mys1\|1610347355177-0\|10 2) mys1\|1610347355180-0\|50 3) mys1\|1610347355184-0\|30

ORDER BY 1

정렬(sort)해서 보여준다.

Example

명령>	*select from stream.sensor_1 order by vib;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355138-0\|13\|35\|24\|20 2) sensor_1\|1610347355141-0\|14\|40\|22\|30 3) sensor_1\|1610347355128-0\|10\|20\|30\|40 4) sensor_1\|1610347355132-0\|11\|25\|28\|50 5) sensor_1\|1610347355135-0\|12\|30\|26\|60
명령>	*select from stream.sensor_1 order by temp desc;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355141-0\|14\|40\|22\|30 2) sensor_1\|1610347355138-0\|13\|35\|24\|20 3) sensor_1\|1610347355135-0\|12\|30\|26\|60 4) sensor_1\|1610347355132-0\|11\|25\|28\|50 5) sensor_1\|1610347355128-0\|10\|20\|30\|40

ORDER BY 2

Example

명령>	*select distinct key from stream.sensor order by key desc;** opcode
결과>	0) key 1) sensor_3 2) sensor_2 3) sensor_1
명령>	*select from stream.sensor* order by key, id desc;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib\|flag 1) sensor_1\|1610347355141-0\|14\|40\|22\|30\|(nil) 2) sensor_1\|1610347355138-0\|13\|35\|24\|20\|(nil) 3) sensor_1\|1610347355135-0\|12\|30\|26\|60\|(nil) 중간 생략 13) sensor_3\|1610347355168-0\|30\|(nil)\|(nil)\|(nil)\|(nil) 14) sensor_3\|1610347355164-0\|50\|(nil)\|(nil)\|(nil)\|(nil) 15) sensor_3\|1610347355160-0\|10\|(nil)\|(nil)\|(nil)\|(nil)

ORDER BY NULLS FIRST/LAST

Example

명령>	*select from stream.sensor_2 order by pres;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355154-0\|128.1\|51.2\|(nil)\|BB2 2) sensor_2\|1610347355157-0\|117.4\|46.7\|(nil)\|BB3 3) sensor_2\|1610347355146-0\|123.4\|50.4\|148.6\|AA2 4) sensor_2\|1610347355150-0\|119.8\|48.1\|149.2\|BB1 5) sensor_2\|1610347355144-0\|124.7\|46.8\|150.4\|AA1
명령>	*select from stream.sensor_2 order by pres nulls last;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355146-0\|123.4\|50.4\|148.6\|AA2 2) sensor_2\|1610347355150-0\|119.8\|48.1\|149.2\|BB1 3) sensor_2\|1610347355144-0\|124.7\|46.8\|150.4\|AA1 4) sensor_2\|1610347355154-0\|128.1\|51.2\|(nil)\|BB2 5) sensor_2\|1610347355157-0\|117.4\|46.7\|(nil)\|BB3

LIMIT

출력 행 수를 제한한다.

Example

명령>	*select from stream.sensor_1 limit 3;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355128-0\|10\|20\|30\|40 2) sensor_1\|1610347355132-0\|11\|25\|28\|50 3) sensor_1\|1610347355135-0\|12\|30\|26\|60
명령>	*select from stream.sensor_1 limit 3, 2;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355138-0\|13\|35\|24\|20 2) sensor_1\|1610347355141-0\|14\|40\|22\|30

FUNCTIONS

COUNT(), LEN()

개수를 조회한다. len()은 value(값)의 개수를 구합니다.

Example

명령>	*select count() from stream.sensor_1;** opcode
결과>	0) count(*) 1) 5
명령>	*select count(key) from stream.sensor;** opcode
결과>	0) count(key) 1) 3
명령>	select key, len(key) from stream.*;
결과>	0) key\|len(key) 1) mys1\|3 2) sensor_1\|5 3) sensor_2\|5 4) sensor_3\|5
명령>	select key, count() from stream. group by key;
결과>	0) key\|count(*) 1) mys1\|3 2) sensor_1\|5 3) sensor_2\|5 4) sensor_3\|5

MAX(), MIN()

최댓값, 최솟값을 조회한다.

Example

명령>	select min(temp), max(temp) from stream.sensor_1; opcode
결과>	0) min(temp)\|max(temp) 1) 10\|14
명령>	select key,id,min(temp) from stream.sensor_1; opcode
결과>	0) key\|id\|min(temp) 1) sensor_1\|1610347355128-0\|10
명령>	*select distinct key,valcnt(key) from stream.;** opcode
결과>	0) key\|valcnt(key) 1) mys1\|3 2) sensor_1\|5 3) sensor_2\|5 4) sensor_3\|5

숫자 함수

SUM(), AVG()

합계, 평균을 구한다.

Example

명령>	select sum(temp), avg(temp) from stream.sensor_1; opcode
결과>	0) sum(temp)\|avg(temp) 1) 60\|12.0

WHERE 조건

비교: <, <=

키 비교

Example

명령>	*select from stream.sensor* where key < 'sensor_2';** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib\|flag 1) sensor_1\|1610347355128-0\|10\|20\|30\|40\|(nil) 2) sensor_1\|1610347355132-0\|11\|25\|28\|50\|(nil) 3) sensor_1\|1610347355135-0\|12\|30\|26\|60\|(nil) 4) sensor_1\|1610347355138-0\|13\|35\|24\|20\|(nil) 5) sensor_1\|1610347355141-0\|14\|40\|22\|30\|(nil)
명령>	*select from stream.sensor* where key <= 'sensor_2';** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib\|flag 1) sensor_1\|1610347355128-0\|10\|20\|30\|40\|(nil) 2) sensor_1\|1610347355132-0\|11\|25\|28\|50\|(nil) 3) sensor_1\|1610347355135-0\|12\|30\|26\|60\|(nil) 4) sensor_1\|1610347355138-0\|13\|35\|24\|20\|(nil) 5) sensor_1\|1610347355141-0\|14\|40\|22\|30\|(nil) 6) sensor_2\|1610347355144-0\|124.7\|46.8\|150.4\|(nil)\|AA1 7) sensor_2\|1610347355146-0\|123.4\|50.4\|148.6\|(nil)\|AA2 8) sensor_2\|1610347355150-0\|119.8\|48.1\|149.2\|(nil)\|BB1 9) sensor_2\|1610347355154-0\|128.1\|51.2\|(nil)\|(nil)\|BB2 10) sensor_2\|1610347355157-0\|117.4\|46.7\|(nil)\|(nil)\|BB3

비교: >, >=

Example

명령>	*select from stream.sensor* where key > 'sensor_2';** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib\|flag 1) sensor_3\|1610347355160-0\|10\|(nil)\|(nil)\|(nil)\|(nil) 2) sensor_3\|1610347355164-0\|50\|(nil)\|(nil)\|(nil)\|(nil) 3) sensor_3\|1610347355168-0\|30\|(nil)\|(nil)\|(nil)\|(nil) 4) sensor_3\|1610347355171-0\|20\|(nil)\|(nil)\|(nil)\|(nil) 5) sensor_3\|1610347355174-0\|40\|(nil)\|(nil)\|(nil)\|(nil)
명령>	*select from stream.sensor* where key >= 'sensor_2';** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib\|flag 1) sensor_2\|1610347355144-0\|124.7\|46.8\|150.4\|(nil)\|AA1 2) sensor_2\|1610347355146-0\|123.4\|50.4\|148.6\|(nil)\|AA2 3) sensor_2\|1610347355150-0\|119.8\|48.1\|149.2\|(nil)\|BB1 4) sensor_2\|1610347355154-0\|128.1\|51.2\|(nil)\|(nil)\|BB2 5) sensor_2\|1610347355157-0\|117.4\|46.7\|(nil)\|(nil)\|BB3 6) sensor_3\|1610347355160-0\|10\|(nil)\|(nil)\|(nil)\|(nil) 7) sensor_3\|1610347355164-0\|50\|(nil)\|(nil)\|(nil)\|(nil) 8) sensor_3\|1610347355168-0\|30\|(nil)\|(nil)\|(nil)\|(nil) 9) sensor_3\|1610347355171-0\|20\|(nil)\|(nil)\|(nil)\|(nil) 10) sensor_3\|1610347355174-0\|40\|(nil)\|(nil)\|(nil)\|(nil)

비교: !=

Example

명령>

select * from stream.sensor* where key != 'sensor_2'; opcode

결과>

0) key|id|temp|humid|pres|vib|flag
1) sensor_1|1610347355128-0|10|20|30|40|(nil)
2) sensor_1|1610347355132-0|11|25|28|50|(nil)
3) sensor_1|1610347355135-0|12|30|26|60|(nil)
4) sensor_1|1610347355138-0|13|35|24|20|(nil)
5) sensor_1|1610347355141-0|14|40|22|30|(nil)
6) sensor_3|1610347355160-0|10|(nil)|(nil)|(nil)|(nil)
7) sensor_3|1610347355164-0|50|(nil)|(nil)|(nil)|(nil)
8) sensor_3|1610347355168-0|30|(nil)|(nil)|(nil)|(nil)
9) sensor_3|1610347355171-0|20|(nil)|(nil)|(nil)|(nil)
10) sensor_3|1610347355174-0|40|(nil)|(nil)|(nil)|(nil)

값(value) 비교

Example

명령>	*select from stream.sensor_1 where temp = 12;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355135-0\|12\|30\|26\|60
명령>	*select from stream.sensor_1 where temp >= 12;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355135-0\|12\|30\|26\|60 2) sensor_1\|1610347355138-0\|13\|35\|24\|20 3) sensor_1\|1610347355141-0\|14\|40\|22\|30

AND, OR

Example

명령>	*select from stream.sensor_1 where temp >= 12 and humid > 30;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355138-0\|13\|35\|24\|20 2) sensor_1\|1610347355141-0\|14\|40\|22\|30
명령>	*select from stream.sensor_1 where temp >= 12 or humid > 30;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355135-0\|12\|30\|26\|60 2) sensor_1\|1610347355138-0\|13\|35\|24\|20 3) sensor_1\|1610347355141-0\|14\|40\|22\|30

BETWEEN

Example

명령>	*select from stream.sensor_1 where vib between 30 and 50;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355128-0\|10\|20\|30\|40 2) sensor_1\|1610347355132-0\|11\|25\|28\|50 3) sensor_1\|1610347355141-0\|14\|40\|22\|30
명령>	*select from stream.sensor_1 where vib not between 30 and 50;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355135-0\|12\|30\|26\|60 2) sensor_1\|1610347355138-0\|13\|35\|24\|20

IN, NOT IN

Example

명령>	*select from stream.sensor_1 where pres in (24,26,28);** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355132-0\|11\|25\|28\|50 2) sensor_1\|1610347355135-0\|12\|30\|26\|60 3) sensor_1\|1610347355138-0\|13\|35\|24\|20
명령>	*select from stream.sensor_1 where pres not in (24,26,28);** opcode
결과>	0) key\|id\|temp\|humid\|pres\|vib 1) sensor_1\|1610347355128-0\|10\|20\|30\|40 2) sensor_1\|1610347355141-0\|14\|40\|22\|30
명령>	*select from stream.sensor_2 where flag in ('AA2','BB1','BB2');** opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355146-0\|123.4\|50.4\|148.6\|AA2 2) sensor_2\|1610347355150-0\|119.8\|48.1\|149.2\|BB1 3) sensor_2\|1610347355154-0\|128.1\|51.2\|(nil)\|BB2
명령>	*select from stream.sensor_2 where flag not in ('AA2','BB1','BB2');** opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355144-0\|124.7\|46.8\|150.4\|AA1 2) sensor_2\|1610347355157-0\|117.4\|46.7\|(nil)\|BB3

IS NULL, IS NOT NULL

Example

명령>	*select from stream.sensor_2 where pres is null;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355154-0\|128.1\|51.2\|(nil)\|BB2 2) sensor_2\|1610347355157-0\|117.4\|46.7\|(nil)\|BB3
명령>	*select from stream.sensor_2 where pres is not null;** opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355144-0\|124.7\|46.8\|150.4\|AA1 2) sensor_2\|1610347355146-0\|123.4\|50.4\|148.6\|AA2 3) sensor_2\|1610347355150-0\|119.8\|48.1\|149.2\|BB1

GLOB

Example

명령>	*select from stream.sensor_2 where flag glob 'AA';* opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355144-0\|124.7\|46.8\|150.4\|AA1 2) sensor_2\|1610347355146-0\|123.4\|50.4\|148.6\|AA2
명령>	*select from stream.sensor_2 where flag not glob 'AA';* opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355150-0\|119.8\|48.1\|149.2\|BB1 2) sensor_2\|1610347355154-0\|128.1\|51.2\|(nil)\|BB2 3) sensor_2\|1610347355157-0\|117.4\|46.7\|(nil)\|BB3

LIKE

Example

명령>	*select from stream.sensor_2 where flag like 'aa%';** opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355144-0\|124.7\|46.8\|150.4\|AA1 2) sensor_2\|1610347355146-0\|123.4\|50.4\|148.6\|AA2
명령>	*select from stream.sensor_2 where flag not like 'aa%';** opcode
결과>	0) key\|id\|temp\|humid\|pres\|flag 1) sensor_2\|1610347355150-0\|119.8\|48.1\|149.2\|BB1 2) sensor_2\|1610347355154-0\|128.1\|51.2\|(nil)\|BB2 3) sensor_2\|1610347355157-0\|117.4\|46.7\|(nil)\|BB3

GROUP BY

Example

명령>	*select left(flag,2), count() from stream.sensor_2 group by left(flag,2);** opcode
결과>	0) left(flag,2)\|count(*) 1) AA\|2 2) BB\|3

GROUP BY HAVING

Example

명령>	*select left(flag,2), count() from stream.sensor_2 group by left(flag,2) having count() > 2;* opcode
결과>	0) left(flag,2)\|count(*) 1) BB\|3

OPCODE

select * from stream.sensor_1;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666986-0|10|20|30|40 2) sensor_1|1643765666989-0|11|25|28|50 3) sensor_1|1643765666991-0|12|30|26|60 4) sensor_1|1643765666993-0|13|35|24|20 5) sensor_1|1643765666996-0|14|40|22|30 # ------------------------------------------------------- # select * from stream.sensor_1; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:1,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:12,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1;" -> 5 rows # used_memory:1679320, total_diff:24

select * from stream.sensor*;

0) key|id|temp|humid|pres|vib|flag 1) sensor_1|1643765666986-0|10|20|30|40|(nil) 2) sensor_1|1643765666989-0|11|25|28|50|(nil) 3) sensor_1|1643765666991-0|12|30|26|60|(nil) 4) sensor_1|1643765666993-0|13|35|24|20|(nil) 5) sensor_1|1643765666996-0|14|40|22|30|(nil) 6) sensor_2|1643765666998-0|124.7|46.8|150.4|(nil)|AA1 7) sensor_2|1643765667001-0|123.4|50.4|148.6|(nil)|AA2 8) sensor_2|1643765667003-0|119.8|48.1|149.2|(nil)|BB1 9) sensor_2|1643765667005-0|128.1|51.2|(nil)|(nil)|BB2 10) sensor_2|1643765667008-0|117.4|46.7|(nil)|(nil)|BB3 11) sensor_3|1643765667010-0|10|(nil)|(nil)|(nil)|(nil) 12) sensor_3|1643765667013-0|50|(nil)|(nil)|(nil)|(nil) 13) sensor_3|1643765667015-0|30|(nil)|(nil)|(nil)|(nil) 14) sensor_3|1643765667017-0|20|(nil)|(nil)|(nil)|(nil) 15) sensor_3|1643765667020-0|40|(nil)|(nil)|(nil)|(nil) # ------------------------------------------------------- # select * from stream.sensor*; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:1,p2:7,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:6,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:13,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor*;" -> 15 rows # used_memory:1679752, total_diff:24

select * from stream.my*;

0) key|id|temp 1) mys1|1643765667022-0|10 2) mys1|1643765667025-0|50 3) mys1|1643765667027-0|30 # ------------------------------------------------------- # select * from stream.my*; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:1,p2:3,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:9,p3:0,p4type:NOTUSED # sql:"select * from stream.my*;" -> 3 rows # used_memory:1680176, total_diff:24

ORDER BY

select * from stream.sensor_1 order by vib;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666993-0|13|35|24|20 2) sensor_1|1643765666996-0|14|40|22|30 3) sensor_1|1643765666986-0|10|20|30|40 4) sensor_1|1643765666989-0|11|25|28|50 5) sensor_1|1643765666991-0|12|30|26|60 # ------------------------------------------------------- # select * from stream.sensor_1 order by vib; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 3 OP_SorterNext, p1:1,p2:15,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:2,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:3,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:4,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:5,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:0,p3:7,p4type:NOTUSED # opcode:125 OP_SorterData, p1:1,p2:9,p3:2,p4type:NOTUSED # opcode: 35 OP_SorterSort, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode:114 OP_OpenPseudo, p1:2,p2:9,p3:8,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:130 OP_SorterInsert, p1:1,p2:8,p3:1,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:1,p2:6,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:1,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:2,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode:111 OP_SorterOpen, p1:1,p2:8,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:25,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 order by vib;" -> 5 rows # used_memory:1680616, total_diff:24

select * from stream.sensor_1 order by temp desc;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666996-0|14|40|22|30 2) sensor_1|1643765666993-0|13|35|24|20 3) sensor_1|1643765666991-0|12|30|26|60 4) sensor_1|1643765666989-0|11|25|28|50 5) sensor_1|1643765666986-0|10|20|30|40 # ------------------------------------------------------- # select * from stream.sensor_1 order by temp desc; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 3 OP_SorterNext, p1:1,p2:15,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:2,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:0,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:5,p3:7,p4type:NOTUSED # opcode:125 OP_SorterData, p1:1,p2:9,p3:2,p4type:NOTUSED # opcode: 35 OP_SorterSort, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode:114 OP_OpenPseudo, p1:2,p2:9,p3:8,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:130 OP_SorterInsert, p1:1,p2:8,p3:1,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:1,p2:6,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:1,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:2,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode:111 OP_SorterOpen, p1:1,p2:8,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:25,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 order by temp desc;" -> 5 rows # used_memory:1681064, total_diff:24

select distinct key from stream.sensor* order by key desc;

0) key 1) sensor_3 2) sensor_2 3) sensor_1 # ------------------------------------------------------- # select distinct key from stream.sensor* order by key desc; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 4 OP_Prev, p1:3,p2:5,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:1,p2:1,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:1,p2:2,p3:0,p4type:NOTUSED # opcode: 53 OP_Eq, p1:1,p2:-4,p3:2,p4type:COLLSEQ # opcode: 89 OP_Column, p1:3,p2:0,p3:1,p4type:NOTUSED # opcode: 33 OP_Last, p1:3,p2:-3,p3:1,p4type:INT32 # opcode: 96 OP_OpenRead, p1:3,p2:1,p3:0,p4type:INDEX # opcode: 72 OP_Null, p1:1,p2:2,p3:0,p4type:NOTUSED # opcode:173 OP_Noop, p1:1,p2:3,p3:0,p4type:NOTUSED # opcode: 62 OP_Init, p1:0,p2:11,p3:0,p4type:NOTUSED # sql:"select distinct key from stream.sensor* order by key desc;" -> 3 rows # used_memory:1681520, total_diff:24

select * from stream.sensor* order by key, id desc;

0) key|id|temp|humid|pres|vib|flag 1) sensor_1|1643765666996-0|14|40|22|30|(nil) 2) sensor_1|1643765666993-0|13|35|24|20|(nil) 3) sensor_1|1643765666991-0|12|30|26|60|(nil) 4) sensor_1|1643765666989-0|11|25|28|50|(nil) 5) sensor_1|1643765666986-0|10|20|30|40|(nil) 6) sensor_2|1643765667008-0|117.4|46.7|(nil)|(nil)|BB3 7) sensor_2|1643765667005-0|128.1|51.2|(nil)|(nil)|BB2 8) sensor_2|1643765667003-0|119.8|48.1|149.2|(nil)|BB1 9) sensor_2|1643765667001-0|123.4|50.4|148.6|(nil)|AA2 10) sensor_2|1643765666998-0|124.7|46.8|150.4|(nil)|AA1 11) sensor_3|1643765667020-0|40|(nil)|(nil)|(nil)|(nil) 12) sensor_3|1643765667017-0|20|(nil)|(nil)|(nil)|(nil) 13) sensor_3|1643765667015-0|30|(nil)|(nil)|(nil)|(nil) 14) sensor_3|1643765667013-0|50|(nil)|(nil)|(nil)|(nil) 15) sensor_3|1643765667010-0|10|(nil)|(nil)|(nil)|(nil) # ------------------------------------------------------- # select * from stream.sensor* order by key, id desc; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 65 OP_Return, p1:12,p2:0,p3:0,p4type:NOTUSED # opcode: 3 OP_SorterNext, p1:1,p2:28,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:3,p2:7,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:3,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:3,p2:0,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:3,p2:2,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:3,p2:3,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:3,p2:4,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:3,p2:5,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:3,p2:6,p3:9,p4type:NOTUSED # opcode:125 OP_SorterData, p1:1,p2:13,p3:3,p4type:NOTUSED # opcode: 35 OP_SorterSort, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode:114 OP_OpenPseudo, p1:3,p2:13,p3:9,p4type:NOTUSED # opcode: 17 OP_Once, p1:0,p2:27,p3:0,p4type:NOTUSED # opcode: 11 OP_Goto, p1:0,p2:-5,p3:0,p4type:NOTUSED # opcode: 12 OP_Gosub, p1:12,p2:-6,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:2,p2:5,p3:0,p4type:NOTUSED # opcode:130 OP_SorterInsert, p1:1,p2:10,p3:2,p4type:INT32 # opcode: 76 OP_Move, p1:1,p2:11,p3:1,p4type:NOTUSED # opcode:138 OP_ResetSorter, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode: 12 OP_Gosub, p1:12,p2:-6,p3:0,p4type:NOTUSED # opcode: 16 OP_Jump, p1:18,p2:21,p3:18,p4type:NOTUSED # opcode: 86 OP_Compare, p1:11,p2:1,p3:1,p4type:KEYINFO # opcode:113 OP_SequenceTest, p1:1,p2:20,p3:0,p4type:NOTUSED # opcode: 91 OP_MakeRecord, p1:2,p2:7,p3:10,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:0,p3:1,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:6,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:0,p3:3,p4type:NOTUSED # opcode:133 OP_DeferredSeek, p1:2,p2:0,p3:0,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:2,p2:-3,p3:1,p4type:INT32 # opcode: 96 OP_OpenRead, p1:2,p2:1,p3:0,p4type:INDEX # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode:111 OP_SorterOpen, p1:1,p2:7,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:40,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor* order by key, id desc;" -> 15 rows # used_memory:1681968, total_diff:24

select * from stream.sensor_2 order by pres;

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765667005-0|128.1|51.2|(nil)|BB2 2) sensor_2|1643765667008-0|117.4|46.7|(nil)|BB3 3) sensor_2|1643765667001-0|123.4|50.4|148.6|AA2 4) sensor_2|1643765667003-0|119.8|48.1|149.2|BB1 5) sensor_2|1643765666998-0|124.7|46.8|150.4|AA1 # ------------------------------------------------------- # select * from stream.sensor_2 order by pres; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 3 OP_SorterNext, p1:1,p2:15,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:2,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:3,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:4,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:0,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:5,p3:7,p4type:NOTUSED # opcode:125 OP_SorterData, p1:1,p2:9,p3:2,p4type:NOTUSED # opcode: 35 OP_SorterSort, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode:114 OP_OpenPseudo, p1:2,p2:9,p3:8,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:130 OP_SorterInsert, p1:1,p2:8,p3:1,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:1,p2:6,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:1,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:2,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode:111 OP_SorterOpen, p1:1,p2:8,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:25,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 order by pres;" -> 5 rows # used_memory:1682408, total_diff:24

select * from stream.sensor_2 order by pres nulls last;

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765667001-0|123.4|50.4|148.6|AA2 2) sensor_2|1643765667003-0|119.8|48.1|149.2|BB1 3) sensor_2|1643765666998-0|124.7|46.8|150.4|AA1 4) sensor_2|1643765667005-0|128.1|51.2|(nil)|BB2 5) sensor_2|1643765667008-0|117.4|46.7|(nil)|BB3 # ------------------------------------------------------- # select * from stream.sensor_2 order by pres nulls last; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 3 OP_SorterNext, p1:1,p2:15,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:2,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:3,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:4,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:0,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:5,p3:7,p4type:NOTUSED # opcode:125 OP_SorterData, p1:1,p2:9,p3:2,p4type:NOTUSED # opcode: 35 OP_SorterSort, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode:114 OP_OpenPseudo, p1:2,p2:9,p3:8,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:130 OP_SorterInsert, p1:1,p2:8,p3:1,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:1,p2:6,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:1,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:2,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode:111 OP_SorterOpen, p1:1,p2:8,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:25,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 order by pres nulls last;" -> 5 rows # used_memory:1682864, total_diff:24

LIMIT

select * from stream.sensor_1 limit 3;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666986-0|10|20|30|40 2) sensor_1|1643765666989-0|11|25|28|50 3) sensor_1|1643765666991-0|12|30|26|60 # ------------------------------------------------------- # select * from stream.sensor_1 limit 3; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode: 59 OP_DecrJumpZero, p1:1,p2:-2,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:2,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 69 OP_Integer, p1:3,p2:1,p3:0,p4type:NOTUSED # opcode: 62 OP_Init, p1:0,p2:14,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 limit 3;" -> 3 rows # used_memory:1683304, total_diff:24

select * from stream.sensor_1 limit 3, 2;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666993-0|13|35|24|20 2) sensor_1|1643765666996-0|14|40|22|30 # ------------------------------------------------------- # select * from stream.sensor_1 limit 3, 2; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:7,p3:0,p4type:NOTUSED # opcode: 59 OP_DecrJumpZero, p1:1,p2:-2,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:4,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:4,p4type:NOTUSED # opcode: 48 OP_IfPos, p1:2,p2:-4,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode:152 OP_OffsetLimit, p1:1,p2:3,p3:2,p4type:NOTUSED # opcode: 15 OP_MustBeInt, p1:2,p2:0,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:3,p2:2,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:2,p2:1,p3:0,p4type:NOTUSED # opcode: 62 OP_Init, p1:0,p2:18,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 limit 3, 2;" -> 2 rows # used_memory:1683744, total_diff:24

select count(*) from stream.sensor_1;

0) count(*) 1) 5 # ------------------------------------------------------- # select count(*) from stream.sensor_1; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:1,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:1,p2:2,p3:0,p4type:NOTUSED # opcode:116 OP_Close, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode: 92 OP_Count, p1:1,p2:1,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:1,p2:1,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:7,p3:0,p4type:NOTUSED # sql:"select count(*) from stream.sensor_1;" -> 1 rows # used_memory:1684184, total_diff:24

select count(key) from stream.sensor*;

0) count(key) 1) 3 # ------------------------------------------------------- # select count(key) from stream.sensor*; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:4,p2:1,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:1,p2:4,p3:0,p4type:NOTUSED # opcode:157 OP_AggFinal, p1:1,p2:1,p3:0,p4type:FUNCDEF # opcode: 5 OP_Next, p1:1,p2:4,p3:0,p4type:NOTUSED # opcode:154 OP_AggStep, p1:0,p2:3,p3:1,p4type:FUNCDEF # opcode: 89 OP_Column, p1:1,p2:0,p3:3,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:1,p2:-4,p3:3,p4type:INT32 # opcode: 96 OP_OpenRead, p1:1,p2:1,p3:0,p4type:INDEX # opcode: 72 OP_Null, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 62 OP_Init, p1:0,p2:11,p3:0,p4type:NOTUSED # sql:"select count(key) from stream.sensor*;" -> 1 rows # used_memory:1684624, total_diff:24

select min(temp), max(temp) from stream.sensor_1;

0) min(temp)|max(temp) 1) 10|14 # ------------------------------------------------------- # select min(temp), max(temp) from stream.sensor_1; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:5,p2:2,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:1,p2:5,p3:1,p4type:NOTUSED # opcode:157 OP_AggFinal, p1:2,p2:1,p3:0,p4type:FUNCDEF # opcode:157 OP_AggFinal, p1:1,p2:1,p3:0,p4type:FUNCDEF # opcode: 5 OP_Next, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:154 OP_AggStep, p1:0,p2:4,p3:2,p4type:FUNCDEF # opcode: 81 OP_CollSeq, p1:0,p2:0,p3:0,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode:154 OP_AggStep, p1:0,p2:4,p3:1,p4type:FUNCDEF # opcode: 81 OP_CollSeq, p1:0,p2:0,p3:0,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-4,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 72 OP_Null, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 62 OP_Init, p1:0,p2:16,p3:0,p4type:NOTUSED # sql:"select min(temp), max(temp) from stream.sensor_1;" -> 1 rows # used_memory:1685072, total_diff:24

select key,id,min(temp) from stream.sensor_1;

0) key|id|min(temp) 1) sensor_1|1643765666986-0|10 # ------------------------------------------------------- # select key,id,min(temp) from stream.sensor_1; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:7,p2:3,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:1,p2:7,p3:2,p4type:NOTUSED # opcode:157 OP_AggFinal, p1:3,p2:1,p3:0,p4type:FUNCDEF # opcode: 5 OP_Next, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:1,p4type:NOTUSED # opcode: 18 OP_If, p1:6,p2:10,p3:0,p4type:NOTUSED # opcode:154 OP_AggStep, p1:0,p2:5,p3:3,p4type:FUNCDEF # opcode: 81 OP_CollSeq, p1:6,p2:0,p3:0,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:2,p3:5,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-4,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 72 OP_Null, p1:0,p2:1,p3:4,p4type:NOTUSED # opcode: 62 OP_Init, p1:0,p2:15,p3:0,p4type:NOTUSED # sql:"select key,id,min(temp) from stream.sensor_1;" -> 1 rows # used_memory:1685520, total_diff:24

select distinct key,valcnt(key) from stream.*;

0) key|valcnt(key) 1) mys1|3 2) sensor_1|5 3) sensor_2|5 4) sensor_3|5 # ------------------------------------------------------- # select distinct key,valcnt(key) from stream.*; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:2,p2:4,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:1,p2:2,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:3,p3:1,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:1,p2:2,p3:3,p4type:NOTUSED # opcode: 30 OP_Found, p1:1,p2:-4,p3:1,p4type:INT32 # opcode: 64 OP_Function, p1:0,p2:3,p3:2,p4type:FUNCCTX # opcode: 89 OP_Column, p1:2,p2:0,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:0,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:2,p2:-3,p3:1,p4type:INT32 # opcode: 96 OP_OpenRead, p1:2,p2:1,p3:0,p4type:INDEX # opcode:100 OP_OpenEphemeral, p1:1,p2:0,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:13,p3:0,p4type:NOTUSED # sql:"select distinct key,valcnt(key) from stream.*;" -> 4 rows # used_memory:1685968, total_diff:24

select sum(temp), avg(temp) from stream.sensor_1;

0) sum(temp)|avg(temp) 1) 60|12.0 # ------------------------------------------------------- # select sum(temp), avg(temp) from stream.sensor_1; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:5,p2:2,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:1,p2:5,p3:1,p4type:NOTUSED # opcode:157 OP_AggFinal, p1:2,p2:1,p3:0,p4type:FUNCDEF # opcode:157 OP_AggFinal, p1:1,p2:1,p3:0,p4type:FUNCDEF # opcode: 5 OP_Next, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:154 OP_AggStep, p1:0,p2:4,p3:2,p4type:FUNCDEF # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode:154 OP_AggStep, p1:0,p2:4,p3:1,p4type:FUNCDEF # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-4,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 72 OP_Null, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 62 OP_Init, p1:0,p2:14,p3:0,p4type:NOTUSED # sql:"select sum(temp), avg(temp) from stream.sensor_1;" -> 1 rows # used_memory:1686416, total_diff:24

WHERE

select * from stream.sensor* where key < 'sensor_2';

0) key|id|temp|humid|pres|vib|flag 1) sensor_1|1643765666986-0|10|20|30|40|(nil) 2) sensor_1|1643765666989-0|11|25|28|50|(nil) 3) sensor_1|1643765666991-0|12|30|26|60|(nil) 4) sensor_1|1643765666993-0|13|35|24|20|(nil) 5) sensor_1|1643765666996-0|14|40|22|30|(nil) # ------------------------------------------------------- # select * from stream.sensor* where key < 'sensor_2'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:1,p2:6,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:7,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:6,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:1,p2:0,p3:2,p4type:NOTUSED # opcode:133 OP_DeferredSeek, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode: 41 OP_IdxGE, p1:1,p2:-3,p3:1,p4type:INT32 # opcode:115 OP_String8, p1:0,p2:1,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 25 OP_SeekGT, p1:1,p2:-3,p3:1,p4type:INT32 # opcode: 72 OP_Null, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:1,p2:1,p3:0,p4type:INDEX # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:18,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor* where key < 'sensor_2';" -> 5 rows # used_memory:1686864, total_diff:24

select * from stream.sensor* where key <= 'sensor_2';

0) key|id|temp|humid|pres|vib|flag 1) sensor_1|1643765666986-0|10|20|30|40|(nil) 2) sensor_1|1643765666989-0|11|25|28|50|(nil) 3) sensor_1|1643765666991-0|12|30|26|60|(nil) 4) sensor_1|1643765666993-0|13|35|24|20|(nil) 5) sensor_1|1643765666996-0|14|40|22|30|(nil) 6) sensor_2|1643765666998-0|124.7|46.8|150.4|(nil)|AA1 7) sensor_2|1643765667001-0|123.4|50.4|148.6|(nil)|AA2 8) sensor_2|1643765667003-0|119.8|48.1|149.2|(nil)|BB1 9) sensor_2|1643765667005-0|128.1|51.2|(nil)|(nil)|BB2 10) sensor_2|1643765667008-0|117.4|46.7|(nil)|(nil)|BB3 # ------------------------------------------------------- # select * from stream.sensor* where key <= 'sensor_2'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:1,p2:6,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:7,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:6,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:1,p2:0,p3:2,p4type:NOTUSED # opcode:133 OP_DeferredSeek, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode: 39 OP_IdxGT, p1:1,p2:-3,p3:1,p4type:INT32 # opcode:115 OP_String8, p1:0,p2:1,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 25 OP_SeekGT, p1:1,p2:-3,p3:1,p4type:INT32 # opcode: 72 OP_Null, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:1,p2:1,p3:0,p4type:INDEX # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:18,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor* where key <= 'sensor_2';" -> 10 rows # used_memory:1687320, total_diff:24

select * from stream.sensor* where key > 'sensor_2';

0) key|id|temp|humid|pres|vib|flag 1) sensor_3|1643765667010-0|10|(nil)|(nil)|(nil)|(nil) 2) sensor_3|1643765667013-0|50|(nil)|(nil)|(nil)|(nil) 3) sensor_3|1643765667015-0|30|(nil)|(nil)|(nil)|(nil) 4) sensor_3|1643765667017-0|20|(nil)|(nil)|(nil)|(nil) 5) sensor_3|1643765667020-0|40|(nil)|(nil)|(nil)|(nil) # ------------------------------------------------------- # select * from stream.sensor* where key > 'sensor_2'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:1,p2:5,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:7,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:6,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:1,p2:0,p3:2,p4type:NOTUSED # opcode:133 OP_DeferredSeek, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode: 25 OP_SeekGT, p1:1,p2:-3,p3:1,p4type:INT32 # opcode:115 OP_String8, p1:0,p2:1,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 96 OP_OpenRead, p1:1,p2:1,p3:0,p4type:INDEX # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:16,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor* where key > 'sensor_2';" -> 5 rows # used_memory:1687768, total_diff:24

select * from stream.sensor* where key >= 'sensor_2';

0) key|id|temp|humid|pres|vib|flag 1) sensor_2|1643765666998-0|124.7|46.8|150.4|(nil)|AA1 2) sensor_2|1643765667001-0|123.4|50.4|148.6|(nil)|AA2 3) sensor_2|1643765667003-0|119.8|48.1|149.2|(nil)|BB1 4) sensor_2|1643765667005-0|128.1|51.2|(nil)|(nil)|BB2 5) sensor_2|1643765667008-0|117.4|46.7|(nil)|(nil)|BB3 6) sensor_3|1643765667010-0|10|(nil)|(nil)|(nil)|(nil) 7) sensor_3|1643765667013-0|50|(nil)|(nil)|(nil)|(nil) 8) sensor_3|1643765667015-0|30|(nil)|(nil)|(nil)|(nil) 9) sensor_3|1643765667017-0|20|(nil)|(nil)|(nil)|(nil) 10) sensor_3|1643765667020-0|40|(nil)|(nil)|(nil)|(nil) # ------------------------------------------------------- # select * from stream.sensor* where key >= 'sensor_2'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:1,p2:5,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:7,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:6,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:1,p2:0,p3:2,p4type:NOTUSED # opcode:133 OP_DeferredSeek, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode: 24 OP_SeekGE, p1:1,p2:-3,p3:1,p4type:INT32 # opcode:115 OP_String8, p1:0,p2:1,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 96 OP_OpenRead, p1:1,p2:1,p3:0,p4type:INDEX # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:16,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor* where key >= 'sensor_2';" -> 10 rows # used_memory:1688224, total_diff:24

select * from stream.sensor* where key != 'sensor_2';

0) key|id|temp|humid|pres|vib|flag 1) sensor_1|1643765666986-0|10|20|30|40|(nil) 2) sensor_1|1643765666989-0|11|25|28|50|(nil) 3) sensor_1|1643765666991-0|12|30|26|60|(nil) 4) sensor_1|1643765666993-0|13|35|24|20|(nil) 5) sensor_1|1643765666996-0|14|40|22|30|(nil) 6) sensor_3|1643765667010-0|10|(nil)|(nil)|(nil)|(nil) 7) sensor_3|1643765667013-0|50|(nil)|(nil)|(nil)|(nil) 8) sensor_3|1643765667015-0|30|(nil)|(nil)|(nil)|(nil) 9) sensor_3|1643765667017-0|20|(nil)|(nil)|(nil)|(nil) 10) sensor_3|1643765667020-0|40|(nil)|(nil)|(nil)|(nil) # ------------------------------------------------------- # select * from stream.sensor* where key != 'sensor_2'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:3,p2:7,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:6,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:3,p4type:NOTUSED # opcode: 53 OP_Eq, p1:2,p2:-4,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:0,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:15,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor* where key != 'sensor_2';" -> 10 rows # used_memory:1688680, total_diff:24

select * from stream.sensor_1 where temp = 12;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666991-0|12|30|26|60 # ------------------------------------------------------- # select * from stream.sensor_1 where temp = 12; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:12,p2:2,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:3,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:3,p4type:NOTUSED # opcode: 52 OP_Ne, p1:2,p2:-4,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:2,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:14,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 where temp = 12;" -> 1 rows # used_memory:1689128, total_diff:24

select * from stream.sensor_1 where temp >= 12;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666991-0|12|30|26|60 2) sensor_1|1643765666993-0|13|35|24|20 3) sensor_1|1643765666996-0|14|40|22|30 # ------------------------------------------------------- # select * from stream.sensor_1 where temp >= 12; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:12,p2:2,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:3,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:3,p4type:NOTUSED # opcode: 56 OP_Lt, p1:2,p2:-4,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:2,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:14,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 where temp >= 12;" -> 3 rows # used_memory:1689576, total_diff:24

select * from stream.sensor_1 where temp >= 12 and humid > 30;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666993-0|13|35|24|20 2) sensor_1|1643765666996-0|14|40|22|30 # ------------------------------------------------------- # select * from stream.sensor_1 where temp >= 12 and humid > 30; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:30,p2:3,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:12,p2:2,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:4,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:4,p4type:NOTUSED # opcode: 55 OP_Le, p1:3,p2:-4,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:3,p3:1,p4type:NOTUSED # opcode: 56 OP_Lt, p1:2,p2:-4,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:2,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:16,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 where temp >= 12 and humid > 30;" -> 2 rows # used_memory:1690048, total_diff:24

select * from stream.sensor_1 where temp >= 12 or humid > 30;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666991-0|12|30|26|60 2) sensor_1|1643765666993-0|13|35|24|20 3) sensor_1|1643765666996-0|14|40|22|30 # ------------------------------------------------------- # select * from stream.sensor_1 where temp >= 12 or humid > 30; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:30,p2:3,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:12,p2:2,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:4,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:4,p4type:NOTUSED # opcode: 55 OP_Le, p1:3,p2:-4,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:3,p3:1,p4type:NOTUSED # opcode: 57 OP_Ge, p1:2,p2:-5,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:2,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:16,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 where temp >= 12 or humid > 30;" -> 3 rows # used_memory:1690520, total_diff:24

BETWEEN

select * from stream.sensor_1 where vib between 30 and 50;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666986-0|10|20|30|40 2) sensor_1|1643765666989-0|11|25|28|50 3) sensor_1|1643765666996-0|14|40|22|30 # ------------------------------------------------------- # select * from stream.sensor_1 where vib between 30 and 50; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:50,p2:4,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:30,p2:3,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:5,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:10,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:5,p4type:NOTUSED # opcode: 54 OP_Gt, p1:4,p2:-4,p3:1,p4type:COLLSEQ # opcode: 56 OP_Lt, p1:3,p2:-4,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:5,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:15,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 where vib between 30 and 50;" -> 3 rows # used_memory:1690976, total_diff:24

select * from stream.sensor_1 where vib not between 30 and 50;

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666991-0|12|30|26|60 2) sensor_1|1643765666993-0|13|35|24|20 # ------------------------------------------------------- # select * from stream.sensor_1 where vib not between 30 and 50; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:50,p2:4,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:30,p2:3,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:5,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:10,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:5,p4type:NOTUSED # opcode: 55 OP_Le, p1:4,p2:-4,p3:1,p4type:COLLSEQ # opcode: 56 OP_Lt, p1:3,p2:-5,p3:1,p4type:COLLSEQ # opcode: 89 OP_Column, p1:0,p2:5,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:15,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 where vib not between 30 and 50;" -> 2 rows # used_memory:1691448, total_diff:24

IN

select * from stream.sensor_1 where pres in (24,26,28);

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666989-0|11|25|28|50 2) sensor_1|1643765666991-0|12|30|26|60 3) sensor_1|1643765666993-0|13|35|24|20 # ------------------------------------------------------- # select * from stream.sensor_1 where pres in (24,26,28); # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:5,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:10,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:5,p4type:NOTUSED # opcode: 29 OP_NotFound, p1:1,p2:-4,p3:4,p4type:INT32 # opcode: 90 OP_Affinity, p1:4,p2:1,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 50 OP_IsNull, p1:4,p2:-4,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:4,p4type:NOTUSED # opcode: 65 OP_Return, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:3,p3:2,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:2,p2:1,p3:3,p4type:FREE_IF_LE/DYNAMIC # opcode: 69 OP_Integer, p1:28,p2:2,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:3,p3:2,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:2,p2:1,p3:3,p4type:FREE_IF_LE/DYNAMIC # opcode: 69 OP_Integer, p1:26,p2:2,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:3,p3:2,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:2,p2:1,p3:3,p4type:FREE_IF_LE/DYNAMIC # opcode: 69 OP_Integer, p1:24,p2:2,p3:0,p4type:NOTUSED # opcode:100 OP_OpenEphemeral, p1:1,p2:1,p3:0,p4type:KEYINFO # opcode: 17 OP_Once, p1:0,p2:15,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:15,p2:1,p3:0,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:29,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 where pres in (24,26,28);" -> 3 rows # used_memory:1691904, total_diff:24

select * from stream.sensor_1 where pres not in (24,26,28);

0) key|id|temp|humid|pres|vib 1) sensor_1|1643765666986-0|10|20|30|40 2) sensor_1|1643765666996-0|14|40|22|30 # ------------------------------------------------------- # select * from stream.sensor_1 where pres not in (24,26,28); # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:7,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:12,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:11,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:10,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:7,p4type:NOTUSED # opcode: 11 OP_Goto, p1:0,p2:-4,p3:0,p4type:NOTUSED # opcode: 11 OP_Goto, p1:0,p2:-4,p3:0,p4type:NOTUSED # opcode: 52 OP_Ne, p1:5,p2:-5,p3:6,p4type:COLLSEQ # opcode: 89 OP_Column, p1:1,p2:0,p3:6,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode: 51 OP_NotNull, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode: 30 OP_Found, p1:1,p2:28,p3:5,p4type:INT32 # opcode: 90 OP_Affinity, p1:5,p2:1,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 50 OP_IsNull, p1:5,p2:-6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:1,p2:0,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:1,p2:19,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 65 OP_Return, p1:2,p2:0,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:4,p3:3,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:3,p2:1,p3:4,p4type:FREE_IF_LE/DYNAMIC # opcode: 69 OP_Integer, p1:28,p2:3,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:4,p3:3,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:3,p2:1,p3:4,p4type:FREE_IF_LE/DYNAMIC # opcode: 69 OP_Integer, p1:26,p2:3,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:4,p3:3,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:3,p2:1,p3:4,p4type:FREE_IF_LE/DYNAMIC # opcode: 69 OP_Integer, p1:24,p2:3,p3:0,p4type:NOTUSED # opcode:100 OP_OpenEphemeral, p1:1,p2:1,p3:0,p4type:KEYINFO # opcode: 17 OP_Once, p1:0,p2:15,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:15,p2:2,p3:0,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:38,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_1 where pres not in (24,26,28);" -> 2 rows # used_memory:1692360, total_diff:24

select

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765667001-0|123.4|50.4|148.6|AA2 2) sensor_2|1643765667003-0|119.8|48.1|149.2|BB1 3) sensor_2|1643765667005-0|128.1|51.2|(nil)|BB2 # ------------------------------------------------------- # select * from stream.sensor_2 where flag in ('AA2','BB1','BB2'); # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:5,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:10,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:5,p4type:NOTUSED # opcode: 29 OP_NotFound, p1:1,p2:-4,p3:4,p4type:INT32 # opcode: 90 OP_Affinity, p1:4,p2:1,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 50 OP_IsNull, p1:4,p2:-4,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:4,p4type:NOTUSED # opcode: 65 OP_Return, p1:1,p2:0,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:3,p3:2,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:2,p2:1,p3:3,p4type:FREE_IF_LE/DYNAMIC # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode:131 OP_IdxInsert, p1:1,p2:3,p3:2,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:2,p2:1,p3:3,p4type:FREE_IF_LE/DYNAMIC # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode:131 OP_IdxInsert, p1:1,p2:3,p3:2,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:2,p2:1,p3:3,p4type:FREE_IF_LE/DYNAMIC # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode:100 OP_OpenEphemeral, p1:1,p2:1,p3:0,p4type:KEYINFO # opcode: 17 OP_Once, p1:0,p2:15,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:15,p2:1,p3:0,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:29,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 where flag in ('AA2','BB1','BB2');" -> 3 rows # used_memory:1692832, total_diff:24

select * from stream.sensor_2 where flag not in ('AA2','BB1','BB2');

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765666998-0|124.7|46.8|150.4|AA1 2) sensor_2|1643765667008-0|117.4|46.7|(nil)|BB3 # ------------------------------------------------------- # select * from stream.sensor_2 where flag not in ('AA2','BB1','BB2'); # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:7,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:12,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:11,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:10,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:7,p4type:NOTUSED # opcode: 11 OP_Goto, p1:0,p2:-4,p3:0,p4type:NOTUSED # opcode: 11 OP_Goto, p1:0,p2:-4,p3:0,p4type:NOTUSED # opcode: 52 OP_Ne, p1:5,p2:-5,p3:6,p4type:COLLSEQ # opcode: 89 OP_Column, p1:1,p2:0,p3:6,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode: 51 OP_NotNull, p1:1,p2:-5,p3:0,p4type:NOTUSED # opcode: 30 OP_Found, p1:1,p2:28,p3:5,p4type:INT32 # opcode: 90 OP_Affinity, p1:5,p2:1,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 50 OP_IsNull, p1:5,p2:-6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:1,p2:0,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:1,p2:19,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 65 OP_Return, p1:2,p2:0,p3:0,p4type:NOTUSED # opcode:131 OP_IdxInsert, p1:1,p2:4,p3:3,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:3,p2:1,p3:4,p4type:FREE_IF_LE/DYNAMIC # opcode:115 OP_String8, p1:0,p2:3,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode:131 OP_IdxInsert, p1:1,p2:4,p3:3,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:3,p2:1,p3:4,p4type:FREE_IF_LE/DYNAMIC # opcode:115 OP_String8, p1:0,p2:3,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode:131 OP_IdxInsert, p1:1,p2:4,p3:3,p4type:INT32 # opcode: 91 OP_MakeRecord, p1:3,p2:1,p3:4,p4type:FREE_IF_LE/DYNAMIC # opcode:115 OP_String8, p1:0,p2:3,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode:100 OP_OpenEphemeral, p1:1,p2:1,p3:0,p4type:KEYINFO # opcode: 17 OP_Once, p1:0,p2:15,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:15,p2:2,p3:0,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:38,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 where flag not in ('AA2','BB1','BB2');" -> 2 rows # used_memory:1693304, total_diff:24

IS NULL, IS NOT NULL

select * from stream.sensor_2 where pres is null;

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765667005-0|128.1|51.2|(nil)|BB2 2) sensor_2|1643765667008-0|117.4|46.7|(nil)|BB3 # ------------------------------------------------------- # select * from stream.sensor_2 where pres is null; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:2,p4type:NOTUSED # opcode: 51 OP_NotNull, p1:1,p2:-4,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:14,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 where pres is null;" -> 2 rows # used_memory:1693752, total_diff:24

select * from stream.sensor_2 where pres is not null;

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765666998-0|124.7|46.8|150.4|AA1 2) sensor_2|1643765667001-0|123.4|50.4|148.6|AA2 3) sensor_2|1643765667003-0|119.8|48.1|149.2|BB1 # ------------------------------------------------------- # select * from stream.sensor_2 where pres is not null; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:2,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:4,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:3,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:2,p4type:NOTUSED # opcode: 50 OP_IsNull, p1:1,p2:-4,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:1,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:14,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 where pres is not null;" -> 3 rows # used_memory:1694208, total_diff:24

GLOB

select * from stream.sensor_2 where flag glob 'AA*';

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765666998-0|124.7|46.8|150.4|AA1 2) sensor_2|1643765667001-0|123.4|50.4|148.6|AA2 # ------------------------------------------------------- # select * from stream.sensor_2 where flag glob 'AA*'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:4,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:4,p4type:NOTUSED # opcode: 20 OP_IfNot, p1:1,p2:-4,p3:1,p4type:NOTUSED # opcode: 64 OP_Function, p1:1,p2:2,p3:1,p4type:FUNCCTX # opcode: 89 OP_Column, p1:0,p2:5,p3:3,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:15,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 where flag glob 'AA*';" -> 2 rows # used_memory:1694656, total_diff:24

select * from stream.sensor_2 where flag not glob 'AA*';

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765667003-0|119.8|48.1|149.2|BB1 2) sensor_2|1643765667005-0|128.1|51.2|(nil)|BB2 3) sensor_2|1643765667008-0|117.4|46.7|(nil)|BB3 # ------------------------------------------------------- # select * from stream.sensor_2 where flag not glob 'AA*'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:4,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:4,p4type:NOTUSED # opcode: 18 OP_If, p1:1,p2:-4,p3:1,p4type:NOTUSED # opcode: 64 OP_Function, p1:1,p2:2,p3:1,p4type:FUNCCTX # opcode: 89 OP_Column, p1:0,p2:5,p3:3,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:15,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 where flag not glob 'AA*';" -> 3 rows # used_memory:1695112, total_diff:24

LIKE

select * from stream.sensor_2 where flag like 'aa%';

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765666998-0|124.7|46.8|150.4|AA1 2) sensor_2|1643765667001-0|123.4|50.4|148.6|AA2 # ------------------------------------------------------- # select * from stream.sensor_2 where flag like 'aa%'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:4,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:4,p4type:NOTUSED # opcode: 20 OP_IfNot, p1:1,p2:-4,p3:1,p4type:NOTUSED # opcode: 64 OP_Function, p1:1,p2:2,p3:1,p4type:FUNCCTX # opcode: 89 OP_Column, p1:0,p2:5,p3:3,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:15,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 where flag like 'aa%';" -> 2 rows # used_memory:1695560, total_diff:24

select * from stream.sensor_2 where flag not like 'aa%';

0) key|id|temp|humid|pres|flag 1) sensor_2|1643765667003-0|119.8|48.1|149.2|BB1 2) sensor_2|1643765667005-0|128.1|51.2|(nil)|BB2 3) sensor_2|1643765667008-0|117.4|46.7|(nil)|BB3 # ------------------------------------------------------- # select * from stream.sensor_2 where flag not like 'aa%'; # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode:115 OP_String8, p1:0,p2:2,p3:0,p4type:FREE_IF_LE/DYNAMIC # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:4,p2:6,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:9,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:4,p3:8,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:3,p3:7,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:2,p3:6,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:1,p3:5,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:0,p3:4,p4type:NOTUSED # opcode: 18 OP_If, p1:1,p2:-4,p3:1,p4type:NOTUSED # opcode: 64 OP_Function, p1:1,p2:2,p3:1,p4type:FUNCCTX # opcode: 89 OP_Column, p1:0,p2:5,p3:3,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-3,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 62 OP_Init, p1:0,p2:15,p3:0,p4type:NOTUSED # sql:"select * from stream.sensor_2 where flag not like 'aa%';" -> 3 rows # used_memory:1696016, total_diff:24

GROUP BY

select left(flag,2), count(*) from stream.sensor_2 group by left(flag,2);

0) left(flag,2)|count(*) 1) AA|2 2) BB|3 # ------------------------------------------------------- # select left(flag,2), count(*) from stream.sensor_2 group by left(flag,2); # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:2,p2:17,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:2,p2:12,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 65 OP_Return, p1:6,p2:0,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 72 OP_Null, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 65 OP_Return, p1:5,p2:0,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:14,p2:2,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:2,p2:15,p3:0,p4type:NOTUSED # opcode: 64 OP_Function, p1:2,p2:16,p3:14,p4type:FUNCCTX # opcode: 77 OP_Copy, p1:1,p2:16,p3:0,p4type:NOTUSED # opcode:157 OP_AggFinal, p1:2,p2:0,p3:0,p4type:FUNCDEF # opcode: 65 OP_Return, p1:5,p2:0,p3:0,p4type:NOTUSED # opcode: 48 OP_IfPos, p1:3,p2:34,p3:0,p4type:NOTUSED # opcode: 65 OP_Return, p1:5,p2:0,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:1,p2:4,p3:0,p4type:NOTUSED # opcode: 11 OP_Goto, p1:0,p2:-2,p3:0,p4type:NOTUSED # opcode: 12 OP_Gosub, p1:5,p2:-3,p3:0,p4type:NOTUSED # opcode: 3 OP_SorterNext, p1:1,p2:15,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:1,p2:3,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:1,p3:1,p4type:NOTUSED # opcode: 18 OP_If, p1:3,p2:26,p3:0,p4type:NOTUSED # opcode:154 OP_AggStep, p1:0,p2:0,p3:2,p4type:FUNCDEF # opcode: 12 OP_Gosub, p1:6,p2:-4,p3:0,p4type:NOTUSED # opcode: 48 OP_IfPos, p1:4,p2:-2,p3:0,p4type:NOTUSED # opcode: 12 OP_Gosub, p1:5,p2:-3,p3:0,p4type:NOTUSED # opcode: 76 OP_Move, p1:8,p2:7,p3:1,p4type:NOTUSED # opcode: 16 OP_Jump, p1:19,p2:23,p3:19,p4type:NOTUSED # opcode: 86 OP_Compare, p1:7,p2:8,p3:1,p4type:KEYINFO # opcode: 89 OP_Column, p1:2,p2:0,p3:8,p4type:NOTUSED # opcode:125 OP_SorterData, p1:1,p2:13,p3:2,p4type:NOTUSED # opcode: 35 OP_SorterSort, p1:1,p2:-2,p3:0,p4type:NOTUSED # opcode:114 OP_OpenPseudo, p1:2,p2:13,p3:2,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:7,p3:0,p4type:NOTUSED # opcode:130 OP_SorterInsert, p1:1,p2:13,p3:0,p4type:NOTUSED # opcode: 91 OP_MakeRecord, p1:9,p2:2,p3:13,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:10,p4type:NOTUSED # opcode: 64 OP_Function, p1:2,p2:11,p3:9,p4type:FUNCCTX # opcode: 89 OP_Column, p1:0,p2:5,p3:11,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-6,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 12 OP_Gosub, p1:6,p2:-4,p3:0,p4type:NOTUSED # opcode: 72 OP_Null, p1:0,p2:7,p3:7,p4type:NOTUSED # opcode: 69 OP_Integer, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:111 OP_SorterOpen, p1:1,p2:2,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:44,p3:0,p4type:NOTUSED # sql:"select left(flag,2), count(*) from stream.sensor_2 group by left(flag,2);" -> 2 # used_memory:1696488, total_diff:24

select left(flag,2), count() from stream.sensor_2 group by left(flag,2) having count() > 2;

0) left(flag,2)|count(*) 1) BB|3 # ------------------------------------------------------- # select left(flag,2), count(*) from stream.sensor_2 group by left(flag,2) having count # ------------------------------------------------------- # opcode: 11 OP_Goto, p1:0,p2:1,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:2,p2:19,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:2,p2:15,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:2,p2:12,p3:0,p4type:NOTUSED # opcode: 2 OP_Transaction, p1:0,p2:0,p3:0,p4type:INT32 # opcode: 68 OP_Halt, p1:0,p2:0,p3:0,p4type:NOTUSED # opcode: 65 OP_Return, p1:6,p2:0,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:0,p2:3,p3:0,p4type:NOTUSED # opcode: 72 OP_Null, p1:0,p2:1,p3:2,p4type:NOTUSED # opcode: 65 OP_Return, p1:5,p2:0,p3:0,p4type:NOTUSED # opcode: 80 OP_ResultRow, p1:16,p2:2,p3:0,p4type:NOTUSED # opcode: 77 OP_Copy, p1:2,p2:17,p3:0,p4type:NOTUSED # opcode: 64 OP_Function, p1:2,p2:18,p3:16,p4type:FUNCCTX # opcode: 77 OP_Copy, p1:1,p2:18,p3:0,p4type:NOTUSED # opcode: 55 OP_Le, p1:15,p2:33,p3:2,p4type:NOTUSED # opcode:157 OP_AggFinal, p1:2,p2:0,p3:0,p4type:FUNCDEF # opcode: 65 OP_Return, p1:5,p2:0,p3:0,p4type:NOTUSED # opcode: 48 OP_IfPos, p1:3,p2:34,p3:0,p4type:NOTUSED # opcode: 65 OP_Return, p1:5,p2:0,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:1,p2:4,p3:0,p4type:NOTUSED # opcode: 11 OP_Goto, p1:0,p2:-2,p3:0,p4type:NOTUSED # opcode: 12 OP_Gosub, p1:5,p2:-3,p3:0,p4type:NOTUSED # opcode: 3 OP_SorterNext, p1:1,p2:15,p3:0,p4type:NOTUSED # opcode: 69 OP_Integer, p1:1,p2:3,p3:0,p4type:NOTUSED # opcode: 89 OP_Column, p1:2,p2:1,p3:1,p4type:NOTUSED # opcode: 18 OP_If, p1:3,p2:26,p3:0,p4type:NOTUSED # opcode:154 OP_AggStep, p1:0,p2:0,p3:2,p4type:FUNCDEF # opcode: 12 OP_Gosub, p1:6,p2:-4,p3:0,p4type:NOTUSED # opcode: 48 OP_IfPos, p1:4,p2:-2,p3:0,p4type:NOTUSED # opcode: 12 OP_Gosub, p1:5,p2:-3,p3:0,p4type:NOTUSED # opcode: 76 OP_Move, p1:8,p2:7,p3:1,p4type:NOTUSED # opcode: 16 OP_Jump, p1:19,p2:23,p3:19,p4type:NOTUSED # opcode: 86 OP_Compare, p1:7,p2:8,p3:1,p4type:KEYINFO # opcode: 89 OP_Column, p1:2,p2:0,p3:8,p4type:NOTUSED # opcode:125 OP_SorterData, p1:1,p2:13,p3:2,p4type:NOTUSED # opcode: 35 OP_SorterSort, p1:1,p2:-2,p3:0,p4type:NOTUSED # opcode:114 OP_OpenPseudo, p1:2,p2:13,p3:2,p4type:NOTUSED # opcode: 5 OP_Next, p1:0,p2:7,p3:0,p4type:NOTUSED # opcode:130 OP_SorterInsert, p1:1,p2:13,p3:0,p4type:NOTUSED # opcode: 91 OP_MakeRecord, p1:9,p2:2,p3:13,p4type:NOTUSED # opcode: 89 OP_Column, p1:0,p2:5,p3:10,p4type:NOTUSED # opcode: 64 OP_Function, p1:2,p2:11,p3:9,p4type:FUNCCTX # opcode: 89 OP_Column, p1:0,p2:5,p3:11,p4type:NOTUSED # opcode: 37 OP_Rewind, p1:0,p2:-6,p3:0,p4type:NOTUSED # opcode: 96 OP_OpenRead, p1:0,p2:0,p3:0,p4type:TABLE # opcode: 12 OP_Gosub, p1:6,p2:-4,p3:0,p4type:NOTUSED # opcode: 72 OP_Null, p1:0,p2:7,p3:7,p4type:NOTUSED # opcode: 69 OP_Integer, p1:0,p2:4,p3:0,p4type:NOTUSED # opcode:111 OP_SorterOpen, p1:1,p2:2,p3:0,p4type:KEYINFO # opcode: 62 OP_Init, p1:0,p2:45,p3:0,p4type:NOTUSED # sql:"select left(flag,2), count(*) from stream.sensor_2 group by left(flag,2) having > 1 rows # used_memory:1696992, total_diff:24

<< Select Hash	Select Stream	Select Subquery >>

Email 返事がかかってなれば、メールでお知らせします。