برنامه نویسی 844 بازدید

آیا تاکنون نیاز داشته‌اید که یک پایگاه داده را با داده‌های تصادفی؛ اما واقعی پر کنید. این داده‌ها با مقاصدی از قبیل تست، نمایش یا تمرین ممکن است مورد استفاده قرار گیرند. این داده‌ها در وضعیت‌های زیادی مورد نیاز هستند و به عنوان یک توسعه‌دهنده ممکن است در موارد متعددی به آن‌ها احتیاج پیدا کنید. در این نوشته، به توضیح روش تولید مجموعه داده های تصادفی با استفاده از SQL می‌پردازیم.

meaningful datasets using only 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 با نسخه‌های متعدد
Postgres.app با نسخه‌های متعدد

Postgres.app همچنین از نوار منو نیز در دسترس شما قرار دارد و می‌توانید به سرعت PostgresSQL را اجرا/متوقف کنید.

Postgres.app

Postico

Postico (+) از سوی همان تیم توسعه‌دهنده Postgres.app طراحی شده است و یک UI ساده اما عالی برای PostgresSQL محسوب می‌شود. چند کلاینت گرافیکی مناسب دیگر نیز وجود دارند، اما بسیاری از توسعه‌دهندگان DBVisualizer (+) را ترجیح می‌دهند. با این وجود، Postico یک کلاینت رایگان است که موجب می‌شود راه‌اندازی و اجرای PostgresSQL بسیار آسان شود. با این که نسخه آزمایشی آن محدودیت‌های مختلفی دارد؛ اما خوشبختانه استفاده از نسخه آزمایشی با محدودیت زمانی مواجه نمی‌شود.

Postico
Postico

اینک که مطالب مقدماتی را مطرح کردیم، آماده هستیم که وارد بحث اصلی خود بشویم.

مقدار تصادفی اولیه

ساده‌ترین بلوک سازنده برای تصادفی سازی در PostgreSQL تابعی به نام random است.

postgres# SELECT RANDOM();

      random       
-------------------
 0.249834946822375
(1 row)

بر اساس مستندات PostgreSQL (+)، تابع random یک مقدار تصادفی در بازه زیر بازگشت می‌دهد:

0.0 <= x < 1.0

ما با استفاده از این تابع ساده می‌توانیم یک مقدار تصادفی را در بازه‌های مختلف مانند 1 تا 100 بسازیم.

مقدار تصادفی در بازه‌های دلخواه

WITH base AS (
   SELECT 1 AS startingValue,
          100 AS endingValue
)
SELECT (startingValue + (endingValue - startingValue) * RANDOM())::NUMERIC
  FROM base;

     numeric      
------------------
 57.9347848887555
(1 row)

عملکرد کد فوق عالی است. برای این که این تابع قابلیت استفاده مجدد برای سناریوهای دیگر نیز داشته باشد آن را به صورت زیر بازنویسی می‌کنیم:

CREATE FUNCTION randomNumber(startingValue IN NUMERIC, endingValue IN NUMERIC)
RETURNS NUMERIC AS
$$
   SELECT (startingValue + (endingValue - startingValue) * RANDOM())::NUMERIC;
$$
LANGUAGE 'sql'
VOLATILE;

در ادامه آن را تست می‌کنیم:

SELECT randomNumber(1,10),
       randomNumber(1,100),
       randomNumber(99,105),
       randomNumber(99.000,99.999);

   randomnumber   |   randomnumber   |   randomnumber   |   randomnumber   
------------------+------------------+------------------+------------------
 1.96116291591898 | 47.4438195298426 | 103.643512582406 | 99.6020536908642
(1 row)

بسیار جالب است. ما می‌توانیم به جای اعداد اعشاری، اعداد صحیح تصادفی به دست آوریم.

SELECT randomNumber(1,10)::INTEGER,
       randomNumber(1,100)::INTEGER,
       randomNumber(99,105)::INTEGER;

 randomnumber | randomnumber | randomnumber 
--------------+--------------+--------------
            3 |           77 |          103
(1 row)

اینک ما می‌توانیم از تابع فوق در موقعیت‌های متفاوت بهره بگیریم.

مقادیر بولی تصادفی

اینک که تابع جدید randomNumber را در اختیار داریم، ایجاد مقادیر بولی تصادفی کاملاً آسان می‌شود، چون PostgreSQL مقدار 0 را به عنوان False و مقدار 1 را به عنوان True شناسایی می‌کند.

SELECT CAST(ROUND(randomNumber(0,1))::INTEGER AS BOOLEAN);

 round 
-------
 f
(1 row)

SELECT CAST(ROUND(randomNumber(0,1))::INTEGER AS BOOLEAN);

 round 
