Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Access Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
Excel 2013.

Доброго дня!
Столкнулся с такой проблемой.

Есть условно две таблицы, которые нужно импортировать из Access и объединить:

T1 с полями KOD, Name, Date, G1, G2, GX... (около 200 000 записей)
T2 с полями KOD, Group, DateIn (около 8 000 записей)

Их нужно объеденить чтобы добавить в T1 поле Group
Объединение по полям KOD но в Т2 это поле не уникально, есть вариации записей по полям DateIn и искомому Group.
Поэтому должно соблюдаться еще одно условие Date = MAX(диапазон DateIn, где DateIn <=Date)

С импортом в Эксель проблем нет, проблемы с объединением (что в Access, что в Эксле)

Написал такой вот SQL.

SELECT T1.*, 
     (SELECT TOP 1 Group FROM T2 WHERE T1.KOD=T2.KOD ORDER BY DateIn DESC) as GroupName
FROM T1



Вложенный запрос сам по себе работает, но как тольок его вставляю во внешний - запрос начинает отработку и зависает.
В Excell - сразу, в Access при попытке получить все резульаты запроса (а так оказывает видимые строки на экране, общее кол-во записей не выводит).

Может кто знает как модифицировать запрос, чтобы он заработал ?
Например можно ли заменить вложенный запрос LEFT JOIN ? Ума не приложу как это сделать. Отчегото кажется что JOIN не должен зависать.
19 сен 17, 11:42    [20805429]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
aleks222
Guest
Для начала, хорошо бы индекс на
T2
(KOD, DateIn) include(Group)
19 сен 17, 12:52    [20805784]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
aleks222
Для начала, хорошо бы индекс на
T2
(KOD, DateIn) include(Group)


Спасибо. А можно подробнее ? А то я не силен в SQL.

У меня T1 - таблица в Accrss
T2 - файл Эксель.


Я могу создать индекс для T2 прямо в SQL запросе ?
19 сен 17, 13:06    [20805834]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 16311
Чёта типа
SELECT t01.*, t02.*
FROM t1 AS t01, t2 AS t02,
  ( SELECT t12.KOD, MAX(t12.DateIn) DateIn
    FROM t1 AS t11,t2 AS t12
    WHERE t11.KOD = t12.KOD
      AND t11.Date >= t12.DateIn
    GROUP BY t12.KOD
  ) AS q1
WHERE t01.KOD = t02.KOD
  AND t02.KOD = q1.KOD
  AND t02.DateIn = q1.DateIn
19 сен 17, 13:35    [20805966]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 16311
PS. Подзапрос лучше сделать статическим запросом, и использовать именно его, а не его текст.
19 сен 17, 13:35    [20805969]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 4671
igor.kovalchuk


Есть условно две таблицы, которые нужно импортировать из Access и объединить:
T1 с полями KOD, Name, Date, G1, G2, GX... (около 200 000 записей)
T2 с полями KOD, Group, DateIn (около 8 000 записей)
igor.kovalchuk
У меня T1 - таблица в Accrss
T2 - файл Эксель.
Какое из утверждений верно?
19 сен 17, 13:36    [20805972]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
aleks222
Guest
igor.kovalchuk
aleks222
Для начала, хорошо бы индекс на
T2
(KOD, DateIn) include(Group)


Спасибо. А можно подробнее ? А то я не силен в SQL.

У меня T1 - таблица в Accrss
T2 - файл Эксель.


Я могу создать индекс для T2 прямо в SQL запросе ?


Нет. Не могешь.

Импортируй таблицу экселя в аксесс.
Приделай к ней индекс.
И будет тебе щастье.
19 сен 17, 13:40    [20805987]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
sdku
igor.kovalchuk


Есть условно две таблицы, которые нужно импортировать из Access и объединить:
T1 с полями KOD, Name, Date, G1, G2, GX... (около 200 000 записей)
T2 с полями KOD, Group, DateIn (около 8 000 записей)
igor.kovalchuk
У меня T1 - таблица в Accrss
T2 - файл Эксель.
Какое из утверждений верно?


Как бы два в зависимост от ситуации:)

Кода тестировал в Accesse - то файл T2 подключил как соединее (внешних данные) к базе.
Но реализовать код нужно все равно в Экселе. А там у меня получается что T1 тяну с Access, а T2 с файла эксель.
T2 будет может изменяться пользователями, потому втягивать ее в Access нету смысла (на рабочих местах нету Access), а T1 - статичный файл базы данных.

