문서번호 : 11-479312
Document Information
•
최초 작성일 : 2022.08.08
•
최종 수정일 : 2022.08.08
•
이 문서는 아래 버전을 기준으로 작성되었습니다.
◦
SinglestoreDB : 7.8
Goal
임의의 개수의 row 를 생성하는 방법 2가지
•
PostgreSQL 의 generate_series() 와 유사한 기능의 함수 구현
•
CREATE_ARRAY, TABLE 함수를 이용한 One-Line Query
Solution
SingleStoreDB 는 현재(v7.8)까지 Hierarchical Query 및 Recursive CTE(Common Table Expression) 를 지원하고 있지 않으므로 특정 개수의 row 를 임의로 생성하려면 이미 존재하고 있는 큰 테이블에서 row 를 limit 하거나, 작은 테이블을 cross join 후 row_number() 윈도우 함수로 row number 를 추출하고 limit 으로 필요한 만큼의 row 를 가져올 수 밖에 없다.
첫번째 방법은 PostgreSQL 의 generate_series() 함수와 유사한 함수를 생성하여 필요한 만큼의 row 를 생성하는 방법이다. SingleStore 의 TVF(Table Valued Function) 는 리턴 타입이 Table 이므로 이를 이용하여 cross join 등을 사용하지 않고 row 를 생성할 수 있도록 한다.
두번째 방법은 CREATE_ARRAY 함수로 원하는 개수의 Array 를 생성하여 Table 로 전달하여 One-Line Query 로 필요한 Row 를 생성한다.
Solution #1 - TVF
•
Base Table (Reference Table) 및 100,000 rows 생성
create rowstore reference table gen_t ( rn bigint primary key );
with cte as (select * from table([1,2,3,4,5,6,7,8,9,10]))
insert into gen_t
select row_number() over ()
from cte c1, cte c2, cte c3, cte c4, cte c5
limit 100000;
SQL
복사
•
TVF(Table Valued Function) 생성
create or replace function gen_rows(n bigint)
returns table as return
select * from gen_t where rn <= n order by rn;
SQL
복사
•
사용 예제
singlestore> select * from gen_rows(3);
+----+
| rn |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.01 sec)
singlestore> select current_date + interval rn day from gen_rows(5);
+--------------------------------+
| current_date + interval rn day |
+--------------------------------+
| 2022-08-09 |
| 2022-08-10 |
| 2022-08-11 |
| 2022-08-12 |
| 2022-08-13 |
+--------------------------------+
5 rows in set (0.01 sec)
SQL
복사
Solution #2 - One-Line Query
•
CREATE_ARRAY 로 필요한 만큼의 Array 를 생성하여 bigint 타입으로 변환하여 TABLE 에 전달
•
실제 생성된 Row 를 row_number() 함수를 이용하여 컬럼 추출
singlestore> select row_number() over () as rn from table(create_array(5):>array(bigint));
+----+
| rn |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)
SQL
복사
•
TVF 또는 CTE 에서 해당 Query 를 사용하여 보다 편하게 SQL 를 작성할 수 있음
create or replace function new_gen_rows(n bigint)
returns table as return
select row_number() over () as rn from table(create_array(n):>array(bigint));
singlestore> select * from new_gen_rows(3);
+----+
| rn |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.01 sec)
SQL
복사
References
•
User-Defined Table Valued Function
History
일자 | 작성자 | 비고 |
2022/08/08 | jsnoh | 최초 작성 |
