تولید داده های سری زمانی واقعی تر با postgresql generate_series ()

در این سری سه قسمتی در مورد تولید داده های سری زمانی نمونه ، ما نحوه استفاده از تابع داخلی PostgreSQL ، GETERATE_SERIES () را نشان می دهیم تا بتوانیم به راحتی مجموعه های بزرگی از داده ها را ایجاد کنیم تا به آزمایش بارهای مختلف کاری ، ویژگی های پایگاه داده یا فقط به آزمایش کمک کند. نمونه های سرگرم کننده ایجاد کنید.

در قسمت اول این سریال ، ما بررسی کردیم که چگونه GETERATE_SERIES () کار می کند ، از جمله امکان پیوستن به چندین سری به یک جدول بزرگتر از داده های سری زمانی - از طریق ویژگی شناخته شده به عنوان صلیب (یا دکارتی). ما اولین پست را به شما نشان دادیم که چگونه می توانید به سرعت تعداد ردیف هایی را که یک پرس و جو تولید می کند ، محاسبه کنید و پارامترها را برای generate_series () برای تنظیم دقیق اندازه و شکل داده ها تغییر دهید.

با این حال ، با داده هایی که می توانیم در پایان پست اول تولید کنیم ، یک مشکل وجود دارد. داده هایی که ما قادر به تولید آن بودیم بسیار اساسی بود و خیلی واقع بینانه نبود. بدون تلاش بیشتر ، استفاده از توابع مانند تصادفی () برای تولید مقادیر ، کنترل زیادی بر آنچه که تعداد تولید می شود ، کنترل زیادی نمی کند ، بنابراین داده ها هنوز هم جعلی تر از آنچه ممکن است بخواهیم احساس می کنند.

این پست دوم چند روش برای ایجاد داده های واقعی تر به نظر می رسد فراتر از یک ستون یا دو مقادیر اعشاری تصادفی نشان می دهد. برای اطلاعات بیشتر بخوانید

در قسمت 3 این سری وبلاگ یک ابزار نهایی را به ترکیب اضافه می کند-ترکیب تکنیک های قالب بندی داده در زیر با معادلات اضافی و داده های رابطه ای برای شکل دادن به خروجی سری زمانی نمونه شما به چیزی که بیشتر شبیه برنامه های زندگی واقعی است.

در پایان این سری ، شما آماده خواهید بود تا تقریباً هر ویژگی را که TimeScaledB ارائه می دهد آزمایش کنید و مجموعه داده های سریع را برای آزمایش و نسخه های نمایشی خود ایجاد کنید!

یک مرور مختصر از generate_series ()

در پست اول ، ما نشان دادیم که چگونه GETERATE_SERIES () (یک تابع بازگشت مجموعه) می تواند به سرعت یک مجموعه داده را بر اساس طیف وسیعی از مقادیر یا تاریخ های عددی ایجاد کند. داده های تولید شده در اصل یک جدول حافظه است که می تواند به سرعت مجموعه های بزرگی از داده های نمونه را ایجاد کند.

ما سپس بحث کردیم که چگونه داده ها به سرعت پیچیده تر می شوند زیرا به مجموعه های مختلف با هم (همراه با برخی از توابع بازگشت مقدار) می پیوندیم تا چندین مورد از هر دو مجموعه را با هم ایجاد کنیم.

این مثال از پست اول به یک مجموعه Timestamp ، یک مجموعه عددی و عملکرد تصادفی () برای ایجاد داده های CPU جعلی برای چهار دستگاه جعلی در طول زمان پیوست.

و سرانجام ، ما در مورد چگونگی محاسبه تعداد کل ردیف هایی که پرس و جو شما بر اساس محدوده زمانی ، فاصله بین زمانی و تعداد "چیزهایی" که برای آنها داده های جعلی ایجاد می کنید ، صحبت کردیم.

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

همانطور که در پرس و جو فوق دیدیم (تولید داده های CPU جعلی) ، هر ستون از داده هایی که به پرس و جو انتخاب می کنیم به هر ردیف مجموعه حاصل اضافه می شود. اگر متن استاتیک اضافه کنیم (مانند "سلام ، بازه زمانی!") ، آن متن برای هر ردیف تکرار می شود. به همین ترتیب ، اضافه کردن یک تابع به عنوان مقدار ستون برای هر ردیف مجموعه نهایی یک بار نامیده می شود.

