Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оконные функций в subqwery / 12 сообщений из 12, страница 1 из 1
02.09.2021, 08:44
    #40094678
Ablaykhan
Ablaykhan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Здравствуйте, у меня не получается записать оконные функций в subqwery для того чтобы я мог обращаться к этим столбцам в других оконных функциях.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT 
	*,
(([Количество (в базовых ед#)]/[Кол-во])*100)
	 AS Dolya,
ceiling(([Количество (в базовых ед#)]/[Кол-во])*[ДЛ#ПОЛКИ]) 
	 AS cm,
floor(ceiling(([Количество (в базовых ед#)]/[Кол-во])*[ДЛ#ПОЛКИ])/[Длина])
	 AS Face,
floor(([ГЛ#ПОЛКИ]/[глубина ])*(ceiling(([Количество (в базовых ед#)]/[Кол-во])*[ДЛ#ПОЛКИ])/[Длина])) 
	 AS QuantFace,
floor([ГЛ#ПОЛКИ]/[глубина ]) 
	 AS GlubinaFace,
 ([ДЛ#ПОЛКИ]*[ГЛ#ПОЛКИ])
     AS [Площадъ полки,cm2],
 floor([Длина]*[глубина ])
     AS [Площадь товара, см2],
	  (([Количество (в базовых ед#)] / sum([Количество (в базовых ед#)]) over(partition by [АДРЕС ]))*100) AS [Доля товара ед., %], 
	   (([Сумма (упр#)] / sum([Сумма (упр#)]) over(partition by [АДРЕС ]))*100)  AS [Доля товара тг., %]
	   FROM Лист2$


именно вот эти оконные функций мне нужно записать в Subqwery
Код: sql
1.
2.
(([Количество (в базовых ед#)] / sum([Количество (в базовых ед#)]) over(partition by [АДРЕС ]))*100) AS [Доля товара ед., %], 
	   (([Сумма (упр#)] / sum([Сумма (упр#)]) over(partition by [АДРЕС ]))*100)  AS [Доля товара тг., %]



но как только я записываю их в subqwery
Код: sql
1.
2.
  (SELECT (([Количество (в базовых ед#)] / sum([Количество (в базовых ед#)]) over(partition by [АДРЕС ]))*100) FROM Лист2$ )  AS [Доля товара ед., %], 
	   (SELECT (([Сумма (упр#)] / sum([Сумма (упр#)]) over(partition by [АДРЕС ]))*100) FROM Лист2$)  AS [Доля товара тг., %]


У меня выходит такая ошибка:

Вложенный запрос вернул больше одного значения. Это запрещено, когда вложенный запрос следует после =, !=, <, <=, >, >= или используется в качестве выражения.

Я знаю что, если добавить TOP 1 ошибка исчезнет, но это неправильный вывод данных. Подскажите пожалуйста как решить эту проблему.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.09.2021, 09:05
    #40094681
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Включить голову и записать этот бред попроще.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.09.2021, 10:58
    #40094735
Ablaykhan
Ablaykhan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
aleks222,
Я объяснил максимально подробно. Голова у меня на месте и включена.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.09.2021, 11:21
    #40094748
Oleg_SQL
Oleg_SQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Ablaykhan,

возможно, если я верно понял, вам нужно использовать CTE ?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
; WITH CTE AS 
(
SELECT 
	*,
(([Количество (в базовых ед#)]/[Кол-во])*100)
	 AS Dolya,
ceiling(([Количество (в базовых ед#)]/[Кол-во])*[ДЛ#ПОЛКИ]) 
	 AS cm,
floor(ceiling(([Количество (в базовых ед#)]/[Кол-во])*[ДЛ#ПОЛКИ])/[Длина])
	 AS Face,
floor(([ГЛ#ПОЛКИ]/[глубина ])*(ceiling(([Количество (в базовых ед#)]/[Кол-во])*[ДЛ#ПОЛКИ])/[Длина])) 
	 AS QuantFace,
floor([ГЛ#ПОЛКИ]/[глубина ]) 
	 AS GlubinaFace,
 ([ДЛ#ПОЛКИ]*[ГЛ#ПОЛКИ])
     AS [Площадъ полки,cm2],
 floor([Длина]*[глубина ])
     AS [Площадь товара, см2],
	  (([Количество (в базовых ед#)] / sum([Количество (в базовых ед#)]) over(partition by [АДРЕС ]))*100) AS [Доля товара ед., %], 
	   (([Сумма (упр#)] / sum([Сумма (упр#)]) over(partition by [АДРЕС ]))*100)  AS [Доля товара тг., %]
	   FROM Лист2$
)

SELECT * FROM CTE



и уже от туда брать что нужно?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.09.2021, 14:12
    #40094823
Ablaykhan
Ablaykhan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Oleg_SQL,

Спасибо большое, вы мне очень помогли. Оттуда мне нужно взять среднее [Доля товара ед., %] между [Доля товара тг., %] после взять минимальное [Доля товара ед., %] между [Доля товара тг., %] после из них, точнее из среднего которое мы получили и минимальным найти среднее. По другому никак описать. Извините что так запутано. В Excel это выглядит примерно так:
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.09.2021, 14:34
    #40094828
Oleg_SQL
Oleg_SQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Ablaykhan,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
DECLARE @cte TABLE(
                   [Доля товара ед., %] MONEY, 
                   [Доля товара тг., %] MONEY
);

INSERT INTO @cte
VALUES (0.033, 0.050), (0.156, 0.528);;

SELECT *, 
       (([Доля товара ед., %] + [Доля товара тг., %]) / 2 + IIF([Доля товара ед., %] > [Доля товара тг., %], [Доля товара тг., %], [Доля товара ед., %])) / 2 AS [Ср.доли, %]
  FROM @cte;



Код: sql
1.
2.
3.
4.
Доля товара ед., %    Доля товара тг., %    Ср.доли, %
--------------------- --------------------- ---------------------
0,033                 0,05                  0,0372
0,156                 0,528                 0,249
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.09.2021, 10:14
    #40095014
Ablaykhan
Ablaykhan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Oleg_SQL,

Спасибо большое, а как сделать так чтобы я мог обращаться и к [Ср.доли, %]
Чтобы я мог использовать оконную функцию (partition by ) для [Ср.доли, %].Ну примерно так:

Код: sql
1.
([Ср.доли, %] / sum([Ср.доли, %]) over(partition by [АДРЕС ]))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.09.2021, 10:30
    #40095019
Oleg_SQL
Oleg_SQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Ablaykhan
Oleg_SQL,

Спасибо большое, а как сделать так чтобы я мог обращаться и к [Ср.доли, %]
Чтобы я мог использовать оконную функцию (partition by ) для [Ср.доли, %].Ну примерно так:

Код: sql
1.
([Ср.доли, %] / sum([Ср.доли, %]) over(partition by [АДРЕС ]))



Пойдем простым логическим путем:
Код: sql
1.
(([Доля товара ед., %] + [Доля товара тг., %]) / 2 + IIF([Доля товара ед., %] > [Доля товара тг., %], [Доля товара тг., %], [Доля товара ед., %])) / 2

- ЭТО И ЕСТЬ [Ср.доли, %]

подставляем в
Код: sql
1.
([Ср.доли, %] / sum([Ср.доли, %]) over(partition by [АДРЕС ]))



получаем
Код: sql
1.
(((([Доля товара ед., %] + [Доля товара тг., %]) / 2 + IIF([Доля товара ед., %] > [Доля товара тг., %], [Доля товара тг., %], [Доля товара ед., %])) / 2) / sum(((([Доля товара ед., %] + [Доля товара тг., %]) / 2 + IIF([Доля товара ед., %] > [Доля товара тг., %], [Доля товара тг., %], [Доля товара ед., %])) / 2)) over(partition by [АДРЕС ]))



либо оборачиваем далее для читабельности кода:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
;WITH CTE AS (
километры кода формул
),
CTE2 AS
(
SELECT *, 
       (([Доля товара ед., %] + [Доля товара тг., %]) / 2 + IIF([Доля товара ед., %] > [Доля товара тг., %], [Доля товара тг., %], [Доля товара ед., %])) / 2 AS [Ср.доли, %]
  FROM cte
)
SELECT  [Ср.доли, %], [Ср.доли, %] / sum([Ср.доли, %]) over(partition by [АДРЕС ])
FROM CTE2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.09.2021, 12:13
    #40095068
Ablaykhan
Ablaykhan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Oleg_SQL,

Вы волшебник, Спасибо большое
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2021, 12:16
    #40095924
Ablaykhan
Ablaykhan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Ablaykhan,

Здравствуйте, у меня не получается выгрузить [Ср.доля на полке, %].
Код вы уже знаете
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
WITH CTE1
     AS (SELECT *, 
                ([Количество (в базовых ед#)] / [Кол-во]) * 100 AS Dolya, 
                CEILING(([Количество (в базовых ед#)] / [Кол-во]) * [ДЛ#ПОЛКИ]) AS cm, 
                FLOOR(CEILING(([Количество (в базовых ед#)] / [Кол-во]) * [ДЛ#ПОЛКИ]) / [Длина]) AS Face, 
                FLOOR(([ГЛ#ПОЛКИ] / [глубина ]) * CEILING(([Количество (в базовых ед#)] / [Кол-во]) * [ДЛ#ПОЛКИ]) / [Длина]) AS QuantFace, 
                FLOOR([ГЛ#ПОЛКИ] / [глубина ]) AS GlubinaFace, 
                [ДЛ#ПОЛКИ] * [ГЛ#ПОЛКИ] AS [Площадъ полки,cm2], 
                FLOOR([Длина] * [глубина ]) AS [Площадь товара, см2], 
                ([Количество (в базовых ед#)] / SUM([Количество (в базовых ед#)]) OVER(PARTITION BY [АДРЕС ])) * 100 AS [Доля товара ед., %], 
                ([Сумма (упр#)] / SUM([Сумма (упр#)]) OVER(PARTITION BY [АДРЕС ])) * 100 AS [Доля товара тг., %]
           FROM dbo.Лист2$),
     CTE2
     AS (SELECT *, 
                (([Доля товара ед., %] + [Доля товара тг., %]) / 2 + IIF([Доля товара ед., %] > [Доля товара тг., %], [Доля товара тг., %], [Доля товара ед., %])) / 2 AS [Ср.доли, %]
           FROM CTE1),
     CTE3
     AS (SELECT *, 
                [Ср.доли, %] / SUM([Ср.доли, %]) OVER(PARTITION BY [АДРЕС ]) AS [Доля на полке, %], 
                ([Ср.доли, %] / SUM([Ср.доли, %]) OVER(PARTITION BY [АДРЕС ])) * [ДЛ#ПОЛКИ] AS [Кол-во фэйсов на полке, см], 
                CEILING((([Ср.доли, %] / SUM([Ср.доли, %]) OVER(PARTITION BY [АДРЕС ])) * [ДЛ#ПОЛКИ]) / [Длина]) AS [Кол-во фэйсов на полке, ед]
           FROM CTE2)
     SELECT *
       FROM CTE3
	   WHERE [Длина] IS NOT NULL;


Для того чтобы найти [Ср.доля на полке, %] нужно сперва найти минимальное значение [Доля на полке, %] по [АДРЕС ] после по такой же логике нужно найти среднее значение и уже из них найти среднее значение после разделить результат на 3 и округлить итог до 5 значений.
Извините что так часто у вас спрашиваю и обращаюсь к вам. Если что то непонятно я приложил скрин от excel файла. Примерно как это выглядит в Excel-е.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2021, 15:16
    #40096019
Oleg_SQL
Oleg_SQL
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Ablaykhan,

авторДля того чтобы найти [Ср.доля на полке, %] нужно сперва найти минимальное значение [Доля на полке, %] по [АДРЕС ] после по такой же логике нужно найти среднее значение и уже из них найти среднее значение после разделить результат на 3 и округлить итог до 5 значений.


но это же однотипно тому что уже было ранее...

Код: sql
1.
2.
3.
4.
...
SELECT ROUND((MIN([Доля на полке, %]) OVER (PARTITION BY [АДРЕС ]) + AVG([Доля на полке, %]) OVER (PARTITION BY [АДРЕС ])) / 2 / 3, 5) AS [Ср.доля на полке, %]
	,*
FROM CTE3
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2021, 07:31
    #40096203
Ablaykhan
Ablaykhan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оконные функций в subqwery
Oleg_SQL,
Спасибо большое. Да вы правы, это тоже самое. Я просто не знал как сделать так чтобы между минимальным и среднем найти среднее, а оказалось это просто "+". Еще раз спасибо большое вам, что помогаете.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оконные функций в subqwery / 12 сообщений из 12, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 395ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1684322]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 31ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 57ms
get tp. blocked users:
 2ms
others: 257ms
total:  395ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]