При работе над одним проектом возникла необходимость написать некое подобие тестовой системы. Задача формулировалась примерно так:

• из N записей в базе необходимо выбрать m (3-5) случайных строк в серии из k выборок (преимущественно k=2).

А теперь то же самое человеческим языком: из таблицы нужно два раза выбрать по 3-5 случайных записей. При этом не должно быть дубликатов и выборка должна происходить случайным образом.

Первое, что приходит в голову:

 SELECT *
  FROM data_set
  WHERE id NOT IN (1,2,3,4, 5)
  ORDER BY random()
  LIMIT 5;

И это даже будет работать. Вот только цена такого решения…

В дискуссии к предыдущей статье dezconnect поднял интересный вопрос о возможностях OData выполнять рекурсивные запросы (по аналогии с SQL CTE).

В документации OData в разделе 11.2.4.2 описывается опция запроса $expand. Эта опция позволяет получать объекты вместе со связанными объектами. Например, Вы можете получить данные о компании вместе со всеми данными о ее президенте:
https://samples.databoom.space/api1/sampledb/collections/companies(company1)?$expand=president

Без опции $expand результат будет включать только id президента компании (или ссылку при других настройках).
https://samples.databoom.space/api1/sampledb/collections/companies(company1)

В 4й версии OData существенно расширены возможности опции $expand для выполнения рекурсивных запросов.

Как ни странно, чтобы понять рекурсию, в PostgreSQL не надо понимать рекурсию. Потому что WITH RECURSIVE, который присутствует в посгресе (и в других серьёзных базах) — это скорее вычисление чего-то итерациями до того, как будет выполнено некоторое условие.
Тем не менее это очень полезный функционал базы, который можно использовать, например, чтобы вывести все подкатегории заданной категории, если таблица задана в виде (id, parent_id, ...)

К написанию статьи сподвигли часы раздумий и экспериментов в области построения иерархических списков. Изначально логика обкатывалась на SQL запросах, но в последствии решил реализовать на PHP, дабы снять зависимость от СУБД. На простом примере я покажу как можно пройти от корня иерархии до каждого конечного элемента и обратно, информация скорее для новичков.

Итак, тестовая иерархия, с которой нам предстоит работать:



В базе данных имеется самая простая таблица на самом простом MSSQL сервере, тонкости подключения опустим, наша цель — разобраться с иерархией и рекурсией.

Создадим таблицу:

CREATE TABLE [dbo].[Test](
	[uid] [int] IDENTITY(1,1) NOT NULL,  -- уникальное поле, автоинкрементное
	[pid] [int] NULL,                    -- это поле указывает на элемент уровнем выше, содержит uid родителя
	[name] [varchar](255) NULL,
	[access] [varchar](50) NULL,         -- права доступа
) ON [PRIMARY]

Несколько лет назад на форуме SQL.ru решили провести сравнение реализаций трассировщиков лучей на разных языках программирования. К сожалению, моя заявка не может участвовать т.к. она не выводит надпись «PIXAR», поэтому публикую ее здесь.

Для чистоты эксперимента я использовал SQLite без расширений. Оказалось, что там нет даже функции SQRT.

WITH RECURSIVE numbers AS (SELECT 0 AS n UNION ALL SELECT n+1 FROM numbers WHERE n<89),
pixels AS (SELECT rows.n as row, cols.n as col FROM numbers as rows CROSS JOIN
numbers as cols WHERE rows.n > 4 AND rows.n < 38 AND cols.n > 9 AND cols.n < 89),
rawRays AS (SELECT row, col, -0.9049 + col * 0.0065 + row * 0.0057 as x,
-0.1487 + row * -0.0171 as y, 0.6713 + col * 0.0045 + row * -0.0081 as z FROM pixels),
norms AS (SELECT row, col, x, y, z, (1 + x * x + y * y + z * z) / 2 as n FROM rawRays),
rays AS (SELECT row, col, x / n AS x, y / n AS y, z / n AS z FROM norms),
iters AS (SELECT row, col, 0 as it, 0 as v FROM rays UNION ALL
SELECT rays.row, rays.col, it + 1 AS it, v + MAX(ABS(0.7+v*x) - 0.3,
ABS(0.7+v*y) - 0.3, ABS(-1.1+v*z) - 0.3, -((0.7+v*x) * (0.7+v*x) +
(0.7+v*y) * (0.7+v*y) + (-1.1+v*z) * (-1.1+v*z)) * 1.78 + 0.28) AS v
FROM iters JOIN rays ON rays.row = iters.row AND rays.col = iters.col WHERE it < 15),
lastIters AS (SELECT it0.row, it0.col, it0.v AS v0, it1.v AS v1, it2.v AS v2
FROM iters as it0 JOIN iters AS it1 ON it0.col = it1.col AND it0.row = it1.row
JOIN iters AS it2 ON it0.col = it2.col AND it0.row = it2.row
WHERE it0.it = 15 AND it1.it = 14 AND it2.it = 13),
res AS (SELECT col, (v0 - v1) / (v1 - v2) as v FROM lastIters)
SELECT group_concat(
substr('$@B%8&WM#*oahkbdpqwmZO0QLCJUYXzcvunxrjft/|()1{}[]?-_+~<>i!lI;:,"^. ',
round(1 + max(0, min(66, v * 67))), 1) || CASE WHEN col=88 THEN X'0A' ELSE '' END, '')
FROM res;

Здесь можно покрутить кубик

Под катом построчный разбор запроса. Как обычно, достаточно знания основ SQL и школьной математики.