Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
 Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
myrzilka
Member

Откуда:
Сообщений: 54
 		Добрый день.
Есть запрос вида

SELECT top 2 with ties ROW_NUMBER() OVER (PARTITION BY Продукт ORDER BY Цена DESC) AS N , * FROM
table1
where Продукт in ('Продукт 1', 'Продукт 2')
order by ROW_NUMBER() OVER (PARTITION BY Организация ORDER BY COUNT(Цена) DESC)

Но результат всё равно выдает по одной строке на каждый продукт. Если поставить TOP 6 - выдает по 3 строки.
Как сделать запрос, чтобы результат был по 2 или более ROW_NUMBER() для каждого продукта?
26 окт 17, 11:19    [20901340]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
myrzilka
Member

Откуда:
Сообщений: 54
 		Прошу прощения опечатка..

Добрый день.
Есть запрос вида

SELECT top 2 with ties ROW_NUMBER() OVER (PARTITION BY Продукт ORDER BY Цена DESC) AS N , * FROM
table1
where Продукт in ('Продукт 1', 'Продукт 2')
order by ROW_NUMBER() OVER (PARTITION BY Продукт ORDER BY Цена DESC)

Но результат всё равно выдает по одной строке на каждый продукт. Если поставить TOP 6 - выдает по 3 строки.
Как сделать запрос, чтобы результат был по 2 или более ROW_NUMBER() для каждого продукта?
26 окт 17, 11:25    [20901370]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
LoopN
Guest 		select * from
(
SELECT top 2 with ties ROW_NUMBER() OVER (PARTITION BY Продукт ORDER BY Цена DESC) AS N , * FROM
table1
where Продукт in ('Продукт 1', 'Продукт 2')
) z
where N <3
26 окт 17, 11:25    [20901372]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
LoopN
Guest 
select * from
(
SELECT ROW_NUMBER() OVER (PARTITION BY Продукт ORDER BY Цена DESC) AS N , * FROM
table1
where Продукт in ('Продукт 1', 'Продукт 2')
) z
where N <3
26 окт 17, 11:26    [20901377]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
myrzilka
Member

Откуда:
Сообщений: 54
 		LoopN, в этом случае не сработает 'with ties', а это важно в моем случае.

Если написать

select * from
(
SELECT ROW_NUMBER() OVER (PARTITION BY Продукт ORDER BY Цена DESC) AS N , * FROM
table1
where Продукт in ('Продукт 1', 'Продукт 2')
) z
where N <3


то получим только те записи, у которых ROW_NUMBER() = 3, а with ties дал бы результат всех записей, у которых цена такая же как и у ROW_NUMBER() = 3
26 окт 17, 11:37    [20901425]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
 		myrzilka,

Словами опишите, что должно быть на выходе.
TOP(2) WITH TIES вам не подойдёт - печёнкой чую! Но надо убедиться после ваших разъяснений.

Например, один продукт (PARTITION BY Продукт) может иметь две и более цены? Как это?
26 окт 17, 12:34    [20901695]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
o-o
Guest
iap
Например, один продукт (PARTITION BY Продукт) может иметь две и более цены? Как это?

ну например там еще есть колонка "модель" или "сорт".
и вот у него есть продукт = яблоко, сортов аж 10, из них только у 5ти одинаковая цена.
и хочет он вывести 2 любых самых дорогих, но именно яблок.
т.е. из 5 сортов по одной и той же цене вывести только 2(первые попавшиеся?).
и то же самое для продукта "груша"
---
уж собрать тестовую табличку с 3 полями и десятком строк наверное дело 5ти минут
26 окт 17, 13:20    [20901943]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20197
o-o
и вот у него есть продукт = яблоко, сортов аж 10, из них только у 5ти одинаковая цена.
и хочет он вывести 2 любых самых дорогих, но именно яблок.
т.е. из 5 сортов по одной и той же цене вывести только 2(первые попавшиеся?).
и то же самое для продукта "груша"

Хуже. Он хочет получить как минимум первые 2 самые дорогие, причём если есть ещё записи, цена в которых равна цене второй записи - получить и их тоже.
26 окт 17, 13:24    [20901963]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
o-o
Guest 		а, ну да, тогда все логично:
2 самых дорогих "яблок" со всеми видами-подвидами.
---
почему бы не сложить свои 'Продукт 1', 'Продукт 2' хотя бы даже в табличную переменную
и прилепить к ним стоки из top 2 with ties через cross apply?
26 окт 17, 13:34    [20902007]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
 		myrzilka, похоже на экзамен на знание старых тем на форуме Недавно такую задачу решали.