این همان اتفاقی است که با عملکرد تصادفی () در مثال داده CPU رخ داده است. هر ردیف مقدار متفاوتی دارد زیرا این عملکرد برای هر ردیف داده های تولید شده به طور جداگانه خوانده می شود. ما می توانیم از این امر به نفع خود استفاده کنیم تا شروع به واقعی تر شدن داده ها شود.

با کمی بیشتر فکر و کارکردهای سفارشی postgreSQL ، می توانیم داده های نمونه خود را "زنده کنیم".

داده های واقع بینانه چیست؟

این یک زمان خوب برای اطمینان از ما در همان صفحه است. منظور من از داده های "واقع بینانه" چیست؟

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

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

• آیا CPU درصدی است؟از 100 ٪ یا ما نماینده CPU های چند هسته ای هستیم که می تواند 200 ٪ ، 400 ٪ یا 800 ٪ را ارائه دهد؟
• آیا دما در فارنهایت اندازه گیری می شود یا سانتیگراد؟مقادیر معقول برای دمای CPU در هر واحد چیست؟آیا ما دما را با اعشار یا به عنوان یک عدد صحیح در طرح ذخیره می کنیم؟
• چه می شود اگر یک قسمت "یادداشت" را برای پیام هایی که نرم افزار نظارت ما ممکن است هر از گاهی به خوانش ها اضافه کنیم ، به طرحواره اضافه کنیم؟آیا هر خواندن یک یادداشت یا درست هنگام رسیدن آستانه دارد؟آیا در بالای هر ساعت پیام تشخیصی خاصی وجود دارد که باید به نوعی تکرار کنیم؟

استفاده از متن تصادفی () و استاتیک به خودی خود به ما امکان می دهد داده های زیادی را با ستون های زیادی تولید کنیم ، اما در آزمایش ویژگی های موجود در پایگاه داده بسیار جالب یا مفید نخواهد بود.

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

و با استفاده از توابع ، همه کارها از جدول به جدول به راحتی قابل استفاده مجدد هستند.

قبل از دویدن راه بروید

در هر یک از مثالهای زیر ، ما به همان اندازه که در کلاس ریاضی ابتدایی آموخته ایم به راه حل های خود نزدیک می شویم: کار خود را نشان دهید! ایجاد یک عملکرد یا رویه در PostgreSQL اغلب دشوار است بدون اینکه ابتدا با بیانیه SQL ساده بازی کنید. این امر نیاز به فکر کردن در مورد ورودی ها و خروجی های عملکرد را در ابتدا از بین می برد تا بتوانیم بر نحوه عملکرد SQL برای تولید مقدار مورد نظر خود تمرکز کنیم.

بنابراین ، مثالهای زیر به شما نشان می دهد که چگونه می توانید قبل از تبدیل SQL به تابعی که قابل استفاده مجدد است ، یک مقدار (شماره های تصادفی ، متن ، JSON و غیره) دریافت کنید. این نوع فرآیند تکراری یک روش عالی برای یادگیری ویژگی های PostgreSQL است ، به ویژه هنگامی که با GETERATE_SERIES () ترکیب شود.

بنابراین ، یک پا را بگیرید و آن را در مقابل دیگری قرار دهید ، و بیایید داده های نمونه بهتری را شروع کنیم.

ایجاد اعداد واقع بینانه تر

در داده های سری زمانی ، مقادیر عددی اغلب متداول ترین نوع داده هستند. استفاده از تابعی مانند تصادفی () بدون هیچ گونه قالب بندی دیگر ، بسیار خوب ایجاد می کند. اعداد تصادفی (و دقیق) با نقاط اعشاری زیادی. در حالی که کار می کند ، ارزش ها واقع بینانه نیستند. بیشتر کاربران و دستگاه ها استفاده از CPU را به 12+ اعشار ردیابی نمی کنند. ما به راهی برای دستکاری و محدود کردن مقدار نهایی که در پرس و جو برگشته است نیاز داریم.

