Главная страница
Навигация по странице:

  • Общие табличные выражения (CTE)

  • Trip_candidates

    		•

    лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас


    Скачать 7.88 Mb.
    НазваниеУчебник по sql если вы хотите узнать, что такое sql этот сайт для вас
    Анкорлекция
    Дата21.12.2022
    Размер7.88 Mb.
    Формат файлаpdf
    Имя файлаMoiseenko_SQL.pdf
    ТипУчебник
    #857633
    страница35 из 47
    1   ...   31   32   33   34   35   36   37   38   ...   47
    col
    value
    a
    1
    b
    2

    c
    -1
    Остался последний шаг, о котором многие забывают, решая задачи на сайте sql-ex.ru
    . А именно, обратное преобразование. Вместо этого пытаются подобрать такое значение, которое позволило бы "удовлетворить" систему проверки. Иногда это получается, например, если сравнение NULL и '' (пустой строки) оценивается на сайте как true. Но понятно, что на это полагаться не стоит. Итак, обратное пребразование:
    1.
    WITH
    utest
    AS
    2.
    (
    SELECT
    1
    a,
    2
    b, COALESCE
    (
    CAST
    (
    NULL
    AS
    INT
    )
    ,
    -1
    )
    c
    )
    3.
    SELECT
    col, NULLIF
    (
    value,
    -1
    )
    value
    FROM
    utest
    4.
    UNPIVOT
    (
    5. value
    FOR
    col
    IN
    (
    a, b, c
    )
    6.
    )
    AS
    unpvt;
    col
    value
    a
    1
    b
    2
    c
    NULL
    Здесь как нельзя более кстати пришлась функция
    NULLIF
    CROSSTAB
    в
    PostgreSQL
    Повернуть таблицу в PostgreSQL можно при помощи функции CROSSTAB. Эта функция принимает в качестве текстового параметра SQL-запрос, который возвращает 3 столбца:


    идентификатор строки - т.е. этот столбец содержит значения, определяющие результирующую
    (повернутую) строку;

    категорию - уникальные значения из этого столбца образуют столбцы повернутой таблицы. Нужно отметить, что в отличие от
    PIVOT
    сами значения роли не играют; важно лишь их количество, которое определяет максимально допустимое количество столбцов;

    значение категории - собственно значения категорий. Размещение значений по столбцам производится слева направо, и имена категорий роли не играют, а только их порядок, определяемый сортировкой запроса.
    Поясним сказанное на примере базы данных "
    Окраска ".
    Давайте для каждого квадрата просуммируем
    количество краски каждого цвета:
    1.
    SELECT
    b_q_id, v_color,
    SUM
    (
    b_vol
    )
    qty
    FROM
    utb
    JOIN
    utv
    ON
    b_v_id = v_id
    2.
    WHERE
    b_q_id
    BETWEEN
    12
    AND
    16 3.
    GROUP
    BY
    b_q_id, v_color
    4.
    ORDER
    BY
    b_q_id,
    CASE
    v_color
    WHEN
    'R'
    THEN
    1
    WHEN
    'G'
    THEN
    2
    ELSE
    3
    END
    ;
    Здесь мы ограничились только квадратами с номерами в диапазоне 12-16, чтобы, с одной стороны, уменьшить вывод, а, с другой стороны, сделать вывод презентативным. Сортировка по цветам выполнена в порядке RGB. Вот результат:
    b_q_id
    v_color
    Qty
    12
    R
    255
    12
    G
    255
    12
    B
    255

    13
    B
    123
    14
    R
    50
    14
    B
    111
    15
    R
    100
    15
    G
    100
    16
    G
    100
    16
    B
    150
    В терминологии CROSSTAB номера баллонов являются идентификаторами строк, а цвета - категориями. Результат поворота должен быть следующим:
    square
    R
    G
    B
    12
    255 255 255
    13
    123
    14
    50 111
    15
    100 100
    16
    100 150
    Теперь с помощью CROSSTAB попытаемся написать запрос, который бы дал требуемый результат:
    1.
    SELECT
    *
    FROM

    2. crosstab
    (
    3. $$select b_q_id, v_color,
    SUM
    (
    b_vol
    )
    qty
    FROM
    utb
    JOIN
    utv
    ON
    b_v_id = v_id
    4.
    WHERE
    b_q_id
    BETWEEN
    12
    AND
    16 5.
    GROUP
    BY
    b_q_id, v_color
    6.
    ORDER
    BY
    b_q_id,
    CASE
    v_color
    WHEN
    'R'
    THEN
    1
    WHEN
    'G'
    THEN
    2
    ELSE
    3
    END
    ; $$
    7.
    )
    AS
    ct
    (
    square int,
    "R"
    bigint,
    "G"
    bigint,
    "B"
    bigint
    )
    8.
    ORDER
    BY
    square;
    Здесь мы должны перечислить список столбцов с указанием их типа. При этом столбцы категорий могут быть перечислены не все. Посмотрим на результат (вы можете проверять запросы в консоли
    , выбрав для исполнения
    PostgreSQL
    square
    R
    G
    B
    12
    255 255 255
    13
    123
    14
    50 111
    15
    100 100
    16
    100 150
    Этот результат не вполне совпадает с ожидаемым. Напомним, что здесь важен только порядок. Если квадрат окрашивался только одним цветом, то значение (суммарный объем краски) попадет в первую категорию (у нас она называется R), каким бы этот единственный цвет ни был. Давайте перепишем запрос таким образом, чтобы он давал значения для всех цветов причем в нужном порядке. При этом отсутствующий цвет будем заменять NULL- значением. Чтобы добиться этого, добавим для каждого квадрата по одной строке каждого цвета со значением объема краски равным NULL:

    1.
    SELECT
    b_q_id, v_color,
    SUM
    (
    b_vol
    )
    qty
    FROM
    (
    2.
    SELECT
    b_q_id, v_color, b_vol
    FROM
    utb
    3.
    JOIN
    utv
    ON
    b_v_id = v_id
    4.
    UNION
    ALL
    --вот эта добавка
    5.
    SELECT
    *
    FROM
    (
    SELECT
    DISTINCT
    b_q_id
    FROM
    utb
    )
    X
    6.
    CROSS
    JOIN
    (
    SELECT
    'R'
    color,
    NULL
    ::smallint vol
    7.
    UNION
    ALL
    SELECT
    'G'
    ,
    NULL
    UNION
    ALL
    SELECT
    'B'
    ,
    NULL
    )
    Y
    8.
    )
    X
    9.
    WHERE
    b_q_id
    BETWEEN
    12
    AND
    16 10.
    GROUP
    BY
    b_q_id, v_color
    11.
    ORDER
    BY
    b_q_id,
    CASE
    v_color
    WHEN
    'R'
    THEN
    1
    WHEN
    'G'
    THEN
    2
    ELSE
    3
    END
    ;
    b_q_id
    v_color
    Qty
    12
    R
    255
    12
    G
    255
    12
    B
    255
    13
    R
    13
    G
    13
    B
    123
    14
    R
    50
    14
    G
    14
    B
    111
    15
    R
    100
    15
    G
    100
    15
    B
    16
    R

    16
    G
    100
    16
    B
    150
    Теперь ниже представленный запрос даст требуемый результат.
    1.
    SELECT
    *
    FROM
    2. crosstab
    (
    3. $$select b_q_id, v_color,
    SUM
    (
    b_vol
    )
    qty
    FROM
    (
    4.
    SELECT
    b_q_id, v_color, b_vol
    FROM
    utb
    5.
    JOIN
    utv
    ON
    b_v_id = v_id
    6.
    UNION
    ALL
    7.
    SELECT
    *
    FROM
    (
    SELECT
    DISTINCT
    b_q_id
    FROM
    utb
    )
    X
    8.
    CROSS
    JOIN
    (
    SELECT
    'R'
    color,
    NULL
    ::smallint vol
    9.
    UNION
    ALL
    SELECT
    'G'
    ,
    NULL
    UNION
    ALL
    SELECT
    'B'
    ,
    NULL
    )
    Y
    10.
    )
    X
    11.
    WHERE
    b_q_id
    BETWEEN
    12
    AND
    16 12.
    GROUP
    BY
    b_q_id, v_color
    13.
    ORDER
    BY
    b_q_id,
    CASE
    v_color
    WHEN
    'R'
    THEN
    1
    WHEN
    'G'
    THEN
    2
    ELSE
    3
    END
    ;$$
    14.
    )
    AS
    ct
    (
    square int,
    "R"
    bigint,
    "G"
    bigint,
    "B"
    bigint
    )
    15.
    ORDER
    BY
    square;
    Наверное, у вас уже возник вопрос: а нельзя ли это сделать как-нибудь проще?
    Ответ положительный. Оказывается, у функции CROSSTAB есть второй необязательный параметр - запрос, возвращающий список категорий в том же порядке, в котором выводятся столбцы. Тогда первый запрос, чтобы он давал правильный результат, мы можем переписать следующим образом:
    1.
    SELECT
    *
    FROM
    2. crosstab
    (

    3. $$select b_q_id, v_color,
    SUM
    (
    b_vol
    )
    qty
    FROM
    utb
    JOIN
    utv
    ON
    b_v_id = v_id
    4.
    WHERE
    b_q_id
    BETWEEN
    12
    AND
    16 5.
    GROUP
    BY
    b_q_id, v_color
    6.
    ORDER
    BY
    b_q_id,
    CASE
    v_color
    WHEN
    'R'
    THEN
    1
    WHEN
    'G'
    THEN
    2
    ELSE
    3
    END
    ; $$,
    7. $$select
    'R'
    UNION
    ALL
    SELECT
    'G'
    UNION
    ALL
    SELECT
    'B'
    ;$$
    8.
    )
    AS
    ct
    (
    square int,
    "R"
    bigint,
    "G"
    bigint,
    "B"
    bigint
    )
    9.
    ORDER
    BY
    square;
    Поскольку PostgreSQL допускает использование конструктора таблиц, запрос
    1.
    SELECT
    'R'
    UNION
    ALL
    SELECT
    'G'
    UNION
    ALL
    SELECT
    'B'
    ; можно заменить на более короткий:
    1.
    VALUES
    (
    'R'
    )
    ,
    (
    'G'
    )
    ,
    (
    'B'
    )
    ;
    Если вы при выполнении запроса получаете ошибку, что функции crosstab не существует, это означает, что у вас не установлен модуль tablefunc. Он устанавливается простой командой (начиная с версии 9.1)
    1.
    CREATE
    EXTENSION
    IF
    NOT
    EXISTS
    tablefunc; для конкретной базы данных. Для версий, предшествующих 9.1, достаточно загрузить в pgadmin следующий файл share/contrib/tablefunc.sql и выполнить его.

    Общие табличные
    выражения (CTE)
    Чтобы выяснить назначение общих табличных выражений, давайте начнем с примера.
    Найти максимальную сумму
    прихода/расхода среди всех 4-х таблиц
    базы данных "Вторсырье", а также тип
    операции, дату и пункт приема, когда и
    где она была зафиксирована.
    Задачу можно решить, например, следующим способом.
    1.
    SELECT
    inc
    AS
    max_sum, type, date, point
    2.
    FROM
    (
    SELECT
    inc,
    'inc'
    type, date, point
    3.
    FROM
    Income
    UNION
    ALL
    SELECT
    inc,
    'inc'
    type, date, point
    4.
    FROM
    Income_o
    5.
    UNION
    ALL
    6.
    SELECT
    out,
    'out'
    type, date, point
    7.
    FROM
    Outcome_o
    8.
    UNION
    ALL
    9.
    SELECT
    out,
    'out'
    type, date, point
    FROM
    Outcome
    )
    X
    10.
    WHERE
    inc >=
    ALL
    (
    SELECT
    inc
    FROM
    Income
    11.
    UNION
    ALL
    12.
    SELECT
    inc
    FROM
    Income_o
    13.
    UNION
    ALL
    SELECT
    out
    FROM
    Outcome_o
    14.
    UNION
    ALL
    SELECT
    out
    FROM
    Outcome
    )
    ;
    Здесь мы сначала объединяем всю имеющуюся информацию, а затем выбираем только те строки, у которых сумма не меньше, чем каждая из сумм той же выборки из 4-х таблиц.
    Фактически, мы дважды написали код объединений четырех таблиц. Как избежать этого? Можно создать представление, а затем адресовать запрос уже к нему:
    1.
    CREATE
    VIEW
    Inc_Out
    AS
    2.
    SELECT
    inc,
    'inc'
    type, date, point
    3.
    FROM
    Income

    4.
    UNION
    ALL
    5.
    SELECT
    inc,
    'inc'
    type, date, point
    6.
    FROM
    Income_o
    7.
    UNION
    ALL
    8.
    SELECT
    out,
    'out'
    type, date, point
    9.
    FROM
    Outcome_o
    10.
    UNION
    ALL
    11.
    SELECT
    out,
    'out'
    type,date, point
    12.
    FROM
    Outcome;
    13.
    GO
    14.
    SELECT
    inc
    AS
    max_sum, type, date, point
    15.
    FROM
    Inc_Out
    WHERE
    inc >=
    ALL
    (
    SELECT
    inc
    FROM
    Inc_Out
    )
    ;
    Так вот, CTE играет роль представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы. Предыдущий вариант решения можно переписать с помощью CTE следующим образом:
    1.
    WITH
    Inc_Out
    AS
    (
    2.
    SELECT
    inc,
    'inc'
    type, date, point
    3.
    FROM
    Income
    4.
    UNION
    ALL
    5.
    SELECT
    inc,
    'inc'
    type, date, point
    6.
    FROM
    Income_o
    7.
    UNION
    ALL
    8.
    SELECT
    out,
    'out'
    type, date, point
    9.
    FROM
    Outcome_o
    10.
    UNION
    ALL
    11.
    SELECT
    out,
    'out'
    type,date, point
    FROM
    Outcome
    )
    12.
    SELECT
    inc
    AS
    max_sum, type, date, point
    13.
    FROM
    Inc_Out
    WHERE
    inc >=
    ALL
    (
    SELECT
    inc
    FROM
    Inc_Out
    )
    ;
    Как видите, все аналогично использованию представления за исключением обязательных скобок, ограничивающих запрос; формально, достаточно лишь заменить CREATE VIEW на WITH. Как и для представления, в скобках после имени CTE может быть указан список столбцов, если нам потребуется включить их не все из подлежащего запроса и/или переименовать. Например,

    (я добавил дополнительно определение минимальной суммы в предыдущий запрос),
    1.
    WITH
    Inc_Out
    (
    m_sum, type, date, point
    )
    AS
    (
    2.
    SELECT
    inc,
    'inc'
    type, date, point
    3.
    FROM
    Income
    4.
    UNION
    ALL
    5.
    SELECT
    inc,
    'inc'
    type, date, point
    6.
    FROM
    Income_o
    7.
    UNION
    ALL
    8.
    SELECT
    out,
    'out'
    type, date, point
    9.
    FROM
    Outcome_o
    10.
    UNION
    ALL
    11.
    SELECT
    out,
    'out'
    type,date, point
    FROM
    Outcome
    )
    12.
    SELECT
    'max'
    min_max,*
    FROM
    Inc_Out
    13.
    WHERE
    m_sum >=
    ALL
    (
    SELECT
    m_sum
    FROM
    Inc_Out
    )
    14.
    UNION
    ALL
    15.
    SELECT
    'min'
    , *
    FROM
    Inc_Out
    16.
    WHERE
    m_sum <=
    ALL
    (
    SELECT
    m_sum
    FROM
    Inc_Out
    )
    ;
    Общие табличные выражения позволяют существенно уменьшить объем кода, если многократно приходится обращаться к одним и тем же производным таблицам.
    Заметим, что CTE могут использоваться не только с оператором SELECT, но и с другими операторами языка DML. Давайте решим такую задачу:
    Пассажиров рейса 7772 от 11 ноября 2005 года требуется
    отправить другим ближайшим рейсом, вылетающим позже в тот
    же день в тот же пункт назначения.
    Т.е. эта задача на обновление записей в таблице Pass_in_trip. Я не буду приводить здесь решение этой задачи, которое не использует CTE, но вы можете сами это сделать, чтобы сравнить объемы кода двух решений.
    Предлагаю пошагово строить решение и представлять результаты в виде запросов на выборку, чтобы вы могли контролировать результаты, выполняя эти запросы онлайн. Поскольку операторы модификации данных пока
    запрещены на сайте, я приведу окончательное решение лишь в самом конце.
    Начнем с таблицы, которую нужно будет обновить:
    1.
    WITH
    Trip_for_replace
    AS
    (
    2.
    SELECT
    *
    FROM
    Pass_in_trip
    3.
    WHERE
    trip_no=
    7772
    AND
    date=
    '20051129'
    )
    4.
    SELECT
    *
    FROM
    Trip_for_replace;
    Поскольку CTE играют роль представлений, то их можно в принципе использовать для обновления данных. Слова «в принципе» означают, что CTE является обновляемым, если выполняются определенные условия, аналогичные условиям обновления представлений. В частности, в определении должна использоваться только одна таблица без группировки и вычисляемых столбцов. Отметим, что необходимые условия в нашем случае выполнены.
    Теперь нам нужна информация о рейсе 7772 для того, чтобы найти ближайший к нему подходящий рейс. Добавим еще одно CTE в определение:
    1.
    WITH
    Trip_for_replace
    AS
    (
    2.
    SELECT
    *
    FROM
    Pass_in_trip
    3.
    WHERE
    trip_no=
    7772
    AND
    date=
    '20051129'
    )
    ,
    4. Trip_7772
    AS
    (
    SELECT
    *
    FROM
    Trip
    WHERE
    trip_no=
    7772
    )
    5.
    SELECT
    *
    FROM
    Trip_7772;
    Обратите внимание, что в одном запросе можно определить любое количество общих табличных выражений. И что особенно важно, CTE может включать ссылку на другое CTE, чем мы, собственно, сейчас и воспользуемся
    (обратите внимание на ссылку Trip_7772 в определении Trip_candidates).
    1.
    WITH
    Trip_for_replace
    AS
    (
    2.
    SELECT
    *
    FROM
    Pass_in_trip
    3.
    WHERE
    trip_no=
    7772
    AND
    date=
    '20051129'
    )
    ,
    4. Trip_7772
    AS
    (
    SELECT
    *
    FROM
    Trip
    WHERE
    trip_no=
    7772
    )
    ,
    5. Trip_candidates
    AS
    (
    SELECT
    Trip.*
    6.
    FROM
    Trip, Trip_7772

    7.
    WHERE
    Trip.town_from+Trip.town_to
    =
    Trip_7772.town_from +
    8.
    Trip_7772.town_to
    AND
    Trip.time_out
    >
    Trip_7772.time_out
    )
    9.
    SELECT
    *
    FROM
    Trip_candidates;
    Trip_candidates – это табличное выражение, которое определяет кандидатов на замену, а именно, рейсы, которые вылетают позже, чем 7772, и которые совершаются между теми же городами. Я использую конкатенацию строк town_from+town_to, чтобы не писать отдельные критерии для пункта отправления и места назначения.
    Найдем теперь среди строк-кандидатов наиболее близкий по времени рейс:
    1.
    WITH
    Trip_for_replace
    AS
    (
    2.
    SELECT
    *
    FROM
    Pass_in_trip
    3.
    WHERE
    trip_no=
    7772
    AND
    date=
    '20051129'
    )
    ,
    4. Trip_7772
    AS
    (
    SELECT
    *
    FROM
    Trip
    WHERE
    trip_no=
    7772
    )
    ,
    5. Trip_candidates
    AS
    (
    SELECT
    Trip.*
    FROM
    Trip, Trip_7772 6.
    WHERE
    Trip.town_from+Trip.town_to = Trip_7772.town_from
    +
    7.
    Trip_7772.town_to
    AND
    Trip.time_out
    >
    Trip_7772.time_out
    )
    ,
    8. Trip_replace
    AS
    (
    9.
    SELECT
    *
    FROM
    Trip_candidates
    10.
    WHERE
    time_out <=
    ALL
    (
    SELECT
    time_out
    FROM
    Trip_candidates
    )
    )
    11.
    SELECT
    *
    FROM
    Trip_replace;
    Теперь нам осталось последний оператор SELECT заменить на UPDATE, чтобы решить задачу:
    1.
    WITH
    Trip_for_replace
    AS
    (
    2.
    SELECT
    *
    FROM
    Pass_in_trip
    3.
    WHERE
    trip_no=
    7772
    AND
    date=
    '20051129'
    )
    ,
    4. Trip_7772
    AS
    (
    SELECT
    *
    FROM
    Trip
    WHERE
    trip_no=
    7772
    )
    ,
    5. Trip_candidates
    AS
    (

    6.
    SELECT
    Trip.*
    FROM
    Trip, Trip_7772 7.
    WHERE
    Trip.town_from+Trip.town_to = Trip_7772.town_from
    +
    8.
    Trip_7772.town_to
    AND
    Trip.time_out
    >
    Trip_7772.time_out
    )
    ,
    9. Trip_replace
    AS
    (
    SELECT
    *
    FROM
    Trip_candidates
    10.
    WHERE
    time_out <=
    ALL
    (
    SELECT
    time_out
    FROM
    Trip_candidates
    )
    )
    11.
    UPDATE
    Trip_for_replace
    SET
    trip_no =
    (
    SELECT
    trip_no
    FROM
    Trip_replace
    )
    ;
    Здесь мы исходим из довольно естественного предположения о том, что между заданными городами нет двух рейсов, которые бы отправлялись в одно и то же время в одном направлении. В противном случае, понадобился бы дополнительный критерий для отбора единственного рейса, т.к. наша цель – обновление данных, а не представление всех возможных кандидатов на замену.
    С использованием
    CTE
    с оператором DELETE вы можете познакомиться на примере удаления дубликатов строк из таблицы
    Запрос
    , который мы использовали для удаления дубликатов в SQL Server
    1.
    WITH
    CTE
    AS
    (
    2.
    SELECT
    name, ROW_NUMBER
    ()
    OVER
    (
    PARTITION
    BY
    name
    ORDER
    BY
    name
    )
    rnk
    3.
    FROM
    T
    4.
    )
    5.
    DELETE
    FROM
    CTE
    6.
    WHERE
    rnk >
    1
    ; в PostgreSQL завершится ошибкой:
    ОШИБКА: отношение "cte" не существует
    Эта ошибка означает, что мы можем удалять строки из базовых таблиц, но не из CTE. Тем не менее, возможно выполнить удаление дубликатов одним запросом, используя CTE.

    Поступим следующим образом:
    1. Удалим все строки из базовой таблицы, возвращая их в табличное выражение (первое CTE).
    2. Используя результат 1 шага, формируем уникальные строки, которые должны остаться в таблице (второе CTE).
    3. Вставляем строки, полученные на шаге 2 в базовую таблицу.
    Воспользуемся таблицей из цитируемого примера, чтобы написать запрос:
    1.
    CREATE
    TABLE
    T
    (
    name varchar
    (
    10
    ))
    ;
    2.
    INSERT
    INTO
    T
    VALUES
    3.
    (
    'John'
    )
    ,
    4.
    (
    'Smith'
    )
    ,
    5.
    (
    'John'
    )
    ,
    6.
    (
    'Smith'
    )
    ,
    7.
    (
    'Smith'
    )
    ,
    8.
    (
    'Tom'
    )
    ;
    Вот и сам запрос
    1.
    WITH
    t_deleted
    AS
    2.
    (
    DELETE
    FROM
    T returning *
    )
    ,
    -- 1 шаг
    3. t_inserted
    AS
    4.
    (
    SELECT
    name, ROW_NUMBER
    ()
    OVER
    (
    PARTITION
    BY
    name
    ORDER
    BY
    name
    )
    rnk
    5.
    FROM
    t_deleted
    )
    -- 2 шаг
    6.
    INSERT
    INTO
    T
    SELECT
    name
    FROM
    t_inserted
    7.
    WHERE
    rnk=
    1
    ;
    -- 3 шаг (сюда мы перенесли условие отбора
    из 2 шага для сокращения кода)
    Если теперь выполнить запрос
    1.
    SELECT
    *
    FROM
    T;
    то получим требуемый результат
    1   ...   31   32   33   34   35   36   37   38   ...   47

    написать администратору сайта