این ۶ مفهوم چالش‌برانگیز SQL را برای مصاحبه بعدی خود یاد بگیرید

آیا برای یک مصاحبه شغلی در حوزه داده آماده می‌شوید؟ 🚀 سوالات SQL اغلب برای سنجش درک عمیق شما طراحی شده‌اند، نه فقط حافظه شما. مصاحبه‌کنندگان می‌خواهند ببینند شما چگونه با مسائل ظریف برخورد می‌کنید و چند مفهوم محبوب دارند که معمولاً کارجویان را به چالش می‌کشد. بیایید مطمئن شویم شما یکی از آنها نخواهید بود!

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

۱. توابع پنجره‌ای (WINDOW FUNCTIONS)

چرا دشوار است: بسیاری از کارجویان هدف توابعی مانند LEAD()، LAG() یا RANK() را حفظ می‌کنند اما مکانیک پارتیشن‌ها و مهم‌تر از آن، ترتیب‌دهی درون قاب پنجره را به طور کامل درک نمی‌کنند.

اشتباه رایج: متداول‌ترین خطا، حذف عبارت ORDER BY در بلوک OVER() است. بدون آن، پایگاه داده ردیف‌ها را به ترتیب دلخواه و غیرقطعی پردازش می‌کند. این بدان معناست که کوئری شما ممکن است بدون خطا اجرا شود (به‌ویژه در PostgreSQL) اما نتیجه‌ای نادرست یا متناقض تولید خواهد کرد.

مثال: کاربرانی را پیدا کنید که دومین خرید خود را ظرف ۷ روز پس از خرید قبلی انجام داده‌اند.

رویکرد نادرست:

WITH ordered_tx AS (
  SELECT user_id,
         created_at::date AS tx_date,
         LAG(created_at::DATE) OVER (PARTITION BY user_id) AS prev_tx_date
  FROM amazon_transactions
)
SELECT DISTINCT user_id
FROM ordered_tx
WHERE prev_tx_date IS NOT NULL AND tx_date - prev_tx_date <= 7;

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

راه‌حل صحیح:

WITH ordered_tx AS (
  SELECT user_id,
         created_at::date AS tx_date,
         LAG(created_at::DATE) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_tx_date
  FROM amazon_transactions
)
SELECT DISTINCT user_id
FROM ordered_tx
WHERE prev_tx_date IS NOT NULL AND tx_date - prev_tx_date <= 7;

با افزودن ORDER BY created_at، ما به صراحت به تابع می‌گوییم که مقدار را از ردیف بلافاصله قبلی به ترتیب زمانی پیدا کند.

۲. فیلتر کردن با توابع تجمعی (HAVING در مقابل WHERE)

چرا دشوار است: این موضوع افراد را گیج می‌کند زیرا ترتیب منطقی عملیات در یک کوئری SQL را فراموش می‌کنند: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.

اشتباه رایج: تلاش برای استفاده از یک تابع تجمعی (مانند SUM()، MIN()، AVG()) در عبارت WHERE. عبارت WHERE ردیف‌ها را قبل از گروه‌بندی و تجمیع فیلتر می‌کند، بنابراین مقدار تجمعی هنوز وجود ندارد.

مثال: درآمد کل کارخانه‌های شرابی را پیدا کنید که کمترین امتیاز برای هر یک از شراب‌هایشان ۹۰ یا بیشتر است.

رویکرد نادرست:

SELECT winery, SUM(price) AS total_revenue
FROM winemag_p1
WHERE MIN(points) >= 90
GROUP BY winery;

این کوئری خطا خواهد داد. شما نمی‌توانید از MIN() در WHERE استفاده کنید.

راه‌حل صحیح:

SELECT winery, SUM(price) AS total_revenue
FROM winemag_p1
GROUP BY winery
HAVING MIN(points) >= 90;

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

۳. اتصال به خود (SELF-JOINS) برای مقایسه‌ها

چرا دشوار است: ایده اتصال یک جدول به خودش در نگاه اول شهودی نیست، بنابراین کارجویان اغلب به سراغ راه‌حل‌های پیچیده‌تر مانند زیرکوئری‌های همبسته می‌روند.

اشتباه رایج: پیچیده کردن یک کوئری با زیرکوئری‌های تو در تو در حالی که یک SELF JOIN ساده‌تر، خواناتر و اغلب کارآمدتر خواهد بود، به‌ویژه برای مقایسه‌های مبتنی بر زمان.

مثال: تغییر نرخ ارز را بین ۱ ژانویه ۲۰۲۰ و ۱ ژوئیه ۲۰۲۰ نشان دهید.

رویکرد پیچیده:

SELECT 
    jan_rates.source_currency,
    (SELECT exchange_rate 
     FROM sf_exchange_rate 
     WHERE source_currency = jan_rates.source_currency AND date = '2020-07-01') - jan_rates.exchange_rate AS difference
FROM 
    (SELECT source_currency, exchange_rate FROM sf_exchange_rate WHERE date = '2020-01-01') AS jan_rates;

راه‌حل صحیح:

SELECT 
    jan.source_currency,
    jul.exchange_rate - jan.exchange_rate AS difference
