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

Откуда:
Сообщений: 18
Есть некая таблица Table1
idnamesummaperiod
1adr15201501
1adr18201502
1adr21201503
1adr25201504
1adr15201505
2bdr50201503
2bdr60201504
2bdr70201505
2bdr80201506
2bdr90201507
2bdr70201508
2bdr100201509
3cdr30201601
3cdr40201602
3cdr15201603
4ddr50201601
4ddr10201602


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

результат типа :
idnameresult
1adr201505
3cdr201603
4 апр 17, 11:54    [20364520]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
wikend33,

ну если через окна, то можно получить прирост так:
($1.*summa)/SUM(summa) OVER (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING )

если версия позволяет
4 апр 17, 12:05    [20364600]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
лолл
Member

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

SELECT
  [id]      = [id],
  [name]    = [name],
  [result]  = [period]
FROM
(
  SELECT
    *,
    [X] = CASE
            WHEN [summa] <= 0.2 * [K] + [K]  -- Сумма меньше либо равна среднему арифметическому двух предыдущих сумм
             AND [Убывает] = 1               -- Сумма убывает в порядке периода
             AND LAG([K]) OVER (PARTITION BY [name] ORDER BY [period]) IS NOT NULL -- Для контроля наличия двух предыдущих значений
            THEN 1
            ELSE 0
          END
  FROM
  (
    SELECT
      *,
      [K]       = SUM([summa]) OVER (PARTITION BY [name] ORDER BY [period] ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) / 2.,
      [Убывает] = CASE WHEN
                    [summa]
                    >=
                    MAX([summa]) OVER (PARTITION BY [name] ORDER BY [period] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
                    THEN 1
                    ELSE 0
                  END
    FROM
    (
      VALUES
      (1, 'adr', 15,  201501),
      (1, 'adr', 18,  201502),
      (1, 'adr', 21,  201503),
      (1, 'adr', 25,  201504),
      (1, 'adr', 15,  201505),
      (2, 'bdr', 50,  201503),
      (2, 'bdr', 60,  201504),
      (2, 'bdr', 70,  201505),
      (2, 'bdr', 80,  201506),
      (2, 'bdr', 90,  201507),
      (2, 'bdr', 70,  201508),
      (2, 'bdr', 100, 201509),
      (3, 'cdr', 30,  201601),
      (3, 'cdr', 40,  201602),
      (3, 'cdr', 15,  201603),
      (4, 'ddr', 50,  201601),
      (4, 'ddr', 10,  201602)
    ) [1]([id], [name], [summa], [period])
  ) [2]
) [3]
WHERE [X] = 1
4 апр 17, 12:50    [20364844]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
лолл
Member

Откуда:
Сообщений: 450
Заметил ошибку... Убывание неверно определял, нужно ж удостовериться, что убывает на всем диапазоне оставшихся значений периода:
SELECT
  [id]      = [id],
  [name]    = [name],
  [result]  = [period]
FROM
(
  SELECT
    *,
    [X] = CASE
            WHEN [summa] <= 0.2 * [K] + [K]  -- Сумма уменьшилась на 20% от среднего арифметического предыдущих двух сумм
             AND [Убывает] = 1               -- Сумма убывает в порядке периода
             AND MIN([Убывает]) OVER (PARTITION BY [name] ORDER BY [period] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) = 1 -- Убывает на всех следующих периодах
             AND LAG([K]) OVER (PARTITION BY [name] ORDER BY [period]) IS NOT NULL -- Для контроля наличия двух предыдущих значений
            THEN 1
            ELSE 0
          END
  FROM
  (
    SELECT
      *,
      [K]       = SUM([summa]) OVER (PARTITION BY [name] ORDER BY [period] ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) / 2.,
      [Убывает] = CASE WHEN
                    [summa]
                    >=
                    MAX([summa]) OVER (PARTITION BY [name] ORDER BY [period] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
                    THEN 1
                    ELSE 0
                  END
    FROM
    (
      VALUES
      (1, 'adr', 15,  201501),
      (1, 'adr', 18,  201502),
      (1, 'adr', 21,  201503),
      (1, 'adr', 25,  201504),
      (1, 'adr', 15,  201505),
      (2, 'bdr', 50,  201503),
      (2, 'bdr', 60,  201504),
      (2, 'bdr', 70,  201505),
      (2, 'bdr', 80,  201506),
      (2, 'bdr', 90,  201507),
      (2, 'bdr', 70,  201508),
      (2, 'bdr', 100, 201509),
      (3, 'cdr', 30,  201601),
      (3, 'cdr', 40,  201602),
      (3, 'cdr', 15,  201603),
      (4, 'ddr', 50,  201601),
      (4, 'ddr', 10,  201602)
    ) [1]([id], [name], [summa], [period])
  ) [2]
) [3]
WHERE [X] = 1
4 апр 17, 14:11    [20365347]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
лолл
Member

Откуда:
Сообщений: 450
Исправленный вариант:

SELECT
  [id]      = [id],
  [name]    = [name],
  [result]  = [period]
FROM
(
  SELECT
    *,
    [X] = CASE
            WHEN [summa] <= 0.2 * [K] + [K]  -- Сумма уменьшилась на 20% от среднего арифметического предыдущих двух сумм
             AND [Убывает] = 1               -- Сумма убывает в порядке периода
             AND MIN([Убывает]) OVER (PARTITION BY [name] ORDER BY [period] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) = 1 -- Убывает во всех последующих периодах
             AND LAG([K]) OVER (PARTITION BY [name] ORDER BY [period]) IS NOT NULL -- Для контроля наличия двух предыдущих значений
            THEN 1
            ELSE 0
          END
  FROM
  (
    SELECT
      *,
      [K]       = SUM([summa]) OVER (PARTITION BY [name] ORDER BY [period] ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) / 2.,
      [Убывает] = CASE
                    WHEN [summa] <= LAG([summa]) OVER (PARTITION BY [name] ORDER BY [period])
                      THEN 1
                      ELSE 0
                  END
    FROM
    (
      VALUES
      (1, 'adr', 15,  201501),
      (1, 'adr', 18,  201502),
      (1, 'adr', 21,  201503),
      (1, 'adr', 25,  201504),
      (1, 'adr', 15,  201505),
      (2, 'bdr', 50,  201503),
      (2, 'bdr', 60,  201504),
      (2, 'bdr', 70,  201505),
      (2, 'bdr', 80,  201506),
      (2, 'bdr', 90,  201507),
      (2, 'bdr', 70,  201508),
      (2, 'bdr', 100, 201509),
      (3, 'cdr', 30,  201601),
      (3, 'cdr', 40,  201602),
      (3, 'cdr', 15,  201603),
      (4, 'ddr', 50,  201601),
      (4, 'ddr', 10,  201602)
    ) [1]([id], [name], [summa], [period])
  ) [2]
) [3]
WHERE [X] = 1
4 апр 17, 14:23    [20365396]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
wikend33
Member

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

я так понимаю что надо добавить [result] = min([period]) as periodstart

а в концовке

group by id,name
,

а если количество среднеарифметических значений произвольная, то есть сначала берем (summa1+summa2+summa3+...+summa_n)/n , и % - тоже значение произвольное?

можна ли заменить их:

DECLARE @N INT
SET @N=3

DECLARE @PR DECIMAL(2,1)
SET @PR=0,4
4 апр 17, 15:07    [20365592]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
лолл
Member

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

если количество среднеарифметических значений внутри одного [name] произвольное, то партицию [name] надо разбить на партиции в соответствии с этим количеством.. еще одним уровнем подзапроса
4 апр 17, 15:38    [20365741]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
wikend33
Member

Откуда:
Сообщений: 18
Типа чтобы было так:


DECLARE @N INT
DECLARE @PR DECIMAL(2,1)

SET @N=3
SET @PR=0,4

SELECT
[id] = [id],
[name] = [name],
[result] = min([period]) as periodstart
FROM
(
SELECT
*,
[X] = CASE
WHEN [summa] <= @PR * [K] + [K] -- Сумма уменьшилась на 20% от среднего арифметического предыдущих двух сумм
AND [Убывает] = 1 -- Сумма убывает в порядке периода
AND MIN([Убывает]) OVER (PARTITION BY [name] ORDER BY [period] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) = 1 -- Убывает во всех последующих периодах
AND LAG([K]) OVER (PARTITION BY [name] ORDER BY [period]) IS NOT NULL -- Для контроля наличия двух предыдущих значений
THEN 1
ELSE 0
END
FROM
(
SELECT
*,
[K] = SUM([summa]) OVER (PARTITION BY [name] ORDER BY [period] ROWS BETWEEN @n PRECEDING AND 1 PRECEDING) / @n.,
[Убывает] = CASE
WHEN [summa] <= LAG([summa]) OVER (PARTITION BY [name] ORDER BY [period])
THEN 1
ELSE 0
END
FROM
(
VALUES
(1, 'adr', 15, 201501),
(1, 'adr', 18, 201502),
(1, 'adr', 21, 201503),
(1, 'adr', 25, 201504),
(1, 'adr', 15, 201505),
(2, 'bdr', 50, 201503),
(2, 'bdr', 60, 201504),
(2, 'bdr', 70, 201505),
(2, 'bdr', 80, 201506),
(2, 'bdr', 90, 201507),
(2, 'bdr', 70, 201508),
(2, 'bdr', 100, 201509),
(3, 'cdr', 30, 201601),
(3, 'cdr', 40, 201602),
(3, 'cdr', 15, 201603),
(4, 'ddr', 50, 201601),
(4, 'ddr', 10, 201602)
) [1]([id], [name], [summa], [period])
) [2]
) [3]
WHERE [X] = 1
group by id,name
4 апр 17, 15:43    [20365774]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
wikend33
Member

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

нет среднеарифметическое зачення (id, name) одинаковое
4 апр 17, 15:54    [20365846]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
лолл
Member

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

нет, вам нужно будет дробить ваши данные на более мелкие партиции, а зачем посчитать в них суммы и разделить на количество записей в каждой партиции. SUM()/COUNT()
4 апр 17, 16:01    [20365877]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
wikend33
Member

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

либо я не понял Вашу идею, либо неправильно обозначел задачу. начальное виражение одно і тоже для всех id:

если (summa1+summa2+...+summaN)/n*(1-@procent)<=summa(n+1) тогда проверка (summa2+summa3+...+summa(N+2)/n*(1-@procent)>summa(n+2) и т.д.
4 апр 17, 16:44    [20366113]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
лолл
Member

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

действительно, я вас совершенно не понимаю) за 6 лет на форуме вы не только не научились составлять запросы самостоятельно, но и формулировать ваши задачи :)
4 апр 17, 17:02    [20366194]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
wikend33
Member

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

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

За предыдущие ответы и потраченное время всеравно спасибо.
4 апр 17, 17:22    [20366261]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
лолл
Member

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

вы открываете вашу задачу частями, прочтите ваше первое сообщение. там была одна формулировка, которую вы начали дополнять в других сообщениях, но общей задачи я не увидел. мне кажется, что вам следует попрактиковаться в использовании оконных функций и тогда вы без труда доделаете свою задачу.
4 апр 17, 18:22    [20366467]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
wikend33
Member

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

спасибо :)
5 апр 17, 08:38    [20367426]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить