7 ключови умения за ефективна работа с SQL

Езикът SQL e мощен и гъвкав инструмент с широко приложение в анализа на данни и подготовката на отчети. За ефективното му използване в процеса на анализ на данни ключова роля играят седем основни умения описани в тази статия.

1. Извличане на данни

Извличането на данни от таблиците е основна (а често и единствена) операция за всички анализатори на данни независимо от областта, в която работят, целите и задачите, които стоят пред тях.

Извличането на данни се извършва със SELECT заявка като тази заявка изисква много добро познаване както на SQL (по сравнение с другите DML заявки) така и на модела на базата данни.

SELECT
    product_name
,   unit_price
,   quantity_per_unit
,   units_in_stock
FROM
    products
ORDER BY
    unit_price DESC

2. Филтриране на данни

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

За филтриране на данните в SELECT има специална клауза – WHERE, в която се поставят критериите за извличане на данни. Умението точно да се състави WHERE клаузата и правилно да се свържат критериите позволява да се получат в резултата 100% от необходимите данни.

SELECT
    product_name
,   unit_price
,   quantity_per_unit
,   units_in_stock
FROM
    products
WHERE
    unit_price > 20
    AND
    REGEXP_LIKE(quantity_per_unit, 'tins|jars|bottles')
ORDER BY
    unit_price DESC

3. Заявки по няколко таблици

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

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

SELECT
    t1.product_name
,   t1.unit_price
,   t1.quantity_per_unit
,   t2.quantity
,   t3.order_date
FROM
    products t1
        INNER JOIN
    order_details t2
        ON t2.product_id = t1.product_id
        INNER JOIN
    orders t3
        ON t2.order_id = t3.order_id
WHERE
    t1.unit_price > 20
    AND
    REGEXP_LIKE(t1.quantity_per_unit, 'tins|jars|bottles')
    AND
    t3.order_date BETWEEN '2015-01-01' AND '2015-12-31' 
ORDER BY
    t1.unit_price DESC

4. Трансформации и изчисления с данни

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

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

SELECT
    t1.product_name
,   REGEXP_SUBSTR(t1.quantity_per_unit, 'tins|jars|bottles') pkg
,   t2.quantity * t1.unit_price sale_price
,   EXTRACT( MONTH FROM t3.order_date) month
FROM
    products t1
        INNER JOIN
    order_details t2
        ON t2.product_id = t1.product_id
        INNER JOIN
    orders t3
        ON t2.order_id = t3.order_id
WHERE
    t1.unit_price > 20
    AND
    REGEXP_LIKE(t1.quantity_per_unit, 'tins|jars|bottles')
    AND
    EXTRACT(YEAR FROM t3.order_date) = 2015 
ORDER BY
    t1.unit_price DESC

5. Изчисления с групиране на данни

Групирането на данни в комбинация с подходящи агрегиращи функции позволява индивидуалните резултати да се обобщят. Клаузата, с която данните се групират е GROUP BY като най-важният момент в този процес е правилният избор на колони, по които да се извърши групирането.

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

SELECT
    EXTRACT( MONTH FROM t3.order_date) month
,   REGEXP_SUBSTR(t1.quantity_per_unit, 'tins|jars|bottles') pkg
,   SUM(t2.quantity * t1.unit_price) sale_price
FROM
    products t1
        INNER JOIN
    order_details t2
        ON t2.product_id = t1.product_id
        INNER JOIN
    orders t3
        ON t2.order_id = t3.order_id
WHERE
    t1.unit_price > 20
    AND
    REGEXP_LIKE(t1.quantity_per_unit, 'tins|jars|bottles')
    AND
    EXTRACT(YEAR FROM t3.order_date) = 2015 
GROUP BY
    EXTRACT( MONTH FROM t3.order_date)
,   REGEXP_SUBSTR(t1.quantity_per_unit, 'tins|jars')
ORDER BY
    month
,   pkg

6. Вложени заявки

В различните клаузи на SELECT заявка могат да се влагат други заявки. В зависимост от точката на влагане (SELECT, FROM или WHERE) вложената заявка решава различни задачи.

Например вложената във FROM клаузата заявка се явява като източник на данни, с който могат да се извършат допълнителни изчисления. В SQL за да се улесни влагането на заявки във FROM е добавена и специална клауза – WITH, която значително улеснява този процес.

WITH
    pkg_sales
    AS (SELECT
            EXTRACT( MONTH FROM t3.order_date) month
        ,   REGEXP_SUBSTR(t1.quantity_per_unit, 'tins|jars|bottles') pkg
        ,   SUM(t2.quantity * t1.unit_price) sale_price
        FROM
            products t1
                INNER JOIN
            order_details t2
                ON t2.product_id = t1.product_id
                INNER JOIN
            orders t3
                ON t2.order_id = t3.order_id
        WHERE
            t1.unit_price > 20
            AND
            REGEXP_LIKE(t1.quantity_per_unit, 'tins|jars|bottles')
            AND
            EXTRACT(YEAR FROM t3.order_date) = 2015 
        GROUP BY
            EXTRACT( MONTH FROM t3.order_date)
        ,   REGEXP_SUBSTR(t1.quantity_per_unit, 'tins|jars|bottles'))
SELECT
    ps.month
,   ROUND(AVG(ps.sale_price)) avg_sales
FROM
    pkg_sales ps
GROUP BY
    ps.month
ORDER BY
    month

7. Изчисления с аналитични функции

Аналитичните функции в SQL, по начина си на действие, са специална група функции. При изпълнението на заявката те се прилагат към получения резултат и това позволява лесно да се извършват действия като например изчисления с данни намиращи се на различни редове. За подобни изчисления с класическите средства са необходими сложни заявки.

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

WITH
    pkg_sales
    AS (SELECT
            EXTRACT( MONTH FROM t3.order_date) month
        ,   SUM(t2.quantity * t1.unit_price) sale_price
        FROM
            products t1
                INNER JOIN
            order_details t2
                ON t2.product_id = t1.product_id
                INNER JOIN
            orders t3
                ON t2.order_id = t3.order_id
        WHERE
            t1.unit_price > 20
            AND
            REGEXP_LIKE(t1.quantity_per_unit, 'tins|jars|bottles')
            AND
            EXTRACT(YEAR FROM t3.order_date) = 2015 
        GROUP BY
            EXTRACT( MONTH FROM t3.order_date))
SELECT
    ps.month
,   ps.sale_price
,   LAG(ps.sale_price, 1)
        OVER(
            ORDER BY ps.month
        ) pm_sales
FROM
    pkg_sales ps

Искате да научите повече за SQL?

Включете се в курса по анализ на данни с SQL.

Научете повече

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