Мой самый сложный SQL запрос

-
Я работу по большей части во фронтэнде на как-то раза два у меня была задача написать сложный SQL-запрос. В принципе получилось довольно быстро при использовании интернет. Может кому и пригодиться.

Задача 1 - посчитать количество ордеров определенного типа

 
 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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
DROP TABLE IF EXISTS Objects;
DROP TABLE IF EXISTS ObjectTypes;
DROP TABLE IF EXISTS Attrs;
DROP TABLE IF EXISTS Params;

CREATE TABLE Objects (id int, objectTypeId int, name varchar(30));
INSERT INTO Objects (id, objectTypeId, name) VALUES (1, 1, 'Order-Tv#1');
INSERT INTO Objects (id, objectTypeId, name) VALUES (2, 1, 'Order-Tv#2');
INSERT INTO Objects (id, objectTypeId, name) VALUES (3, 1, 'Order-Tv#3');
INSERT INTO Objects (id, objectTypeId, name) VALUES (4, 1, 'Order-Internet#1');
INSERT INTO Objects (id, objectTypeId, name) VALUES (5, 1, 'Order-Internet#2');
INSERT INTO Objects (id, objectTypeId, name) VALUES (6, 1, 'Order-Radio#1');

CREATE TABLE ObjectTypes (id int, name varchar(30));
INSERT INTO ObjectTypes (id, name) VALUES (1, 'order');

CREATE TABLE Attrs (id int, name varchar(30));
INSERT INTO Attrs (id, name) VALUES (1, 'not started');
INSERT INTO Attrs (id, name) VALUES (2, 'failed');
INSERT INTO Attrs (id, name) VALUES (3, 'success');