برای مقادیر عددی ، PostgreSQL بسیاری از توابع داخلی را برای اصلاح خروجی فراهم می کند. در بسیاری از موارد ، استفاده از دور () و کف () با حسابی اساسی می تواند به سرعت شکل دادن به داده ها به گونه ای شروع شود که متناسب با طرح شما باشد و مورد استفاده شود.

بیایید پرس و جو مثال را برای دریافت معیارهای دستگاه ، بازگشت مقادیر CPU و دما اصلاح کنیم. ما می خواهیم پرس و جو را به روز کنیم تا اطمینان حاصل شود که مقادیر داده برای هر ستون "سفارشی" شده و مقادیر را در یک محدوده و دقت خاص باز می گردانند. بنابراین ، ما باید یک فرمول استاندارد را برای هر مقدار عددی در پرس و جو انتخاب خود اعمال کنیم.

این معادله همیشه مقدار اعشاری بین (و شامل) مقدار حداقل و حداکثر ایجاد می کند. اگر تصادفی () مقدار 1 را برگرداند ، خروجی نهایی با حداکثر مقدار برابر خواهد بود. اگر تصادفی () مقدار 0 را برگرداند ، نتیجه با حداقل مقدار برابر خواهد بود. هر عدد دیگری که به صورت تصادفی () باز می گردد ، خروجی بین مقادیر حداقل و حداکثر ایجاد می کند.

بسته به اینکه آیا ما یک عدد اعشاری یا عدد صحیح می خواهیم ، می توانیم "مقدار نهایی" فرمول خود را با گرد () و کف () قالب بندی کنیم.

این مثال هر دقیقه یک ساعت را برای یک ساعت برای 10 دستگاه تولید می کند. مقدار CPU همیشه بین 3 تا 100 کاهش می یابد (با چهار اعشار از دقت) و درجه حرارت همیشه یک عدد صحیح بین 28 تا 83 خواهد بود.

با استفاده از فرمول ساده ما و قالب بندی صحیح نتیجه ، پرس و جو خروجی "سرپرستی" را (تصادفی همانطور که هست) تولید کردیم.

قدرت توابع

اما در اینجا نیز کمی کمرنگ وجود دارد ، مگر نه؟تایپ کردن آن فرمول به طور مکرر برای هر مقدار - تلاش برای به یاد آوردن ترتیب پارامترها و هنگامی که نیاز به ارزش گذاری دارم - به سرعت خسته کننده می شود. از این گذشته ، شما فقط کلیدهای زیادی باقی مانده است.

راه حل ایجاد و استفاده از توابع PostgreSQL است که می توانند ورودی های مورد نیاز خود را بگیرند ، محاسبات صحیح را انجام دهند و مقدار فرمت شده مورد نظر ما را برگردانند. روش های زیادی وجود دارد که می توانیم محاسبه ای مانند این را در یک عملکرد انجام دهیم. از این مثال به عنوان مکان شروع برای یادگیری و اکتشاف خود استفاده کنید.

توجه: در این مثال ، من تصمیم گرفتم که مقدار را از این عملکرد به عنوان یک نوع داده عددی برگردانم زیرا می تواند مقادیری را که به نظر می رسد مانند عدد صحیح (بدون اعشار) یا شناور (اعشار) بازگرداند. تا زمانی که مقادیر بازگشت با طرح در نظر گرفته شده در یک جدول قرار داده شود ، این یک "ترفند" برای بصری است که آنچه را که انتظار داریم - یک عدد صحیح یا شناور است. به طور کلی ، نوع داده عددی اغلب به دلیل نحوه ارائه مقادیر عددی در داخل ، در نمایش داده ها و ویژگی هایی مانند فشرده سازی بدتر عمل می کند. توصیه می کنیم در هر زمان ممکن از انواع عددی در طراحی طرحواره جلوگیری کنید ، به جای آن انواع شناور یا عدد صحیح را ترجیح دهید.

