دستورات RegEx در بیگ کوئری

دستورات RegEx در بیگ کوئری

عبارات منظم (Regular expressions) که به اختصار به عنوان دستوران RegEx شناخته می شوند، یک الگو یا دنباله‌ای از کاراکترها هستند که به شما این امکان را می دهند یک داده از جنس رشته را از نظر میزان مطابقت بررسی کنید، آن را جستجو کنید و یا با مقدار دیگری جایگزین کنید. موارد زیادی وجود دارد که در حین تجزیه و تحلیل می‌خواهیم قسمت های خاصی از یک رشته را استخراج کنیم، بررسی کنیم که آیا یک رشته فرمت خاصی دارد یا نه، و حتی کاراکترهای خاصی را جایگزین یا حذف کنیم. توابع RegEx در بیگ کوئری،  دستوراتی هستند که شما برای اجرای عملیات مختلف گفته شده به آنها نیاز دارید.

ترفندهای توابع RegEx در بیگ کوئری

قبل از اینکه نکات و ترفندهای جالب در مورد استفاده از عبارات منظم (RegEx) و توابع RegExp در BigQuery  را برای استخراج و دستکاری داده‌هایتان توضیح دهم، باید داده‌های خود را وارد بیگ کوئری کنید. اگر با روش های ایمپورت داده در بیگ کوئری آشنایی ندارید، لطفاً روی لینک کلیک کنید.

تابعساختارتوضیحات
REGEXP_CONTAINSREGEXP_CONTAINS(value, regexp)بررسی می کند که آیا ورودی با الگوی عبارت منظم داده شده مطابقت دارد یا خیر
REGEXP_EXTRACTREGEXP_EXTRACT(value, regexp)اولین عبارت منطبق با الگوی عبارت منظم مطابقت دارد را به عنوان خروجی باز می‌گرداند
REGEXP_EXTRACT_ALLREGEXP_EXTRACT_ALL(value, regexp)هر عبارتی که با الگوی عبارت منظم مطابقت دارد استخراج می کند.
REGEXP_INSTRREGEXP_INSTR(source_value, regexp, [position], [occurrence], [occurrence_position])اولین موقعیتی که با الگوی تعیین شده مطابقت دارد، به عنوان خروجی باز می‌گرداند.
REGEXP_REPLACEREGEXP_REPLACE(value, regexp, replacement)بعد از پیدا کردن الگوی منطبق با ساختار تعیین شده، عبارت موجود در تابع را با عبارت قبلی جایگزین میکند.
REGEXP_SUBSTRREGEXP_SUBSTR(value, regexp, [position], [occurrence])این تابع درواقع همان تابع REGEXP_EXTRACT است.

نحوه بررسی درست بودن ساختار اطلاعات با استفاده از توابع RegEx

اولین ترفندی که در خصوص توابع RegEx در بیگ کوئری بررسی می‌‌کنیم این است که بررسی کنیم آیا ساختار تاریخ ایونتها در جداول آنالیتیکس در بیگ کوئری، صحیح هست یا نه. با فرض اینکه event_date به عنوان یک فیلد تاریخ در قالب YYYYMMDD ذخیره می شود، ما از تابع REGEXP_CONTAINS و عبارت منظم زیر استفاده خواهیم کرد:

^\d{8}$

  • ^: شروع رشته را نشان می دهد.
  • \d{8}: مقدار موجود در ستون دقیقا باید 8 رقم داشته باشد. (به دلیل فرمت YYYYMMDD )
  • $: انتهای رشته را نشان می‌دهد.
نحوه بررسی درست بودن ساختار اطلاعات با استفاده از توابع RegEx

پیدا کردن یک کلمه خاص با توابع RegEx در بیگ کوئری

