استفاده از CTE برای تجزیه و تحلیل داده های SQL

Summarize this content to 400 words in Persian Lang Common Table Expressions (CTEs) یک ویژگی قدرتمند در SQL است که خوانایی و ساختار پرس و جوهای پیچیده را بهبود می بخشد و تجزیه و تحلیل داده ها را برای مدیریت و تفسیر آسان تر می کند. CTEها به ویژه برای تجزیه تبدیل داده‌های چند مرحله‌ای به قطعات ساده‌تر و مدولار مفید هستند و جایگزینی تمیزتر برای سوالات فرعی سنتی ارائه می‌دهند.

CTE چیست؟

یک CTE یک مجموعه نتایج موقتی است که شما در یک عبارت WITH تعریف می‌کنید و می‌توانید با کوئری اصلی زیر به آن ارجاع دهید. برخلاف پرسش‌های فرعی سنتی، CTE‌ها اغلب خواناتر هستند، زیرا به شما امکان می‌دهند نامی را به مجموعه نتایج اختصاص دهید و درک پرسش‌های پیچیده را آسان‌تر می‌کنند. نحو اصلی این است:

WITH cte_name AS (
SELECT …
)
SELECT …
FROM cte_name;

مزایای استفاده از CTE

خوانایی: CTE ها به شما امکان می دهند پرس و جوها را در بخش های منطقی ساختار دهید و آنها را خواناتر و اشکال زدایی آنها را آسان تر می کند. به‌عنوان مثال، به‌جای قرار دادن چندین زیرپرس و جو در عبارت FROM، می‌توانید هر مرحله را در یک CTE جداگانه تعریف کنید و مشخص کنید که هر بخش از پرس و جو چه کاری انجام می‌دهد.
عملکرد: CTE ها اغلب کارآمدتر از پرس و جوهای فرعی درون خطی هستند زیرا یک بار اجرا می شوند و در حافظه ذخیره می شوند. این بدان معنی است که اگر CTE را چندین بار در پرس و جو خود ارجاع دهید، نیازی به محاسبه مجدد آن در هر بار نیست.
ماژولاریت: هنگام تجزیه و تحلیل داده ها با محاسبات پیچیده – مانند محاسبه میانگین متحرک، مجموع تجمعی، یا پیوندهای پیچیده – CTE ها می توانند مشکل را به مراحل قابل مدیریت تقسیم کنند که هر کدام هدف مشخصی دارند. این مدولار بودن ویرایش یا گسترش تجزیه و تحلیل را در طول زمان آسان تر می کند.

مثال: استفاده از CTE برای تجزیه و تحلیل داده ها

فرض کنید می‌خواهید داده‌های فروش را تجزیه و تحلیل کنید و درآمد ماهانه را به همراه مجموع انباشته محاسبه کنید. استفاده از CTE فرآیند را ساده می کند:

WITH monthly_sales AS (
SELECT
DATE_TRUNC(‘month’, sale_date) AS sale_month,
SUM(amount) AS total_sales
FROM sales
GROUP BY sale_month
)
SELECT
sale_month,
total_sales,
SUM(total_sales) OVER (ORDER BY sale_month) AS cumulative_sales
FROM monthly_sales;

در این مثال، monthly_sales CTE، فروش در هر ماه را محاسبه می‌کند، و استفاده از مجموعه نتایج را در جستار نهایی برای محاسبه فروش تجمعی آسان‌تر می‌کند. این کار تکرار کد را کاهش می دهد و وضوح هر مرحله محاسبه را افزایش می دهد.

Refactoring سوالات فرعی با CTE

همانطور که با SQL کار می کنید، پرس و جوهای فرعی می توانند طولانی و پیچیده شوند و ردیابی هر قسمت از پرس و جو را دشوار می کند. CTE ها با استفاده از عبارت WITH، راهی برای مدیریت این پیچیدگی ارائه می دهند که به شما امکان می دهد پرس و جو فرعی را قبل از پرس و جو اصلی اعلام کنید. به جای قرار دادن پرس و جوهای فرعی در داخل یک دستور FROM، یک CTE مانند هر جدول دیگری نامگذاری و ارجاع داده می شود.

به عنوان مثال، یک پرس و جو که در ابتدا از یک پرس و جو فرعی در عبارت FROM برای فیلتر کردن مسابقات با 10 هدف یا بیشتر استفاده می کند، می تواند با استفاده از یک CTE بازنویسی شود:

WITH high_scoring_matches AS (
SELECT
m.id, m.country_id
FROM matches AS m
WHERE (m.home_goal + m.away_goal) >= 10
)
SELECT
c.name,
COUNT(hsm.id) AS high_scoring_matches_count
FROM high_scoring_matches AS hsm
JOIN country AS c ON hsm.country_id = c.id
GROUP BY c.name;

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

زمان استفاده از CTE

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

افکار نهایی

CTE ها ابزاری عالی برای تحلیلگران داده و توسعه دهندگان پایگاه داده هستند و راهی تمیز، قابل استفاده مجدد و قابل فهم برای مدیریت پرس و جوهای پیچیده SQL ارائه می دهند. آنها تعادلی بین سادگی و عملکرد ایجاد می کنند و تجزیه و تحلیل داده های مبتنی بر SQL را کارآمدتر می کنند.

Common Table Expressions (CTEs) یک ویژگی قدرتمند در SQL است که خوانایی و ساختار پرس و جوهای پیچیده را بهبود می بخشد و تجزیه و تحلیل داده ها را برای مدیریت و تفسیر آسان تر می کند. CTEها به ویژه برای تجزیه تبدیل داده‌های چند مرحله‌ای به قطعات ساده‌تر و مدولار مفید هستند و جایگزینی تمیزتر برای سوالات فرعی سنتی ارائه می‌دهند.

CTE چیست؟

یک CTE یک مجموعه نتایج موقتی است که شما در یک عبارت WITH تعریف می‌کنید و می‌توانید با کوئری اصلی زیر به آن ارجاع دهید. برخلاف پرسش‌های فرعی سنتی، CTE‌ها اغلب خواناتر هستند، زیرا به شما امکان می‌دهند نامی را به مجموعه نتایج اختصاص دهید و درک پرسش‌های پیچیده را آسان‌تر می‌کنند. نحو اصلی این است:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

مزایای استفاده از CTE

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

• عملکرد: CTE ها اغلب کارآمدتر از پرس و جوهای فرعی درون خطی هستند زیرا یک بار اجرا می شوند و در حافظه ذخیره می شوند. این بدان معنی است که اگر CTE را چندین بار در پرس و جو خود ارجاع دهید، نیازی به محاسبه مجدد آن در هر بار نیست.

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

مثال: استفاده از CTE برای تجزیه و تحلیل داده ها

فرض کنید می‌خواهید داده‌های فروش را تجزیه و تحلیل کنید و درآمد ماهانه را به همراه مجموع انباشته محاسبه کنید. استفاده از CTE فرآیند را ساده می کند:

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', sale_date) AS sale_month,
        SUM(amount) AS total_sales
    FROM sales
    GROUP BY sale_month
)
SELECT 
    sale_month,
    total_sales,
    SUM(total_sales) OVER (ORDER BY sale_month) AS cumulative_sales
FROM monthly_sales;

در این مثال، monthly_sales CTE، فروش در هر ماه را محاسبه می‌کند، و استفاده از مجموعه نتایج را در جستار نهایی برای محاسبه فروش تجمعی آسان‌تر می‌کند. این کار تکرار کد را کاهش می دهد و وضوح هر مرحله محاسبه را افزایش می دهد.

Refactoring سوالات فرعی با CTE

همانطور که با SQL کار می کنید، پرس و جوهای فرعی می توانند طولانی و پیچیده شوند و ردیابی هر قسمت از پرس و جو را دشوار می کند. CTE ها با استفاده از عبارت WITH، راهی برای مدیریت این پیچیدگی ارائه می دهند که به شما امکان می دهد پرس و جو فرعی را قبل از پرس و جو اصلی اعلام کنید. به جای قرار دادن پرس و جوهای فرعی در داخل یک دستور FROM، یک CTE مانند هر جدول دیگری نامگذاری و ارجاع داده می شود.

به عنوان مثال، یک پرس و جو که در ابتدا از یک پرس و جو فرعی در عبارت FROM برای فیلتر کردن مسابقات با 10 هدف یا بیشتر استفاده می کند، می تواند با استفاده از یک CTE بازنویسی شود:

WITH high_scoring_matches AS (
    SELECT 
        m.id, m.country_id
    FROM matches AS m
    WHERE (m.home_goal + m.away_goal) >= 10
)
SELECT 
    c.name, 
    COUNT(hsm.id) AS high_scoring_matches_count
FROM high_scoring_matches AS hsm
JOIN country AS c ON hsm.country_id = c.id
GROUP BY c.name;

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

زمان استفاده از CTE

• پرس و جوهای بازگشتی: CTE ها از بازگشت پشتیبانی می کنند و آنها را به ابزاری عالی برای جست و جوی داده های سلسله مراتبی مانند نمودارهای سازمانی یا ساختارهای درختی تبدیل می کند.

• شکستن پرس و جوهای پیچیده: هنگامی که با لایه های متعدد فیلتر، تجمع و تبدیل سروکار دارید، CTE ها به سازماندهی واضح این مراحل کمک می کنند.

• پاکسازی و تبدیل داده ها: CTE ها می توانند به پیش پردازش داده ها (مانند فیلتر کردن ورودی های نامعتبر) قبل از انجام تجزیه و تحلیل بیشتر کمک کنند.

افکار نهایی

CTE ها ابزاری عالی برای تحلیلگران داده و توسعه دهندگان پایگاه داده هستند و راهی تمیز، قابل استفاده مجدد و قابل فهم برای مدیریت پرس و جوهای پیچیده SQL ارائه می دهند. آنها تعادلی بین سادگی و عملکرد ایجاد می کنند و تجزیه و تحلیل داده های مبتنی بر SQL را کارآمدتر می کنند.