این تابع مثال از حداقل و حداکثر مقادیر ارائه شده استفاده می کند ، فرمول "دامنه" را که قبلاً در مورد آن بحث کردیم ، اعمال می کند و در نهایت یک مقدار عددی را که دارای اعشار است (به تعداد مشخص شده ارقام) باز می گرداند یا خیر. با استفاده از این عملکرد در پرس و جو ما ، می توانیم ایجاد مقادیر فرمت شده برای داده های نمونه را ساده کنیم و SQL را تمیز می کند و خواندن و استفاده از آن را آسان تر می کند.

این پرس و جو همان خروجی فرمت شده را فراهم می کند ، اما اکنون تکرار روند کار بسیار ساده تر است.

ایجاد متن واقع بینانه تر

متن چطور؟تاکنون ، در هر دو مقاله ، ما فقط در مورد نحوه تولید داده های عددی بحث کرده ایم. با این حال ، همه ما می دانیم که داده های سری زمانی اغلب حاوی بیش از مقادیر عددی هستند. بیایید به یک نوع داده مشترک دیگر مراجعه کنیم: متن.

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

بیایید پرس و جو مورد استفاده در ابتدا را در نظر بگیریم که داده های CPU و دما را برای مجموعه ای از دستگاه ها ایجاد می کند. اگر دستگاه ها واقعی بودند ، داده هایی که ایجاد می کنند ممکن است حاوی پیام وضعیت متناوب با طول متفاوت باشد.

برای فهمیدن چگونگی تولید این متن تصادفی ، ما همان فرآیند را مانند گذشته دنبال خواهیم کرد ، و مستقیماً در یک پرس و جو SQL مستقل کار می کنیم قبل از انتقال راه حل خود به یک عملکرد قابل استفاده مجدد. پس از برخی تلاش های اولیه (و Googling کافی) ، من برای تولید متن تصادفی از طول متغیر با استفاده از یک مجموعه کاراکتر تعریف شده ، این مثال را ارائه کردم. همانطور که با عملکرد Random_between () در بالا ، این امر می تواند متناسب با نیازهای شما اصلاح شود. به عنوان مثال ، با محدود کردن مجموعه شخصیت ها و طول ، می توان مقادیر شش ضلعی و تصادفی منحصر به فرد و تصادفی را بدست آورد.

اجازه دهید خلاقیت شما را راهنمایی کند.

در این حالت ، تولید یک مقدار تصادفی در داخل CTE آسان تر بود که بعداً می توانیم در پرس و جو به آن اشاره کنیم. با این حال ، این رویکرد یک مشکل دارد که در چند ردیف اول داده های برگشتی بسیار آسان است.

در حالی که CTE متن تصادفی از 10 کاراکتر ایجاد می کند (برای تأیید چند بار پیش بروید و آن را اجرا کنید) ، مقدار CTE یک بار یک بار تولید می شود و سپس ذخیره می شود و همان نتیجه را برای هر سطر تکرار می کند. پس از انتقال پرس و جو به یک تابع ، انتظار داریم برای هر ردیف مقدار متفاوتی را مشاهده کنیم.

برای این تابع مثال دوم برای تولید "کلمات" از طول تصادفی (یا اصلاً متن در بعضی موارد) ، کاربر باید حداقل و حداکثر طول متن تولید شده یک عدد صحیح را تهیه کند. پس از آزمایش ، ما همچنین یک ویژگی تصادفی ساده را اضافه کردیم.

به IF توجه کنید. سپس شرط اضافه کردیم. هر زمان که تعداد تولید شده به پنج تقسیم شود و دارای صفر یا یک باقیمانده باشد ، عملکرد مقدار متن را بر نمی گرداند. هیچ چیز خاصی در مورد این رویکرد برای ارائه تصادفی در فرکانس خروجی وجود ندارد ، بنابراین احساس راحتی کنید تا این بخش از عملکرد را متناسب با نیازهای خود تنظیم کنید.

هنگامی که ما از این عملکرد برای اضافه کردن متن تصادفی به پرس و جو سری زمانی خود استفاده می کنیم ، توجه کنید که متن به طول تصادفی (بین 2 تا 10 کاراکتر) و فرکانس است.

امیدوارم که شما شروع به دیدن یک الگوی کنید. با استفاده از GETERATE_SERIES () و برخی از کارکردهای سفارشی می تواند به شما در ایجاد داده های سری زمانی با بسیاری از شکل ها و اندازه ها کمک کند.

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

