آموزش هوش تجاری از ۰ تا ۱۰۰

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

یک عبارت جدول رایج یا CTE یک مجموعه نتیجه با نام موقت است که از یک دستور SELECT ساده ایجاد شده است که می تواند در دستور SELECT بعدی استفاده شود. هر SQL CTE مانند یک پرس و جو با نام است که نتیجه آن در یک جدول مجازی (یک CTE) ذخیره می شود تا بعداً در پرس و جو اصلی به آن ارجاع داده شود.

بهترین راه برای یادگیری عبارات رایج جدول از طریق تمرین است. من دوره پرس و جوهای بازگشتی تعاملی LearnSQL.com را توصیه می کنم. این شامل بیش از 100 تمرین است که CTE ها را با اصول اولیه و پیشرفت به موضوعات پیشرفته مانند عبارات جدول رایج بازگشتی آموزش می دهد.

CTE ها به ساده سازی پرس و جوها کمک می کنند
بیایید با نحو یک عبارت جدول مشترک شروع کنیم.

با my_cte AS (
  a,b,c را انتخاب کنید
  از T1
)
a,c را انتخاب کنید
از my_cte
جایی که ....
نام این CTE my_cte است و کوئری CTE SELECT a,b,c از T1 است. CTE با کلمه کلیدی WITH شروع می شود، پس از آن نام CTE خود را مشخص می کنید، سپس محتوای پرس و جو را در پرانتز مشخص می کنید. پرس و جو اصلی بعد از پرانتز بسته می شود و به CTE اشاره می کند. در اینجا، پرس و جو اصلی (همچنین به عنوان پرس و جو خارجی شناخته می شود) SELECT a,c FROM my_cte WHERE ... است.

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

در این مقاله مقدماتی عالی در مورد CTE، نمونه‌های سطح ورودی زیادی وجود دارد. سایر مقالات مقدماتی عبارتند از: «بهبود خوانایی پرس و جو با عبارات رایج جدول» و «چه زمانی باید از عبارت جدول رایج (CTE) استفاده کنم؟» که عبارات جدول رایج را توضیح می دهند.

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

تعداد اقلام تاریخ شعبه فروشنده مقدار واحد_قیمت
Paris-1 2021-12-07 چارلز هدفون A2 1 80
London-1 2021-12-06 John Cell Phone X2 2 120
London-2 2021-12-07 Mary Headphones A1 1 60
Paris-1 2021-12-07 Charles Battery Charger 1 50
London-2 2021-12-07 مری تلفن همراه B2 2 90
London-1 2021-12-07 John Headphones A0 5 75
London-1 2021-12-07 Sean Cell Phone X1 2 100
در مثال اول، گزارشی با همان رکوردها از جدول فروش به دست می آوریم اما یک ستون اضافی با قیمت گران ترین کالای فروخته شده در همان شعبه در آن روز اضافه می کنیم. برای به دست آوردن قیمت گران ترین کالا، از عبارت جدول رایج مانند زیر استفاده می کنیم:

با بالاترین AS (
  انتخاب کنید
    شاخه،
    تاریخ،
    MAX(unit_price) AS بالاترین_قیمت
  از فروش
  گروه به شاخه، تاریخ
)
انتخاب کنید
  حراجی.*،
  h.highest_price
از فروش
به بالاترین h بپیوندید
  ON sales.branch = h.branch
    AND sales.date = h.date
این کوئری یک SQL CTE به نام بالاترین را تعریف می کند که نتیجه آن یک جدول مجازی ایجاد می کند. جدول مجازی دارای ستون های شاخه، تاریخ و بالاترین_قیمت است که به ترتیب شامل نام شعبه، تاریخ و بالاترین قیمت واحد فروخته شده در آن روز در این شعبه است.

سپس کوئری بیرونی اجرا می شود که از بالاترین جدول مجازی مانند یک جدول معمولی استفاده می کند. در نهایت با جدول فروش به نتیجه بالاترین CTE می‌پیوندیم.

نتیجه کل پرس و جو در زیر نشان داده شده است:

تاریخ شعبه فروشنده مقدار واحد_قیمت بالاترین_ قیمت
Paris-1 2021-12-07 چارلز هدفون A2 1 80 80
London-1 2021-12-06 John Cell Phone X2 2 120 120
London-2 2021-12-07 Mary Headphones A1 1 60 90
Paris-1 2021-12-07 Charles Battery Charger 1 50 80
London-2 2021-12-07 مری تلفن همراه B2 2 90 90
London-1 2021-12-07 John Headphones A0 5 75 100
London-1 2021-12-07 Sean Cell Phone X1 2 100 100
در مثال بعدی، گزارشی با بیشترین درآمد روزانه به تفکیک شعب تهیه می کنیم.