-------
 f
(1 row)

SELECT CAST(ROUND(randomNumber(0,1))::INTEGER AS BOOLEAN);

 round 
-------
 t
(1 row)

این کد را نیز به یک تابع تبدیل می‌کنیم که به صورت خلاصه زیر درمی‌آید:

CREATE FUNCTION randomBoolean()
RETURNS BOOLEAN AS
$$
   SELECT CAST(ROUND(randomNumber(0,1))::INTEGER AS BOOLEAN);
$$
LANGUAGE 'sql'
VOLATILE;

اینک یک آزمایش اجرایی انجام می‌دهیم تا مطمئن شویم که همه چیز مطابق میل ما پیش می‌رود:

SELECT randomBoolean(),
       randomBoolean(),
       randomBoolean(),
       randomBoolean();

 randomboolean | randomboolean | randomboolean | randomboolean 
---------------+---------------+---------------+---------------
 f             | t             | t             | f
(1 row)

نیمی از مقادیر بولی تصادفی به صورت TRUE و نیمی دیگر به صورت FALSE هستند و این عالی است.

مقادیر بولی تصادفی وزن‌دار

در موارد متعددی لازم می‌آید که تغییرات معتبر در داده‌های seed را شبیه‌سازی کنیم و وزن‌دار کردن یک روش برای اجرای این وظیفه است. به عنوان مثال، تصور کنید می‌خواهیم مقادیر بولی را برای جمعیتی از داده‌ها تولید کنیم، که اغلب مقادیرشان باید false باشند. این کار با استفاده از یک تابع دیگر انجام می‌گیرد به طوری که می‌توانیم آن را به راحتی در SQL مورد ارجاع قرار دهیم:

CREATE FUNCTION randomWeightedBoolean(trueWeight IN NUMERIC)
RETURNS BOOLEAN AS
$$ 
   SELECT randomNumber(0,1) < trueWeight
$$
LANGUAGE 'sql' 
VOLATILE;

اینک به سناریوی فرضی قبلی خود بازمی‌گردیم. اگر بخواهیم مقادیر false در اکثریت باشند، می‌توانیم به سادگی یک مقدار پایین‌تر برای trueWeight ارسال کنیم. از آنجا که تابع randomNumber ما مقادیری بین 0 و 1 بازگشت می‌دهد، از این رو باید trueWeight را به صورت یک «درصد» اعشاری بیان کنیم. بخش دیگر تست شامل این است که مقادیر کافی تولید کنیم تا مشخص شود که منطق وزن‌دار کردن روی جمعیت‌های بزرگ نیز پاسخگو است. به این منظور از یک تابع بسیار کارآمد PostgreSQL به نام generate_series استفاده می‌کنیم.

WITH base AS (
   SELECT randomWeightedBoolean(.75) AS randomBoolean
     FROM GENERATE_SERIES(1,100)
)
SELECT randomBoolean, COUNT(*) AS "Occurrences"
  FROM base
 GROUP BY randomBoolean;
 
 randomboolean | Occurrences 
---------------+-------------
 f             |          27
 t             |          73
(2 rows)

بدین ترتیب در روی 100 تکرار، تابع بولی وزن‌دار ما تقریباً 75% مقادیر False تولید می‌کند.

ردیف یا ردیف‌های تصادفی از یک جدول

یک ویژگی جالب دیگر تابع random این است که می‌توان از آن در بند ORDER BY استفاده کرد. به مثال زیر که در آن یک مجموعه داده نمونه را «با» و «بدون» ترتیب تصادفی تولید می‌کنیم توجه کنید. در این مورد نیز از generate_series استفاده کرده‌ایم:

/* Without Randomized Ordering */
WITH base AS (
   SELECT generate_series(1,10) AS val 
)
SELECT val
  FROM base;

 val 
-----
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10
(10 rows)

/* With Randomized Ordering */
WITH base AS (
   SELECT generate_series(1,10) AS val 
)
SELECT val
  FROM base
 ORDER BY RANDOM();

 val 
-----
   9
   4
   5
   7
   2
  10
   3
   1
   6
   8
(10 rows)

اینک می‌توانیم به سادگی یک ردیف تصادفی منفرد را با استفاده از بند LIMIT در PosrgresSQL از مجموعه داده‌های مفروض انتخاب کنیم.

WITH base AS (
   SELECT generate_series(1,10) AS val 
)
SELECT val
  FROM base
 ORDER BY RANDOM()
 LIMIT 1;

 val 
-----
   7
(1 row)

مقدار تصادفی از یک لیست شمارشی (Enumerated)

این یکی از رایج‌ترین سناریوهایی است که با آن مواجه خواهیم شد. در این حالت ما به داده‌های تصادفی‌شده نیاز داریم؛ اما مقادیر آن باید محدود به یک لیست شمارشی که از قبل داریم باشند.

برای نمونه فرض کنید یک مقدار تصادفی از یک لیست به صورت [Cyan, Magenta, Yellow, Black] بازگشت می‌دهیم. نخستین چالش آن است که لیست خود را به یک مجموعه داده تبدیل کنیم. این کار به سهولت با کمک گرفتن از آرایه‌های PostgreSQL ممکن است. برای نمونه به کد زیر توجه کنید:

WITH base AS (
   SELECT ARRAY['Cyan','Magenta','Yellow','Black'] AS colors
)
SELECT colors
  FROM base;

           colors            
-----------------------------
 {Cyan,Magenta,Yellow,Black}
(1 row)

در کد فوق یک لیست از مقادیر به یک آرایه (ARRAY) تبدیل شده است. به آکولادهای پیرامون لیست توجه کنید. اما اینک سؤال این است که چگونه می‌توان مقادیر را به صورت ردیف‌های منفرد بازگشت داد؟ PostgreSQL یک تابع آرایه کارآمد به نام unnest دارد که این کار را برای ما انجام می‌دهد:

WITH base AS (
   SELECT ARRAY['Cyan','Magenta','Yellow','Black'] AS colors
)
SELECT UNNEST(colors) AS color
  FROM base;

  color  
---------
 Cyan
 Magenta
 Yellow
 Black
(4 rows)

بر اساس این مفهوم و همچنین چندین مورد از مثال‌های قبلی، می‌توانیم یک تابع دیگر بسازیم که به ما امکان می‌دهد از این رویکرد برای هر لیست شمارش شده استفاده کنیم. ما به طور خاص از نمادگذاری [] برای تعریف کردن آرایه Text استفاده می‌کنیم، چون این که با همه مقادیر ورودی به صورت TEXT رفتار کنیم، انعطاف‌پذیری زیادی به ما می‌دهد.

SELECT randomValueFromList(ARRAY['Which','One','Will','It','Be','?']) AS "randomString1",
       randomValueFromList(ARRAY['Yes','No']) AS "randomString2",
       randomValueFromList(ARRAY['Yes','No'])::BOOLEAN AS "randomBoolean1",
       randomValueFromList(ARRAY['True','False'])::BOOLEAN "randomBoolean2",
       randomValueFromList(ARRAY['1','2','3'])::INTEGER "randomInteger",
       randomValueFromList(ARRAY['1.01','1.02','1.03'])::DECIMAL "randomDecimal";

 randomString1 | randomString2 | randomBoolean1 | randomBoolean2 | randomInteger | randomDecimal 
---------------+---------------+----------------+----------------+---------------+---------------
 Will          | No            | t              | f              |             2 |          1.02
(1 row)

متن تصادفی

ما می‌توانیم به سادگی یک رشته تصادفی ایجاد کنیم. دقت کنید که این رشته لزوماً نباید معنی‌دار باشد. یک روش آن به صورت زیر است:

SELECT MD5(RANDOM()::TEXT) AS randomString;

           randomstring           
----------------------------------
 fcc80db4edd11b231f72d2f0131af86a
(1 row)

همچنین می‌توانیم آن را به طول دلخواه خود کوتاه کنیم. دقت کنید که با استفاده از تابع md5 تنها می‌توان یک رشته 32 بایتی ایجاد کرد:

SELECT SUBSTR(MD5(RANDOM()::TEXT),1,10) AS randomString;

 randomstring 
--------------
 aad3e22ff4
(1 row)

همچنین می‌توانیم حالت بزرگی/کوچکی حروف را تنظیم کنیم:

SELECT UPPER(SUBSTR(MD5(RANDOM()::TEXT),1,10)) AS randomUpperCaseString,
       INITCAP(SUBSTR(MD5(RANDOM()::TEXT),1,10)) AS randomProperCaseString;

 randomuppercasestring | randompropercasestring 
-----------------------+------------------------
 9E56E1242C            | D26088672b
(1 row)

این تابع تنها مقادیر محدودی بازگشت می‌دهد و در مواردی که به طور خاص به رشته‌های بلندتر و معنی‎دارتر مانند نام افراد یا نشانی مکان‌ها نیاز باشد، چندان کارآمد نیست.

برای حل این مشکل یک مثال دیگر را بررسی می‌کنیم. در این مثال یک لیست تصادفی از 10 کاراکتر از الفبای زبان انگلیسی را انتخاب می‌کنیم:

SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',randomNumber(1,26)::INTEGER,1) AS letter
  FROM GENERATE_SERIES(1,10);

 letter 
--------
 I
 S
 V
 C
 L
 M
 U
 C
 U
 X
(10 rows)

بر همین مبنا می‌توانیم از تابع خودمان یعنی generate_series به همراه یک تابع جدیدتر به نام string_agg استفاده کرده و کلمات تصادفی با طول خاص بسازیم. دقت کنید که ما به صورت مصنوعی، بیشینه طول رشته را به 100 محدود ساخته‌ایم.

WITH base AS (
   SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',randomNumber(1,26)::INTEGER,1) string1,
          SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',randomNumber(1,26)::INTEGER,1) string2,
          SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',randomNumber(1,26)::INTEGER,1) string3
     FROM GENERATE_SERIES(1,100)
)
SELECT SUBSTR(STRING_AGG(string1,''),1,10) AS stringLength10,
       SUBSTR(STRING_AGG(string2,''),1,5) AS stringLength5,
       SUBSTR(STRING_AGG(string3,''),1,1) AS stringLength1
  FROM base;

 stringlength10 | stringlength5 | stringlength1 
----------------+---------------+---------------
 PRSTUVGWTX     | FVVTY         | B
(1 row)

بدین ترتیب ما یک راه‌حل کارآمد برای تولید واژه‌های تصادفی داریم؛ اما این راه‌حل برای مواردی که به داده‌های واقعی شبیه‌سازی‌شده نیاز داریم کافی نیست. برای تولید رشته‌های تصادفی واقعاً معنی‌دار باید لیستی از مقادیر معنی‌دار داشته باشیم که در یک جدول ذخیره شده باشند. این کار از طریق مقداردهی قبلی جدول «نام‌های جعلی» (dummy names) ممکن می‌شود که با استفاده از آن می‌توانیم به صورت تصادفی یک نام و نام خانوادگی تصادفی برای یک فرد انتخاب کنیم. در این مورد نیز از کتابخانه‌های شخص ثالث مانند faker.js می‌توانیم استفاده کنیم.

موارد استفاده متفرقه

با استفاده از ترکیبی از برخی چیزها که تاکنون یاد گرفته‌ایم، چند مورد استفاده جالب دیگر نیز ارائه کرده‌ایم که در آن از داده‌های seed تصادفی‌شده استفاده شده است:

دمای بدن

SELECT ROUND(randomNumber(35.5,38.0),1) AS degreesCelsius;

 degreescelsius 
----------------
           37.1
(1 row)

شماره تلفن

SELECT TO_CHAR(ROUND(randomNumber(1111111111,9999999999)),'999-999-9999') AS phone;

     phone     
---------------
  733-442-3908
(1 row)

تاریخی در 12 ماه بعد

SELECT CURRENT_DATE AS currentDate,
       TO_DATE((TO_CHAR(CURRENT_TIMESTAMP,'J')::INTEGER + ROUND(randomNumber(1,365)))::TEXT,'J') AS futureDate;
 
 currentdate | futuredate 
-------------+------------
 2019-01-09  | 2019-11-04
(1 row)

تاریخ تصادفی در طی 30 روز آینده

SELECT CURRENT_DATE AS currentDate,
       (CURRENT_DATE-(ROUND(randomNumber(1,29))||' DAYS')::INTERVAL)::DATE AS pastDate;

 currentdate |  pastdate  
-------------+------------
 2019-01-09  | 2018-12-15
(1 row)

داده‌های تصادفی برای تاریخ تولد فردی که زیر 18 سال سن دارد

WITH base AS (
   SELECT CAST(CURRENT_TIMESTAMP - randomNumber(1,18)*365 * INTERVAL '1 day' AS DATE) birthDate
)
SELECT CURRENT_DATE AS currentDate,
       birthDate,
       AGE(CURRENT_DATE,birthDate)
  FROM base;

 currentdate | birthdate  |           age           
-------------+------------+-------------------------
 2019-01-09  | 2006-06-19 | 12 years 6 mons 20 days
(1 row)

اگر این مطلب برای شما مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

==

اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.

«میثم لطفی» دانش‌آموخته ریاضیات و شیفته فناوری به خصوص در حوزه رایانه است. وی در حال حاضر علاوه بر پیگیری علاقه‌مندی‌هایش در رشته‌های برنامه‌نویسی، کپی‌رایتینگ و محتوای چندرسانه‌ای، در زمینه نگارش مقالاتی با محوریت نرم‌افزار نیز با مجله فرادرس همکاری دارد.

بر اساس رای 1 نفر

آیا این مطلب برای شما مفید بود؟

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *