SQL запрос для обработки неструктурированной таблицы

[VadimirL]

Уважаемые Господа!

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

Пример придуман для этого вопроса.

Все совпадения Фамилий и ситуаций являются случайными!

Есть одна большая таблица. Хотелось-бы из нее получить другую. Эта таблица содержит три столба: Фамилия ВремяGMT Состояние. При этом, в поле Состояние может быть все, что угодно, но меня, в отчетной таблице особо интересуют значения “Командировка” и “Заболел” – эти значения особо влияют на отчет, и как-то остальные.

В этой таблице есть куча записей:

Фамилия ВремяGMT Состояние
Петров        07.12          Пришел
Петров        07.15          Ушел
Иванов        05.10          Пришел
Петров        08.10       Командировка

Запрос:
1.   Группируем по  полю Фамилия;
2.   Сортируем по ВреяGMT;

Но, Как реализовать дальнейший алгоритм я не представляю, если это вообще возможно в одном SQL запросе.

3.   Обрабатываем группу записей одной Фамилии по следующему алгоритму:

Если среди всех записей есть Значение  - “Командировка”, независимо от времени, - 1 приоритет, в отчетную таблицу, для этой Фамилии, пойдет значение “Турист”, и соответствующее этой записи ВремяGMT;

Если среди всех записей есть Значение  - “ Заболел ”, независимо от времени, - 2 приоритет, в отчетную таблицу, для этой Фамилии, пойдет значение “Доходяга”, и соответствующее этой записи ВремяGMT;;

Если нет ни первого, ни второго, то в отчетную таблицу, для этой Фамилии,  пойдет последнее по времени Состояние, но к нему добавится слово “Просто” – т.е получится “Просто Пришел” или “Просто Ушел” или “Просто Чего-нибудь” ”, и соответствующее этой записи ВремяGMT;

Надеюсь на Ваши Светлые Головы!

[Sla]

вот почитай
https://forum.shelek.ru/index.php/topic,20638.0.html

[x77]

в нормальных СКУДах делается немножко иначе. контролируемое пространство бьётся на зоны, а для точек доступа прописывается, из какой зоны в какую она ведёт. теперь, если нам надо получить, к примеру, местонахождение всех сотрудников, мы просто линкуем список сотрудников с последним событием. событие указывает на точку доступа, точка доступа говорит, куда пошёл сотрудник.

в вашем случае можно точно также отслеживать не только время события, но и время нахождения в той или иной зоне. наример, зона А (пришёл в офис), зона Б (ушёл из офиса), зона С (командировка) и т.д.

далее, по этим событиям можно расчитать интервалы нахождения каждого сотрудника в каждой зоне. суммируйте эти интервалы - и вы получите общее время, проведённое в командировке, в офисе и пр.

в сиквеле это можно сделать и одним запросом, но лучше вынести формирование таблицы зон на триггера. и дальше тупо делать по ним селекты. это оправдано, т.к. подобного рода таблицы никогда не редактируются. а задачей триггера будет при возникновении события найти предыдущее событие, и занести в БД время нахождения в зоне, привязанной к предыдущей точке доступа.

[Sla]

x77, мы в той указанной теме там очень хорошо все разжевали и практически справились одним запросом

[x77]

Sla, мне показалось, что у автора задача не совсем такая. тут не нужны промежуточные накопительные запросы. тут нужно:

1) получить список всех интервалов между событиями 3 (как минимум) видов
2) суммировать эти интервалы по каждому сотруднику.

может, я неправильно автора понимаю?

[Sla]

x77, задача похожая

[x77]

похожая, но за одним запросом я бы тут не гнался. да, можно для каждого события находить предыдущее событие, определять интервал, группировать и суммировать интервалы по сотруднику. но я бы руки оторвал за такое решение, если честно

[Sla]

x77, давай афффтара подождем

[VadimirL]

Уважаемый  Sla!

Благодарю за ссылку!

