for flooders
:: Главная :: Решения :: Статьи :: Сайт М. Дроздова :: Файловый архив :: Книга по VFP 9 :: Русский Help Online :: OFF-LINE Форум
   Л и с о в о д ы   в с е х   с т р а н,  о б ъ е д и н я й т е с ь !!!  

Список Форумов  :: Visual Foxpro, Foxpro for DOS
   :: Помощь сайту :: 

Можно ли решить запросом?
ry
Автор

Сообщений: 1929
Дата: 07.06.18 09:12:33ОтветитьЦитировать
Есть три таблицы (для примера сделаю три курсора с минимально необходимым количеством полей):
  
  create cursor good (good_id i, code c (8), name c(50), pro c(15)) && Изделия  
  insert into good values (1, "01010001", "Любое название 1", "АБ-123")  
  insert into good values (2, "01010002", "Любое название 2", "АБ-124")  
  insert into good values (3, "02010001", "Любое название 1", "АБ-123")  
  insert into good values (4, "02010002", "Любое название 2", "АБ-124")  
  insert into good values (5, "02020001", "Любое название 1", "АБ-123")  
  insert into good values (6, "02020002", "Любое название 3", "АБ-125")  
    
  create cursor material (mat_id i, name c(30)) && Материалы  
  insert into material values (1, "Материал 1")  
  insert into material values (2, "Материал 2")  
  insert into material values (3, "Материал 3")  
  insert into material values (4, "Материал 4")  
    
  create cursor norma (good_id i, mat_id i, quant i) && Нормы расхода  
  insert into norma values (1, 1, 1)  
  insert into norma values (1, 2, 5)  
  insert into norma values (1, 3, 10)  
  insert into norma values (2, 1, 1)  
  insert into norma values (2, 2, 5)  
  insert into norma values (2, 3, 10)  
  insert into norma values (3, 1, 1)  
  insert into norma values (3, 2, 10)  
  insert into norma values (3, 4, 5)  
  insert into norma values (4, 1, 1)  
  insert into norma values (4, 2, 5)  
  insert into norma values (4, 3, 5)  
  insert into norma values (5, 1, 1)  
  insert into norma values (5, 4, 1)  
  insert into norma values (5, 5, 1)  
  insert into norma values (6, 1, 1)  
  insert into norma values (6, 2, 1)  
  insert into norma values (6, 3, 1)

Нужно сделать выборку, отображающую различие в нормах расхода для изделий с одинаковым полем pro, сравнивая при этом записи для двух заданных значений left(code,4). Для записей с одинаковым left(code,4) значения поля pro уникальные.

Например, требуется сравнить изделия с left(code,4)=="0101" и left(code,4)=="0201", в результате надо получить выборку, которая отобразит лишь не совпадающие значения:

"01010001", "Любое название 1", "Материал 2", 5, "02010001", "Любое название 1", "Материал 2", 10
"01010001", "Любое название 1", "Материал 3", 10, "02010001", "Любое название 1", "Материал 3", 0
"01010001", "Любое название 1", "Материал 4", 0, "02010001", "Любое название 1", "Материал 4", 5
"01010002", "Любое название 2", "Материал 3", 10, "02010002", "Любое название 2", "Материал 3", 5

Решить без запросов смогу и сам, и вообще сомневаюсь, что здесь можно одним-двумя запросами обойтись, но если можно как-нибудь красиво извернуться, подскажите.
Ratings: 0 negative/0 positive

Re: Можно ли решить запросом?
AndyNigmatec

Сообщений: 787
Откуда: Волгоград
Дата: 07.06.18 12:00:17ОтветитьЦитировать
Если я прально понял задачу, то полагаю в лоб так:

SELECT CAST(LEFT(a.code,4) as c(4)) as code1, a.code, a.name,;  
  	c.name as mat_name, b.quant;  
  	FROM good a JOIN norma b ON a.good_id=b.good_id;  
  	JOIN material c ON b.mat_id=c.mat_id;  
  	INTO CURSOR c1 READWRITE  
    
  lcCode1='0101'  
  lcCode2='0201'  
    
  SELECT DISTINCT a1.code, a1.name, a1.mat_name, a1.quant,;  
  	a2.code as code2, a2.name as name2, a2.mat_name as mat_name2, a2.quant as quant2;  
  	FROM c1 a1 JOIN c1 a2 ON a1.name=a2.name AND a1.code1==lcCode1 AND a2.code1==lcCode2;  
  	INTO CURSOR c2 READWRITE
Ratings: 0 negative/0 positive

Re: Можно ли решить запросом?
ry
Автор

Сообщений: 1929
Дата: 07.06.18 12:00:59ОтветитьЦитировать
Тему можно закрывать, уже реализовал через scan. Правда, время формирования отчета вышло около минуты (больше 20 тыс. изделий, больше сотни материалов, записей в нормах - более 500 тыс.). Но это не критично - выборка, можно считать, разовая.
Ratings: 0 negative/0 positive

Re: Можно ли решить запросом?
ry
Автор

Сообщений: 1929
Дата: 07.06.18 12:03:29ОтветитьЦитировать
AndyNigmatec
Если я прально понял задачу, то полагаю в лоб так:
SELECT CAST(LEFT(a.code,4) as c(4)) as code1, a.code, a.name,;  
  	c.name as mat_name, b.quant;  
  	FROM good a JOIN norma b ON a.good_id=b.good_id;  
  	JOIN material c ON b.mat_id=c.mat_id;  
  	INTO CURSOR c1 READWRITE  
    
  lcCode1='0101'  
  lcCode2='0201'  
    
  SELECT DISTINCT a1.code, a1.name, a1.mat_name, a1.quant,;  
  	a2.code as code2, a2.name as name2, a2.mat_name as mat_name2, a2.quant as quant2;  
  	FROM c1 a1 JOIN c1 a2 ON a1.name=a2.name AND a1.code1==lcCode1 AND a2.code1==lcCode2;  
  	INTO CURSOR c2 READWRITE

Сегодня не успеваю, завтра попробую проверить на реальных данных.
Ratings: 0 negative/0 positive

Re: Можно ли решить запросом?
Igor Korolyov

Сообщений: 31499
Дата: 07.06.18 12:52:18ОтветитьЦитировать
LOCAL lcCode1, lcCode2  
  lcCode1 = "0101"  
  lcCode2 = "0201"  
    
  SELECT NVL(p1.pro, p2.pro), material.mat_id, material.NAME, ;  
   p1.CODE, p1.NAME, p1.quant, p2.CODE, p2.NAME, p2.quant FROM ;  
   (SELECT good.CODE, good.NAME, good.pro, NORMA.mat_id, NORMA.quant ;  
   FROM good INNER JOIN NORMA ON NORMA.good_id = good.good_id ;  
   WHERE LEFT(good.CODE,4) == m.lcCode1) p1 ;  
   FULL OUTER JOIN ;  
   (SELECT good.CODE, good.NAME, good.pro, NORMA.mat_id, NORMA.quant ;  
   FROM good INNER JOIN NORMA ON NORMA.good_id = good.good_id ;  
   WHERE LEFT(good.CODE,4) == m.lcCode2) p2 ;  
   ON p2.pro == p1.pro AND p2.mat_id = p1.mat_id;  
   LEFT JOIN material ON material.mat_id = NVL(p1.mat_id, p2.mat_id) ;  
   WHERE NVL(p1.quant, 0) <> NVL(p2.quant, 0) ;  
   ORDER BY 1, 2

Большого смысла писать "виртуальные" данные для различающихся материалов (там где null в курсоре) я не вижу. В принципе эти данные можно вывести, дописав ещё пару join по "странному" условию и брать поля code/name уже из g1 и g2 (если твоё уточнение про "уникальность" реально соблюдается)
INNER JOIN good g1 ON LEFT(g1.CODE, 4) == m.lcCode1 AND g1.pro = NVL(p1.pro, p2.pro) ;  
   INNER JOIN good g2 ON LEFT(g2.CODE, 4) == m.lcCode2 AND g2.pro = NVL(p1.pro, p2.pro)
Только учти, что без индексов и по большим объёмам это будет лишний тормоз.

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

Структуры данных неоптимальна - если уж требуется работать по LEFT(good.CODE,4) то его стоило оформить отдельным полем. Тогда не нужен будет странноватый индекс по LEFT(good.CODE,4) для оптимизации такого рода запросов (или же играться с SET ANSI и длиной переменной, убрав LEFT из текста запроса - тогда и просто индекс по code поможет). Заодно можно сделать реально защищающий от "дубликатов по хитрому условию" candidate индекс - не плохо выглядящий LEFT(CODE,4)+pro а "красивый" code4+pro


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Можно ли решить запросом?
ry
Автор

