Как стать автором
Обновить

Как посчитать всё на свете одним SQL-запросом. Оконные функции PostgreSQL

PostgreSQL *SQL *

Я с удивлением обнаружил, что многие разработчики, даже давно использующие postgresql, не понимают оконные функции, считая их какой-то особой магией для избранных. Ну или в лучшем случае «копипастят» со StackOverflow выражения типа «row_number() OVER ()», не вдаваясь в детали. А ведь оконные функции — полезнейший функционал PostgreSQL.
Попробую по-простому объяснить, как можно их использовать.


Читать дальше →
Всего голосов 75: ↑73 и ↓2 +71
Просмотры 458K
Комментарии 54

Одномерная линейная регрессия, SQL и машинное обучение

SQL *Машинное обучение *
Из песочницы

Всем привет. Это моя первая статья на Хабре, буду рад критике и комментариям.


Статья посвящена простому, но удобному способу построения предикторов особого вида в SQL-подобных языках. Эти предикторы описывают линейный тренд в данных, который можно использовать для решения задач машинного обучения. Идея заключается в том, чтобы по транзакционным данным быстро и эффективно рассчитывать линейные тренды.


Читать дальше →
Всего голосов 17: ↑14 и ↓3 +11
Просмотры 6.2K
Комментарии 0

Проектирование оконных функций, суммирующихся в единицу с заданным уровнем перекрытия

Математика *Звук
Tutorial
Существует ряд задач, в которых длительный по времени сигнал разбивается на сегменты, каждый из которых обрабатывается по отдельности. В частности, такой подход используется для анализа сигнала с помощью оконного преобразования Фурье, или наоборот, при синтезе; а также при спектральной обработке типа удаления шума, изменения темпа, нелинейной фильтрации, сжатии аудиоданных и других.

Сам процесс разбиения математически представляется умножением на некоторую весовую (оконную) функцию со смещением. Для самого простого окна — прямоугольного — это может выглядеть так:

Исходный сигнал:



Разбиения:


Читать дальше →
Всего голосов 28: ↑27 и ↓1 +26
Просмотры 9.2K
Комментарии 21

Нарастающий итог в SQL

SQL *
Tutorial
Нарастающий (накопительный) итог долго считался одним из вызовов SQL. Что удивительно, даже после появления оконных функций он продолжает быть пугалом (во всяком случае, для новичков). Сегодня мы рассмотрим механику 10 самых интересных решений этой задачи – от оконных функций до весьма специфических хаков.
Читать дальше →
Всего голосов 54: ↑52 и ↓2 +50
Просмотры 72K
Комментарии 48

PostgreSQL Antipatterns: CTE x CTE

Блог компании Тензор PostgreSQL *SQL *Администрирование баз данных *
По роду деятельности приходится сталкиваться с ситуациями, когда разработчик пишет запрос и думает "база умная, сама со всем справится!"

В некоторых случаях (частично от незнания возможностей БД, частично от преждевременных оптимизаций) такой подход приводит к появлению «франкенштейнов».
Читать дальше →
Всего голосов 10: ↑9 и ↓1 +8
Просмотры 7.3K
Комментарии 13

SQL HowTo: собираем «цепочки» с помощью window functions

Блог компании Тензор PostgreSQL *SQL *Алгоритмы *
Иногда при анализе данных возникает задача выделения «цепочек» в выборке — то есть упорядоченных последовательностей записей, для каждой из которых выполняется некоторое условие.

Это может быть как условие от данных самой записи, так и сложное выражение относительно одной или нескольких предыдущих записей — например, длина интервала между близкими временными отсчетами.



Традиционные решения предусматривают разные варианты «self join», когда выборка соединяется с собой же, либо использование некоторых фактов «за пределами данных» — например, что записи должны иметь строго определенный шаг (N+1, «за каждый день», ...).

Первый вариант зачастую приводит к квадратичной сложности алгоритма от количества записей, что недопустимо на больших выборках, а второй может легко «развалиться», если каких-то отсчетов в исходных данных вдруг не окажется.

Но эту задачу нам помогут эффективно решить оконные функции в PostgreSQL.
Читать дальше →
Всего голосов 11: ↑11 и ↓0 +11
Просмотры 4.3K
Комментарии 5

Оконные функции с «форточкой» или как пользоваться фреймом

Блог компании OTUS SQL *Microsoft SQL Server *
Всем привет. 26 февраля в OTUS стартовали занятия в новой группе по курсу «MS SQL Server разработчик». В связи с этим я хочу поделиться с вами своей публикацией про оконные функции. Кстати, в ближайшую неделю еще можно записаться в группу ;-).





Оконные функции прочно вошли в нашу практику, но мало кто знает как работают фреймы RANGE и ROWS.

Возможно поэтому они несколько реже встречаются. Цель этой статьи привести примеры использования, чтобы у вас точно не осталось вопросов “Кто есть кто?” и “Как это применять?”. Вопрос “Зачем?” в статье останется не освещенным.

Давайте разберемся что такое фрейм, и как схожего эффекта достичь с помощью ORDER By в предложении OVER().

Для демонстрации будем использовать простую таблицу, чтобы можно было просчитать примеры без использования компилятора. Вообще, очень рекомендую — посмотрите и продумайте, что будет в результате выполнения, а потом проверьте себя — так вы обнаружите белые пятна в восприятии работы оконных функций, которые могут быть совсем не очевидными, когда читаешь уже готовые результаты.
Читать дальше →
Всего голосов 15: ↑15 и ↓0 +15
Просмотры 14K
Комментарии 1