WITH daily_revenue AS (
  انتخاب کنید
    شاخه،
    تاریخ،
    جمع (قیمت_واحد * مقدار) به عنوان درآمد_روزانه
  از فروش
  WHERE EXTRACT (سال از تاریخ) = 2021
  گروه 1،2
)
انتخاب کنید
  شاخه،
  MAX(درآمد_روزانه) حداکثر_درآمد_روزانه
از روزانه_درآمد
گروه 1
سفارش با 2 تخفیف
در این پرس و جو، یک CTE به نام daily_revenue دارای ستون های شاخه، تاریخ، و daily_revenue برای هر تاریخ در سال 2021 است. سپس در پرس و جو بیرونی، بالاترین میزان درآمد را برای هر شعبه در سال 2021 به دست می آوریم. این گزارش بر اساس max_daily_revenue به ترتیب نزولی مرتب می شود. .

در زیر نتایج این پرس و جو آورده شده است.

شعبه حداکثر_درآمد_روزانه
لندن-1 575
لندن-2 240
پاریس-1 135
دوره پرس و جوهای بازگشتی تعاملی ما را امتحان کنید. 114 تمرین عملی برای کمک به شما در مقابله با این مفهوم پیشرفته!

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

فرض کنید گزارشی با کل درآمد ماهانه در لندن در سال 2021 می‌خواهیم، ​​اما درآمد هر شعبه در لندن را نیز در همان گزارش می‌خواهیم. در اینجا، ما دو CTE ایجاد می کنیم و سپس آنها را در پرس و جو اصلی می پیوندیم.

با london1_monthly_revenue AS (
  انتخاب کنید
    EXTRACT (MONTH FROM تاریخ) به عنوان ماه،
    SUM(unit_price * مقدار) درآمد AS
  از فروش
  WHERE EXTRACT (سال از تاریخ) = 2021
    AND شاخه = 'London-1'
  گروه 1
)
london2_monthly_revenue AS (
  انتخاب کنید
    EXTRACT (MONTH FROM تاریخ) به عنوان ماه،
    SUM(unit_price * مقدار) درآمد AS
  از فروش
  WHERE EXTRACT (سال از تاریخ) = 2021
    AND شعبه = 'London-2'
  گروه 1
)
انتخاب کنید
  l1 ماه،
  l1.revenue + l2.revenue AS london_revenue,
  l1.revenue AS london1_revenue,
  l2.revenue AS london2_revenue
از london1_monthly_revenue l1, london2_monthly_revenue l2
WHERE l1.month = l2.month
در پرس و جوی بالا، دو CTE، london1_monthly_revenue و london2_monthly_revenue تعریف می کنیم تا درآمد ماهانه در سال 2021 را برای هر شعبه در لندن به دست آوریم. در نهایت با استفاده از ستون ماه به هر دو CTE ملحق می‌شویم و کل درآمد لندن را با جمع کردن درآمدهای دو شعبه محاسبه می‌کنیم.

نتیجه پرس و جو به شرح زیر است:

ماه london_revenue london1_revenue london2_revenue
11 755 575 180
12 1055 815 240
در مثال زیر، گزارشی به دست می‌آوریم تا به هر شعبه از تاریخ فروخته شدن بزرگترین بلیط (یعنی مقدار ترکیب کالا-مقدار) و مقدار این بلیط اطلاع دهیم. برای انجام این کار، باید یک CTE ایجاد کنیم که بلیط ها را (موقعیت ستون، رتبه بندی است) برای هر شعبه بر اساس مبلغ بلیط رتبه بندی می کند.

با بلیط AS (
  متمایز را انتخاب کنید
    شاخه،
    تاریخ،
    واحد_قیمت * مقدار AS بلیط_مبلغ،
    ROW_NUMBER() بیش از (
      پارتیشن به شاخه
      سفارش بر اساس واحد_قیمت * مقدار DESC
    ) موقعیت AS
  از فروش
  سفارش با 3 تخفیف
)
انتخاب کنید
  شاخه،
  تاریخ،
  بلیط_مبلغ
از بلیط
موقعیت WHERE = 1
در کوئری بالا، یک CTE با شاخه ستون، تاریخ، بلیط_مبلغ و موقعیت ایجاد می کنیم. سپس در پرس و جو بیرونی، ما فقط برای آن رکوردهایی با موقعیت = 1 فیلتر می کنیم تا آنچه را که می خواهیم به دست آوریم، بزرگترین بلیط به شاخه.

