SQL - как эффективно выбирать отдельные записи

ProgramBox

SQL — как эффективно выбирать отдельные записи

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

#sql #sql-server #performance #sqlperformance

#sql #sql-сервер #Производительность #производительность sql

Вопрос:

У меня очень чувствительная к производительности база данных SQL Server. Мне нужно сделать эффективный выбор по следующей проблеме:

У меня есть простая таблица с 4 полями:

 ID [int, PK]
UserID [int, FK]
Active [bit]
GroupID [int, FK]

Каждый идентификатор пользователя может отображаться несколько раз с идентификатором группы (и в нескольких идентификаторах групп) с Active=’false’, но только один раз с Active =’true’.

Например:

 (id,userid,active,groupid)
1,2,false,10
2,2,false,10
3,2,false,10
4,2,true,10

Мне нужно выбрать всех отдельных пользователей из таблицы в определенной группе, где должно храниться последнее активное состояние пользователя. Если у пользователя есть активное состояние — оно не должно возвращать неактивное состояние пользователя, если оно было таковым в какой-то момент времени.

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

Есть ли какой-нибудь разумный способ сделать только один выбор, чтобы получить необходимый запрос? Идеи?

Заранее большое спасибо!

1. «последний» и «момент времени» подразумевают некоторое поле даты, но я его не вижу.

2. или идентификаторы монотонно увеличиваются?

3. точно! идентификаторы вводятся автоматически.

4. Вы показываете денормализованный набор данных, в котором идентификатор группы, по-видимому, зависит от идентификатора пользователя. Является ли это точным представлением ваших данных, или идентификатор группы может различаться в разных записях для одного идентификатора пользователя?

5. @Larry — Идентификатор группы не зависит от идентификатора пользователя. Они независимы. Каждое использование может отображаться в нескольких группах, и каждая группа может содержать несколько записей одного и того же пользователя (с другими полями, которые не имеют отношения к вопросу). Данные нормализованы.

Ответ №1:

Как насчет такого представления, как это :

 createview ACTIVE as select * from USERS where Active = TRUE

Тогда будет достаточно только одного выбора из этого представления :

 select user from ACTIVE where ID ....

1. Это не так хорошо — потому что в этой таблице сотни тысяч записей — я бы не хотел получать все активные…

2. Действительно, СОЗДАНИЕ представления займет несколько минут, но оно выполняется только один раз !

3. Если вы не создаете материализованное представление, представление не запускается, пока вы не прочитаете из него данные, а затем оно запускается каждый раз, когда вы считываете из него данные.

4. Действительно, спасибо за точность.

Ответ №2:

Попробуйте это:

 Select
   ug.GroupId,
   ug.UserId,
   max(ug.Active) LastState
from
   UserGroup ug
group by
   ug.GroupId,
   ug.UserId

Если для активного поля установлено значение 1 для комбинации пользователь / группа, вы получите 1, если нет, вы получите 0 для последнего состояния.

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

2. Но если true запись не является «самой последней», вы все равно получите true , даже если пользователь больше не активен в этой группе.

Ответ №3:

Я не большой поклонник использования столбца «isActive» так, как вы это делаете. Для UPDATE изменения активного статуса требуется два s, что приводит к многократному сохранению информации об активном статусе в разных записях.

Вместо этого я бы удалил active поле и выполнил одну из следующих двух вещей:

Если у вас уже есть где-то таблица, в которой (userid, groupid) есть (или может быть) a PRIMARY KEY или UNIQUE INDEX затем добавьте active столбец в эту таблицу. Когда пользователь становится активным или неактивным по отношению к определенной группе, обновите только эту единственную запись с true помощью или false .
Если такая таблица еще не существует, создайте ее с as the активным ПЕРВИЧНЫМ КЛЮЧОМ (userid, groupid) and the field , а затем обработайте таблицу, как указано выше.

В любом случае вам нужно только запросить эту таблицу (без агрегирования), чтобы определить статус пользователей по отношению к конкретной группе. Не менее важно, что true значение or сохраняется только false один раз, и UPDATE для изменения статуса требуется только одно значение. Наконец, эти таблицы служат местом, в котором вы можете хранить другую информацию, относящуюся к членству этого пользователя в этой группе, которая применяется только один раз для каждого членства, а не один раз для изменения статуса.

Ответ №4:

Попробуйте это:

 SELECT t.* FROM tbl t 
INNER JOIN (
    SELECT MAX(id) id
    FROM tbl
    GROUP BY userid 
) m
ON t.id = m.id

Ответ №5:

Не уверен, что я понимаю, что вы хотите, чтобы ваш запрос возвращал, но в любом случае. Этот запрос предоставит вам пользователей в группе, которая активна в последней записи. Он использует row_number(), поэтому вам нужен как минимум SQL Server 2005.

Определение таблицы:

 create table YourTable
(
  ID int identity primary key,
  UserID int,
  Active bit,
  GroupID int
)

Индекс для поддержки запроса:

 create index IX_YourTable_GroupID on YourTable(GroupID) include(UserID, Active)

Пример данных:

 insert into YourTable values
(1, 0, 10),
(1, 0, 10),
(1, 0, 10),
(1, 1, 10),
(2, 0, 10),
(2, 1, 10),
(2, 0, 10),
(3, 1, 10)

Запрос:

 declare @GroupID int = 10

;with C as 
(
  select UserID,
         Active,
         row_number() over(partition by UserID order by ID desc) as rn
  from YourTable as T
  where T.GroupID = @GroupID
)
select UserID
from C
where rn = 1 and
      Active = 1

Результат:

 UserID
-----------
1
3