Результат объединения потом запизиваю в кєш сводной таблицы, что бы с ней пользватели (в т.ч и я) уже могли работать.
19 сен 17, 13:43    [20805992]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 4671
igor.kovalchuk,
Стесняюсь спросить:а какие значения в поле Т2.KOD,откуда они там берутся и почему ..WHERE T1.KOD=T2.KOD...
19 сен 17, 14:03    [20806067]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
Akina
Чёта типа
SELECT t01.*, t02.*
FROM t1 AS t01, t2 AS t02,
  ( SELECT t12.KOD, MAX(t12.DateIn) DateIn
    FROM t1 AS t11,t2 AS t12
    WHERE t11.KOD = t12.KOD
      AND t11.Date >= t12.DateIn
    GROUP BY t12.KOD
  ) AS q1
WHERE t01.KOD = t02.KOD
  AND t02.KOD = q1.KOD
  AND t02.DateIn = q1.DateIn


Заработало, вы гений ! :)
19 сен 17, 14:14    [20806113]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
Анатолий ( Киев )
Guest
Думаю, что правильнее Т2 не присоединять, а импортировать. После чего проиндексировать поля KOD и DateIn, а также тип поля KOD сделать таким же, как в Т1.
Если данные в Экселе меняются - очистить таблицу и залить новые.

Кроме того:
Имя Group относится к зарезервированным словам Jet (как, кстати, Name и Date), поэтому в запросе его надо брать в квадратные скобки;
Вы уверены, что комбинации значений KOD и DateIn не дублируются? Иначе ваш SELECT TOP 1 захочет вернуть более 1 записи, что в вашем случае недопустимо;
В запросе вы не учли условие DateIn <=Date).
19 сен 17, 14:15    [20806116]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
Анатолий ( Киев ),

Спасибо, по Group, тут лоханулся, на скорую руку писал :) , потому как код запроса я написал на форуме не с оригинала а условный, с измененными названиями для удобства чтения.

Код Akina отработал замечатльно. Щас буду изучать результирующую таблицу.

Все выглядит очень просто.
Есть 3 статичных таблицы в отедльных файлах Acсess, со временем их будет больше.
Я их запросом через UNION соединяю и импортрую на лист в Ексель. Это моя T1
Также импортирую на другой лист Эксель таблицу. Это T2

Но мне нужен результат их соединения. А вот это у меня не заладилось.

Потом запихиваю рекордсет в кэш сводной таблицы. И все.
19 сен 17, 14:25    [20806134]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
igor.kovalchuk
Akina
Чёта типа
SELECT t01.*, t02.*
FROM t1 AS t01, t2 AS t02,
  ( SELECT t12.KOD, MAX(t12.DateIn) DateIn
    FROM t1 AS t11,t2 AS t12
    WHERE t11.KOD = t12.KOD
      AND t11.Date >= t12.DateIn
    GROUP BY t12.KOD
  ) AS q1
WHERE t01.KOD = t02.KOD
  AND t02.KOD = q1.KOD
  AND t02.DateIn = q1.DateIn


Заработало, вы гений ! :)


Рано я обрадовался :(

В общем в поле sGroup (буду его так называть) подтягивается не правильно.

Смысл запроста был в том, что у каждого KD для разной Date в T1 нужно из T2 вытянуть sGroup.
T2 нужна, чтобы отслеживать перемещение KD по группам с течением времени.

Вот так оно примерно все выгядит:

T1 T2
KOD Date Name KOD DateIn sGroup Name
1 31.12.2014 Петя 1 01.01.1900 Тигры Петя
2 31.12.2014 Маша 2 01.01.1900 Львы Маша
1 31.12.2015 Петя 3 01.01.2016 Орлы Коля
2 31.12.2015 Маша 1 01.01.2016 Львы Петя
1 31.12.2016 Петя
2 31.12.2016 Маша
3 31.12.2016 Коля


Ожидаемый результат
KOD Name Date sGroup
1 Петя 31.12.2014 Тигры
2 Маша 31.12.2014 Львы
1 Петя 31.12.2015 Тигры
2 Маша 31.12.2015 Львы
1 Петя 31.12.2016 Львы
2 Маша 31.12.2016 Львы
3 Коля 31.12.2016 Орлы

Смотрим на Петю. Он в 2016 переместился из одной группы в другую.
19 сен 17, 14:48    [20806228]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
igor.kovalchuk,

Картинка с другого сайта.
19 сен 17, 14:51    [20806244]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
igor.kovalchuk
Akina
Чёта типа
SELECT t01.*, t02.*
FROM t1 AS t01, t2 AS t02,
  ( SELECT t12.KOD, MAX(t12.DateIn) DateIn
    FROM t1 AS t11,t2 AS t12
    WHERE t11.KOD = t12.KOD
      AND t11.Date >= t12.DateIn
    GROUP BY t12.KOD
  ) AS q1
WHERE t01.KOD = t02.KOD
  AND t02.KOD = q1.KOD
  AND t02.DateIn = q1.DateIn


Заработало, вы гений ! :)


Понял, почему Ваш вариант не работает правильно.
q1 выполянется всего 1 раз только для какой-то скорее всего первой записи с t1.Date А потом из нее черпаются данные для всех остальных записей, что не есть верно, так как в них t1.Date будет менятся и соответсвенно должна менятся выборка q1.

По єтой логике я и запихнул заивисмій вложенный запрос на пересчет в каждой записи, что почему-то и завешивает Эксель/Ассеss.

Учитывая, что у меня t1.Date меняется редко (всего несколько раз). Можно ли как то принудительно инициировать пересчет q1 при изменении в поле t1.Date ?
19 сен 17, 15:32    [20806411]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 4671
igor.kovalchuk,
пожалуйста,сформулируйте стоящую перед Вами задачу типа: имеются две таблицы,при внесении новой записи в таблицу1 пересчитать таблицу2 в соответствии с новыми данными,для тех записей в которых полеN=полюM новой записи в таблице1
(и все-таки откуда берутся кодовые значения в Т2-насколько я помню в Ёкселе нет счетчика-если же это поле(ячейка) заполняется ручками-ошибки неизбежны.Может вообще все решать в Ёкселе-для чего обращение к Access)
19 сен 17, 16:00    [20806486]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
Попробовал переписать вариант Akina с JOIN.

SELECT t01.*, t02.[Группа]
FROM ([temp$] as t01 LEFT JOIN [Firms$] as t02 ON t01.KD = t02.KD)
LEFT JOIN (SELECT t12.KD, MAX (t12.[Год_вхождения]) as [Год_вхождения]
FROM [temp$] as t11, [Firms$] as t12
WHERE t11.KD = t12.KD
AND CDATE(t11.[sYear]) >= CDATE(t12.[Год_вхождения])
GROUP BY t12.KD) as q1
ON (t02.KD = q1.KD)
AND (t02.[Год_вхождения] = q1.[Год_вхождения])


Работает быстро, но опять не то.

На каждый [KD] у которого менялась [Группа] теперь в таблице есть две дублирующиеся записи : одна со старой, дурная с новой Группой.

Помогите пожалуйста , как переписать JOIN, чтобы устранить дубляжи ?
19 сен 17, 16:08    [20806509]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 16311
igor.kovalchuk
q1 выполянется всего 1 раз только для какой-то скорее всего первой записи с t1.Date А потом из нее черпаются данные для всех остальных записей,
Нет. Проблема в том, что Вы так и не смогли сформулировать задачу нормально. В частности, не указали, что поле KOD, блин, НЕ УНИКАЛЬНОЕ. Соответственно мой запрос решает совсем иную задачу.

Короче - тест-БД в студию.
19 сен 17, 16:11    [20806515]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
sdku,

Смотрите. У меня есть таблица из базы статистики T1. Поле KOD в ней уже заполнено. Это не счетчик. Поле Date - это дата на которую представлены данные. (по сути - последняя дата года отчетности, но это не важно)
Таблица T2. Это по сути справочник в котором практически для каждого KOD T1 проставлен признак sGroup, и DateIn - дата с которой этот признак ему присвоен.

Мне нужно объединить эти две таблицы так, чтобы в таблицe T1 добавилось поле sGroup и в каждой записи для которой T1.KOD=T2.KOD в этом поле стояло значение из T2.sGroup для максимальной T2.DateIn не превышающей дату отчетности T1.Date/

выше я нарисовал условную структуру таблиц и ожидаемый результат их обїединения. Для наглядности.
19 сен 17, 16:18    [20806537]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 4671
Akina
Короче - тест-БД в студию.
19 сен 17, 16:36    [20806630]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
Akina,

Да, наверно нечетко сфомулирвоал задачу. Если бы KOD был уникальным, то мне бы не потребовались бы танцы с полем Date и DateIn.

Тест-БД в студию. Подойдет, если это будут условные данные на двух листах в экселе в которые уже залиты данные из исходников ? В принципе я уже объединяю с листов в Эксле.

Щас он выдает ошибку потому что решил сделать INNER JOIN. Никак не пойму почему ошибка.
19 сен 17, 16:49    [20806681]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
igor.kovalchuk,

К сообщению приложен файл (Пример.xlsm - 87Kb) cкачать
19 сен 17, 16:50    [20806688]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
непоймучка
Guest
igor.kovalchuk
Мне нужно объединить эти две таблицы так, чтобы в таблицe T1 добавилось поле sGroup и в каждой записи для которой T1.KOD=T2.KOD в этом поле стояло значение из T2.sGroup для максимальной T2.DateIn не превышающей дату отчетности T1.Date/