Лучшие вопросы средней сложности по SQL на собеседовании аналитика данных

Блог компании Дата-центр «Миран» PostgreSQL *Программирование *SQL *Карьера в IT-индустрии
Перевод
Первые 70% курса по SQL кажутся довольно простыми. Сложности начинаются на остальных 30%.

С 2015 по 2019 годы я прошёл четыре цикла собеседований на должность аналитика данных и специалиста по анализу данных в более чем десятке компаний. После очередного неудачного интервью в 2017 году — когда я запутался в сложных вопросах по SQL — я начал составлять задачник с вопросами по SQL средней и высокой сложности, чтобы лучше готовиться к собеседованиям. Этот справочник очень пригодился в последнем цикле собеседований 2019 года. За последний год я поделился этим руководством с парой друзей, а благодаря дополнительному свободному времени из-за пандемии отшлифовал его — и составил этот документ.

Есть множество отличных руководств по SQL для начинающих. Мои любимые — это интерактивные курсы Codecademy по SQL и Select Star SQL от Цзы Чон Као. Но в реальности первые 70% из курса SQL довольно просты, а настоящие сложности начинаются в остальных 30%, которые не освещаются в руководствах для начинающих. Так вот, на собеседованиях для аналитиков данных и специалистов по анализу данных в технологических компаниях часто задают вопросы именно по этим 30%.

Удивительно, но я не нашёл исчерпывающего источника по таким вопросам среднего уровня сложности, поэтому составил данное руководство.
Читать дальше →
Всего голосов 29: ↑27 и ↓2 +25
Просмотры 57K
Комментарии 17

Группировки и оконные функции в Oracle

Oracle *SQL *
Привет, Хабр! В компании, где я работаю, часто проходят (за мат извините) митапы. На одном из них выступал мой коллега с докладом об оконных функциях и группировках Oracle. Эта тема показалась мне стоящей того, чтобы сделать о ней пост.



С самого начала хотелось бы уточнить, что в данном случае Oracle представлен как собирательный язык SQL. Группировки и методы их применения подходят ко всему семейству SQL (который понимается здесь как структурированный язык запросов) и применимы ко всем запросам с поправками на синтаксис каждого языка.

Всю необходимую информацию я постараюсь кратко и доступно объяснить в двух частях. Пост скорее будет полезен начинающим разработчикам. Кому интересно — добро пожаловать под кат.
Читать дальше →
Всего голосов 8: ↑5 и ↓3 +2
Просмотры 48K
Комментарии 2

Оконные функции своими руками

Математика *
Tutorial
В цифровой обработке сигналов оконные функции широко используются для ограничения сигнала во времени и их названия хорошо известны всем, кто так или иначе сталкивался с дискретным преобразованием Фурье: Ханна, Хэмминга, Блэкмана, Харриса и прочие. Но являются ли они достаточными, можно ли придумать что-то новое и есть ли в этом смысл?

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


Читать дальше →
Всего голосов 23: ↑23 и ↓0 +23
Просмотры 11K
Комментарии 18

Нарастающий итог в SQL с разрывами в данных

SQL *
Tutorial

Ранее мы рассмотрели способы расчёта нарастающего (накопительного) итога в SQL. Самый распространённый вопрос - как посчитать тоже самое, но на данных с разрывами? После написания исходной статьи мне его задавали неоднократно – так что есть смысл написать об этом отдельно.

Читать далее
Всего голосов 10: ↑10 и ↓0 +10
Просмотры 6.7K
Комментарии 9

7 распространенных ошибок в SQL-запросах, которые делал каждый (почти)

MySQL *PostgreSQL *Программирование *SQL *Microsoft SQL Server *
Tutorial

Сегодня SQL используют уже буквально все на свете: и аналитики, и программисты, и тестировщики, и т.д. Отчасти это связано с тем, что базовые возможности этого языка легко освоить. 

Однако работая с большим количеством junior-ов, мы раз от раза находим в их решениях одни и те же ошибки. Реально — иногда просто создается ощущение, что они копируют друг у друга код. 

Кстати, иногда такая же участь постигает и специалистов более высокого полета. 

Сегодня мы решили собрать 7 таких ошибок в одном месте, чтобы как можно меньше людей их совершали.

Читать далее
Всего голосов 44: ↑38 и ↓6 +32
Просмотры 36K
Комментарии 90

Оконные функции SQL простым языком с примерами

SQL *Администрирование баз данных *Data Engineering *
Recovery mode

Привет всем!

Сразу хочется отметить, что данная статья написана исключительно для людей, начинающих свое путь в изучении SQL и оконных функций. Здесь могут быть не разобраны сложные применения функций и могут не использоваться сложные формулировки определений - все написано максимально простым языком для базового понимания. 

P.S. Если автор что-то не разобрал и не написал, значит он посчитал это не обязательным в рамках этой статьи))) 

Для примеров будем использовать небольшую таблицу, которая показывает оценки учеников по разным предметам. В БД табличка выглядит следующим образом

Читать далее
Всего голосов 64: ↑61 и ↓3 +58
Просмотры 39K
Комментарии 13