Возвращает определенные строки, каскадные результаты в столбце. Если нет 1, то несколько 2, иначе, если нет 2, то 3, иначе, если нет 3, то несколько 4
Я работаю с данными о студентах в MS SQL и придерживаюсь некоторых очень специфических правил.
Образец таблицы
CREATE TABLE students (
encounterId INT,
studentId INT,
positionId INT
);
INSERT INTO students
VALUES
(100,20,1),
(100,32,2),
(100,14,2),
(101,18,1),
(101,87,2),
(101,78,3),
(102,67,2),
(102,20,2),
(103,33,3),
(103,78,4),
(104,16,1),
(104,18,4),
(105,67,4),
(105,18,4),
(105,20,4);
Правила стола
В таблице показаны встречи студентов, в которых учащиеся занимают позиции между 1 и 4.
Во встрече может быть несколько учеников.
В столкновении может быть только один ученик на позиции 1.
В столкновении может быть только один ученик на позиции 3.
Однако несколько учеников могут занимать позиции 2 и 4 во встрече.
Бизнес правила
Бизнес-правило для каждой встречи выглядит следующим образом:
- Если в встрече есть ученик на позиции 1, верните строку этой встречи (единственное число 1), удалив все позиции 2–4 строк для этой встречи.
- ИНАЧЕ, если нет позиции 1, ТОГДА вернуть строки встречи для студентов (может быть несколько) в позиции 2, удалив любые позиции 3 или 4 для этой встречи
- ИНАЧЕ, если нет позиций 1-2, ТОГДА вернуть строку встречи для студентов в позиции 3, удалив все строки позиции 4 для этой встречи
- ИНАЧЕ, если нет позиций 1-3, ТОГДА вернуть строки встречи для студентов в позиции 4
Не совсем работает
Конкатенация значения studentId приемлема, но не идеальна. У меня есть эта полуработа с шаткой серией союзов и string_aggs. Как я заметил в коде, строки с positionId=3 проблематичны.
Кроме того, эта архитектура union/not like работает в моей небольшой БД разработки, но будет иметь серьезные проблемы с производительностью в производственных БД:
WITH tAll
AS ( SELECT
encounterId,
studentId,
positionId
FROM
students)
SELECT
encounterId,
CAST(studentId AS VARCHAR) AS [studentId],
1 AS [ord]
FROM
tAll
WHERE
positionId = 1
UNION
SELECT
encounterId,
CAST(studentId AS VARCHAR),
2 AS [ord]
FROM
(
SELECT
encounterId,
STRING_AGG(studentId, ',') AS [studentId],
STRING_AGG(positionId, ',') AS [positionId]
FROM
tAll
GROUP BY
encounterId
) t2
WHERE
positionId NOT LIKE '%1%'
AND positionId NOT LIKE '%3%'
AND positionId NOT LIKE '%4%'
UNION
SELECT
encounterId,
CAST(studentId AS VARCHAR),
3 AS [ord]
FROM
--tAll WHERE positionId=3
--Limiting to positionId=3 includes results (101,18,1) AND (101,78,3).. I just want (101,18,1)
--Using the below code instead, but this creates other problems
(
SELECT
encounterId,
STRING_AGG(studentId, ',') AS [studentId],
STRING_AGG(positionId, ',') AS [positionId]
FROM
tAll
GROUP BY
encounterId
) t3
WHERE
positionId NOT LIKE '%1%'
AND positionId NOT LIKE '%2%'
AND positionId NOT LIKE '%4%'
--This excludes 103 entirely since it has both positionId values of 3 AND 4... I just want (103,33,3)
UNION
SELECT
encounterId,
CAST(studentId AS VARCHAR),
4 AS [ord]
FROM
(
SELECT
encounterId,
STRING_AGG(studentId, ',') AS [studentId],
STRING_AGG(positionId, ',') AS [positionId]
FROM
tAll
GROUP BY
encounterId
) t4
WHERE
positionId NOT LIKE '%1%'
AND positionId NOT LIKE '%2%'
AND positionId NOT LIKE '%3%';
Что я хочу вернуть
Ответы 2
Это задача на первое место в группе... с ничьей.
Вы можете использовать оконную функцию для rank() в подзапросе, чтобы ранжировать студентов в каждой встрече, а затем отфильтровать по верхней записи для каждой группы во внешнем запросе:
select *
from (
select s.*,
rank() over(partition by encounterid order by positionid) rn
from students s
) s
where rn = 1
order by encounterid
В другом варианте используется with ties, но вы не можете контролировать порядок строк в наборе результатов:
select top (1) with ties *
from students s
order by rank() over(partition by encounterid order by positionid)
Другим типичным решением является фильтрация с помощью коррелированного подзапроса:
select *
from students s
where positionid = (select min(s1.positionid) from students s1 where s1.encounterid = s.encounterid)
@jtrauma: это будет зависеть от ваших данных, поэтому вам, вероятно, нужно оценить каждое решение в вашей базе данных. Коррелированный подзапрос может использовать индекс students(encouterid, positionid). Другой ответ сканирует таблицу дважды, поэтому я не ожидаю, что он будет быстрее, чем приведенные выше решения (но, опять же, вам нужно будет это оценить).
Спасибо за получение данных для тестирования. Ниже запрос работает нормально.
;with cte_minposition as
(
SELECT encounterId, min(positionid) as min_position FROM students
group by encounterId
)
SELECT * FROM students as s
inner join cte_minposition as m
on s.positionId <= m.min_position and s.encounterId = m.encounterId
Как сравнить производительность вашего решения с решением @GMB? Спасибо!
Спасибо за быстрый ответ!! Как мне сравнить производительность этих опций и опции от @venkataraman R ниже? Планы выполнения, по-видимому, указывают на то, что коррелированный подзапрос имеет меньше элементов обработки и меньше общих оценочных строк для обработки; это победитель с точки зрения производительности?