FROM sf_exchange_rate AS jan
JOIN sf_exchange_rate AS jul ON jan.source_currency = jul.source_currency
WHERE jan.date = '2020-01-01' AND jul.date = '2020-07-01';

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

۴. زیرکوئری‌ها در مقابل عبارات جدول مشترک (CTEs)

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

اشتباه رایج: ایجاد کوئری‌های عمیقاً تودرتو در حالی که یک سری CTE منطق را متوالی، ماژولار و آسان برای اشکال‌زدایی می‌کند.

مثال: پیدا کردن برترین بازیگران بر اساس میانگین امتیاز فیلم‌هایشان در ژانری که بیشترین حضور را در آن داشته‌اند. منطق پیچیده و چندلایه است. یک راه‌حل با استفاده از زیرکوئری‌های تودرتو به یک کابوس ناخوانا با منطق تکراری تبدیل می‌شود. یک رویکرد مبتنی بر CTE مسئله را به مراحل قابل مدیریت و نام‌گذاری شده تقسیم می‌کند و کوئری نهایی را ساده و واضح می‌سازد.

راه‌حل صحیح (مفهومی):

WITH genre_counts AS (
    -- ابتدا، شمارش فیلم‌ها برای هر بازیگر در هر ژانر
), max_genre_counts AS (
    -- سپس، پیدا کردن حداکثر شمارش برای هر بازیگر
), top_genres AS (
    -- فیلتر کردن برای ژانرهایی که با حداکثر شمارش مطابقت دارند
), ranked_actors AS (
    -- رتبه‌بندی بازیگران در آن ژانرها بر اساس میانگین امتیاز
)
SELECT ... FROM ranked_actors WHERE rank <= 3;

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

۵. مدیریت مقادیر NULL در منطق

چرا دشوار است: NULL نشان‌دهنده عدم وجود یک مقدار است، نه خود یک مقدار. بنابراین، مانند مقادیر دیگر رفتار نمی‌کند. NULL با هیچ چیز برابر نیست، حتی با یک NULL دیگر.

اشتباه رایج: استفاده از = NULL به جای IS NULL برای فیلتر کردن. یک اشتباه دیگر فراموش کردن این است که چگونه NULLهای حاصل از یک OUTER JOIN می‌توانند منطق را مختل کرده یا منجر به نتایج ناقص شوند که نیازمند توابعی مانند COALESCE() است.

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

رویکرد نادرست:

SELECT i.customer_id, i.total_interactions, c.total_content_items
FROM interactions_summary AS i
FULL OUTER JOIN content_summary AS c ON i.customer_id = c.customer_id;

اگر یک مشتری در یک جدول وجود داشته باشد اما در دیگری نه، customer_id او در یکی از ستون‌های انتخاب شده NULL خواهد بود و شمارش او به جای 0، NULL خواهد بود.

راه‌حل صحیح:

SELECT 
    COALESCE(i.customer_id, c.customer_id) AS customer_id,
    COALESCE(i.total_interactions, 0) AS total_interactions,
    COALESCE(c.total_content_items, 0) AS total_content_items
FROM interactions_summary AS i
FULL OUTER JOIN content_summary AS c ON i.customer_id = c.customer_id;

COALESCE به زیبایی NULLها را مدیریت می‌کند، اولین مقدار غیر NULL را برای شناسه برمی‌گرداند و شمارش‌های NULL را با 0 جایگزین می‌کند.

۶. حذف موارد تکراری مبتنی بر گروه (Group-Based Deduplication)

چرا دشوار است: وظیفه انتخاب یک ردیف خاص از هر گروه است (مثلاً جدیدترین تراکنش برای هر مشتری)، اما یک GROUP BY استاندارد ردیف‌ها را فشرده می‌کند و شما را ملزم به تجمیع تمام ستون‌های دیگر می‌کند.

اشتباه رایج: استفاده از GROUP BY و سپس افزودن LIMIT 1. این کار ردیف برتر کل مجموعه نتایج را پیدا می‌کند، نه ردیف برتر درون هر گروه.

مثال: پرفروش‌ترین کالا را برای هر ماه پیدا کنید.

رویکرد نادرست:

SELECT DATE_PART('MONTH', invoicedate) AS sale_month, description, SUM(unitprice * quantity) AS total_paid
FROM online_retail
GROUP BY sale_month, description
ORDER BY total_paid DESC
LIMIT 1;

این فقط پرفروش‌ترین کالا را در تمام ماه‌ها برمی‌گرداند.

راه‌حل صحیح:

WITH monthly_sales AS (
  SELECT 
    DATE_PART('month', invoicedate) AS month,
    description,
    SUM(unitprice * quantity) AS total_paid,
    RANK() OVER (PARTITION BY DATE_PART('month', invoicedate) ORDER BY SUM(unitprice * quantity) DESC) AS rnk
  FROM online_retail
  GROUP BY month, description
)
SELECT month, description, total_paid
FROM monthly_sales
WHERE rnk = 1;

استفاده از RANK() پارتیشن‌بندی شده بر اساس ماه، به درستی کالای برتر را در هر گروه ماهانه شناسایی می‌کند.

نتیجه‌گیری

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

منبع: kdnuggets.com