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;

select * from stream.sensor*;

select * from stream.my*;

ORDER BY

select * from stream.sensor_1 order by vib;

select * from stream.sensor_1 order by temp desc;

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

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

select * from stream.sensor_2 order by pres;

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

LIMIT

select * from stream.sensor_1 limit 3;

select * from stream.sensor_1 limit 3, 2;

select count(*) from stream.sensor_1;

select count(key) from stream.sensor*;

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

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

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

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

WHERE

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

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

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

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

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

select * from stream.sensor_1 where temp = 12;

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

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

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

BETWEEN

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

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

IN

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

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

select

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

IS NULL, IS NOT NULL

select * from stream.sensor_2 where pres is null;

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

GLOB

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

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

LIKE

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

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

GROUP BY

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

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


<< Select Hash Select Stream Select Subquery >>

Email 답글이 올라오면 이메일로 알려드리겠습니다.