К моему сожалению, я сегодня уже туго воспринимаю то, что там написанное, в силу объективных причин. Завтра прочитаю еще раз 5.

Но уже сегодня, мне ясно, что эта задача может быть решена в ОДНОМ запросе путем создания в нем нескольких виртуальных таблиц, связанных между собой, полученных из одной реальной исходной таблицы с использованием фильтров по ключевым словам – в данном случае это “Командировка” и “Заболел”. Пока не понятно, как задать приоритет. (Может добавить в какую-нибудь виртуальную таблицу дополнительное поле и внести в него цифры 1,2, а потом выбрать min).

Завтра, прочитаю Вашу ссылку еще раз 5. Потом вернусь…

Еще раз спасибо за сотрудничество.

С уважением,
Владимир

[VadimirL]

Уважаемые Господа!

Я 30 раз прочитал Вашу ссылку и каждый раз находил что-то новое и полезное!

Но, к сожалению, я так и не сумел полностью решить свою задачу!

Я предполагаю, что она должна решаться следующим образом:

Пусть исходная таблица называется Т, тогда:

(
SELECT [Т1].[Фамилия], [Т1].[ ВремяGMT], [Т1].[Состояние],
          IIf([Т1].[ Состояние]='Командировка',1,(IIf([Т1].[ Состояние]='Заболел',2,100))) AS [Приоритет]
          Раздою приоритеты в зависимости от значения в [Состояние]: ‘Командировка’-1, 'Заболел'-2, все остальные-100
FROM [Г] )AS [Т1]
) AS [Т_Полная]

т.е. я формирую таблицу, которая содержит все записи + поле Приоритет

и вторую виртуальную таблицу
(
 SELECT  [Т1].[Фамилия],
          Min(IIf([Т1].[ Состояние]=' Командировка ',1,(IIf([Т1].[ Состояние]=' Заболел ',2,100)))) AS [Prioritet],"
          Здесь я вынужден переписать условие, но с групповой операцией Min

        Max([ВремяGMT]) AS [Время]"
FROM [Т] AS [Т2]"
GROUP BY [Т2].[ Фамилия]"
 ) AS [Т_Информационная]"

Здесь мне хотелось - бы ввести еще одну групповую операцию – не зависящую от значения поля, а просто последняя физическая запись.

Далее соединяя таблицы [Т_Полная] и  [Т_Информационная] я получаю [Т_Рабочая]
(
SELECT [Т_Полная].[Фамилия], [Т_Полная].[ ВремяGMT], [Т_Полная].[Состояние],[Т_Полная].[Приоритет],
FROM  [Т_Полная]
JOIN  [Т_Информационная]
ON    ([Т_Полная].[Фамилия]= [Т_Информационная].[Фамилия]
   AND [Т_Полная].[ВремяGMT]= [Т_Информационная].[ Время]
   AND [Т_Полная].[Prioritet]= [Т_Информационная].[ Prioritet])
) AS [Т_Рабочая]

Предполагается, что в [Т_Рабочая] каждая фамилия содержится только один раз и имеет требуемую информацию для этой фамилии.

Но как теперь использовать эту таблицу в конструкции обновления UPDATE другой таблицы в рамках одного запроса Ругается - “В операции должен использоваться обновляемый запрос”
Родных ключевых полей в исходной таблице нет, В “другой” таблице Все фамилии встречаются один раз, но они тоже не ключ.

Надеюсь на Ваше понимание.

С уважением,
Владимир.

[VadimirL]

Помогите пожалуйста!

Ну ничего не получаетс!
Есть ИсходнаяТаблица с полями Фамилия ПриоритетЗаписи Время  Состояние

Петров 1 12 в_рубашке
Петров 1 13 без_рубашки       V
Петров 2 14 в_штанах
Сидоров 1 12 в_рубашке        V
Сидоров 2 13 без_рубашки
Петров 2 14 в_штанах
Иванов 2 13 в_штанах
Иванов 2 13 в_рубашке          V

Хочу получить итоговую таблицу по каждой Фамилии по следующему принципу: Min ПриоритетЗаписи, Max Время, если таких записей много, то последнюю физическую запись и Состояние для выбранной записи:

Петров 1 13 без_рубашки
Сидоров 1 12 в_рубашке
Иванов 2 13 в_рубашке

Select T1. Фамилия, T1.ПриоритетЗаписи, T1.Время, T1. Состояние
From ИсходнаяТаблица as T1
Join ИсходнаяТаблица as T2
       Select T2. Фамилия, Min(T2.ПриоритетЗаписи) as ПриоритетЗаписи2, Max(T2.Время) AS Время2
      Сюда-бы еще последнюю физическую запись, но как?
      GROUP BY Фамилия
ON

Но как их связать – ведь мне нужно Состояние именно отобранной записи
(
T2. Фамилия= T1. Фамилия,
Т2. ПриоритетЗаписи2=Т1. ПриоритетЗаписи,
Т2. Время2=Т1. Время
) - неправильно


В исходной таблице ключевых полей нет! А можно их как-то создать в SQL запросе.

С уважением,
Владимир

[Sla]

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

[VadimirL]

Уважаемый Sla

Задача, которую я хочу решить оказалась сложнее, чем я предполагал.

Благодаря Вашей ссылки часть этой задачи я уже понял. Но я уперся в вопрос, описанный в 10 сообщении. – Здесь несколько иной пример. А кода у меня пока нет. Есть куски кода на двух листах А4 и чего с ними делать я не знаю.

С уважением, Владимир

P.S. Свое первое сообщение я бы уже стер, если-б знал как!

[Sla]

VadimirL, давай без лишних слов (уважаемый, с уважением) приятно, но это лишнее и отвлекает.

Ладно... задача изменилась.
Но осталась практические не разъясненной.
я не хочу догадываться, я хочу чтоб ты внятно объяснил что ты хочешь иметь

Код:

Петров    1 12 в_рубашке
Петров    1 13 без_рубашки        V
Петров    2 14 в_штанах
Сидоров   1 12 в_рубашке          V
Сидоров   2 13 без_рубашки
Петров    2 14 в_штанах
Иванов    2 13 в_штанах
Иванов    2 13 в_рубашке          V

Почему результат такой? какие критерии отбора записей?

Код:

Петров    1 13 без_рубашки       
Сидоров   1 12 в_рубашке        
Иванов    2 13 в_рубашке  
[code]

[/code]

[VadimirL]

Уважаемый Sla

Мне казалось, что на отвлеченном примере будет проще понять решение моей задачи.

Приношу свои извинения за свое словоблудие. Я хотел как лучше, а получилось как всегда…

Реальная ситуация:

В природе есть 1000 заказов. Каждый заказ имеет номер.

Есть таблица T – которая отражает каждое действие с каждым из этих заказов, т.е каждая запись содержит следующие поля:

НомерЗаказа         ДействиеСЗаказом          ДатаДействия
0001/R                   Прием                 01.07.2009
0002/U                   Прием                 01.07.2009
0001/R                    Сбор                 02.07.2009
0001/R                  Выполнен               02.07.2009
0001/R                  Отложен                02.07.2009

Записи в эту таблицу попадают хаотично. А некоторые записи, которые должны быть, вообще не попадают. Есть ошибки в датах, т.е может получится, что заказ Выполнен раньше, чем принят. В поле ДействиеСЗаказом може быть, что угодно.

Необходимо получить аналитическую Сводку, которая содержала - бы следующие поля:
1.   НомерЗаказа – Каждый заказ одна строка;
2.   ДействиеСЗаказом – Если в Т существует хотя-бы одна запись со значением “Выполнен” – тогда здесь “Выполнен”, если нет “Выполнен”, тогда что есть на последнюю дату, если таких записей несколько, то последнюю физическую запись;
3.   ДатаДействия – дата, соответствующая записи, которая попала в эту таблицу, соответствует полю ДействиеСЗаказом;
4.   ФлагВыполнения – True – если заказ “Выполнен” или False в любом, другом случае;


С этими данными я надеюсь получить в Сводка:
НомерЗаказа         ДействиеСЗаказом          ДатаДействия          ФлагВыполнения
0001/R                 Выполнен                02.07.2009               True
0002/U                  Прием                  01.07.2009               False


В перспективе (я надеюсь решить этот вопрос самостоятельно):
этой Сводкой  необходимо обновить, и при необходимости дополнить Информационную таблицу. В этой информационной таблице произвести расчет сроков Выполнения заказов и времени простоя заказов и т.д., используя Дополнительные данные.

С уважением, Владимир.

[Sla]

идея понятно, но не понятно, что такое последняя физическая запись

дело в том что в данные в базе ( в основном) располагаются хаотически и определить последнюю запись не реально (при указанных тобой условиях).

[VadimirL]

Уважаемый Sla!

В таблице Т может быть несколько записей для одного заказа (одинаковые НомерЗаказа) с одинаковой ДатаДействия, но разными ДействиеСЗаказом, при этом ДействиеСЗаказом не “Выполнен” – а надо выбрать только одну запись(в принципе – любую).

Не хочу опять запутать, но я пытался ввести ПриоритетДействия, с помощью которого я максимально сократил - бы такие ситуации:

Если ДействиеСЗаказом = “Выполнен” тогда ПриоритетДействия=1
Если ДействиеСЗаказом = “Отложен” тогда ПриоритетДействия=2
И т.д.

Но придусмотреть все возможные значения ДействиеСЗаказом не возможно.

С уважением, Владимир.

[Sla]

Из всего сказанного

Выбрать "Заказ" не имеющие состояния "Выполнен"
В выборке показать Заказы с максимальной датой состояния.

[VadimirL]

P.S. Кусок кода для ПриоритетДействия я уже набрал:
Select…          , IIf([ДействиеСЗаказом]='Выполнен',1,(IIf([ДействиеСЗаказом]='Отложен',2,100))) AS [ПриоритетДействия]

Я предполагаю, что это сравнение лучше делать именно в запросе.

С уважением, Владимир.

[VadimirL]

Уважаемый Sla!

В отношении 17 ответа: Если при этом возьмется точно только одна запись – то да.

С уважением, Владимир.

[Sla]

п.1

Выбрать "Заказ"ы не имеющие состояния "Выполнен"

[Sla]

Как я понимаю база на accesse

Применение условных конструкций - это "круто", а нужно ли?

[VadimirL]

Уважаемый Sla!

База на Access – пока – понять - бы принцип.

Условные конструкции меня совершенно не смущают.

Мне кажется, что поле ПриоритетДействия я смогу эффективно использовать в дальнейшем.

По п.1 А куда денутся заказы, которые “Выполнен”. Мне хотелось бы Видеть полную картину в Сводка

С уважением, Владимир.

[Sla]

VadimirL, ты хочешь чтоб я написал запрос?

п.1

Выбрать "Заказ"ы не имеющие состояния "Выполнен"

[Dimka]

последнюю физическую запись

Чистый SQL не оперирует физическим порядком записей. Максимум, что возможно - это задать порядок через ORDER BY, и тогда получить последовательность записей в этом заданном порядке. Во всех остальных случаях движок SQL в процессе выполнения запроса может переставлять записи как угодно - т.е. в случае использования порядка записей в полученном результате есть риск, что этот результат не будет соответстветствовать действительности. Отношение (таблица или выборка) - это неупорядоченное множество кортежей (записей). Все операции реляционной алгебры (на которой основан SQL) отображают одни множества (исходные) в другие (результаты).