در مورد ارزش های JSON چیست؟

ایجاد نمونه JSON

توجه: نمونه های زیر در زیر رشته های JSON را به عنوان خروجی با هدف قرار دادن آن در یک جدول برای آزمایش و یادگیری بیشتر ایجاد می کنند. در PostgreSQL ، داده های رشته JSON را می توان در یک ستون JSON یا JSONB ذخیره کرد که هر کدام ویژگی های مختلفی را برای پرس و جو و نمایش داده های JSON ارائه می دهند. در بیشتر شرایط ، JSONB نوع ستون ارجح است زیرا ذخیره کارآمدتر و امکان ایجاد شاخص ها را بر روی محتویات فراهم می کند. نکته مهم اصلی این است که قالب بندی واقعی رشته JSON ، از جمله ترتیب کلیدها و مقادیر ، حفظ نمی شود و تولید مثل دقیقاً دشوار است. برای درک بهتر تفاوت های زمانی که می توانید داده های رشته JSON را با یک نوع ستون در قسمت دیگر ذخیره کنید ، لطفاً به مستندات PostgreSQL مراجعه کنید.

PostgreSQL سالها از انواع داده های JSON و JSONB پشتیبانی کرده است. با هر نسخه اصلی ، این ویژگی برای همکاری با JSON و عملکرد کلی پرس و جو بهبود می یابد. در تعداد فزاینده ای از مدل های داده ، به ویژه هنگامی که API های استراحت یا نمودار درگیر هستند ، ذخیره اطلاعات متا اضافی به عنوان یک سند JSON می تواند مفید باشد. در صورت لزوم داده ها در صورت لزوم در هنگام تسهیل نمایش داده های کارآمد در داده های سریالی ذخیره شده در ستون های معمولی در دسترس است.

ما از یک الگوی طراحی شبیه به این در کیت استارت NFT استفاده کردیم. API OpenSea JSON که به عنوان منبع داده برای کیت استارت مورد استفاده قرار می گیرد ، شامل بسیاری از خصوصیات و مقادیر برای هر دارایی و مجموعه است. بسیاری از مقادیر برای تجزیه و تحلیل خاص در آن آموزش مفید نبودند. با این حال ، ما می دانستیم که برخی از مقادیر موجود در خصوصیات JSON می تواند در تجزیه و تحلیل آینده ، آموزش ها یا تظاهرات مفید باشد. بنابراین ، ما ابرداده اضافی را در مورد دارایی ها و مجموعه ها در یک قسمت JSONB ذخیره کردیم تا در صورت لزوم آن را پرس و جو کنیم. با این وجود ، این طرح طرح را برای داده های متداول مانند نام و Asset_id پیچیده تر نمی کند.

ذخیره داده ها در یک زمینه JSON نیز در مناطقی مانند داده های دستگاه IIOT یک روش معمول است. مهندسان معمولاً یک طرحواره توافق شده برای ذخیره و پرس و جوهای تولید شده توسط دستگاه دارند و به دنبال آن یک ستون "فرم رایگان" JSON که به مهندسان امکان می دهد داده های تشخیصی یا تشخیصی را ارسال کنند که با گذشت زمان با تغییر سخت افزار تغییر می کنند یا به روز می شوند.

چندین روش برای افزودن داده های JSON به پرس و جو نمونه ما وجود دارد. یک چالش اضافه شده این است که داده های JSON شامل یک کلید و یک مقدار ، همراه با احتمال تعداد بی شماری از لانه سازی شیء کودک است. رویکردی که شما انجام می دهید بستگی به این دارد که چگونه می خواهید عملکرد PostgreSQL و هدف نهایی داده های نمونه پیچیده باشد. در این مثال ، ما تابعی را ایجاد خواهیم کرد که مجموعه ای از کلیدها را برای JSON می گیرد و مقادیر عددی تصادفی را برای هر کلید بدون لانه سازی تولید می کند. تولید رشته JSON در SQL از مقادیر ما ساده است ، به لطف توابع داخلی PostgreSQL برای خواندن و نوشتن رشته های JSON. پلتفرم معاملاتی فارکس...