Блог

У меня есть таблица SQLite ‘Details’ со структурой:

 ID  Name    Category   
---------------------
1   Matt    0 
2   Shervin 0 
3   Bob     0   
4   Lee     0 
5   Rick    0   
6   Suraya  0 
7   Susan   0   
8   Adam    0   
9   Jon     1   
10  Lorna   1 
... and so on .......  
  

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

 ID  Name  Category  Name1  Name2  Name 3  
----------------------------------------
3   Bob   0         Matt   Lee    Susan 
  

Мою попытку этого можно увидеть ниже, но у нее есть две проблемы:

1. Три дополнительных имени не обязательно всегда разные — похоже, я не могу исключить имя, которое было выбрано ранее, потому что переменные b / c / d не входят в область видимости, кроме их собственной функции объединения.
2. Поскольку каждый вложенный select использует функцию Random (), это не очень эффективно.

Кто-нибудь может предложить другой способ выбрать нужные мне данные (используя базу данных SQLite)? Приветствуется любая помощь / совет — надеюсь, вам ясно, чего я пытаюсь достичь, не стесняйтесь обращаться за любыми разъяснениями.

Моя текущая попытка:

 SELECT a.Id,
       a.Name,
       a.Category,
       COALESCE((SELECT b.Name 
                   FROM Details b 
                  WHERE b.Id NOT IN (a.Id)
                    AND b.Category IN (0)
               ORDER BY Random()
                  LIMIT 1),'') as "Name1",
       COALESCE((SELECT c.Name 
                   FROM Details c 
                  WHERE c.Id NOT IN (a.Id)
                    AND c.Category IN (0)
               ORDER BY Random()
                  LIMIT 1),'') as "Name2",
       COALESCE((SELECT d.Name 
                   FROM Details d
                  WHERE d.Id NOT IN (a.Id)
                    AND d.Category IN (0)
               ORDER BY Random()
                  LIMIT 1),'') as "Name3"
    FROM Details a
     AND a.Category IN (0)
ORDER BY Random()
   LIMIT 1
  

Здесь я согласен с neurino. Вы не сказали, почему вам нужно поместить четыре произвольно выбранных имени в одну строку и почему это должно быть сделано в серверной части.

Если вас беспокоит производительность, генерируйте случайные целые числа в вашем клиенте (диапазон >= min (pkcol) и <= max (pkcol)), пока не найдете четыре отдельные строки (т. Е. сущности / имена). Существует вероятность того, что не существует строки с одним из сгенерированных идентификаторов, но для выяснения этого требуется всего несколько миллисекунд. Используя этот подход со случайным ключом, вы могли бы избежать заказа. Этот подход будет работать быстро даже для таблиц с миллиардами строк.

PS (После того, как выяснилось, что это было приложение для iPhone) Вам нужен один вызов, чтобы получить значения min и max ID (это PK so, который использует индекс). Затем вам понадобится по крайней мере еще один вызов базы данных (опять же, с поддержкой индекса), чтобы получить четыре отдельные строки, используя ваши случайно сгенерированные значения PK [где ID в (a, b, c, d) ] Максимальное количество вызовов неизвестно; сколько будет зависеть от плотности вашей последовательности первичных ключей. Я не думаю, что это было бы чрезмерным объемом операций ввода-вывода, и это было бы значительно менее ресурсоемким, чем порядок с помощью Random(), особенно если в таблице много строк. Вы всегда можете сгенерировать список идентификаторов из 8, 12, 16 идентификаторов случайным образом и попросить вашего клиента отобрать только требуемые 4 строки, если будет возвращено более 4.

P.P.S. Обычно создание экземпляра подключения к базе данных обходится дорого, и вы не хотите делать это в цикле или чаще, чем вам нужно. Но вы можете открыть соединение, выполнить два или три эффективных выбора, которые возвращают по несколько строк каждый, а затем закрыть, если вы закончили с текущей задачей.

Решение с несколькими инструкциями, использующее временную таблицу:

 CREATE TEMP TABLE names
AS
SELECT
  Id,
  Name,
  Category
FROM Details
WHERE Category IN (0)
ORDER BY Random()
LIMIT 4;

SELECT
  MAX(CASE rowid WHEN 1 THEN Id END) AS Id,
  MAX(CASE rowid WHEN 1 THEN Name END) AS Name,
  MAX(CASE rowid WHEN 1 THEN Category END) AS Id,
  MAX(CASE rowid WHEN 2 THEN Name END) AS Name1,
  MAX(CASE rowid WHEN 3 THEN Name END) AS Name2,
  MAX(CASE rowid WHEN 4 THEN Name END) AS Name3
FROM names;

DROP TABLE names;
  

Как насчет выполнения полного внешнего соединения x3, а затем простого выбора строки случайным образом, где имена не равны?

Вы также могли бы достичь желаемого, вложив запросы так, чтобы имена использовались в качестве возвращаемого значения. По сути, сначала вы получаете четвертое значение, а затем третье и так далее. При этом убедитесь, что они не совпадают. Я должен был пройти через поле Id и проверить, что идентификаторы не конфликтуют, а не имена, но этот способ означает уникальные имена.

  SELECT Id
      ,Name
      ,Category
      ,bName
      ,cName
      ,dName 
FROM Details,
    (
        SELECT Name AS bName, cName, dName 
        FROM Details,
            (
                SELECT Name AS cName, dName 
                FROM Details,
                    (
                        SELECT Name AS dName 
                        FROM Details 
                        WHERE Category IN (0) 
                        ORDER BY Random() 
                        LIMIT 1
                    ) td
                WHERE Name <> dName 
                AND Category IN (0) 
                ORDER BY Random() 
                LIMIT 1
            ) tc 
        WHERE Name <> dName 
        AND Name <> cName 
        AND Category IN (0) 
        ORDER BY Random() 
        LIMIT 1
    ) tb
WHERE Name <> dName 
AND Name <> cName 
AND Name <> bName 
AND Category IN (0) 
ORDER BY Random() 
LIMIT 1;
  

Я не вижу способа обойти функцию Random () и медлительность, которую она генерирует, кроме генерации случайных идентификаторов в коде, но у этого есть другие проблемы.

Существует два основных метода оптимизации порядка с помощью инструкции random().

Первый — полностью удалить этап сортировки всей таблицы, но это работает не на всех платформах: limit 1 offset random() , а не order by random() limit 1 .

Другой работает на всех платформах, но требует, чтобы ваши первичные ключи были достаточно плотными (автоматически увеличивающееся целое число без каких-либо удалений, если таковые имеются, гарантирует, что они есть). Предварительно извлеките меньший набор идентификаторов, начинающийся со случайной начальной точки, и используйте их в подзапросе:

 select *
from (select *
      from tbl
      where id between :x and :x   20
      )
order by random()
limit 1