SELECT top 1 with ties ROW_NUMBER() OVER (PARTITION BY Продукт ORDER BY Цена DESC) AS N , * 
FROM table1
where Продукт in ('Продукт 1', 'Продукт 2')
order by (row_number() OVER (PARTITION BY Продукт ORDER BY Цена DESC)-1) / 2
26 окт 17, 13:36    [20902018]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
 		ошибся слегка, вот так больше похоже:

SELECT top 1 with ties * 
from table1
where Продукт in ('Продукт 1', 'Продукт 2')
order by (rank() OVER (PARTITION BY Продукт ORDER BY Цена DESC)-1) / 2
26 окт 17, 13:40    [20902038]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
myrzilka
Member

Откуда:
Сообщений: 54
 		Похоже, это то, что нужно! Спасибо большое!
26 окт 17, 13:47    [20902088]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
o-o
Guest 		и что получается в результате?
вот на таких данных получили 3 вида яблок по самой дорогой цене (и где тут топ 2 цен для яблока?)
и 2 вида груш (тут ок, 2 цены вышло)

declare @t table(prod_id int, price int, model int);
insert into @t values 
(1, 40, 1), (1, 40, 2), (1, 40, 3), (1, 20, 1), (1, 30, 1), 
(2, 10, 1), (2, 30, 2), (2, 40, 3), (2, 50, 4), (2, 60, 5),
(3, 10, 1), 
(4, 15, 1)


SELECT top 1 with ties * 
from @t
where prod_id in (1,2)
order by (rank() OVER (PARTITION BY prod_id ORDER BY price DESC)-1) / 2
26 окт 17, 13:59    [20902164]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
o-o
и что получается в результате?
вот на таких данных получили 3 вида яблок по самой дорогой цене (и где тут топ 2 цен для яблока?)
и 2 вида груш (тут ок, 2 цены вышло)

declare @t table(prod_id int, price int, model int);
insert into @t values 
(1, 40, 1), (1, 40, 2), (1, 40, 3), (1, 20, 1), (1, 30, 1), 
(2, 10, 1), (2, 30, 2), (2, 40, 3), (2, 50, 4), (2, 60, 5),
(3, 10, 1), 
(4, 15, 1)


SELECT top 1 with ties * 
from @t
where prod_id in (1,2)
order by (rank() OVER (PARTITION BY prod_id ORDER BY price DESC)-1) / 2

запрос сделан с расчетом на постановку, что нужно получить минимум 2 модели с самыми высокими целями - тогда результат достигнут.
Для вашей постановки (все модели с 2 самыми высокими ценами) нужно использовать dense_rank.
26 окт 17, 14:07    [20902209]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Minamoto
запрос сделан с расчетом на постановку, что нужно получить минимум 2 модели с самыми высокими целями ценами- тогда результат достигнут.
Для вашей постановки (все модели с 2 самыми высокими ценами) нужно использовать dense_rank.
26 окт 17, 14:08    [20902216]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
myrzilka
Member

Откуда:
Сообщений: 54
 		у меня при таком запросе вообще у всех одинаковый rank ставится и выводятся все значения.

Поясню еще раз, что нужно.
Есть яблоки, груши, помидоры... у них могут быть виды - красное, белое, зеленое. У каждого вида своя цена. Яблок и груш n- ное количество.
Надой найти top 2 тех овощей или фруктов, у которых сумма со всеми подвидами наибольшая. Но при этом в результате запросам надо получить такие столбцы

N Фрукт/Овощ Вид Кол-во
1 Яблоки Красное 45
2 Груши Белое 41
3 Яблоки Зеленое 14
4 Помидоры Красное 13

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

Не знаю, может вообще так нельзя сделать...
26 окт 17, 14:10    [20902227]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
myrzilka
у меня при таком запросе вообще у всех одинаковый rank ставится и выводятся все значения.

