تولید مجموعه داده های تصادفی با SQL – راهنمای مقدماتی

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

در مسیر توسعه یک اپلیکیشن زمان چندانی طول نمی‌کشد که یکی از افراد تیم نیاز پیدا کند اپلیکیشن را با داده‌های واقعی تست کند. بنابراین باید روشی برای مدیریت و رفع این نیاز بیابیم. یکی از بهترین روش‌ها استفاده از زبان SQL است.

SQL یک زبان جالب و شگفت‌انگیز است و کارهایی که با آن می‌توان به سهولت انجام داد بی‌شمار هستند. البته بدیهی است که کتابخانه‌های زیادی وجود دارند که می‌توان این کار را با آن‌ها اجرا کرد. یکی از بهترین این کتابخانه‌ها faker.js (+) است. بسیاری از مثال‌هایی که در این متن مشاهده خواهید کرد با استفاده از faker.js به کد جاوا اسکریپت تبدیل شده‌اند.

با این وجود هدف این مقاله آن است که نمایش بدهد چه کارهای متنوعی را می‌توان با استفاده از SQL اجرا کرد. پیش از آن که کتابخانه‌هایی مانند faker.js ساخته شوند، SQL وجود داشته و با آن کارهای زیادی اجرا شده است. بنابراین بهتر است تلاش کنیم با یادگیری بهتر آن، مهارت‌های خود را تعمیق ببخشیم.

قراردادها و ابزارها

ما در این نوشته از محیط تست PostgreSQL روی OS X استفاده می‌کنیم و همه نکاتی که در ادامه مطرح شده‌اند، در دیالکت PG نوشته شده‌اند. بسیاری از این نکات را می‌توان روی پلتفرم‌های دیگر پایگاه داده رابطه‌ای یا دیالکت‌های SQL نیز استفاده کرد.

در ادامه متوجه خواهید شد که در این نوشته به طور مکرر از «عبارت‌های جدول مشترک» (Common Table Expressions) یا به اختصار CTE در SQL استفاده می‌کنیم. این عبارت‌ها به نام بندهای 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 است.

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

0.0 <= x < 1.0

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

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

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

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

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

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

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

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

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

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

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

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

در موارد متعددی لازم می‌آید که تغییرات معتبر در داده‌های seed را شبیه‌سازی کنیم و وزن‌دار کردن یک روش برای اجرای این وظیفه است.

فیلم آموزش پایگاه داده اس کیو لایت SQLite در پایتون در فرادرس

کلیک کنید

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

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

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

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

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

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

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

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

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

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

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

متن تصادفی

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

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

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

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

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

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

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

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

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

دمای بدن

شماره تلفن

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

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

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

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

• مجموعه آموزش‌‌های برنامه‌‌ نویسی
• مجموعه آموزش‌‌های پایگاه داده و سیستم های مدیریت اطلاعات
• آموزش SQL Server – مقدماتی
• آموزش دستورهای SQL – مجموعه مقالات جامع وبلاگ فرادرس
• آموزش SQL Server – تکمیلی
• 13 دستور SQL مهم که هر برنامه‌نویسی باید بداند
• آموزش SQL Server Management Studio | کامل، رایگان و گام به گام
• MongoDB چیست؟ — راهنمای شروع با دیتابیس مانگو دی بی

==