Каков наилучший способ обработки групп внутри групп в T-SQL?

ProgramBox

Каков наилучший способ обработки групп внутри групп в T-SQL?

Post author:admin
Запись опубликована:23 ноября, 2022
Post category:Вопросы по программированию

#sql-server #tsql

Вопрос:

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

Идентификатор маршрута	BookingID	Тип	numberOfPeople	* Флаг
1001	211	Вступительный взнос	2	F
1001	212	Плата за кемпинг	2	T
1002	361	Вступительный взнос	4	T
1003	388	Вступительный взнос	2	F
1003	389	Вступительный взнос	2	F
1003	390	Плата за кемпинг	2	T
1003	391	Плата за кемпинг	2	T
1005	401	Плата за кемпинг	2	T

Последний столбец — это то, что я собираюсь создать, и у меня нет хорошего способа разработать SQL-запрос.

При составлении маршрута посетители оплачивали вступительный взнос и / или плату за кемпинг. Если и кемпинг, и вход оплачены, тогда мы должны посчитать «количество людей» из строки платы за кемпинг (отметка T). Если в маршруте есть только запись или кемпинг, отметьте T

Дальнейшее объяснение:

В системе бронирования были некоторые ошибки, поэтому посетители могут платить только за кемпинг, а не покупать входной билет, например, 1005
Система бронирования имеет возможность совершать групповые покупки и указывать информацию о посетителях отдельно. например, 1003: две пары совершили одну транзакцию, заплатили как за вход, так и за кемпинг

Для идентификатора маршрута 1001 общее количество людей равно 2, для 1003 общее количество людей равно 4. тогда для приведенной выше примерной таблицы для получения общего количества посетителей SUM(case when Type='Camping Fee' then NumberOfPeople else 0 end) OVER (PARTITION BY ItineraryID, Type) должно быть в порядке, просто интересно, есть ли какой-либо другой надежный способ сделать это?

И я застрял на создании столбца флага, реальная таблица содержит более миллиона строк…

1. Если вы можете написать запрос для вычисления последнего столбца, почему бы просто не оставить его как запрос и всегда вычислять его, пока не исправите ошибки? (Вычисление и сохранение в таблице — это неприятность, если что-то обновляется, но может быть полезно для производительности, если данные статичны)

2. Мне кажется, что ваша жизнь станет проще, если вы напишете запрос, который разбивает таблицу на таблицу camping и таблицу ввода (т. Е. подзапросы, ГДЕ type = x), Которые группируются / подсчитываются и т.д..

3. @Caius Jard для более миллиона строк сохранение запроса как представления приведет к снижению производительности. Поэтому необходимо сохранить флаг в таблице. И я не мог понять, как правильно создать этот флаг.

4. Аналогично предложению Caius и в зависимости от того, в чем заключаются ваши проблемы с производительностью, вы можете создать две временные таблицы… одна временная таблица предназначена для хранения сгруппированных записей о плате за кемпинг, а другая — для хранения сгруппированных записей о вступительном взносе. Далее все, что вам нужно будет сделать, это объединить эти две временные таблицы вместе с помощью идентификатора маршрута. На этом этапе каждая таблица будет иметь взаимно однозначное отношение, и с ней будет проще работать, чтобы получить то, что вы хотите. Также вы можете индексировать свои временные таблицы, если это необходимо.

5. @CaiusJard да, извините, у меня свое мышление, следует сказать, что логика BI заключается в создании описательных цифр для панели мониторинга, например. общее количество посетителей по годам / кварталам / месяцам, процентная доля кемпинга, занятого в каждый день / месяц, модель прогнозирования запуска для анализа временных рядов на основе количества посетителей каждый месяцданные за последние 10 лет

Ответ №1:

Рассмотрим это:

 WITH tots AS(
  SELECT 
    itineraryID, 
    SUM(CASE WHEN Type = 'Entry Fee' THEN NumberOfPeople END) as E,
    SUM(CASE WHEN Type = 'Camping Fee' THEN NumberOfPeople END) as C
  FROM
    t
  GROUP BY itineraryID
)

Если мы присоединим это обратно к нашей таблице ( SELECT * FROM t JOIN tots ON t.itineraryID = tots.itineraryID ), то мы сможем использовать значения E и C для каждой строки, чтобы решить некоторые проблемы:

Если E или C равно 0, то отметьте T («Если в маршруте есть только вход или кемпинг, тогда отметьте T»)
Если E = C и это строка Camping, то отметьте «T»
Если E = C и это строка ввода, отметьте ‘F’

После того, как эта логика будет выполнена в ВЫБРАННОМ СЛУЧАЕ, когда вам просто нужно преобразовать ее в UPDATE JOIN, где вы изменяете t ( UPDATE t SET flag = CASE WHEN ... FROM t JOIN tots ... )

Или вы можете создать новую таблицу с результатом выбора (или вы можете просмотреть ее и просто запросить, и каждый раз она будет динамически вычислять T / F)

ПРИМЕЧАНИЕ: В ваших данных примера, похоже, не учитывалось, что произойдет, если будут куплены 2 входа и 4 кемпинга.. Но логику легко расширить

1. Спасибо Caius, я попробую поиграть с вашим примером запроса. Что касается «2 входа и 4 кемпинга», согласованное правило расчета от руководства заключается в подсчете только строки «плата за кемпинг». например, дополните 1003 еще 2 строками «плата за кемпинг», тогда общее количество посетителей равно 8

2. Для этих строк у вас может быть «если это строка ввода и C> E, затем установите флаг CONCAT(‘X’, C — E)», чтобы он получил флаг X2, что означает, что вам нужно расширить строки ввода еще на 2.. Их было бы легко искать с помощью a WHERE flag like как одноразовой операции исправления, или вы можете использовать перекрестный продукт для генерации того же количества строк, что и после X (есть что-то вроде WITH multiplier AS (SELECT * FROM (VALUES ('X1'),('X2'),('X2'),('X3'),('X3'),('X3'))t) — если вы внутренне присоединитесь к этому, это приведет к удвоению всех строк X2, строк X3 в три раза.. из-за повторений в ЗНАЧЕНИЯХ)