select_stream
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 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 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
답글이 올라오면 이메일로 알려드리겠습니다.