نتیجه پرس و جو در زیر نشان داده شده است:

تاریخ شعبه بلیط_مبلغ
London-1 2021-11-2 450
London-2 2021-11-1 270
Paris-1 2021-12-7 80
CTE های تودرتو در پرس و جوهای SQL

مثال بعدی یک CTE تو در تو را نشان می دهد. ایده این است که گزارشی با قیمت تمام اقلام بیش از 90 دلار و تعداد این اقلام فروخته شده توسط شعبه London-2 تهیه شود.

با بیش از_90_ آیتم AS (
  متمایز را انتخاب کنید
    مورد،
    قیمت واحد
  از فروش
  WHERE واحد_قیمت>=90
)
london2_over_90 AS (
  انتخاب کنید
    o90.item،
    o90.unit_price,
    coalesce(SUM(s.quantity)، 0) as total_sold
  از بیش از 90_ آیتم o90
  LEFT JOIN فروش s
  ON o90.item = s.item AND s.branch = 'London-2'
  GROUP BY o90.item, o90.unit_price
)
انتخاب مورد، واحد_قیمت، کل_فروش
از london2_over_90;
اولین CTE over_90_items است که تمام اقلامی را با قیمت بیشتر یا مساوی 90 دلار انتخاب می کند. دومین CTE london2_over_90 است که مقدار فروخته شده توسط London-2 را برای هر کالای موجود در بیش از 90_ آیتم انتخاب می کند. این پرس و جو دارای یک CTE تو در تو است - FROM را در CTE دوم با اشاره به اولی توجه کنید. ما از فروش LEFT JOIN استفاده می کنیم زیرا لندن-2 ممکن است همه موارد را در بیش از 90_ آیتم فروخته باشد.

نتیجه پرس و جو این است:

واحد کالا_قیمت کل_فروخته شد
تلفن همراه X1 100 0
تلفن همراه X2 120 0
تلفن همراه B2 90 7
قبل از رفتن به بخش بعدی، چند مقاله در مورد عبارات رایج جدول پیشنهاد می کنم. هر دو "SQL CTEs توضیح داده شده با مثال" و "از کجا می توانم تمرین های خوب SQL CTE را پیدا کنم؟" مثال ها و تمرین های زیادی داشته باشید.

پرس و جوهای بازگشتی و عبارات جدول رایج
در پایگاه‌های داده رابطه‌ای، داشتن جداول نشان دهنده سلسله مراتب داده‌ها مانند مدیر-کارمند، بخش جزئی یا والد-فرزند معمول است. برای پیمایش این سلسله مراتب در هر جهت (از بالا به پایین یا از پایین به بالا)، پایگاه های داده از ساختاری به نام CTE های بازگشتی استفاده می کنند.

بازگشتی یک کلمه رزرو شده برای تعریف CTE برای عبور از یک ساختار داده بازگشتی است. شکل کوئری بازگشتی به این صورت است:

با بازگشتی cte_name AS (
     CTE_query_definition -- عبارت پرس و جو غیر بازگشتی
اتحاد همه
     CTE_query_definition - عبارت جستجوی بازگشتی
)
SELECT * FROM cte_name;
پرس و جوهای بازگشتی خارج از محدوده این مقاله مقدماتی هستند، اما من سه مورد دیگر را برای کسانی که می خواهند درباره این موضوع بیشتر می خواهند پیشنهاد کنم: "چگونه پرس و جوهای SQL را با CTE سازماندهی کنیم"، "این کار را در SQL انجام دهید: پیمایش درخت بازگشتی SQL" و "با قدرت پرس و جوهای بازگشتی SQL آشنا شوید." آنها پرس و جوهای بازگشتی را با مثال های فراوان به تفصیل توضیح می دهند.

SQL CTE ها یک منبع قدرتمند هستند

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

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

از دوره تعاملی با رتبه برتر ما، SQL Recursive Queries بیاموزید و یک متخصص حرفه ای SQL شوید!

همچنین، آهنگ Advanced SQL را بررسی کنید، جایی که شما فراتر از اصول اولیه می روید تا به یک استاد SQL تبدیل شوید. اگر نیاز به آماده شدن برای مصاحبه SQL دارید، یک مقاله جالب با عنوان "5 سوال برتر مصاحبه SQL CTE" وجود دارد که عبارات جدول رایج را از منظر دیگری مورد بحث قرار می دهد.

اگر نحوه استفاده از CTE ها را یاد گرفته اید، یک قدم جلوتر به سمت توسعه دهنده SQL هستید. حفظ رشد!