Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите с запросом  [new]
Arik
Member

Откуда:
Сообщений: 99
Добрый день!

Ребята подскажите как сделать следующее. Я получил таблицу следующего вида:

FromIDToIDf1f2f3f4t1t2t3t4
87326859251231012469347436382160216000
87050859259642963115528732160216000


Где f1-f4 ресурс у донора, t1-t4 необходимое количество ресурса у акцептора. Таблица наполняется таким образом, что в поле ToID всегда один получатель.
8 янв 16, 10:31    [18651875]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Arik
Member

Откуда:
Сообщений: 99
Опубликовалось, без вопроса.

Собственно сам вопрос.
Возможно ли добавить столбцы v1-v4 в которых будет содержаться количество ресурса, которое необходимо отправить с донора, с учетом того, чтобы не было переполнения на акцепторе. Т.е. сейчас перед каждой отправкой ,в коде программы проверяется сколько ресурса необходимо отправить, смотрим у кого его больше всего, и после этого только отправляем. Хотелось бы все это сделать в запросе. Т.е. Получить табличку примерно такого вида:

FromID ToID f1 f2 f3 f4 t1 t2 t3 t4v1v2v3v4
87326 85925 12310 1469 3474 3638 2160 2160 0 02160 197 0 0
87050 85925 964 1963 1155 2873 2160 2160 0 00 1963 0 0
8 янв 16, 10:43    [18651894]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21248
Ну если Вы опишете логику получения значений этих доп. столбцов...
8 янв 16, 13:41    [18652277]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Arik
Member

Откуда:
Сообщений: 99
Прошу прощения, думал это и так понятно.

FromIDToIDf1f2f3f4t1t2t3t4v1v2v3v4
8732685925123101469347436382160216000216019700
870508592596419631155287321602160000196300


B полях v1-v4 должно содержаться количество ресурса которое конкретный склад должен отправить, исходя из дефицита на акцепторе (столбцы t1-t4)


В столбце f1 содержится количество ресурса на складе с ID=87326, в поле t1 дефицит того же ресурса у акцептора. У нас имеется 2 записи. В первую очередь отправляем будем отправлять ресурс, со склада где его больше всего. Максимальное значение у первой записи, поэтому ресурс будет браться отсюда, количество ресурса полностью перекрывает потребность, следовательно в поле v1 ставим значение из поля t1. Далее во второй записи в поле v1 ставим нулевое значение, так как первый склад полностью перекрыл потребность.

В столбце f2 количество ресурса, t2 его дефицит на акцепторе. Больше всего второго ресурса на складе с ID=87050, но его недостаточно, чтобы перекрыть дефицит, следовательно мы отправляем все имеющееся на складе, а недостающее количество отправляем с других складов (руководствуясь правилом: в первую очередь отправляем со склада где этого ресурса наибольшее количество). Т.е. v2 второй записи будет иметь значение 1963 (так как его больше фактически нет на складе), а поле v2 первой записи будет иметь значение 197 (дефицит минус уже отправленное количество)
8 янв 16, 22:17    [18653825]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Arik,

Может не самое оптимальное решение, но с такой структурой таблицы ничего другого пока не смог придумать.
+
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #T
GO
CREATE TABLE #T(FromID int,ToID int,f1 int,f2 int,f3 int,f4 int,t1 int,t2 int,t3 int,t4 int,v1 int DEFAULT(0),v2 int DEFAULT(0),v3 int DEFAULT(0),v4 int DEFAULT(0))
GO
INSERT #T(FromID ,ToID ,f1 ,f2 ,f3 ,f4 ,t1 ,t2 ,t3 ,t4) VALUES(87326, 85925, 12310, 1469, 3474, 3638, 2160, 2160, 0, 0)
INSERT #T(FromID ,ToID ,f1 ,f2 ,f3 ,f4 ,t1 ,t2 ,t3 ,t4) VALUES(87050, 85925, 964, 1963, 1155, 2873, 2160, 2160, 0, 0)

