Возвращает определенные строки, каскадные результаты в столбце. Если нет 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%';

Что я хочу вернуть

идентификатор встречи Студенческий билет порядок 100 20 1 101 18 1 102 67 2 102 20 2 103 33 3 104 16 1 105 67 4 105 18 4 105 20 4
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
55
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 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)

Спасибо за быстрый ответ!! Как мне сравнить производительность этих опций и опции от @venkataraman R ниже? Планы выполнения, по-видимому, указывают на то, что коррелированный подзапрос имеет меньше элементов обработки и меньше общих оценочных строк для обработки; это победитель с точки зрения производительности?

jtrauma 15.12.2020 17:10

@jtrauma: это будет зависеть от ваших данных, поэтому вам, вероятно, нужно оценить каждое решение в вашей базе данных. Коррелированный подзапрос может использовать индекс students(encouterid, positionid). Другой ответ сканирует таблицу дважды, поэтому я не ожидаю, что он будет быстрее, чем приведенные выше решения (но, опять же, вам нужно будет это оценить).

GMB 15.12.2020 17:13

Спасибо за получение данных для тестирования. Ниже запрос работает нормально.

;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
идентификатор встречи Студенческий билет positionId идентификатор встречи min_position 100 20 1 100 1 101 18 1 101 1 102 67 2 102 2 102 20 2 102 2 103 33 3 103 3 104 16 1 104 1 105 67 4 105 4 105 18 4 105 4 105 20 4 105 4

Как сравнить производительность вашего решения с решением @GMB? Спасибо!

jtrauma 15.12.2020 17:08

Другие вопросы по теме