Автор: Дикран Хачикян
Тази статия е посветена на един сравнително рядко използван начин за свързване на таблици – 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ски задачи.
Извеждане на всички дати между начална и крайна дата
В практиката се срешат задачи, при които е необходимо за събитие с начална и крайна дата да имаме в резултата и всичките дати между тях. Примери за подобни събития са:
- дата на поръчка и дата на доставката й
- дата на настаняване в хотел и освобождаване на стаята
- дата на отпътуване и пристигане и т.н.
При това детайлността на отчета не е задължително да бъде сведена до ниво дата. В зависимост от продължителността на събитието можем да работим в години (напр. годините между постъпването и напускането на служител) или в часове и минути.
Без значение каква е детайността, за да се получи отчета, в базата данни трябва да имаме таблица съдържаща нужните периоди от време. Например, ако са ни необходими всички дати между началната и крайната, то в таблицата с датите трябва да присъства
всички дати от периода.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 е начинът да комбинираме всяка държава с всички продукти и след това да изведем и продажбите им със следната заявка:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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 представлява техника за комбиниране на редовете от първата заявка със стойностите получени във втората заявка (с един ред.)
Пример за подобно комбиниране е следващата заявка, с която се изчислява кой служител колко артикула е продал, колко поръчки е обслужил, каква е стойността на обслужениете поръчки и какъв процент представляват трите (артикули,поръчки, стойност) спрямо общия обем продадени артикули, поръчки и стойност.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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