CREATE TABLE Params (objectId int, attrId int, val varchar(30));
INSERT INTO Params (objectId, attrId, val) VALUES (1, 1, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (1, 2, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (1, 3, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (2, 1, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (3, 2, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (1, 3, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (4, 1, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (4, 4, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (5, 1, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (6, 1, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (6, 2, '+');
INSERT INTO Params (objectId, attrId, val) VALUES (6, 4, '+');

SELECT t0."type", t1."failed", t2."success", t3."not started" FROM
        (
                SELECT name AS "type" FROM ObjectTypes WHERE id IN (1)
        ) AS t0
FULL OUTER JOIN
        (
                SELECT COUNT(*) as "failed" FROM Params, Objects WHERE
                Params.objectId IN (SELECT id FROM Objects WHERE objectTypeId IN (1))
                AND Params.attrId = 1
                AND Params.value = 'failed'
                AND Params.objectId = Objects.id
                GROUP BY objectTypeId
        ) as t1
ON t0.objectTypeId = t1.ObjectTypeId
FULL OUTER JOIN
        (
                SELECT COUNT(*) as "success" FROM Params, Objects WHERE
                Params.objectId IN (SELECT id FROM Objects WHERE objectTypeId IN (1))
                AND Params.attrId = 2
                AND Params.objectId = Objects.id
                GROUP BY objectTypeId
        ) as t2
ON t0.objectTypeId = t1.ObjectTypeId
FULL OUTER JOIN
        (
                SELECT COUNT(*) as "not started" FROM Params, Objects WHERE
                Params.objectId IN (SELECT id FROM Objects WHERE objectTypeId IN (1))
                AND Params.attrId = 3
                AND Params.objectId = Objects.id
                GROUP BY objectTypeId
        ) as t3
ON t0.objectTypeId = t1.ObjectTypeId;

 Cначала находим все типа объектов в строке 55 и называем такую таблицу t0. Затем находим все объекты у которых тип это Order, а атрибут используется номер 1 и записываем результат в t1 (строка 58-65). То же самое повторяем для всех типов и затем склеиваем через JOIN. Конечно здесь есть не оптимальности типа копипаста t1-t3, но я же не SQL девелопер.

Отмечу, что запрос переписан и может не работать, т.к. я убирал всю специфику и чуть упростил запрос - убрал табличу и уменьшил количество типов, а там запрос у меня занимал А4 шрифтом 10.

 Задача 2 - пробежаться по иерархии

Здесь задача посложнее нужно пробежаться по иерархии Order и найти все Offering, причем Offering из родительского Order может быть заоверрайджен, тогда его показывать не нужно.
 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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
(
  /*compatibility - get Offerings FROM Order which are children for Order Container*/
  SELECT
    OrderOffering.id AS objectId,
    OrderOffering.name AS value
  FROM
    Objects OrderContainerOffering,
    Objects OrderContainerOfferingFolder,
    Refs OrderContainer2Order,
    Objects OrderOfferingFolder,
    Objects OrderOffering
  WHERE
    OrderContainerOffering.id IN (#$objectids$#)
    AND OrderContainerOfferingFolder.id = OrderContainerOffering.parentId
    AND OrderContainer2Order.objectId = OrderContainerOfferingFolder.parentId
    AND OrderContainer2Order.attrId = 1 /*order*/
    AND OrderOfferingFolder.parentId = OrderContainer2Order.objectRefId
    AND OrderOfferingFolder.typeId = OrderContainerOfferingFolder.typeId
    AND OrderOffering.parentId = OrderOfferingFolder.id
    AND OrderOffering.typeId = 1 /*offering*/
)
UNION
(
  /*get Offerings FROM Order hierarchy*/
  SELECT
    ParentOrderOffering.id AS objectId,
    ParentOrderOffering.name AS value
  FROM
    Objects OrderOffering,
    Objects OrderOfferingFolder,
    Objects ParentOrderOfferingFolder,
    Objects ParentOrderOffering
  WHERE
    OrderOffering.id IN (#$objectids$#)
    AND OrderOfferingFolder.id = OrderOffering.parentId
    AND ParentOrderOfferingFolder.parentId IN (
      /*get all parent Orders*/
      SELECT reference FROM Refs ref
      START WITH ref.objectId = OrderOfferingFolder.parentId
      /*OrderOfferingFolder.parentId: parent of Offering Folder
        is Order AND this is Order to start find parents FROM*/
      CONNECT BY PRIOR ref.objectRefId = ref.objectId 
      AND PRIOR ref.attrId = 2 /*parent order*/
    )
    AND ParentOrderOffering.parentId = ParentOrderOfferingFolder.id
    AND ParentOrderOffering.typeId = 1 /*offering*/
    AND ParentOrderOffering.id not IN (
      /*exclude Offerings which are already overrided - 
        only not overrided Offerings available for override*/
      SELECT reference FROM Refs WHERE attr_id = 3 /*ancestor offering*/
      AND objectId IN (
        /*SELECT Offerings which are set IN attr ancestor offering*/
                SELECT /*get offerings FROM order hierarchy*/
          ParentOrderOffering.id AS objectId
                FROM
          Objects OrderOffering,
          Objects OrderOfferingFolder,
          Objects ParentOrderOfferingFolder,
          Objects ParentOrderOffering
                WHERE
          OrderOffering.id IN (#$objectids$#)
          AND OrderOfferingFolder.id = OrderOffering.parentId
          AND ParentOrderOfferingFolder.parentId IN (
            /*get all parent orders*/
            SELECT reference FROM Refs ref
            START WITH ref.objectId = OrderOfferingFolder.parentId
            /*OrderOfferingFolder.parentId: parent of Offering folder 
              is Order AND this is Order to start find parents FROM*/
            CONNECT BY PRIOR ref.reference = ref.object_id 
            AND PRIOR ref.attr_id = 2 /*attr parent order*/
            UNION ALL
            SELECT id FROM Objects 
            WHERE id = OrderOfferingFolder.parentId /*plus current Order*/
          )
          AND ParentOrderOffering.parentId = ParentOrderOfferingFolder.id
          AND ParentOrderOffering.typeId = 1 /*offering*/
      )
    )
)

Итог

  • я познакомился с иерархическими запросами - START WITH
  • первый раз использовал UNION
  • наконец в боевых условиях использовал JOIN
  • попрактиковался в сложных WHERE и GROUP BY
  • использовал вложенные запросы
  • сделал параметризацию запросов
  • попробовал AS для именования
SQL не такой уж и сложный поначалу, хотя конечно чем дальше тем дремучей лес.

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Нейронная сеть для обхода препятствий

-
Изображение
Некоторое время назад мне нужно было написать нейронную сеть для управления простым роботом, цель которого ездить и не врезаться в препятствия. Программа, написанная мной на java находится здесь , и предоставляется "как есть". Для просмотра работы программы нажимаем кнопку NEW, удостоверяемся что checkbox DRAW находится в положении false, нажимаем DO EDUCATION, ждем секунд 20 - теперь сеть обучена, ставим DRAW в положение true, нажимаем DO EDUCATED AI. Робот должен не врезаться в препятствия. Если это происходит то сделайте эти шаги заново. Врезание после обучения происходит из-за неправильного распределения начальных весов сети (они распределяются случайно) и эта ситуация довольно редка в миниум 90% случаях сеть обучается обходить препятствия. Поскольку я раньше почти не сталкивался с нейронными сетями мне пришлось прочитать несколько книжек, а также перерыть интернет. И может конечно я плохо искал, но готовой программы или алгоритма как это сделать я не нашел. Однако по
Read more »

Time Zones

-
Аннотация В данном посте будет описана немного истории для понимания, что такое тайм зона и в чем ее сложности. Также будет объяснено, что такое Date, как она представляется в коде и БД. Будут разысканы проблемы, которые возникают с датами в GWT и их решения. С чего все началось Форматирование даты в GWT по маске происходит через DateTimeFormat. Однако оказалось, что при выводе дата зависит от клиентской тайм зоны, т.е. часового пояса, который установили на компьютере в Windows. Это явно не очень хорошо, потому что в enterprise приложениях тайм зона (TZ) может не равняться тайм зоне на клиенте. Логично сделать прокидывание TZ в DateTimeFormat при форматировании и парсинге, но в GWT на клиенте это можно сделать только для форматирования, да и прокинуть тайм зону тоже не очень получиться - нет метода создания TZ по имени. Для справки в Java это есть: TimeZone timeZone = TimeZone.getTimeZone(“Europe/Moscow”); SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy HH:mm
Read more »

Еще одна статья о Latex

-
Изображение
В этой статья рассказывается о тех командах и фокусах, которые пригодились мне для написания диплома. Поскольку иногда удобнее изучать что-либо на примере, сразу привожу пример моего Ссылка на диплом (но только несущественную по смыслу часть). А так он выглядит после конвертации в pdf. Начнем с самого начала, хотя порядок может быть и другим. Доктор Who, Meglos Установка Скачиваем LaTeX . При установки разрешаем скачивать дополнительные файлы из интернета, т.к. в последующем при подключении разных модулей для форматирования текста, потребуется докачка файлов. Полезные ссылки Ссылка не небольшой справочник . При наборе формул может помочь редактор формул . Создание документа Набирать диплом в стандартной оболочке Miktex не очень удобно, т.к. у него есть маленькие проблемы с русским языком. Вместо этого я набирал диплом в Notepad++, в котором также есть поддержка синтаксиса LaTeX. Теперь определимся с иерархией файлов для документа. Я использовал следующую структуру: main
Read more »