تصور کنید که در جداول آنالیتیکس، به دنبال بازدید از صفحاتی هستیم که به عنوان مثال مربوط به دسته بندی آموزش آنالیتیکس 4 در سایت من هستند. ساختار urlها در سایت من به گونه ای است که عبارت /ga4/، آدرس‌هایی که مربوط به دسته بندی آموزش آنالیتیکس هست را نشان می‌دهند.

پیدا کردن یک کلمه خاص با توابع RegEx در بیگ کوئری

تشخیص عدم وجود چند کلمه در یک ستون در بیگ کوئری

از تابع [NOT] LIKE میتوان برای بررسی عدم وجود یک کلمه در یک ستون استفاده کرد. اما از توابع RegExp در بیگ کوئری میتوان برای بررسی عدم وجود چند کلمه و عدم حساسیت به حروف بزرگ یا کوچک استفاده کرد. دستوری که برای انجام این کار استفاده میکنیم، مشابه با دستور قبلی است، با این تفاوت که با اضافه کردن یک NOT، عدم وجود عبارت را بررسی میکنیم.

تشخیص عدم وجود چند کلمه در یک ستون در بیگ کوئری

بررسی اینکه آیا یک ستون حاوی کلمه ای از ستون دیگر است یا خیر در بیگ کوئری

برای بررسی اینکه آیا یگ ستون حاوی کلمه ای از ستون دیگر است یا خیر، از تابع REGEXP_CONTAINS استفاده می‌کنیم. CONCAT یکی از پرکاربردترین دستوارت SQL در بیگ کوئری است که با استفاده از آن میتوان اطلاعات دو ستون را کنار یکدیگر قرار داد.

بررسی اینکه آیا یک ستون حاوی کلمه ای از ستون دیگر است یا خیر در بیگ کوئری

جایگزین یک عبارت در یک ستون با توابع RegExp در بیگ کوئری

یکی دیگر از کوئری‌های محبوب که در آن از عبارات منظم استفاده می شود، جایگزین کردن یک کلمه خاص با یک کلمه دیگر است. برای انجام این کار، از تابع REGEXP_REPLACE استفاده می کنیم و کلمه “ga4” را با کلمه “google-analytics-4” در مقدار page_location جایگزین می کنیم:

جایگزین یک عبارت در یک ستون با توابع RegExp در بیگ کوئری

جدا کردن بخشی از استرینگ با توابع RegExp در بیگ کوئری

یکی دیگر از جذاب‌ترین کاربرد دستورات RegExp در بیگ کوئری این است که به شما این امکان را می‌دهند تا بخشی از یک رشته را انتخاب و استخراج کنید. با استفاده از تابع REGEXP_EXTRACT و این عبارت منظم r”^(.+?) “، می‌توانیم هر چیزی را که قبل از اولین space در یک رشته ظاهر می شود استخراج کنیم:

جدا کردن بخشی از استرینگ با توابع RegExp در بیگ کوئری

نحوه جایگزینی کاراکترهای خاص با دستورات RegExp

گاهی اوقات نیاز است که برخی از کاراکترهای خاص مانند نقطه، کاما، فاصله و… را حذف کنیم. برای انجام این کار می‌توانیم از دستور زیر استفاده کنیم:

نحوه جایگزینی کاراکترهای خاص با دستورات RegExp

نحوه استخراج یک عدد از یک رشته با دستورات RegExp

اگر می‌خواهید تمام اعداد موجود در یک رشته را پیدا کنید و/یا حتی آنها را جایگزین کنید، می توانید از دستوران RegExp استفاده کنید. برای انجام این کار، می‌توانیم از تابع REGEXP_EXTRACT_ALL به همراه عبارت منظم ([0-9]+) برای استخراج همه اعدادی که در یک آدرس ظاهر می‌شوند استفاده کنیم:

دیدگاهتان را بنویسید

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

دریافت مشاوره و آموزش

برای دریافت مشاوره نصب و راه اندازی آنالیتیکس 4 و ایونت ترکینگ از طریق گوگل تگ منیجر، از طریق شماره تماس زیر با من در ارتباط باشید.