так попробуйте
select T.KOD,T.Name,T.[Date],S.sGroup from
(select T1.*, 
(select max(T2.DateIn) as D from T2 where T2.KOD=T1.KOD and T2.DateIn<=T1.[Date]) as D 
 from T1
) T left join T2 as S on (T.KOD=S.KOD AND T.D=S.DateIn)
19 сен 17, 17:13    [20806810]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
igor.kovalchuk,

Корявый пример. Там Группы не расписаны.
Перевкладываю.

К сообщению приложен файл (Пример.xlsm - 141Kb) cкачать
19 сен 17, 18:55    [20807038]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
непоймучка,

Попробовал. Зависает : (
20 сен 17, 20:21    [20810250]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
непоймучка
Guest
igor.kovalchuk
непоймучка,

Попробовал. Зависает : (

Ну если записей много, то да, там ведь идет частичное перемножение таблиц.

Как вариант, можно делать через промежуточную/временную таблицу,
для этого в исходной таблице temp нужен Код (код записи - уникальный/счетчик)

Сначала создать времянку (temp1), записав туда результаты max-подзапроса:
SELECT
 T.Код,
 (SELECT Max(T0.Год_вхождения) FROM firms T0 WHERE T0.KD=T.KD and T0.Год_вхождения<=T.sYear) AS Год
INTO temp1
FROM temp AS T

А потом уже делать итоговую выборку через эту времянку:
SELECT T.*, S.Группа
FROM
(SELECT T.*, T1.Год FROM temp AS T INNER JOIN temp1 AS T1 ON T.Код=T1.Код) AS T
LEFT JOIN firms AS S ON (T.KD=S.KD) AND (T.Год=S.Год_вхождения)

Чтобы все шевелилось побыстрее, делать это лучше в таблицах MSAccess,
имея там индексы по полям KD(типа LONG) и sYear/Год_вхождения.
21 сен 17, 09:16    [20810949]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
непоймучка,

вся проблема в том, что Accessa на рабочем месте нету. Отчего-то компания офис без него только разрешила юзать.

А времянка поможет ?

я почему делаю под запрос, потому что поле sYear меняет свое значение и под него каждый раз нужно вычислять поле Группа так как поле kd в таблице firms не уникально из-за того что могут быть записи с одним kd но разными Год_вхождения.
21 сен 17, 21:09    [20813681]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
непоймучка
Guest
igor.kovalchuk
непоймучка,

вся проблема в том, что Accessa на рабочем месте нету. Отчего-то компания офис без него только разрешила юзать.

А времянка поможет ?

я почему делаю под запрос, потому что поле sYear меняет свое значение и под него каждый раз нужно вычислять поле Группа так как поле kd в таблице firms не уникально из-за того что могут быть записи с одним kd но разными Год_вхождения.
Ну я протестил те запросы, что выше предлагал (в temp~200K записей, в firms~10K записей). Первый запрос на создание врем.таблицы работал ~50 сек, второй запрос на выборку - секунд 30 читает, попробуйте сами.
21 сен 17, 22:08    [20813861]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
igor.kovalchuk
Member

Откуда:
Сообщений: 17
непоймучка,

спасибо.
ваш вариант еще не пробовал. завтра потестю.

сам пока родил такой, который наконец-то заработал на маленькой тестовой базе. завтра попробую на большой.

SELECT first(q1.KD) AS KD, first(Area) AS fArea, first(q1.sYear) AS fsYear, last(q1.Group) AS lGroup, max(q1.DateIn) AS m_DateIn
FROM (SELECT t1.*, t2.Group, t2.DateIn FROM temp AS t1 LEFT JOIN Firms AS t2 ON t1.KD = t2.KD ORDER BY DateIn)  AS q1
WHERE q1.sYear >= q1.DateIn
GROUP BY (CSTR(q1.kd) +"-"+ CSTR(q1.sYear))

UNION 

SELECT temp.KD, temp.Area, temp.sYear , 'other' as lGroup, '01.01.1900' as m_DateIn
FROM temp LEFT JOIN Firms ON temp.[KD] = Firms.[KD]
WHERE ((Firms.KD) Is Null);
22 сен 17, 00:00    [20814126]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
Гость проходящий мимо
Guest
igor.kovalchuk,

попробуй OUTER APPLY
22 сен 17, 09:09    [20814425]     Ответить | Цитировать Сообщить модератору
 Re: Зависает SQL-запрос содержащий вложенный зависимый запрос  [new]
непоймучка
Guest
igor.kovalchuk
непоймучка,

вся проблема в том, что Accessa на рабочем месте нету. Отчего-то компания офис без него только разрешила юзать.

А времянка поможет ?

Это не проблема. Чтобы создать и обработать времянки в mdb-файле, сам MSAccess не нужен.
Можно все сделать в VBA Excel через объекты доступа к данным (тот же ADODB, который вы в своем коде используете).
22 сен 17, 11:02    [20814903]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft Access Ответить