Поясню еще раз, что нужно.
Есть яблоки, груши, помидоры... у них могут быть виды - красное, белое, зеленое. У каждого вида своя цена. Яблок и груш n- ное количество.
Надой найти top 2 тех овощей или фруктов, у которых сумма со всеми подвидами наибольшая. Но при этом в результате запросам надо получить такие столбцы

N Фрукт/Овощ Вид Кол-во
1 Яблоки Красное 45
2 Груши Белое 41
3 Яблоки Зеленое 14
4 Помидоры Красное 13

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

Не знаю, может вообще так нельзя сделать...

Мда, понятнее не стало. Дайте первоначальное наполнение - приведите код создания и заполнения тестовых таблиц, и определение, что вы понимаете под "суммой с подвидами". А то в запросе у вас только цена фигурирует, а в этом объяснении внезапно количество появляется.
26 окт 17, 14:15    [20902248]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
o-o
Guest 		из его постановки задачи конечно вообще непонятно, что надо.
но слова минимум там точно нет.
меня просто удивила неконсистентность результата в том плане,
что для одного продукта вываливает всего 1 цену, для второго две.

с вашей формулировкой конечно понятнее
26 окт 17, 14:16    [20902253]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
o-o
Guest
myrzilka
Надой найти top 2 тех овощей или фруктов, у которых сумма со всеми подвидами наибольшая.

теперь уже сумма вылезла.
а где же она считается в первом посте?
да вообще по-моему впервые появилась ближе к концу обсуждения...
26 окт 17, 14:18    [20902262]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
myrzilka
Member

Откуда:
Сообщений: 54
 		o-o,
сумма - имеется ввиду "Количество".

Яблоки должны быть в top, потому что в сумме их больше всех - 59, а сейчас выводятся груши в топ.

rank Фрукт/Овощ Вид Кол-во
1 Груши Белое 50
2 Яблоки Красное 45
3 Яблоки Зеленое 14
4 Помидоры Красное 13
26 окт 17, 14:25    [20902305]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
o-o
Guest 		час от часу не легче.
сперва в запросе была цена, потом она ушла и не вернулась.
зато вылезло Количество, а где оно в первоначальном запросе?
автор
сумма - имеется ввиду "Количество".

Яблоки должны быть в top, потому что в сумме их больше всех - 59

так все-таки сумма есть?
или я чего-то не вижу строку с Количество = 59.
зато вижу 45 + 14!!!
26 окт 17, 14:30    [20902343]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
myrzilka
Member

Откуда:
Сообщений: 54
 		o-o,
да, я имею виду именно 45-14 :) (простите за сумбурность).
Вопрос - можно ли как-то присвоить Яблокам одинаковый Rank ?
Чтобы, когда я напишу в итоге вывести значения, у которых Rank >= 2 , то отобралось бы 3 строки - Груши и два Яблока.
26 окт 17, 14:36    [20902393]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
 		myrzilka,

with a as
(
 select
  N, [Фрукт/Овощ], [Вид], [Кол-во],
  sum([Кол-во]) over(partition by [Фрукт/Овощ]) as s
 from
  [Таблица]
),
b as
(
 select
  N, [Фрукт/Овощ], [Вид], [Кол-во],
  dense_rank() over (order by s desc) as r
 from
  a
)
select
 N, [Фрукт/Овощ], [Вид], [Кол-во]
from
 b
where
 r < 3;
26 окт 17, 14:52    [20902490]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
 		myrzilka, это, что ли, хотите?

SELECT table1.* 
from    table1
        inner join (select top 2 [Продукт] from table1 group by [Продукт] order by count([Количество]) desc) as table2
                on table1.[Продукт] = table2.[Продукт]
26 окт 17, 14:54    [20902496]     Ответить | Цитировать Сообщить модератору
 Re: Top 2 with ties (Некорректный результат запроса).Помогите, пожалуйста  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Minamoto
myrzilka, это, что ли, хотите?

SELECT table1.* 
from    table1
        inner join (select top 2 [Продукт] from table1 group by [Продукт] order by count([Количество]) desc) as table2
                on table1.[Продукт] = table2.[Продукт]


SELECT table1.* 
from    table1
        inner join (select top 2 [Продукт] from table1 group by [Продукт] order by sum([Количество]) desc) as table2
                on table1.[Продукт] = table2.[Продукт]
26 окт 17, 14:55    [20902503]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить