תרגיל זה הוא ברמת קושי קלה ומתרגל אלמנטים חיבור Left join , Inner join ו full join בשפת SQL
לעמוד התרגילים המלא לחצו כאן.
התרגיל נכתב ע"י יובל מרנין.
לפניך חלק מטבלת customers מתוך דאטה-בייס בשם e_commerce
| avg_order | sum_orders | orders | last_order | first_order | country | customer_id |
|---|---|---|---|---|---|---|
| 214.3831564674 | 4,073.2799728811 | 19 | 2011-12-05 | 2011-02-25 | United Kingdom | 16,592 |
| 390.2108135815 | 1,447,682.1183872335 | 3,710 | 2011-12-09 | 2010-12-01 | Unspecified | [NULL] |
| 112.7099992633 | 450.8399970531 | 4 | 2011-11-29 | 2011-05-04 | United Kingdom | 14,173 |
| 282.9699971676 | 2,263.7599773407 | 8 | 2011-11-06 | 2011-01-17 | United Kingdom | 13,527 |
| 187.3499979824 | 374.6999959648 | 2 | 2011-10-07 | 2011-09-04 | United Kingdom | 14,067 |
| 744.7739968061 | 3,723.8699840307 | 5 | 2011-09-05 | 2011-02-03 | Spain | 12,502 |
| 1,013.0099925995 | 1,013.0099925995 | 1 | 2011-11-10 | 2011-11-10 | United Kingdom | 17,197 |
| 153.1199979782 | 153.1199979782 | 1 | 2011-10-12 | 2011-10-12 | France | 12,602 |
| 595.4976872068 | 7,741.4699336886 | 13 | 2011-03-17 | 2010-12-01 | United Kingdom | 13,093 |
| 435.968750298 | 3,487.7500023842 | 8 | 2011-10-12 | 2010-12-03 | Switzerland | 13,520 |
| 84.7033323348 | 254.1099970043 | 3 | 2011-11-10 | 2011-06-16 | United Kingdom | 14,765 |
| 382.7999964952 | 382.7999964952 | 1 | 2011-11-15 | 2011-11-15 | United Kingdom | 18,181 |
| 472.7816674709 | 2,836.6900048256 | 6 | 2011-10-12 | 2011-04-01 | United Kingdom | 14,852 |
| 324.6285712378 | 2,272.3999986649 | 7 | 2011-11-10 | 2010-12-02 | United Kingdom | 14,466 |
| 206.9799995422 | 206.9799995422 | 1 | 2011-01-24 | 2011-01-24 | United Kingdom | 15,820 |
| 382.6699956506 | 765.3399913013 | 2 | 2011-07-31 | 2011-06-21 | United Kingdom | 17,695 |
| 271.0454526706 | 2,981.4999793768 | 11 | 2011-12-01 | 2011-01-13 | United Kingdom | 15,687 |
| 90.7249996662 | 362.8999986649 | 4 | 2011-11-10 | 2011-09-07 | United Kingdom | 17,573 |
| 155.1700005531 | 155.1700005531 | 1 | 2011-01-13 | 2011-01-13 | United Kingdom | 15,447 |
| 591.8291273726 | 13,612.06992957 | 23 | 2011-12-08 | 2010-12-09 | Germany | 12,621 |
לטבלה customers יש טבלאות אחיות בשם: e_commerce.customers_2011_jan ו e_commerce.customers_2011_feb שמתיחסות רק ללקוחות שרכשו בחודשים ינואר ופברואר.
- הסירו מטבלת customers את הלקוחות שרכשו בחודש ינואר.
טבלת הלקוחות שרכשו בינואר היא – e_commerce.customers_2011_jan - מטבלת הלקוחות השאירו רק את הלקוחות שרכשו בינואר
* בטבלה שיצרתם הוסיפו את סכום הרכישה בינואר.
* הוסיפו שדה עם החלק היחסי של הרכישות בינואר מכלל הרכישה של הלקוח. - חברו את טבלאות customers_2011_jan ו- customers_2011_feb ב full join.
* חשבו שדה של מספר הרכישות שהיו בינואר ושדה של מספר הרכישות בפברואר. אם לא היו רכישות רשמו 0.
* הוסיפו שדה בשם compare שבו יהיה רשום היכן יש יותר רכישות – JAN או FEB (אם מספר הרכישות שווה, רישמו EQUAL).
תשובה תרגיל 1
select
customers.*,
from
e_commerce.customers as customers
left join
e_commerce.customers_2011_jan as jan
on jan.customer_id = customers.customer_id
where
jan.sum_orders is not null
תשובה תרגיל 2
select
customers.*,
jan.sum_orders as jan_sum_orders,
jan.sum_orders/(case when customers.sum_orders=0 then 0.000000001 else customers.sum_orders end) as share_of_jan
from
e_commerce.customers as customers
inner join
e_commerce.customers_2011_jan as jan
on jan.customer_id = customers.customer_id
תשובה תרגיל 3
select
coalesce (jan.customer_id ,feb.customer_id) as customer_id,
coalesce (jan.orders ,0) as jan_orders,
coalesce (feb.orders ,0) as jan_orders,
case
when coalesce (jan.orders ,0)>coalesce(feb.orders ,0) then 'jan'
when coalesce (jan.orders ,0)<coalesce(feb.orders ,0) then 'feb'
when coalesce (jan.orders ,0)=coalesce(feb.orders ,0) then 'equal'
end as compare
from
e_commerce.customers_2011_jan as jan
full join
e_commerce.customers_2011_feb as feb
on jan.customer_id =feb.customer_id
