PostgreSQL | Чередование значения столбца на 0 или 1 в зависимости от повторяемости значения в соседней ячейке слева или справа?

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

Например когда нужно цветами чередовать записи в HTML-таблице. Но нюанс заключается именно в том, что чередование происходит не между отдельными записями таблицы, а между группами записей под какое-то определённое условие.

Пример запроса в PostgreSQL:

select
   *
   ,case
      when (dense_rank() over (order by id desc)) % 2 = 0
      then 0
      else 1
   end as w
from (select unnest(array[4,4,4,5,5,6,7,7,7,8,9]) as id) as q;

Я использую вместо таблицы выражение «(select unnest(array[4,4,4,5,5,6,7,7,7,8,9]) as id)«, которое создаёт табличный вид со столбцом «id«. Вы можете использовать также результат запроса из таблицы.

Визуальное представление таблицы.

Создали двоичное чередование по группам данных столбца - PostgreSQL — Создали двоичное чередование по группам данных столбца — PostgreSQL

Обратите внимание, как сгруппировались значения столбца «w«.

Группировка с чередованием по признаку повторяемости значения - PostgreSQL — Группировка с чередованием по признаку повторяемости значения — PostgreSQL

Как работает чередование в этом примере?

Так как нам нужно иметь всего два значения, то нас интересует остаток от деления целого числа на 2. В случае если целое кратно двум, то мы получаем ноль. Если целое не кратно двум, то мы получаем единицу.

select 2 % 2; --0
select 4 % 2; --0
select 3 % 2; --1
select 7 % 2; --1

За остаток от деления отвечает оператор, который оформляется в виде процента — «%«.

Как мы получаем корректное значение для чередования?

Мы используем оконную функцию dense_rank(), чтобы расставлять значения для каждой группы записей таблицы. Плюс в том, что dense_rank() постоянно увеличивает значение целого числа ровно на единицу. А это означает, что нечётное чередуется с чётным. И так до конца таблицы (запроса).

А дальше в зависимости от чётности или нечётности в игру вступает остаток от деления.

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Официальный сайт клиента DBeaver для СУБД PostgreSQL — https://dbeaver.io

Оконные функции — https://postgrespro.ru/docs/postgresql/15/functions-window

Агрегатные функции — https://postgrespro.ru/docs/postgresql/15/functions-aggregate