Необычные запросы Postgresql

1. Найти все слова, которые появляются более одного раза в столбце текста.

SELECT word FROM (

SELECT regexp_split_to_table(text_column, E'\\s+') AS word

FROM table_name

) subquery

GROUP BY word

HAVING COUNT(*) > 1;

2. Найти отсутствующие значения в последовательности чисел.

WITH RECURSIVE t(n) AS (

SELECT 1

UNION ALL

SELECT n + 1 FROM t WHERE n < 100

)

SELECT n+1 AS missing

FROM t

WHERE NOT EXISTS (SELECT 1 FROM table_name WHERE id = t.n+1);

3. Найти среднее значение из множества столбцов, игнорируя значения NULL.

SELECT (

SELECT AVG(column_name)

FROM table_name

WHERE column_name IS NOT NULL

) AS avg_value;

4. Создать новый столбец, содержащий индексы сортировки другого столбца.

ALTER TABLE table_name ADD COLUMN sort_index INTEGER;

UPDATE table_name SET sort_index = subquery.row_num FROM (

SELECT id, ROW_NUMBER() OVER (ORDER BY column_name) as row_num

FROM table_name

) subquery WHERE table_name.id = subquery.id;

5. Расшифровать зашифрованные данные в базе данных.

SELECT pgp_sym_decrypt(column_name::bytea, 'encryption_key') as decrypted_value

FROM table_name;

6. Найти все столбцы в таблице, которые содержат даты.

SELECT column_name FROM information_schema.columns

WHERE table_name = 'table_name' AND data_type LIKE '%date%';

7. Проверить, является ли строка в столбце текста валидным JSON-объектом.

SELECT text_column FROM table_name

WHERE json_typeof(text_column::json) = 'object';

8. Вычислить дистанцию Левенштейна между двумя строками в базе данных.

CREATE EXTENSION fuzzystrmatch;

SELECT levenshtein('string1', 'string2');

9. Извлечь все уникальные теги из столбца с HTML-кодом.

SELECT DISTINCT regexp_replace(regexp_split_to_table(html_column, '>|<'), E'\\s+', ' ', 'g') AS tag

FROM table_name

WHERE html_column LIKE '%<%' AND html_column LIKE '%>%' ;

10. Найти распределение значений в столбце с помощью гистограммы.

SELECT width_bucket(column_name, MIN(column_name), MAX(column_name), 10) AS bucket,

COUNT(*) AS count,

CONCAT(ROUND(MIN(column_name), 1), '-', ROUND(MAX(column_name), 1)) AS range

FROM table_name

GROUP BY 1

ORDER BY 1;