Создание случайных пар из столбца

Я не знаю дакдб. Но это вызовет ошибку времени выполнения почти во всех СУБД:

SELECT
  (SELECT protein_upkb FROM proteins ORDER BY x DESC) as p1,
  (SELECT protein_upkb FROM proteins ORDER BY x ASC) as p2,
LIMIT 10

Ну, во-первых, после p2 слишком много запятой. Но этот запрос выбирает одну строку, потому что вы выбираете из ничего (в основном предложении SELECT нет предложения FROM). Эта одна строка имеет два столбца: p1 и p2. Теперь все в порядке, пока два подзапроса возвращают только одну строку, но из вашей таблицы мы знаем, что это не так. Запрос пытается заполнить каждую из двух ячеек в строке результата всеми значениями таблицы. Обычно это приводит к ошибке. Однако я вижу, что DuckDB поддерживает типы данных массива, поэтому я предполагаю, что эти два столбца представляют собой своего рода массивы. (Тогда даже ORDER BY может иметь некоторый эффект, чего не происходит в стандартном SQL, где ORDER BY в подзапросе является излишним, поскольку результаты подзапроса по определению представляют собой неупорядоченные наборы данных.)

Что касается представлений: в SQL все является таблицей :-D Существуют хранимые таблицы, такие как ваша таблица белков. Существуют таблицы результатов как результат всех наших запросов. Существуют результаты подзапроса, которые также являются таблицами. И есть мнения, которые точно такие же. Таблицы представляют собой неупорядоченные наборы данных. Следовательно, ORDER BY в представлении не имеет смысла, поскольку СУБД может его полностью игнорировать.

В любом случае вы перекрестно объединяете два представления, т. е. объединяете каждую строку первого представления с каждой строкой второго представления. То, что вы видите, является частью результата, а именно первой строкой первого представления в сочетании с первыми десятью строками второго представления. (Так что, возможно, СУБД даже приложила немало усилий, чтобы удовлетворить ваши предложения ORDER BY :-) Вы видите только первые 10 результатов, конечно, из-за примененного вами предложения LIMIT.

Вместо этого вы хотите объединить случайные строки с другими случайными строками из той же таблицы. Тогда это зависит от того, сколько случайности вы хотите. Если вы просто хотите получить несколько случайных пар, в которых вы не получаете белка дважды, вы можете сделать:

WITH 
  data AS 
  (
    SELECT protein_upkb, ROW_NUMBER() OVER (ORDER BY random()) AS sortkey 
    FROM proteins 
    ORDER BY random()
  )
SELECT 
  FIRST(protein_upkb ORDER BY sortkey) AS code1,
  LAST(protein_upkb ORDER BY sortkey) AS code2
FROM data
GROUP BY ((sortkey - 1) // 2) -- Integer division 0, 0, 1, 1, 2, 2, etc.
LIMIT 10;

Или, если вас устраивают дубликаты, вы можете просто

SELECT 
  a.protein_upkb AS code1,
  b.protein_upkb AS code2
FROM proteins a CROSS JOIN proteins b
ORDER BY random()
LIMIT 10;

который может дать вам такой результат, как

Есть несколько способов сделать это. Точный способ будет зависеть от ваших дополнительных требований.

Это очень просто: дважды возьмите случайную выборку, объедините их вместе. Вы можете получить один и тот же белок дважды, но можете исключить его с помощью дополнительного пункта where.

• образец()
• позиционное соединение

duckdb.sql("""
  with cte as (
    select protein_upkb from proteins using sample(10)
  )
  select *
  from cte as c1
    positional join cte as c2
""")

┌──────────────┬──────────────┐
│ protein_upkb │ protein_upkb │
│   varchar    │   varchar    │
├──────────────┼──────────────┤
│ A0A0F6TCU1   │ A0A4C1ULV9   │
│ D4YJT4       │ A0A3Q3FTK5   │
│ A0A319DTU8   │ C6LIN2       │
│ A0A1Q3D9X9   │ A0A1B3BCY8   │
│ M5F4R3       │ M1NUZ3       │
│ A0A553PJQ2   │ A0A165P0W9   │
│ G7M9F2       │ A0A182JZX3   │
│ A0A0Q1CIG2   │ G3HMK9       │
│ C7YU85       │ A0A3Q2E7T6   │
│ A0A199VI77   │ A0A0R1JQR6   │
├──────────────┴──────────────┤
│ 10 rows           2 columns │
└─────────────────────────────┘

Или назначьте row_number случайным образом, а затем поверните таким образом, чтобы даже нечетные строки создавали пары:

• row_number()
• поворот

duckdb.sql("""
with cte1 as (
  select ps1.protein_upkb, row_number() over(order by random()) as rn
  from proteins as ps1
), cte2 as (
  select
    protein_upkb,
    rn % 2 as col,
    rn // 2 as r
  from cte1
)
pivot cte2
on col
using any_value(protein_upkb)
limit 10
""")

───────┬────────────┬────────────┐
│   r   │     0      │     1      │
│ int64 │  varchar   │  varchar   │
├───────┼────────────┼────────────┤
│ 66322 │ A0A1N7AVA0 │ A0A175R4H7 │
│ 66325 │ K9FKM7     │ D8QP02     │
│ 66327 │ A0A1I5KRT3 │ W0V524     │
│ 66328 │ A0A4U2YU79 │ A0A452RP46 │
│ 66334 │ A8RCK1     │ A0A165U1L8 │
│ 66335 │ A0A3Q3QVI9 │ C7MCJ1     │
│ 66336 │ Q3SLR9     │ A0A3B4B0Q2 │
│ 66338 │ A0A1W1XBB2 │ A0A0B7J5C1 │
│ 66339 │ A0A1I4KH70 │ A0A3S4SEU1 │
│ 66340 │ A0A1W0D573 │ Q4ZR49     │
├───────┴────────────┴────────────┤
│ 10 rows               3 columns │
└─────────────────────────────────┘