Тази статия е посветена на един сравнително рядко използван начин за свързване на таблици – CROSS JOIN
В същност, когато говорим за CROSS JOIN`, думите свързване на таблици не са съвсем точни. При `CROSS JOIN
Нагледно резултът от CROSS JOIN
| firstname |
|---|
| Anna |
| Markus |
| country |
|---|
| Germany |
| France |
| Argentina |
| firstname | country |
|---|---|
| Anna | Germany |
| Anna | France |
| Anna | Argentina |
| Markus | Germany |
| Markus | France |
| Markus | Argentina |
Не е трудно да преценим, че в повечето ситуации подобна комбинация няма да има полезен смисъл. Например, ако в една таблица са клиентите, а в другата поръчки, то от комбинирането на всеки клиент с всяка една от поръчките едва ли ще можем да извлечем полезна информация.
Именно това свойство обаче, ни позволява в някои случаи да получим резултат, непостижим с други средства. Да разгледаме тези ситуации в три практичeски задачи.
Извеждане на всички дати между начална и крайна дата
В практиката се срешат задачи, при които е необходимо за събитие с начална и крайна дата да имаме в резултата и всичките дати между тях. Примери за подобни събития са:
- дата на поръчка и дата на доставката й
- дата на настаняване в хотел и освобождаване на стаята
- дата на отпътуване и пристигане и т.н.
При това детайлността на отчета не е задължително да бъде сведена до ниво дата. В зависимост от продължителността на събитието можем да работим в години (напр. годините между постъпването и напускането на служител) или в часове и минути.
Без значение каква е детайността, за да се получи отчета, в базата данни трябва да имаме таблица съдържаща нужните периоди от време. Например, ако са ни необходими всички дати между началната и крайната, то в таблицата с датите трябва да присъства
всички дати от периода.
WITH ords AS (SELECT t1.order_id
, t1.order_date
, t1.shipped_date
FROM orders t1
WHERE
order_date BETWEEN TO_DATE('2015-01-01','RRRR-MM-DD') AND
TO_DATE('2015-01-31','RRRR-MM-DD')
AND
shipped_date IS NOT NULL)
SELECT r.order_id
, r.order_date
, r.shipped_date
, c.the_date
FROM ords r CROSS JOIN calendar c
WHERE r.order_date <= c.the_date
AND
r.shipped_date >= c.the_date
ORDER BY 1Продажби на продукти по държави
Необходимо ни е да направим отчет за държавите и стойността и/или количествата на продажбите на продуктите за всяка държава като в резултата искаме да видим и онези продукти, които не са продавани в дадена държава.
CROSS JOIN е начинът да комбинираме всяка държава с всички продукти и след това да изведем и продажбите им със следната заявка:
WITH plist AS (SELECT DISTINCT t1.country
, t2.product_id
, t2.product_name
FROM customers t1
CROSS JOIN
products t2 )
, psales AS (SELECT t1.country
, t3.product_id
, SUM(t3.quantity) sum_quantity
, SUM(t3.quantity * t3.unit_price) sum_price
FROM customers t1
INNER JOIN
orders t2
ON t2.customer_id = t1.customer_id
INNER JOIN
order_details t3
ON t3.order_id = t2.order_id
GROUP BY t1.country
, t3.product_id)
SELECT p.country
, p.product_name
, NVL(s.sum_quantity,0) sum_qnt
, NVL(s.sum_price,0) sum_price
FROM plist p
LEFT OUTER JOIN
psales s
ON p.product_id = s.product_id
AND
p.country = s.country
ORDER BY 1, 4 DESCИзчисления с проценти
Друга типична типична ситуация, при която CROSS JOIN е добро решение, възниква в задачите, при които с редовете от една заявка трябва да се извършат изчисления със стойности от друга заявка, която връща един ред. В този случай, CROSS JOIN представлява техника за комбиниране на редовете от първата заявка със стойностите получени във втората заявка (с един ред.)
Пример за подобно комбиниране е следващата заявка, с която се изчислява кой служител колко артикула е продал, колко поръчки е обслужил, каква е стойността на обслужениете поръчки и какъв процент представляват трите (артикули,поръчки, стойност) спрямо общия обем продадени артикули, поръчки и стойност.
WITH emps AS (SELECT t1.firstname || ' ' || t1.lastname Employee
, COUNT(DISTINCT t3.product_id) cnt_items
, COUNT(DISTINCT t2.order_id ) cnt_orders
, SUM(t3.unit_price * t3.quantity) sum_orders
FROM employees t1
INNER JOIN
orders t2
ON t2.employee_id = t1.employee_id
INNER JOIN
order_details t3
ON t3.order_id = t2.order_id
GROUP BY t1.firstname || ' ' || t1.lastname)
, sums AS (SELECT COUNT(DISTINCT product_id) all_items
, COUNT(DISTINCT order_id) all_orders
, SUM(unit_price * quantity ) grnd_total
FROM order_details)
SELECT p.employee
, p.cnt_items
, TO_CHAR(p.cnt_items/s.all_items * 100,'999.99') perc_items
, p.cnt_orders
, TO_CHAR( p.cnt_orders / s.all_orders * 100, '999.99') perc_orders
, p.sum_orders
, TO_CHAR(p.sum_orders / s.grnd_total * 100,'999.99') perc_total
FROM emps p CROSS JOIN sums s
ORDER BY 6 DESCРазгледаните задачи не изчерпват всички случаи, в които CROSS JOIN може да ни окаже ограмна помощ в подготовката на отчети. Надявам се, стъпвайки на разгледаните задачи, самостоятелно да отриете и други полезни приложения на CROSS JOIN.
Искате да научите повече за заявките в SQL?
Включете се в предстоящия курс Анализ на данни и изготвяне на отчети с SQL
Автор: Дикран Хачикян