تبدو أول 70٪ من دورة SQL مباشرة جدًا. تبدأ الصعوبات على الـ 30٪ المتبقية.من عام 2015 إلى عام 2019 ، مررت بأربع مقابلات لشغل وظائف محلل بيانات وأخصائي تحليل بيانات في أكثر من اثني عشر شركة. بعد مقابلة أخرى فاشلة في عام 2017 - عندما كنت مرتبكًا حول أسئلة SQL المعقدة - بدأت في تجميع كتاب مهام مع أسئلة SQL ذات التعقيد المتوسط والعالي من أجل الاستعداد بشكل أفضل للمقابلات. لقد أصبح هذا الدليل مفيدًا في الجولة الأخيرة من المقابلات في عام 2019. خلال العام الماضي ، شاركت هذا الدليل مع اثنين من الأصدقاء ، وبفضل وقت الفراغ الإضافي بسبب الوباء ، قمت بتلميعه وجمع هذا المستند.هناك العديد من دروس SQL الرائعة للمبتدئين. المفضلة هيSQL التفاعلية كودكاديمي ل و حدد ستار SQL الدورات التي كتبها زي تشونغ كاو. ولكن في الواقع ، فإن أول 70 ٪ من دورة SQL بسيطة للغاية ، وتبدأ الصعوبات الحقيقية في الـ 30 ٪ المتبقية ، والتي لم يتم تغطيتها في أدلة المبتدئين. لذا ، في المقابلات التي أجراها محللو البيانات والمتخصصون في تحليل البيانات في شركات التكنولوجيا غالبًا ما يطرحون أسئلة حول هذه الـ 30٪.والمثير للدهشة ، أنني لم أجد مصدرًا شاملاً لهذه القضايا ذات الصعوبة المتوسطة ، لذلك قمت بتجميع هذا الدليل.إنها مفيدة لإجراء المقابلات ، ولكنها في الوقت نفسه ستزيد من فعاليتك في وظائفك الحالية والمستقبلية. أنا شخصياً أعتقد أن بعض قوالب SQL المذكورة مفيدة أيضًا لأنظمة ETL التي تقوم بتشغيل أدوات إعداد التقارير ووظائف تحليل البيانات لتحديد الاتجاهات.
عليك أن تفهم أنه أثناء المقابلات مع محللي البيانات ومحللي البيانات ، يطرحون أسئلة ليس فقط حول SQL. تشمل المواضيع الشائعة الأخرى مناقشة المشاريع السابقة ، واختبار A / B ، وتطوير المقاييس ، والمشكلات التحليلية المفتوحة. منذ حوالي ثلاث سنوات ، نشرت Quora نصائح حول إجراء المقابلات لوظيفة محلل منتجات على Facebook. هناك ، تمت مناقشة هذا الموضوع بمزيد من التفصيل. ومع ذلك ، إذا كان تحسين معرفتك بـ SQL سيساعدك في مقابلتك ، فإن هذا الدليل يستحق الوقت.في المستقبل ، يمكنني نقل الشفرة من هذا الدليل إلى موقع مثل Select Star SQLلتسهيل كتابة عبارات SQL - ومعرفة نتيجة تنفيذ التعليمات البرمجية في الوقت الحقيقي. كخيار ، أضف أسئلة كمشكلات إلى النظام الأساسي للتحضير لمقابلات LeetCode . في هذه الأثناء ، أردت فقط نشر هذا المستند حتى يتمكن الأشخاص الآن من التعرف على هذه المعلومات.الافتراضات التي تم إجراؤها وكيفية استخدام الدليل
افتراضات حول معرفة لغة SQL: من المفترض أن لديك معرفة عملية بـ SQL. ربما تستخدمه كثيرًا في العمل ، لكنك ترغب في صقل مهاراتك في مواضيع مثل الارتباطات الذاتية ووظائف النوافذ.كيفية استخدام هذا الدليل: نظرًا لأنه غالبًا ما يتم استخدام لوحة أو مفكرة افتراضية في المقابلة (بدون تجميع الرمز) ، أوصي بأخذ قلم رصاص وورقة وكتابة حلول لكل مشكلة ، وبعد الانتهاء ، قارن ملاحظاتك بالإجابات. أو ضع إجاباتك مع صديق يعمل كمحاور!- لا تهم الأخطاء اللغوية البسيطة كثيرًا خلال مقابلة مع السبورة أو المفكرة. ولكن يمكنهم تشتيت انتباه المحاور ، لذا حاول بشكل مثالي تقليل عددهم من أجل تركيز كل الاهتمام على المنطق.
- الإجابات المقدمة ليست بالضرورة الطريقة الوحيدة لحل كل مشكلة. لا تتردد في كتابة التعليقات مع حلول إضافية يمكنك إضافتها إلى هذا الدليل!
نصائح لحل المهام المعقدة في مقابلات SQL
أولاً ، نصائح قياسية لجميع مقابلات البرمجة ...- استمع بعناية إلى وصف المشكلة ، وكرر جوهر المشكلة للمحاور
- قم بصياغة حالة حدية لإثبات أنك تفهم المشكلة حقًا (على سبيل المثال ، سطر لن يتم تضمينه في استعلام SQL النهائي الذي ستكتبه)
- ( ) , — : ,
- SQL, , . , .
يتم تكييف بعض المشاكل المدرجة هنا من إدخالات مدونة Periscope القديمة (التي كتبها معظمها Sean Cook حوالي عام 2014 ، على الرغم من أنه يبدو أنه تم إزالة تأليفه من المواد بعد دمج SiSense مع Periscope ) ، وكذلك من المناقشات حول StackOverflow. إذا لزم الأمر ، يتم وضع علامة المصادر في بداية كل سؤال.على Select Star SQL هي أيضًا مجموعة جيدة من المسابقات الذهنية ، والقضايا التكميلية لهذا المستند.يرجى ملاحظة أن هذه الأسئلة ليست نسخًا حرفيًا من الأسئلة من المقابلات الخاصة بي ، ولم يتم استخدامها في الشركات التي عملت أو عملت فيها.مهام الارتباط الذاتي
رقم 1. النسبة المئوية للتغير من شهر لآخر
السياق: من المفيد غالبًا معرفة كيف يتغير مقياس رئيسي ، على سبيل المثال ، الجمهور الشهري للمستخدمين النشطين ، من شهر لآخر. لنفترض أن لدينا جدولًا logins
بهذا الشكل:| user_id | التاريخ |
| --------- | ------------ |
| 1 | 2018-07-01 |
| 234 | 2018-07-02 |
| 3 | 2018-07-02 |
| 1 | 2018-07-02 |
| ... | ... |
| 234 | 2018-10-04 |
الهدف : إيجاد النسبة المئوية للتغير الشهري في الجمهور الشهري للمستخدمين النشطين (MAU).الحل:(يحتوي هذا الحل ، مثل كتل التعليمات البرمجية الأخرى في هذا المستند ، على تعليقات حول عناصر بناء جملة SQL التي قد تختلف بين متغيرات SQL المختلفة وملاحظات أخرى)WITH mau AS
(
SELECT
DATE_TRUNC('month', date) month_timestamp,
COUNT(DISTINCT user_id) mau
FROM
logins
GROUP BY
DATE_TRUNC('month', date)
)
SELECT
a.month_timestamp previous_month,
a.mau previous_mau,
b.month_timestamp current_month,
b.mau current_mau,
ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change
FROM
mau a
JOIN
mau b ON a.month_timestamp = b.month_timestamp - interval '1 month'
رقم 2. وسم هيكل الشجرة
السياق: لنفترض أن لديك جدولًا tree
يحتوي على عمودين: يشير الأول إلى العقد ، والثاني إلى العقد الأصلية.العقدة الأم
12
2 5
3 5
4 3
5 فارغة
المهمة: اكتب SQL بطريقة تجعلنا نعيّن كل عقدة على أنها داخلية ، أو جذر ، أو ورقة أو ورقة ، بحيث نحصل على القيم المذكورة أعلاه لما يلي:node label
1 Leaf
2 Inner
3 Inner
4 Leaf
5 Root
(ملاحظة: يمكن العثور على مزيد من المعلومات حول مصطلحات بنية البيانات الشبيهة بالشجرة هنا . ومع ذلك ، ليست هناك حاجة لحل هذه المشكلة!)الحل:شكر وتقدير: اقترح فابيان هوفمان هذا الحل الأكثر عمومية في 2 مايو 2020. شكرا فابيانWITH join_table AS
(
SELECT
cur.node,
cur.parent,
COUNT(next.node) AS num_children
FROM
tree cur
LEFT JOIN
tree next ON (next.parent = cur.node)
GROUP BY
cur.node,
cur.parent
)
SELECT
node,
CASE
WHEN parent IS NULL THEN "Root"
WHEN num_children = 0 THEN "Leaf"
ELSE "Inner"
END AS label
FROM
join_table
حل بديل ، بدون اتصالات صريحة:شكر وتقدير: لفت وليام تشارجين في 2 مايو 2020 الانتباه إلى الحاجة إلى شرط WHERE parent IS NOT NULL
عودة هذا الحل Leaf
بدلاً من ذلك NULL
. شكرا لك ويليام!SELECT
node,
CASE
WHEN parent IS NULL THEN 'Root'
WHEN node NOT IN
(SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
END AS label
from
tree
رقم 3. الاحتفاظ المستخدم في الشهر (عدة أجزاء)
إقرار: تم تكييف هذه المهمة من مقالة مدونة SiSense ، "استخدام الارتباطات الذاتية لحساب الاحتفاظ والتدفق وإعادة التنشيط " .الجزء الأول
السياق: لنفترض أن لدينا إحصاءات حول تفويض المستخدم على موقع في الجدول logins
:| user_id | التاريخ |
| --------- | ------------ |
| 1 | 2018-07-01 |
| 234 | 2018-07-02 |
| 3 | 2018-07-02 |
| 1 | 2018-07-02 |
| ... | ... |
| 234 | 2018-10-04 |
المهمة: كتابة طلب يتلقى عدد المستخدمين المستبقين شهريًا. في حالتنا ، يتم تعريف هذه المعلمة على أنها عدد المستخدمين الذين قاموا بتسجيل الدخول إلى النظام في هذا الشهر والشهر السابق.القرار:SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT a.user_id) retained_users
FROM
logins a
JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
GROUP BY
date_trunc('month', a.date)
شكر وتقدير:أشار Tom Moertel إلى أن تكرار معرّف المستخدم قبل الانضمام الذاتي يجعل الحل أكثر كفاءة واقترح الكود أدناه. شكرا توم!حل بديل:WITH DistinctMonthlyUsers AS (
SELECT DISTINCT
DATE_TRUNC('MONTH', a.date) AS month_timestamp,
user_id
FROM logins
)
SELECT
CurrentMonth.month_timestamp month_timestamp,
COUNT(PriorMonth.user_id) AS retained_user_count
FROM
DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN
DistinctMonthlyUsers AS PriorMonth
ON
CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
AND
CurrentMonth.user_id = PriorMonth.user_id
الجزء 2
المهمة: الآن نأخذ المهمة السابقة لحساب عدد المستخدمين المستبقين شهريًا - ونقلبها رأسًا على عقب. سنكتب طلبًا لإحصاء المستخدمين الذين لم يعودوا إلى الموقع هذا الشهر. أي المستخدمين "الضائعين".القرار:SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT b.user_id) churned_users
FROM
logins a
FULL OUTER JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
WHERE
a.user_id IS NULL
GROUP BY
DATE_TRUNC('month', a.date)
يرجى ملاحظة أنه يمكن أيضًا حل هذه المشكلة مع LEFT
أو RIGHT
.الجزء 3
ملحوظة: ربما تكون هذه مهمة أكثر صعوبة مما سيتم تقديمه لك في مقابلة حقيقية. فكر في الأمر وكأنه لغز - أو يمكنك التخطي والانتقال إلى المهمة التالية.السياق : لذا قمنا بعمل جيد من مشكلتين سابقتين. بموجب شروط المهمة الجديدة ، لدينا الآن جدول بالمستخدمين الضائعين user_churns
. إذا كان المستخدم نشطًا في الشهر الماضي ، ولكنه لم يكن نشطًا في ذلك الوقت ، فسيتم إدخاله في الجدول لهذا الشهر. إليك ما يبدو عليه user_churns
:| user_id | month_date |
| --------- | ------------ |
| 1 | 2018-05-01 |
| 234 | 2018-05-01 |
| 3 | 2018-05-01 |
| 12 | 2018-05-01 |
| ... | ... |
| 234 | 2018-10-01 |
المهمة : الآن تريد إجراء تحليل جماعي ، أي تحليل لمجموع المستخدمين النشطين الذين أعيد تنشيطهم في الماضي . قم بإنشاء جدول مع هؤلاء المستخدمين. يمكنك استخدام الجداول user_churns
و إلى إنشاء فوج logins
. في Postgres ، يمكن الوصول إلى الطابع الزمني الحالي عبر current_timestamp
.القرار:WITH user_login_data AS
(
SELECT
DATE_TRUNC('month', a.date) month_timestamp,
a.user_id,
MAX(b.month_date) as most_recent_churn,
MAX(DATE_TRUNC('month', c.date)) as most_recent_active
FROM
logins a
JOIN
user_churns b
ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date
JOIN
logins c
ON a.user_id = c.user_id
AND
DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
WHERE
DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
GROUP BY
DATE_TRUNC('month', a.date),
a.user_id
HAVING
most_recent_churn > most_recent_active
رقم 4. زيادة المجموع
الإقرار: تم تكييف هذه المهمة من مقالة مدونة SiSense ، نمذجة التدفق النقدي في SQL .السياق: لنفترض أن لدينا جدولًا transactions
بهذا الشكل:| التاريخ | التدفق النقدي |
| ------------ | ----------- |
| 2018-01-01 | -1000 |
| 2018-01-02 | -100 |
| 2018-01-03 | 50 |
| ... | ... |
أين cash_flow
الإيرادات ناقص تكاليف كل يوم.الهدف: كتابة طلب للحصول على إجمالي تراكمي للتدفق النقدي كل يوم حتى تحصل في النهاية على جدول في هذا النموذج:| التاريخ | cumulative_cf |
| ------------ | --------------- |
| 2018-01-01 | -1000 |
| 2018-01-02 | -1100 |
| 2018-01-03 | -1050 |
| ... | ... |
القرار:SELECT
a.date date,
SUM(b.cash_flow) as cumulative_cf
FROM
transactions a
JOIN b
transactions b ON a.date >= b.date
GROUP BY
a.date
ORDER BY
date ASC
حل بديل باستخدام وظيفة النافذة (أكثر كفاءة!):SELECT
date,
SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf
FROM
transactions
ORDER BY
date ASC
رقم 5. المتوسط المتحرك
إقرار: تم تكييف هذه المهمة من مقالة مدونة SiSense ، Moving Averages في MySQL و SQL Server .ملاحظة: يمكن حساب المتوسط المتحرك بطرق مختلفة. هنا نستخدم المتوسط السابق. وبالتالي ، سيكون مقياس اليوم السابع من الشهر هو متوسط الأيام الستة السابقة ونفسه.السياق : لنفترض أن لدينا جدولًا signups
بهذا الشكل:| التاريخ | تسجيلات |
| ------------ | ---------- |
| 2018-01-01 | 10 |
| 2018-01-02 | 20 |
| 2018-01-03 | 50 |
| ... | ... |
| 2018-10-01 | 35 |
المهمة : كتابة طلب للحصول على متوسط متحرك لمدة 7 أيام للتسجيلات اليومية.القرار:SELECT
a.date,
AVG(b.sign_ups) average_sign_ups
FROM
signups a
JOIN
signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY
a.date
رقم 6. العديد من شروط الاتصال
إقرار: تم تكييف هذه المهمة من مقالة مدونة SiSense ، "تحليل بريدك الإلكتروني باستخدام SQL" .السياق: لنفترض أن جدولنا emails
يحتوي على رسائل البريد الإلكتروني المرسلة من العنوان zach@g.com
والمستلمة عليه:| معرف | الموضوع | من | إلى | الطابع الزمني |
| ---- | ---------- | -------------- | -------------- | --- ------------------ |
| 1 | يوسمايت | zach@g.com | thomas@g.com | 2018-01-02 12:45:03 |
| 2 | بيج سور | sarah@g.com | thomas@g.com | 2018-01-02 16:30:01 |
| 3 | يوسمايت | thomas@g.com | zach@g.com | 2018-01-02 16:35:04 |
| 4 | قيد التشغيل | jill@g.com | zach@g.com | 2018-01-03 08:12:45 |
| 5 | يوسمايت | zach@g.com | thomas@g.com | 2018-01-03 14:02:01 |
| 6 | يوسمايت | thomas@g.com | zach@g.com | 2018-01-03 15:01:05 |
| .. | .. | .. | .. | .. |
المهمة: اكتب طلبًا للحصول على وقت الاستجابة لكل حرف ( id
) تم إرساله إلى zach@g.com
. لا تقم بتضمين رسائل إلى عناوين أخرى. افترض أن كل موضوع لديه موضوع فريد. ضع في اعتبارك أن سلسلة المحادثات قد تحتوي على عدة رسائل ذهاب وعودة بين zach@g.com
المستلمين الآخرين.القرار:SELECT
a.id,
MIN(b.timestamp) - a.timestamp as time_to_respond
FROM
emails a
JOIN
emails b
ON
b.subject = a.subject
AND
a.to = b.from
AND
a.from = b.to
AND
a.timestamp < b.timestamp
WHERE
a.to = 'zach@g.com'
GROUP BY
a.id
مهام وظائف النافذة
رقم 1. ابحث عن المعرف مع القيمة القصوى
السياق: لنفترض أن لدينا جدولًا salaries
يحتوي على بيانات حول الأقسام ورواتب الموظفين بالصيغة التالية: اسم | empno | الراتب |
----------- + ------- + -------- +
تطوير | 11 | 5200 |
تطوير | 7 | 4200 |
تطوير | 9 | 4500 |
تطوير | 8 | 6000 |
تطوير | 10 | 5200 |
الأفراد | 5 | 3500 |
الأفراد | 2 | 3900 |
مبيعات | 3 | 4800 |
مبيعات | 1 | 5000 |
مبيعات | 4 | 4800 |
المهمة : كتابة طلب للحصول empno
على أعلى راتب. تأكد من أن الحل الخاص بك يعالج حالات الرواتب المتساوية!القرار:WITH max_salary AS (
SELECT
MAX(salary) max_salary
FROM
salaries
)
SELECT
s.empno
FROM
salaries s
JOIN
max_salary ms ON s.salary = ms.max_salary
حل بديل باستخدام RANK()
:WITH sal_rank AS
(SELECT
empno,
RANK() OVER(ORDER BY salary DESC) rnk
FROM
salaries)
SELECT
empno
FROM
sal_rank
WHERE
rnk = 1;
رقم 2. متوسط القيمة والترتيب مع وظيفة النافذة (عدة أجزاء)
الجزء الأول
السياق : لنفترض أن لدينا جدولًا salaries
بهذا التنسيق: اسم | empno | الراتب |
----------- + ------- + -------- +
تطوير | 11 | 5200 |
تطوير | 7 | 4200 |
تطوير | 9 | 4500 |
تطوير | 8 | 6000 |
تطوير | 10 | 5200 |
الأفراد | 5 | 3500 |
الأفراد | 2 | 3900 |
مبيعات | 3 | 4800 |
مبيعات | 1 | 5000 |
مبيعات | 4 | 4800 |
المهمة: كتابة استعلام يُرجع الجدول نفسه ، ولكن بعمود جديد يُظهر متوسط الراتب للقسم. نتوقع جدولاً مثل هذا: اسم | empno | الراتب | avg_salary |
----------- + ------- + -------- + ------------ +
تطوير | 11 | 5200 | 5020 |
تطوير | 7 | 4200 | 5020 |
تطوير | 9 | 4500 | 5020 |
تطوير | 8 | 6000 | 5020 |
تطوير | 10 | 5200 | 5020 |
الأفراد | 5 | 3500 | 3700 |
الأفراد | 2 | 3900 | 3700 |
مبيعات | 3 | 4800 | 4867 |
مبيعات | 1 | 5000 | 4867 |
مبيعات | 4 | 4800 | 4867 |
القرار:SELECT
*,
ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
salaries
الجزء 2
المهمة: اكتب استعلامًا يضيف عمودًا مع وضع كل موظف في الجدول الزمني بناءً على راتبه في قسمه ، حيث يحصل الموظف ذو الراتب الأعلى على المنصب 1. نتوقع جدولًا في هذا النموذج: اسم | empno | الراتب | المرتبات |
----------- + ------- + -------- + ------------- +
تطوير | 11 | 5200 | 2 |
تطوير | 7 | 4200 | 5 |
تطوير | 9 | 4500 | 4 |
تطوير | 8 | 6000 | 1 |
تطوير | 10 | 5200 | 2 |
الأفراد | 5 | 3500 | 2 |
الأفراد | 2 | 3900 | 1 |
مبيعات | 3 | 4800 | 2 |
مبيعات | 1 | 5000 | 1 |
مبيعات | 4 | 4800 | 2 |
القرار:SELECT
*,
RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
FROM
salaries
مهام أخرى ذات صعوبة متوسطة وعالية
رقم 1. الرسوم البيانية
السياق: لنفترض أن لدينا جدولًا يمثل sessions
فيه كل صف جلسة بث فيديو بطول بالثواني:| session_id | length_seconds |
| ------------ | ---------------- |
| 1 | 23 |
| 2 | 453 |
| 3 | 27 |
| .. | .. |
المهمة: اكتب استعلامًا لحساب عدد الجلسات التي تقع في فترات خمس ثوانٍ ، أي بالنسبة للجزء أعلاه ، ستكون النتيجة شيئًا كالتالي:| دلو | العد |
| --------- | ------- |
| 20-25 | 2 |
| 450-455 | 1 |
تحتسب الدرجة القصوى لتسميات الخطوط المناسبة ("5-10" ، إلخ.)الحل:WITH bin_label AS
(SELECT
session_id,
FLOOR(length_seconds/5) as bin_label
FROM
sessions
)
SELECT
CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket,
COUNT(DISTINCT session_id) count
GROUP BY
bin_label
ORDER BY
bin_label ASC
رقم 2. اتصال عبر (عدة أجزاء)
الجزء الأول
السياق: لنفترض أن لدينا جدولًا state_streams
حيث يُشار إلى اسم الولاية وإجمالي عدد ساعات البث من استضافة الفيديو في كل سطر:| الدولة | إجمالي_المسالك |
| ------- | --------------- |
| NC | 34569 |
| SC | 33999 |
| CA | 98324 |
| MA | 19345 |
| .. | .. |
(في الواقع ، في الجداول المجمعة من هذا النوع عادة ما يكون هناك أيضًا عمود تاريخ ، ولكننا سنستبعده لهذه المهمة)المهمة: اكتب استعلامًا للحصول على أزواج من الحالات مع إجمالي عدد سلاسل الرسائل في غضون ألف من بعضها البعض. بالنسبة للمقتطف أعلاه ، نود أن نرى شيئًا مثل:| state_a | state_b |
| --------- | --------- |
| NC | SC |
| SC | NC |
القرار:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a
CROSS JOIN
state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state
للحصول على معلومات ، يمكن أيضًا كتابة الصلات المشتركة دون تحديد صلات صراحة:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state
الجزء 2
ملاحظة: هذا سؤال إضافي وليس قالب SQL مهم حقًا. يمكنك تخطيه!المهمة: كيف يمكنني تعديل SQL من حل سابق لإزالة التكرارات؟ على سبيل المثال ، مثال نفس الجدول ، على البخار NC
وكان SC
هناك مرة واحدة فقط ، وليس مرتين.القرار:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state > b.state
رقم 3. الحسابات المتقدمة
شكر وتقدير: تم تكييف هذه المهمة من مناقشة حول سؤال طرحته على StackOverflow (اسمي المستعار هو zthomas.nc).ملحوظة: ربما تكون هذه مهمة أكثر صعوبة مما سيتم تقديمه لك في مقابلة حقيقية. فكر في الأمر وكأنه لغز - أو يمكنك تخطيه!السياق: لنفترض أن لدينا جدولًا من table
هذا النوع ، حيث user
يمكن أن تتوافق القيم المختلفة للفصل مع نفس المستخدم class
:| المستخدم | الفئة |
| ------ | ------- |
| 1 | أ |
| 1 | ب |
| 1 | ب |
| 2 | ب |
| 3 | أ |
المشكلة: افترض أن هناك قيمتان محتملتان فقط للفئة. اكتب استعلامًا لحساب عدد المستخدمين في كل فئة. في هذه الحالة، للمستخدمين مع كل من التسميات a
و b
يجب أن تشير إلى فئة b
.لعينتنا ، نحصل على النتيجة التالية:| الفئة | العد |
| ------- | ------- |
| أ | 1 |
| ب | 2 |
القرار:WITH usr_b_sum AS
(
SELECT
user,
SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
FROM
table
GROUP BY
user
),
usr_class_label AS
(
SELECT
user,
CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class
FROM
usr_b_sum
)
SELECT
class,
COUNT(DISTINCT user) count
FROM
usr_class_label
GROUP BY
class
ORDER BY
class ASC
يستخدم الحل البديل تعليمات SELECT
في عوامل التشغيل SELECT
و UNION
:SELECT
"a" class,
COUNT(DISTINCT user_id) -
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count
UNION
SELECT
"b" class,
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count