Сообщений: 1929
Дата: 08.06.18 10:56:21ОтветитьЦитировать
To Igor Korolyov:
Почти то, что нужно. Дублировать название материала, конечно же, не обязательно. Это я просто в примере делал. Еще добавил отсев записей с пустым полем pro (то есть уникальности по полю на самом деле нет, так как пустые значения могут повторяться в пределах одного left(code,4). Проверил на реальных данных, работает быстро - 2-3 секунды. Правда, в выборку также попадают "лишние" изделия, которые есть только в одном из подзапросов (впрочем, таких оказалось не много, так что не проблема). А вот отобразить одновременно коды и названия изделий в обеих группах мне не удалось, что не понравилось заказчику. То есть хотелось бы полностью избавиться от NULL. Пока оставил свое решение как есть, время выполнения заказчика пока не напрягает.

Структура таблиц и поля code не оптимальная, но "так исторически сложилось" - менять не могу, надо только анализировать данные. Индексы есть по всем полям в запросе (кроме количества), но нет индекса по left(code,4). Все равно запрос работает почти мгновенно.

To AndyNigmatec:
Немного не то - получается что-то вроде декартова произведения отобранных записей.
Ratings: 0 negative/0 positive

Re: Можно ли решить запросом?
Igor Korolyov

Сообщений: 31499
Дата: 08.06.18 13:13:00ОтветитьЦитировать
Я имел в виду вот так добавлять код/имя для null-строк. Заодно использование INNER JOIN отбросит "изделия, которые есть только в одном из подзапросов".
Ну и оптимизация - раз структуру не поменять, то индекс по code можно вот таким финтом использовать - только обязательно задавать параметры 4-мя символами, иначе ANSI OFF и = не тот результат дадут.
SET ANSI OFF  
  SELECT NVL(p1.pro, p2.pro) pro, material.mat_id, material.NAME material_name, ;  
   g1.CODE goods_code1, g1.NAME goods_name1, CAST(NVL(p1.quant, 0) AS I) material_quantity1, ;  
   g2.CODE goods_code2, g2.NAME goods_name2, CAST(NVL(p2.quant, 0) AS I) material_quantity2 FROM ;  
   (SELECT good.CODE, good.NAME, good.pro, NORMA.mat_id, NORMA.quant ;  
   FROM good INNER JOIN NORMA ON NORMA.good_id = good.good_id ;  
   WHERE good.CODE = m.lcCode1) p1 ;  
   FULL OUTER JOIN ;  
   (SELECT good.CODE, good.NAME, good.pro, NORMA.mat_id, NORMA.quant ;  
   FROM good INNER JOIN NORMA ON NORMA.good_id = good.good_id ;  
   WHERE good.CODE = m.lcCode2) p2 ;  
   ON p2.pro = p1.pro AND p2.mat_id = p1.mat_id;  
   LEFT JOIN material ON material.mat_id = NVL(p1.mat_id, p2.mat_id) ;  
   INNER JOIN good g1 ON g1.CODE = m.lcCode1 AND g1.pro = NVL(p1.pro, p2.pro) ;  
   INNER JOIN good g2 ON g2.CODE = m.lcCode2 AND g2.pro = NVL(p1.pro, p2.pro) ;  
   WHERE NVL(p1.quant, 0) <> NVL(p2.quant, 0) ;  
   ORDER BY 1, 2


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Можно ли решить запросом?
ry
Автор

Сообщений: 1929
Дата: 12.06.18 09:25:24ОтветитьЦитировать
Сегодня добрался поэкспериментировать. Время выполнения запроса на реальных данных возросло (в зависимости от начальных параметров выборки) где-то до 10 секунд и даже до полминуты. При SET ANSI OFF и сравнении по предложенному варианту 4 символов с полным полем через "=" на реальных данных получил неверные результаты. С чем это связано, разбираться не стал. При сравнении через left(code,4) и "==" результаты верные, но для некоторых пар lcCode1 и lcCode2 неожиданно получил ошибку "File ...tmp is too large". Я так понимаю, промежуточный запрос формирует временной файл, вылетающий за предел 2 ГБ. В общем, решил дальше не продолжать. Конечно, перебор сканом работает дольше (от одной до трех минут), но зато выдает нужный результат, а время выполнения пока не критично.
Ratings: 0 negative/0 positive



Извините, только зарегистрированные пользователи могут писать в этом форуме.

On-line: 49 and Guests: 49


© 2000-2018 Fox Club 
Яндекс.Метрика