Для обработки записей в физическом порядке нужно писать алгоритм перебора этих записей при помощи курсора по исходной таблице (в подавляющем большинстве случаев простой запрос по одной таблице возвращает записи в физическом порядке) - если диалект SQL позволяет, алгоритм можно писать на нём, иначе на языке программирования общего назначения (например VB с применением технологии ADO).

Можно поступить иначе. Сперва курсором пронумеровать записи в физическом порядке, добавив новое поле. Затем уже, опираясь на на это поле, составлять SQL-запрос. Если в таблице используется автоинкрементируемый фиктивный первичный ключ (обычно такое поле называется ID), то для определения физического порядка записей можно оперировать им, тогда обработка курсором не нужна.

[HandKot]

если брать таблицу с заказами и получить результат как в топике №14

1) Выбрать "Заказ"ы не имеющие состояния "Выполнен", с группировкой по номеру заказа и выборкой максимальной даты + агрегат на "Состояние"
2) Выбрать "Заказ"ы имеющие состояния "Выполнен", с группировкой по немеру заказа
3) Объединить результаты первых пунктов по FULL JOIN по полю "Номер заказа"
4) если в объединении есть строка с "Выполнен", то выдаём её, иначе выдаём другое значение


ЗЫЖ
- конечно придётся использовать агрегат на текстовое поле. Это некорректно, но бывает иногда необходимо
- считаем, что для одного заказа не может быть строк с состоянием "Выполнен" более чем одна
- пункты 1 и 2 сделать в виде отдельных запросов и потом к ним можно будет обращатся как к таблицам

дерзайте, не проверял, но, по идее, должно получится

[VadimirL]

Уважаемые господа!

Благодарю Вас за сотрудничество.

Вопрос по 14 ответу решился просто…

Select
   [НомерЗаказа],
   Min (IIf([ДействиеСЗаказом]='Выполнен',1,(IIf([ДействиеСЗаказом]='Отложен',2,100)))) AS [ПриоритетДействия],
   Max ([ДатаДействия]) AS [ИтогДатаДействия],
   Last([ФлагВыполнения]) AS [Итог ФлагВыполнения],


   Может – быть будет полезна запись
   Last([ДатаДействия]) AS [([ВременнаяДатаДействия]],


   Если в таблице Т были-бы еще необходимые поле [AAA], [BBB] тогда
   Last( [AAA]) AS [ИтогААА],
   Last( [BВВ]) AS [Итог BВВ],

From T

Group By [НомерЗаказа]

Этот запрос, по любому, возвращает одну и только Полную одну запись для каждого [НомерЗаказа].

Затем, не заморачиваясь, результат этого запроса записывается в промежуточную таблицу, а дальше …

С этим разобрались…

Мне теперь предстоит борьба с MySQL через Internet.
Честно скажу: что кроме магического слова MySQL, то, что это база данных и то, что она стыкуется с MS ACCESS через дополнительные библиотеки - на эту тему я не знаю ничего – надеюсь на то, что Вы поможете.

Еще раз Благодарю Вас за сотрудничество.

С уважением, Владимир.

[Dimka]

Этот запрос, по любому, возвращает одну и только Полную одну запись для каждого [НомерЗаказа].

Да ничего подобного. Этот запрос возвращает не запись таблицы, а новую "вымышленную" запись, механически составленную из Номера заказа, минимального номера состояния внутри заказа из одной записи, максимальной даты действия из другой записи (не факт, что соответствующей первой). Что такое LAST - я не знаю, возможно специфика этой СУБД.

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

[VadimirL]

Уважаемый Dimka

Вы абсолютно правы!

Вы уберегли меня от неприятностей!

Как Вы написали, так и сделал.
Получилось что-то типа Ёлки.

Дополнительно добавил поле тип Counter. Max значение этого поля – считаю последней (требуемой) записью в группе , при нескольких, удовлетворяющих имеющимся условиям, записям.

Спасибо, все работает.

С уважением, Владимир.

[Dimka]

VadimirL, лучше покажи запрос. А то нет у меня доверия к "ёлкам" и особенно к Counter-ам.