فرادرستولید مجموعه داده های تصادفی با SQL — راهنمای مقدماتی
آیا تاکنون نیاز داشته‌اید که یک پایگاه داده را با داده‌های تصادفی؛ اما واقعی پر کنید. این داده‌ها با مقاصدی از قبیل تست، نمایش یا تمرین ممکن است مورد استفاده قرار گیرند. این داده‌ها در وضعیت‌های زیادی مورد نیاز هستند و به عنوان یک توسعه‌دهنده ممکن است در موارد متعددی به آن‌ها احتیاج پیدا کنید. در این نوشته، به توضیح روش تولید مجموعه داده های تصادفی با استفاده از SQL می‌پردازیم.
در مسیر توسعه یک اپلیکیشن زمان چندانی طول نمی‌کشد که یکی از افراد تیم نیاز پیدا کند اپلیکیشن را با داده‌های واقعی تست کند. بنابراین باید روشی برای مدیریت و رفع این نیاز بیابیم. یکی از بهترین روش‌ها استفاده از زبان SQL است.
SQL یک زبان جالب و شگفت‌انگیز است و کارهایی که با آن می‌توان به سهولت انجام داد بی‌شمار هستند. البته بدیهی است که کتابخانه‌های زیادی وجود دارند که می‌توان این کار را با آن‌ها اجرا کرد. یکی از بهترین این کتابخانه‌ها faker.js (+) است. بسیاری از مثال‌هایی که در این متن مشاهده خواهید کرد با استفاده از faker.js به کد جاوا اسکریپت تبدیل شده‌اند.
با این وجود هدف این مقاله آن است که نمایش بدهد چه کارهای متنوعی را می‌توان با استفاده از SQL اجرا کرد. پیش از آن که کتابخانه‌هایی مانند faker.js ساخته شوند، SQL وجود داشته و با آن کارهای زیادی اجرا شده است. بنابراین بهتر است تلاش کنیم با یادگیری بهتر آن، مهارت‌های خود را تعمیق ببخشیم.
قراردادها و ابزارها
ما در این نوشته از محیط تست PostgreSQL روی OS X استفاده می‌کنیم و همه نکاتی که در ادامه مطرح شده‌اند، در دیالکت PG نوشته شده‌اند. بسیاری از این نکات را می‌توان روی پلتفرم‌های دیگر پایگاه داده رابطه‌ای یا دیالکت‌های SQL نیز استفاده کرد.
در ادامه متوجه خواهید شد که در این نوشته به طور مکرر از «عبارت‌های جدول مشترک» (Common Table Expressions) یا به اختصار CTE استفاده می‌کنیم. این عبارت‌ها به نام بندهای WITH نیز شناخته می‌شوند. به شدت توصیه می‌کنیم که این عبارت‌ها را بیاموزید و به طور مرتب از آن‌ها استفاده کنید. CTE-ها بسیار قدرتمند هستند و به عنوان بلوک‌های تشکیل‌دهنده منطق پیچیده‌ای محسوب می‌شوند که در نهایت باعث می‌شوند کوئری‌های ما خوانا و قابل نگهداری باشند.
علاوه بر آن بهتر است از سبک‌بندی خاص SQL استفاده کنید که در آن کوئری‌ها به صورت راست-چین و با کلیدواژه‌های حروف بزرگ نوشته می‌شوند. همچنین برای تابع‌های داخلی و غیره از حروف بزرگ استفاده می‌شود.
با این که همه کدهای این راهنما با استفاده از خط فرمان psql برای PostgreSQL ارائه شده است؛ اما استفاده از یک کلاینت GUI که با ترجیح‌های شخصی شما همخوانی داشته باشد، کار را بسیار ساده‌تر خواهد ساخت. ضمناً فراموش نکنید که باید PostgreSQL را نصب کنید.
دقت کنید که گرچه می‌توانید کوئری‌های SQL را با استفاده از ابزارهایی مانند db-fiddle یا sqlfiddle اجرا کنید؛ اما بسیاری از کوئری‌ها در عمل نیازمند ایجاد تابع هستند. متأسفانه تلاش‌های ما برای ساخت تابع با استفاده از این ابزارها ناموفق بوده است.
برای اینکه سریع‌ترین مسیر دستیابی به یک محیط PostgreSQL را روی MacOS داشته باشد، بهتر است از چند ابزاری که در ادامه معرفی کرده‌ایم استفاده کنید:
Postgres.app
Postgres.app (+) اقدام به نصب PostgresSQL به عنوان یک اپلیکیشن «بومی» (Native) می‌کند و آسان‌ترین روش رأی راه‌اندازی و اجرای سریع آن محسوب می‌شود که همزمان از اجرای نسخه‌های متعدد به موازات هم نیز پشتیبانی می‌کند.
Postgres.app همچنین از نوار منو نیز در دسترس شما قرار دارد و می‌توانید به سرعت PostgresSQL را اجرا/متوقف کنید.
Postico
Postico (+) از سوی همان تیم توسعه‌دهنده Postgres.app طراحی شده است و یک UI ساده اما عالی برای PostgresSQL محسوب می‌شود. چند کلاینت گرافیکی مناسب دیگر نیز وجود دارند، اما بسیاری از توسعه‌دهندگان DBVisualizer (+) را ترجیح می‌دهند. با این وجود، Postico یک کلاینت رایگان است که موجب می‌شود راه‌اندازی و اجرای PostgresSQL بسیار آسان شود. با این که نسخه آزمایشی آن محدودیت‌های مختلفی دارد؛ اما خوشبختانه استفاده از نسخه آزمایشی با محدودیت زمانی مواجه نمی‌شود.
اینک که مطالب مقدماتی را مطرح کردیم، آماده هستیم که وارد بحث اصلی خود بشویم.
مقدار تصادفی اولیه
ساده‌ترین بلوک سازنده برای تصادفی سازی در PostgreSQL تابعی به نام random است.
1postgres# SELECT RANDOM();
2
3 random
4-------------------
5 0.249834946822375
6(1 row)بر اساس مستندات PostgreSQL (+)، تابع random یک مقدار تصادفی در بازه زیر بازگشت می‌دهد:
0.0 <= x < 1.0
ما با استفاده از این تابع ساده می‌توانیم یک مقدار تصادفی را در بازه‌های مختلف مانند 1 تا 100 بسازیم.
مقدار تصادفی در بازه‌های دلخواه
1WITH base AS (
2 SELECT 1 AS startingValue,
3 100 AS endingValue
4)
5SELECT (startingValue + (endingValue - startingValue) * RANDOM())::NUMERIC
6 FROM base;
7
8 numeric
9------------------
10 57.9347848887555
11(1 row)عملکرد کد فوق عالی است. برای این که این تابع قابلیت استفاده مجدد برای سناریوهای دیگر نیز داشته باشد آن را به صورت زیر بازنویسی می‌کنیم:
1CREATE FUNCTION randomNumber(startingValue IN NUMERIC, endingValue IN NUMERIC)
2RETURNS NUMERIC AS
3$$
4 SELECT (startingValue + (endingValue - startingValue) * RANDOM())::NUMERIC;
5$$
6LANGUAGE 'sql'
7VOLATILE;در ادامه آن را تست می‌کنیم:
1SELECT randomNumber(1,10),
2 randomNumber(1,100),
3 randomNumber(99,105),
4 randomNumber(99.000,99.999);
5
6 randomnumber | randomnumber | randomnumber | randomnumber
7------------------+------------------+------------------+------------------
8 1.96116291591898 | 47.4438195298426 | 103.643512582406 | 99.6020536908642
9(1 row)بسیار جالب است. ما می‌توانیم به جای اعداد اعشاری، اعداد صحیح تصادفی به دست آوریم.
1SELECT randomNumber(1,10)::INTEGER,
2 randomNumber(1,100)::INTEGER,
3 randomNumber(99,105)::INTEGER;
4
5 randomnumber | randomnumber | randomnumber
6--------------+--------------+--------------
7 3 | 77 | 103
8(1 row)اینک ما می‌توانیم از تابع فوق در موقعیت‌های متفاوت بهره بگیریم.
مقادیر بولی تصادفی
اینک که تابع جدید randomNumber را در اختیار داریم، ایجاد مقادیر بولی تصادفی کاملاً آسان می‌شود، چون PostgreSQL مقدار 0 را به عنوان False و مقدار 1 را به عنوان True شناسایی می‌کند.
1SELECT CAST(ROUND(randomNumber(0,1))::INTEGER AS BOOLEAN);
2
3 round
4-------
5 f
6(1 row)
7
8SELECT CAST(ROUND(randomNumber(0,1))::INTEGER AS BOOLEAN);
9
10 round
11-------
12 f
13(1 row)
14
15SELECT CAST(ROUND(randomNumber(0,1))::INTEGER AS BOOLEAN);
16
17 round
18-------
19 t
20(1 row)این کد را نیز به یک تابع تبدیل می‌کنیم که به صورت خلاصه زیر درمی‌آید:
1CREATE FUNCTION randomBoolean()
2RETURNS BOOLEAN AS
3$$
4 SELECT CAST(ROUND(randomNumber(0,1))::INTEGER AS BOOLEAN);
5$$
6LANGUAGE 'sql'
7VOLATILE;اینک یک آزمایش اجرایی انجام می‌دهیم تا مطمئن شویم که همه چیز مطابق میل ما پیش می‌رود:
1SELECT randomBoolean(),
2 randomBoolean(),
3 randomBoolean(),
4 randomBoolean();
5
6 randomboolean | randomboolean | randomboolean | randomboolean
7---------------+---------------+---------------+---------------
8 f | t | t | f
9(1 row)نیمی از مقادیر بولی تصادفی به صورت TRUE و نیمی دیگر به صورت FALSE هستند و این عالی است.
مقادیر بولی تصادفی وزن‌دار
در موارد متعددی لازم می‌آید که تغییرات معتبر در داده‌های seed را شبیه‌سازی کنیم و وزن‌دار کردن یک روش برای اجرای این وظیفه است.
به عنوان مثال، تصور کنید می‌خواهیم مقادیر بولی را برای جمعیتی از داده‌ها تولید کنیم، که اغلب مقادیرشان باید false باشند. این کار با استفاده از یک تابع دیگر انجام می‌گیرد به طوری که می‌توانیم آن را به راحتی در SQL مورد ارجاع قرار دهیم:
1CREATE FUNCTION randomWeightedBoolean(trueWeight IN NUMERIC)
2RETURNS BOOLEAN AS
3$$
4 SELECT randomNumber(0,1) < trueWeight
5$$
6LANGUAGE 'sql'
7VOLATILE;اینک به سناریوی فرضی قبلی خود بازمی‌گردیم. اگر بخواهیم مقادیر false در اکثریت باشند، می‌توانیم به سادگی یک مقدار پایین‌تر برای trueWeight ارسال کنیم. از آنجا که تابع randomNumber ما مقادیری بین 0 و 1 بازگشت می‌دهد، از این رو باید trueWeight را به صورت یک «درصد» اعشاری بیان کنیم. بخش دیگر تست شامل این است که مقادیر کافی تولید کنیم تا مشخص شود که منطق وزن‌دار کردن روی جمعیت‌های بزرگ نیز پاسخگو است. به این منظور از یک تابع بسیار کارآمد PostgreSQL به نام generate_series استفاده می‌کنیم.
1WITH base AS (
2 SELECT randomWeightedBoolean(.75) AS randomBoolean
3 FROM GENERATE_SERIES(1,100)
4)
5SELECT randomBoolean, COUNT(*) AS "Occurrences"
6 FROM base
7 GROUP BY randomBoolean;
8
9 randomboolean | Occurrences
10---------------+-------------
11 f | 27
12 t | 73
13(2 rows)بدین ترتیب در روی 100 تکرار، تابع بولی وزن‌دار ما تقریباً 75% مقادیر False تولید می‌کند.
ردیف یا ردیف‌های تصادفی از یک جدول
یک ویژگی جالب دیگر تابع random این است که می‌توان از آن در بند ORDER BY استفاده کرد. به مثال زیر که در آن یک مجموعه داده نمونه را «با» و «بدون» ترتیب تصادفی تولید می‌کنیم توجه کنید. در این مورد نیز از generate_series استفاده کرده‌ایم:
1/* Without Randomized Ordering */
2WITH base AS (
3 SELECT generate_series(1,10) AS val
4)
5SELECT val
6 FROM base;
7
8 val
9-----
10 1
11 2
12 3
13 4
14 5
15 6
16 7
17 8
18 9
19 10
20(10 rows)
21
22/* With Randomized Ordering */
23WITH base AS (
24 SELECT generate_series(1,10) AS val
25)
26SELECT val
27 FROM base
28 ORDER BY RANDOM();
29
30 val
31-----
32 9
33 4
34 5
35 7
36 2
37 10
38 3
39 1
40 6
41 8
42(10 rows)اینک می‌توانیم به سادگی یک ردیف تصادفی منفرد را با استفاده از بند LIMIT در PosrgresSQL از مجموعه داده‌های مفروض انتخاب کنیم.
1WITH base AS (
2 SELECT generate_series(1,10) AS val
3)
4SELECT val
5 FROM base
6 ORDER BY RANDOM()
7 LIMIT 1;
8
9 val
10-----
11 7
12(1 row)مقدار تصادفی از یک لیست شمارشی (Enumerated)
این یکی از رایج‌ترین سناریوهایی است که با آن مواجه خواهیم شد. در این حالت ما به داده‌های تصادفی‌شده نیاز داریم؛ اما مقادیر آن باید محدود به یک لیست شمارشی که از قبل داریم باشند.
برای نمونه فرض کنید یک مقدار تصادفی از یک لیست به صورت [Cyan, Magenta, Yellow, Black] بازگشت می‌دهیم. نخستین چالش آن است که لیست خود را به یک مجموعه داده تبدیل کنیم. این کار به سهولت با کمک گرفتن از آرایه‌های PostgreSQL ممکن است. برای نمونه به کد زیر توجه کنید:
1WITH base AS (
2 SELECT ARRAY['Cyan','Magenta','Yellow','Black'] AS colors
3)
4SELECT colors
5 FROM base;
6
7 colors
8-----------------------------
9 {Cyan,Magenta,Yellow,Black}
10(1 row)در کد فوق یک لیست از مقادیر به یک آرایه (ARRAY) تبدیل شده است. به آکولادهای پیرامون لیست توجه کنید. اما اینک سؤال این است که چگونه می‌توان مقادیر را به صورت ردیف‌های منفرد بازگشت داد؟ PostgreSQL یک تابع آرایه کارآمد به نام unnest دارد که این کار را برای ما انجام می‌دهد:
1WITH base AS (
2 SELECT ARRAY['Cyan','Magenta','Yellow','Black'] AS colors
3)
4SELECT UNNEST(colors) AS color
5 FROM base;
6
7 color
8---------
9 Cyan
10 Magenta
11 Yellow
12 Black
13(4 rows)بر اساس این مفهوم و همچنین چندین مورد از مثال‌های قبلی، می‌توانیم یک تابع دیگر بسازیم که به ما امکان می‌دهد از این رویکرد برای هر لیست شمارش شده استفاده کنیم. ما به طور خاص از نمادگذاری [] برای تعریف کردن آرایه Text استفاده می‌کنیم، چون این که با همه مقادیر ورودی به صورت TEXT رفتار کنیم، انعطاف‌پذیری زیادی به ما می‌دهد.
1SELECT randomValueFromList(ARRAY['Which','One','Will','It','Be','?']) AS "randomString1",
2 randomValueFromList(ARRAY['Yes','No']) AS "randomString2",
3 randomValueFromList(ARRAY['Yes','No'])::BOOLEAN AS "randomBoolean1",
4 randomValueFromList(ARRAY['True','False'])::BOOLEAN "randomBoolean2",
5 randomValueFromList(ARRAY['1','2','3'])::INTEGER "randomInteger",
6 randomValueFromList(ARRAY['1.01','1.02','1.03'])::DECIMAL "randomDecimal";
7
8 randomString1 | randomString2 | randomBoolean1 | randomBoolean2 | randomInteger | randomDecimal
9---------------+---------------+----------------+----------------+---------------+---------------
10 Will | No | t | f | 2 | 1.02
11(1 row)متن تصادفی
ما می‌توانیم به سادگی یک رشته تصادفی ایجاد کنیم. دقت کنید که این رشته لزوماً نباید معنی‌دار باشد. یک روش آن به صورت زیر است:
1SELECT MD5(RANDOM()::TEXT) AS randomString;
2
3 randomstring
4----------------------------------
5 fcc80db4edd11b231f72d2f0131af86a
6(1 row)همچنین می‌توانیم آن را به طول دلخواه خود کوتاه کنیم. دقت کنید که با استفاده از تابع md5 تنها می‌توان یک رشته 32 بایتی ایجاد کرد:
1SELECT SUBSTR(MD5(RANDOM()::TEXT),1,10) AS randomString;
2
3 randomstring
4--------------
5 aad3e22ff4
6(1 row)همچنین می‌توانیم حالت بزرگی/کوچکی حروف را تنظیم کنیم:
1SELECT UPPER(SUBSTR(MD5(RANDOM()::TEXT),1,10)) AS randomUpperCaseString,
2 INITCAP(SUBSTR(MD5(RANDOM()::TEXT),1,10)) AS randomProperCaseString;
3
4 randomuppercasestring | randompropercasestring
5-----------------------+------------------------
6 9E56E1242C | D26088672b
7(1 row)این تابع تنها مقادیر محدودی بازگشت می‌دهد و در مواردی که به طور خاص به رشته‌های بلندتر و معنیدارتر مانند نام افراد یا نشانی مکان‌ها نیاز باشد، چندان کارآمد نیست.
برای حل این مشکل یک مثال دیگر را بررسی می‌کنیم. در این مثال یک لیست تصادفی از 10 کاراکتر از الفبای زبان انگلیسی را انتخاب می‌کنیم:
1SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',randomNumber(1,26)::INTEGER,1) AS letter
2 FROM GENERATE_SERIES(1,10);
3
4 letter
5--------
6 I
7 S
8 V
9 C
10 L
11 M
12 U
13 C
14 U
15 X
16(10 rows)بر همین مبنا می‌توانیم از تابع خودمان یعنی generate_series به همراه یک تابع جدیدتر به نام string_agg استفاده کرده و کلمات تصادفی با طول خاص بسازیم. دقت کنید که ما به صورت مصنوعی، بیشینه طول رشته را به 100 محدود ساخته‌ایم.
1WITH base AS (
2 SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',randomNumber(1,26)::INTEGER,1) string1,
3 SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',randomNumber(1,26)::INTEGER,1) string2,
4 SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',randomNumber(1,26)::INTEGER,1) string3
5 FROM GENERATE_SERIES(1,100)
6)
7SELECT SUBSTR(STRING_AGG(string1,''),1,10) AS stringLength10,
8 SUBSTR(STRING_AGG(string2,''),1,5) AS stringLength5,
9 SUBSTR(STRING_AGG(string3,''),1,1) AS stringLength1
10 FROM base;
11
12 stringlength10 | stringlength5 | stringlength1
13----------------+---------------+---------------
14 PRSTUVGWTX | FVVTY | B
15(1 row)بدین ترتیب ما یک راه‌حل کارآمد برای تولید واژه‌های تصادفی داریم؛ اما این راه‌حل برای مواردی که به داده‌های واقعی شبیه‌سازی‌شده نیاز داریم کافی نیست. برای تولید رشته‌های تصادفی واقعاً معنی‌دار باید لیستی از مقادیر معنی‌دار داشته باشیم که در یک جدول ذخیره شده باشند. این کار از طریق مقداردهی قبلی جدول «نام‌های جعلی» (dummy names) ممکن می‌شود که با استفاده از آن می‌توانیم به صورت تصادفی یک نام و نام خانوادگی تصادفی برای یک فرد انتخاب کنیم. در این مورد نیز از کتابخانه‌های شخص ثالث مانند faker.js می‌توانیم استفاده کنیم.
موارد استفاده متفرقه
با استفاده از ترکیبی از برخی چیزها که تاکنون یاد گرفته‌ایم، چند مورد استفاده جالب دیگر نیز ارائه کرده‌ایم که در آن از داده‌های seed تصادفی‌شده استفاده شده است:
دمای بدن
1SELECT ROUND(randomNumber(35.5,38.0),1) AS degreesCelsius;
2
3 degreescelsius
4----------------
5 37.1
6(1 row)شماره تلفن
1SELECT TO_CHAR(ROUND(randomNumber(1111111111,9999999999)),'999-999-9999') AS phone;
2
3 phone
4---------------
5 733-442-3908
6(1 row)تاریخی در 12 ماه بعد
1SELECT CURRENT_DATE AS currentDate,
2 TO_DATE((TO_CHAR(CURRENT_TIMESTAMP,'J')::INTEGER + ROUND(randomNumber(1,365)))::TEXT,'J') AS futureDate;
3
4 currentdate | futuredate
5-------------+------------
6 2019-01-09 | 2019-11-04
7(1 row)تاریخ تصادفی در طی 30 روز آینده
1SELECT CURRENT_DATE AS currentDate,
2 (CURRENT_DATE-(ROUND(randomNumber(1,29))||' DAYS')::INTERVAL)::DATE AS pastDate;
3
4 currentdate | pastdate
5-------------+------------
6 2019-01-09 | 2018-12-15
7(1 row)داده‌های تصادفی برای تاریخ تولد فردی که زیر 18 سال سن دارد
1WITH base AS (
2 SELECT CAST(CURRENT_TIMESTAMP - randomNumber(1,18)*365 * INTERVAL '1 day' AS DATE) birthDate
3)
4SELECT CURRENT_DATE AS currentDate,
5 birthDate,
6 AGE(CURRENT_DATE,birthDate)
7 FROM base;
8
9 currentdate | birthdate | age
10-------------+------------+-------------------------
11 2019-01-09 | 2006-06-19 | 12 years 6 mons 20 days
12(1 row)اگر این مطلب برای شما مفید بوده است، آموزشهای زیر نیز به شما پیشنهاد می‌شوند:
- مجموعه آموزشهای برنامه نویسی
- مجموعه آموزشهای پایگاه داده و سیستم های مدیریت اطلاعات
- آموزش SQL Server – مقدماتی
- آموزش دستورهای SQL – مجموعه مقالات جامع وبلاگ فرادرس
- آموزش SQL Server – تکمیلی
- 13 دستور SQL مهم که هر برنامه‌نویسی باید بداند
- آموزش SQL Server Management Studio | کامل، رایگان و گام به گام
- MongoDB چیست؟ — راهنمای شروع با دیتابیس مانگو دی بی
==
