ReaderMX.
PostgreSQL码农集散地

DuckDB 快速生成海量数据的方法

Pdigoal约 4 分钟

文章开始前推荐2个学习环境: 

1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》

2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、应用等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

常用函数

1、时间戳、日期、时间、间隔:

  • now()

  • current_date

  • current_time

  • to_timestamp(sec) Converts sec since epoch to a timestamp

  • epoch_ms(ms) Converts ms since epoch to a timestamp

  • date_part('epoch' from now())

  • interval '1 day 10 second'

2、uuid

  • uuid()

3、随机数

  • random()

4、随机字符串

  • md5(random()::text)

5、取模

  • abs(mod(100,2))

6、一连串数字

  • range(1,100)

  • generate_series(1,101)

7、一连串时间

  • generate_series(now(), now()+interval '1 year', interval '1 day')

例子

create table tbl (id int8, gid int, info text, u uuid, crt_time timestamp);    
    insert into tbl     
select i, (random()*100)::int, md5(random()::text), uuid(), now()+(i||' second')::interval    
from     
(select #1 as i from generate_series(1,10000)) as t;     
        D select * from tbl limit 10;    
┌────┬─────┬──────────────────────────────────┬──────────────────────────────────────┬─────────────────────────┐    
│ id │ gid │               info               │                  u                   │        crt_time         │    
├────┼─────┼──────────────────────────────────┼──────────────────────────────────────┼─────────────────────────┤    
│ 1  │ 29  │ aca282d34e3158526dd2f404cc2831ed │ 91081814-3547-c1a0-a0cf-fc18e215b185 │ 2022-08-29 10:00:13.784 │    
│ 2  │ 82  │ b1aacf8e7d06f2a0bd9d64d4f83a1a4d │ 98a70fa2-ac4c-cea4-a451-90a4d323add9 │ 2022-08-29 10:00:14.784 │    
│ 3  │ 2   │ a27b2b279a35cb0997cebb364c083d18 │ 808fac68-3540-46b7-b738-a02120ea6e10 │ 2022-08-29 10:00:15.784 │    
│ 4  │ 68  │ 8fc430fd60edbcb03fa32884e1fb75db │ 5a6b67cb-cc49-28b3-b387-d58919e5207f │ 2022-08-29 10:00:16.784 │    
│ 5  │ 17  │ b751693f31ecea36191d7cec22f3b2bd │ 074f4faf-fa48-7882-82bc-8aceffb6d83d │ 2022-08-29 10:00:17.784 │    
│ 6  │ 92  │ 65c208f01b89c236cbbcaf0d9c21164d │ e922f846-d34b-b092-92f9-b9d22f6f0b95 │ 2022-08-29 10:00:18.784 │    
│ 7  │ 27  │ 5a4f8585499305721b050366ed14b4a1 │ 7cb0de25-fc4a-3f8a-8afa-7dbd5b260435 │ 2022-08-29 10:00:19.784 │    
│ 8  │ 53  │ 4d35ac83efc8eef92b199ccf936464b8 │ 1d7c11ef-fd41-13b1-b15f-1294bfd18d16 │ 2022-08-29 10:00:20.784 │    
│ 9  │ 33  │ 0c8a7245d7470d28b40ff911c945489d │ 4fdbb4ed-6d43-5aa6-a662-f5153c33c0c9 │ 2022-08-29 10:00:21.784 │    
│ 10 │ 54  │ 158d92da65f7fc763385a75215662d76 │ 7d88f527-6543-c9a9-a9dd-f29aee093353 │ 2022-08-29 10:00:22.784 │    
└────┴─────┴──────────────────────────────────┴──────────────────────────────────────┴─────────────────────────┘
drop table tbl;    
create table tbl (id int8, gid int, info text, u uuid, crt_time timestamp);    
        insert into tbl     
select i, x, md5(random()::text), uuid(), now()+(i||' second')::interval    
from     
(select #1 as i from generate_series(1,10000)) as t1,    
(select #1 as x from generate_series(1,100)) as t2;     
        D select * from tbl limit 10;    
┌────┬─────┬──────────────────────────────────┬──────────────────────────────────────┬─────────────────────────┐    
│ id │ gid │               info               │                  u                   │        crt_time         │    
├────┼─────┼──────────────────────────────────┼──────────────────────────────────────┼─────────────────────────┤    
│ 1  │ 1   │ f070245a35463499dc737af62749adaa │ daa815da-0148-0989-89e9-c025fc1d4fa8 │ 2022-08-29 10:05:01.886 │    
│ 2  │ 1   │ 85c26c037885a18bb0eba309a1a1e8c7 │ 0fc35b25-4b4d-a5b3-b3a2-bfbdb0be6a06 │ 2022-08-29 10:05:02.886 │    
│ 3  │ 1   │ 3e940cf017a1a7bdfc45fd4c68e51db3 │ c24432d6-bf41-ea8b-8bc7-b274a4add7e9 │ 2022-08-29 10:05:03.886 │    
│ 4  │ 1   │ 672e9f47fd6d5f15cdaf5423fa61038d │ 4691e970-804a-ecae-ae4c-0167d4bfe45b │ 2022-08-29 10:05:04.886 │    
│ 5  │ 1   │ e33ffd23b3e1e19f776d5e3cf046547d │ 37706346-ab40-1e9c-9cd6-64f246dcaa25 │ 2022-08-29 10:05:05.886 │    
│ 6  │ 1   │ e62d97ffc45caf7991a82d0ba5c388b5 │ e5da95d2-6a4b-05b9-b92b-fc60567c5943 │ 2022-08-29 10:05:06.886 │    
│ 7  │ 1   │ 76c371c727c1f6b1c48868d19491d724 │ 4bd89f1c-3a41-4aad-ad0a-924287f9106a │ 2022-08-29 10:05:07.886 │    
│ 8  │ 1   │ 584e7f695b0c6dd124240bb9c5264a3f │ 73f6acc7-e942-6fb3-b345-a061f47faf71 │ 2022-08-29 10:05:08.886 │    
│ 9  │ 1   │ fdb84347934e98734668653e931d202e │ 3e747d4c-8444-dfa6-a60d-915fa1e01902 │ 2022-08-29 10:05:09.886 │    
│ 10 │ 1   │ 4e8fd8bc15c0f5a71d03606b1a31208d │ 7085bd66-3a48-2295-95d0-ad35305f8928 │ 2022-08-29 10:05:10.886 │    
└────┴─────┴──────────────────────────────────┴──────────────────────────────────────┴─────────────────────────┘    
  D SUMMARIZE tbl;  
┌─────────────┬─────────────┬──────────────────────────────────────┬──────────────────────────────────────┬───────────────┬────────┬────────────────────┬──────┬──────┬──────┬─────────┬─────────────────┐  
│ column_name │ column_type │                 min                  │                 max                  │ approx_unique │  avg   │        std         │ q25  │ q50  │ q75  │  count  │ null_percentage │  
├─────────────┼─────────────┼──────────────────────────────────────┼──────────────────────────────────────┼───────────────┼────────┼────────────────────┼──────┼──────┼──────┼─────────┼─────────────────┤  
│ id          │ BIGINT      │ 1                                    │ 10000                                │ 10061         │ 5000.5 │ 2886.7527748911193 │ 2501 │ 4998 │ 7459 │ 1000000 │ 0.0%            │  
│ gid         │ INTEGER     │ 1                                    │ 100                                  │ 98            │ 50.5   │ 28.86608448076809  │ 26   │ 51   │ 75   │ 1000000 │ 0.0%            │  
│ info        │ VARCHAR     │ 000008a2679bfaba58aa5895233b5a06     │ ffffef3c1535bdb87106acffb46689e7     │ 974964        │        │                    │      │      │      │ 1000000 │ 0.0%            │  
│ u           │ UUID        │ 0000016b-d944-45b5-b530-eca62e5dd483 │ fffff4fd-954d-b39f-9f59-39833b4d9914 │ 1006600       │        │                    │      │      │      │ 1000000 │ 0.0%            │  
│ crt_time    │ TIMESTAMP   │ 2022-08-29 10:05:01.886              │ 2022-08-29 12:51:40.886              │ 10197         │        │                    │      │      │      │ 1000000 │ 0.0%            │  
└─────────────┴─────────────┴──────────────────────────────────────┴──────────────────────────────────────┴───────────────┴────────┴────────────────────┴──────┴──────┴──────┴─────────┴─────────────────┘

参考

https://duckdb.org/docs/sql/functions/datepart
https://duckdb.org/docs/sql/functions/timestamp
https://duckdb.org/docs/sql/functions/dateformat
https://duckdb.org/docs/sql/functions/date
https://duckdb.org/docs/sql/functions/interval
https://duckdb.org/docs/sql/functions/utility
https://duckdb.org/docs/sql/functions/nested

范围函数

  • range(start, stop, step)

  • range(start, stop)

  • range(stop)

  • generate_series(start, stop, step)

  • generate_series(start, stop)

  • generate_series(stop)

The functions range and generate_series create a list of values in the range between start and stop.
The start parameter is inclusive. start 都包含
For the range function, the stop parameter is exclusive, while for generate_series, it is inclusive. range 包含stop, generate_series 不包含stop
The default value of start is 0 and the default value of step is 1.

range[]    
generate_series[)
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
Image
文章中的参考文档请点击阅读原文获得. 

KEEP THE THREAD GOING

更多来源文章