SELECT Stream

SELECT로 Stream 데이터를 조회

테스트 데이터 입력

온도 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

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;
결과> 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*;
결과> 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*;
결과> 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;
결과> 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;
결과> 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;
결과> 0) key
1) sensor_3
2) sensor_2
3) sensor_1
명령>select * from stream.sensor* order by key, id desc;
결과> 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;
결과> 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;
결과> 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;
결과> 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;
결과> 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()

개수를 조회한다.

Example

명령>select count(*) from stream.sensor_1;
결과> 0) count(*)
1) 5
명령>select count(key) from stream.sensor*;
결과> 0) count(key)
1) 3

MAX(), MIN()

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

Example

명령>select min(temp), max(temp) from stream.sensor_1;
결과> 0) min(temp)|max(temp)
1) 10|14
명령>select key,id,min(temp) from stream.sensor_1;
결과> 0) key|id|min(temp)
1) sensor_1|1610347355128-0|10
명령>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

숫자 함수

SUM(), AVG()

합계, 평균을 구한다.

Example

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

WHERE 조건

비교: <, <=

키 비교

Example

명령>select * from stream.sensor* where key < 'sensor_2';
결과> 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';
결과> 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';
결과> 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';
결과> 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';
결과> 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;
결과> 0) key|id|temp|humid|pres|vib
1) sensor_1|1610347355135-0|12|30|26|60
명령>select * from stream.sensor_1 where temp >= 12;
결과> 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;
결과> 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;
결과> 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;
결과> 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;
결과> 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

NOT IN

Example

명령>select * from stream.sensor_1 where pres in (24,26,28);
결과> 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);
결과> 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');
결과> 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');
결과> 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;
결과> 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;
결과> 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

LIKE

Example

명령>select * from stream.sensor_2 where flag like 'aa%';
결과> 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%';
결과> 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

GLOB

Example

명령>select * from stream.sensor_2 where flag glob 'AA*';
결과> 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*';
결과> 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);
결과> 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;
결과> 0) left(flag,2)|count(*)
1) BB|3
<< Select Hash Select Stream Select Subquery >>

Email 답글이 올라오면 이메일로 알려드리겠습니다.
Email redisgate@gmail.com Address Seoul Gangnam-gu Gangnam-daero 342 Yeoksam Bldg 5F Telephone 02.503.2235 Copyright © 2014-2021 redisGate
All right reserved