WHILE NOT EXISTS(
  SELECT 1
  FROM #T
  HAVING SUM(v1)=AVG(t1) AND SUM(v2)=AVG(t2) AND SUM(v3)=AVG(t3) AND SUM(v4)=AVG(t4)
)
BEGIN
  UPDATE T
  SET 
    v1 =
      CASE 
        WHEN a1 = 1 AND v1 = 0 THEN 
        CASE 
          WHEN (t1-v1_new) < f1 
          THEN t1-v1_new
          ELSE f1 
        END 
      ELSE v1 
      END,
    v2 =
      CASE 
        WHEN a2 = 1 AND v2 = 0 THEN 
        CASE 
          WHEN (t2-v2_new) < f2 
          THEN t2-v2_new
          ELSE f2 
        END 
      ELSE v2 
      END,
    v3 =
      CASE 
        WHEN a3 = 1 AND v3 = 0 THEN 
        CASE 
          WHEN (t3-v3_new) < f3 
          THEN t3-v3_new
          ELSE f3 
        END 
      ELSE v3 
      END,
    v4 =
      CASE 
        WHEN a4 = 1 AND v4 = 0 THEN 
        CASE 
          WHEN (t4-v4_new) < f4 
          THEN t4-v4_new 
          ELSE f4 
        END 
      ELSE v4 
      END
  FROM (
    SELECT *, 
      ROW_NUMBER() OVER (ORDER BY f1-v1 DESC) a1,
      ROW_NUMBER() OVER (ORDER BY f2-v2 DESC) a2,
      ROW_NUMBER() OVER (ORDER BY f3-v3 DESC) a3,
      ROW_NUMBER() OVER (ORDER BY f4-v4 DESC) a4
    FROM #T  
      CROSS JOIN (SELECT SUM(v1) as v1_new, SUM(v2) as v2_new, SUM(v3) as v3_new, SUM(v4) as v4_new FROM #T) SS
  ) T

  IF EXISTS(
    SELECT 1
    FROM #T
    HAVING 
      SUM(f1) = SUM(v1) AND SUM(v1) < AVG(t1)
    OR  SUM(f2) = SUM(v2) AND SUM(v2) < AVG(t2)
    OR  SUM(f3) = SUM(v3) AND SUM(v3) < AVG(t3)
    OR  SUM(f4) = SUM(v4) AND SUM(v4) < AVG(t4)
  )
  BEGIN
    RAISERROR ('Insufficient amount', 16, 10)
    BREAK
  END
END

SELECT * FROM #T

Ну и вариант попроще если нормализовать таблицы:
+
IF OBJECT_ID('tempdb..#F') IS NOT NULL DROP TABLE #F
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #T
GO
CREATE TABLE #F(FromID int, ResourceID int, f int, v int DEFAULT(0))
CREATE TABLE #T(ToID int, ResourceID int, t int)
GO
INSERT #F(FromID, ResourceID, f) 
VALUES
(87326,  1, 12310),
(87326,  2, 1469),
(87326,  3, 3474),
(87326,  4, 3638),
(87050,  1, 964),
(87050,  2, 1963),
(87050,  3, 1155),
(87050,  4, 2873)

INSERT #T(ToID, ResourceID, t) 
VALUES
(85925, 1, 2160),
(85925, 2, 2160),
(85925, 3, 0),
(85925, 4, 0)

WHILE 1=1
BEGIN
  UPDATE T
  SET v = CASE 
            WHEN t-v_new < f 
            THEN t-v_new
            ELSE f 
          END 
  FROM (
    SELECT F.*, SS.v_new, T.t,
      ROW_NUMBER() OVER (PARTITION BY T.ResourceID ORDER BY f-v DESC) a
    FROM #F  F
      INNER JOIN #T T ON F.ResourceID = T.ResourceID
      INNER JOIN (SELECT ResourceID, SUM(v) as v_new FROM #F GROUP BY ResourceID) SS ON T.ResourceID = SS.ResourceID
  ) T
  WHERE a = 1 AND v = 0 AND t-v_new > 0

  IF @@ROWCOUNT = 0 BREAK
END

SELECT * FROM #F
9 янв 16, 00:46    [18654281]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Arik
Member

Откуда:
Сообщений: 99
Mind, Спасибо! Очень здорово!
9 янв 16, 09:39    [18654940]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Arik
Member

Откуда:
Сообщений: 99
Как оказалось, все же акцепторы тоже могут быть несколькими, кроме того еще имеется правило по которому высчитывается дефицит товара на складе. В варианте с нормализованными таблицами мне понятно как это сделать:

+ Вариант с нормализованными таблицами
IF OBJECT_ID('tempdb..#F') IS NOT NULL DROP TABLE #F
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #T
GO
CREATE TABLE #F(FromID int, ToID int DEFAULT(0), ResourceID int, f int, v int DEFAULT(0), RuleID int)
CREATE TABLE #T(ToID int, ResourceID int, t int, RuleID int)
GO
INSERT #F(FromID, ToID, ResourceID, f, RuleID) 
VALUES
(87326, 85925, 1, 12310, 1),
(87326, 85925, 2, 1469, 1),
(87326, 85925, 3, 3474, 1),
(87326, 85925, 4, 3638, 1),
(87050, 85925, 1, 964, 1),
(87050, 85925, 2, 1963, 1),
(87050, 85925, 3, 1155, 1),
(87050, 85925, 4, 2873, 1),
(87326, 90000, 1, 12310, 1),
(87326, 90000, 2, 1469, 1),
(87326, 90000, 3, 3474, 1),
(87326, 90000, 4, 3638, 1),
(87050, 90000, 1, 964, 1),
(87050, 90000, 2, 1963, 1),
(87050, 90000, 3, 1155, 1),
(87050, 90000, 4, 2873, 1),
(87751, 85925, 1, 2700, 2),
(87751, 85925, 2, 1000, 2),
(87751, 85925, 3, 5000, 2),
(87751, 85925, 4, 2100, 2),
(81019, 85925, 1, 13500, 3),
(81019, 85925, 2, 7412, 3),
(81019, 85925, 3, 3651, 3),
(81019, 85925, 4, 1121, 3)

INSERT #T(ToID, ResourceID, t, RuleID) 
VALUES
(85925, 1, 2160,1),
(85925, 2, 2160,1),
(85925, 3, 0,1),
(85925, 4, 0,1),
(90000, 1, 0,1),
(90000, 2, 0,1),
(90000, 3, 0,1),
(90000, 4, 15000,1),
(85925, 1, 500,2),
(85925, 2, 500,2),
(85925, 3, 1200,2),
(85925, 4, 3000,2),
(85925, 1, 0,3),
(85925, 2, 0,3),
(85925, 3, 0,3),
(85925, 4, 7000,3)

WHILE 1=1
BEGIN
  UPDATE T
  SET v = CASE 
            WHEN t-v_new < f 
            THEN t-v_new
            ELSE f 
          END 
  FROM (
    SELECT F.*, SS.v_new, T.t,
      ROW_NUMBER() OVER (PARTITION BY T.ResourceID, T.RuleID, T.ToID ORDER BY f-v DESC) a
    FROM #F  F
      INNER JOIN #T T ON F.ResourceID = T.ResourceID AND F.RuleID = T.RuleID AND F.ToID=T.ToID
      INNER JOIN (SELECT ResourceID, SUM(v) as v_new, RuleID, ToID FROM #F GROUP BY ResourceID, RuleID, ToID) SS ON T.ResourceID = SS.ResourceID AND T.RuleID = SS.RuleID AND F.ToID=T.ToID
  ) T
  WHERE a = 1 AND v = 0 AND t-v_new > 0

  IF @@ROWCOUNT = 0 BREAK
END

SELECT * FROM #F


FromID ToID ResourceID f v RuleID
87326 85925 1 12310 2160 1
87326 85925 2 1469 197 1
87326 85925 3 3474 0 1
87326 85925 4 3638 0 1
87050 85925 1 964 0 1
87050 85925 2 1963 1963 1
87050 85925 3 1155 0 1
87050 85925 4 2873 0 1
87326 90000 1 12310 0 1
87326 90000 2 1469 0 1
87326 90000 3 3474 0 1
87326 90000 4 3638 3638 1
87050 90000 1 964 0 1
87050 90000 2 1963 0 1
87050 90000 3 1155 0 1
87050 90000 4 2873 2873 1
87751 85925 1 2700 500 2
87751 85925 2 1000 500 2
87751 85925 3 5000 1200 2
87751 85925 4 2100 2100 2
81019 85925 1 13500 0 3
81019 85925 2 7412 0 3
81019 85925 3 3651 0 3
81019 85925 4 1121 1121 3



А вот с текущей структурой таблиц ума не приложу как выйти из ситуации, хоть пиши процедуру возвращающую нормализованную таблицу исходя из текущих данных.
+
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #T
IF OBJECT_ID('tempdb..#r') IS NOT NULL DROP TABLE #R
GO
CREATE TABLE #T(FromID int,ToID int,f1 int,f2 int,f3 int,f4 int,t1 int,t2 int,t3 int,t4 int, RuleID int,v1 int DEFAULT(0),v2 int DEFAULT(0),v3 int DEFAULT(0),v4 int DEFAULT(0))
CREATE TABLE #R(ID int, r1 int, r2 int, r3 int, r4 int)
GO

INSERT #T(FromID ,ToID ,f1 ,f2 ,f3 ,f4 ,t1 ,t2 ,t3 ,t4, RuleID) 
VALUES 
	(87326, 85925, 12310, 1469, 3474, 3638, 2160, 2160, 0, 0, 1),
	(87050, 85925, 964, 1963, 1155, 2873, 2160, 2160, 0, 0, 1),
	(87326, 90000, 12310, 1469, 3474, 3638, 0, 0, 0, 15000, 1),
	(87050, 90000, 964, 1963, 1155, 2873, 0, 0, 0, 15000, 1),
	(87751, 85925, 2700, 1000, 5000, 2100, 500, 500, 1200, 3000, 2),
	(81019, 85925, 13500, 7412, 3651, 1121, 0, 0, 0, 7000, 3)

INSERT #R(ID, r1, r2, r3, r4) 
VALUES 
	(1, 2160, 2160, 0, 0),
	(2, 500, 500, 1200, 3000),
	(3, 0, 0, 0, 7000)

SELECT t.FromID, t.ToID, t.f1, t.f2, t.f3, t.f4, t.t1, t.t2, t.t3, t.t4, r.r1, r.r2, r.r3, r.r4 FROM #T t, #R r WHERE t.RuleID=r.ID


FromID ToID f1 f2 f3 f4 t1 t2 t3 t4 r1 r2 r3 r4
87326 85925 12310 1469 3474 3638 2160 2160 0 0 2160 2160 0 0
87050 85925 964 1963 1155 2873 2160 2160 0 0 2160 2160 0 0
87326 90000 12310 1469 3474 3638 0 0 0 15000 2160 2160 0 0
87050 90000 964 1963 1155 2873 0 0 0 15000 2160 2160 0 0
87751 85925 2700 1000 5000 2100 500 500 1200 3000 500 500 1200 3000
81019 85925 13500 7412 3651 1121 0 0 0 7000 0 0 0 7000



Возможно ли сделать подобное с такой структурой данных?
9 янв 16, 17:11    [18656104]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Arik
Добрый день!

Ребята подскажите как сделать следующее. Я получил таблицу следующего вида:

FromIDToIDf1f2f3f4t1t2t3t4
87326859251231012469347436382160216000
87050859259642963115528732160216000


Где f1-f4 ресурс у донора, t1-t4 необходимое количество ресурса у акцептора. Таблица наполняется таким образом, что в поле ToID всегда один получатель.



необходимо прежде всего переделывать бд, это нарушение 1НФ.
11 янв 16, 12:09    [18661507]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Добрый Э - Эх
Guest
MasterZiv
необходимо прежде всего переделывать бд, это нарушение 1НФ.
человеку не нужен скучный секс в миссионерской позиции на ортопедическом матраце. человек хочет секса стоя, в гамаке, в костюме аквалангиста. и он спрашивает способ, как подставить ко всему этому приставную лестницу, чтобы занять нужную позицию в гамаке... зачем ему знать о наличии кровати и удобного матраца?
11 янв 16, 12:32    [18661639]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить