SQL: Три практически задачи с CROSS JOIN

Тази статия е посветена на един сравнително рядко използван начин за свързване на таблици – CROSS JOIN, но даващ в определени задачи изключително полезни възможности за комбиниране на данните от различни таблици.

В същност, когато говорим за CROSS JOIN, думите свързване на таблици не са съвсем точни. При CROSS JOIN, ако вземем две таблици, то всеки ред на първата таблица ще се комбинира с всички редове на другата таблица и дори не е необходимо двете таблици да имат обща колона както при другите връзки.

Нагледно резултът от CROSS JOIN може да се представи така:

Не е трудно да преценим, че в повечето ситуации подобна комбинация няма да има полезен смисъл. Например, ако в една таблица са клиентите, а в другата поръчки, то от комбинирането на всеки клиент с всяка една от поръчките едва ли ще можем да извлечем полезна информация.

Именно това свойство обаче, ни позволява в някои случаи да получим резултат, непостижим с други средства. Да разгледаме тези ситуации в три практичeски задачи.

Извеждане на всички дати между начална и крайна дата

В практиката се срешат задачи, при които е необходимо за събитие с начална и крайна дата да имаме в резултата и всичките дати между тях. Примери за подобни събития са:

• дата на поръчка и дата на доставката й
• дата на настаняване в хотел и освобождаване на стаята
• дата на отпътуване и пристигане и т.н.

При това детайлността на отчета не е задължително да бъде сведена до ниво дата. В зависимост от продължителността на събитието можем да работим в години (напр. годините между постъпването и напускането на служител) или в часове и минути.

Без значение каква е детайността, за да се получи отчета, в базата данни трябва да имаме таблица съдържаща нужните периоди от време. Например, ако са ни необходими всички дати между началната и крайната, то в таблицата с датите трябва да присъства
всички дати от периода.

Задача 1 с видео

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 е начинът да комбинираме всяка държава с всички продукти и след това да изведем и продажбите им със следната заявка:

Задача 2 с видео

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 представлява техника за комбиниране на редовете от първата заявка със стойностите получени във втората заявка (с един ред.)

Пример за подобно комбиниране е следващата заявка, с която се изчислява кой служител колко артикула е продал, колко поръчки е обслужил, каква е стойността на обслужениете поръчки и какъв процент представляват трите (артикули,поръчки, стойност) спрямо общия обем продадени артикули, поръчки и стойност.

Задача 3 с видео

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

Автор: Дикран Хачикян