Одна вещь, которая заставляет меня смотреть со стороны на ORM, как они так стараются скрыть и абстрагировать все силу и выразительность SQL. Прежде чем я напишу дальше, позвольте мне сказать что, Frans Bouma напомнил мне вчера, что есть разница между ORM и людьми, которые их используют. Это всего лишь инструменты (в ORM) и я с этим согласен, так же я согласен, что не плохой фастфуд делает людей полными, а это люди, которые едят его слишком много.

Вместо того чтобы, ругать ORM я хотел бы рассказать вам, почему я не использую ОО абстракцию в основании моей базы данных. Если коротко: потому что SQL может вам сильно помочь выразить значение вашего приложения с точки зрения данных. Единственный способ знать как работает ваше приложение это знать по каким данным оно генерируется.

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

Postgres Built-in Fun

С самого начала в Postgres много синтаксического сахара и с ним действительно очень весело. SQL это ANSI стандартизованные языки – это означает что вы можете рассчитывать на некоторые правила при переходе от одной системы в другую. Postgres следует стандартам почти до буквы, но выходит за рамки с очень забавными дополнениями.

Регулярные выражения

В какой то момент вам возможно придется запустить некоторую цепочку алгоритмов. Многие базы данных включая SQL сервер (извините за ссылку на MSDN) позволяют использовать Regex паттерны через функции или другие некоторые конструкции. С Posters работать одно удовольствие. Простой способ (используя PSQL для старой Takepub базы данных):

select sku,title from products where title ~* 'master';
    sku     |              title
------------+---------------------------------
 aspnet4    | Mastering ASP.NET 4.0
 wp7        | Mastering Windows Phone 7
 hg         | Mastering Mercurial
 linq       | Mastering Linq
 git        | Mastering Git
 ef         | Mastering Entity Framework 4.0
 ag         | Mastering Silverlight 4.0
 jquery     | Mastering jQuery
 csharp4    | Mastering C# 4.0 with Jon Skeet
 nhibernate | Mastering NHibernate 2
(10 rows)

Оператор ~* говорит: что за ним идет шаблон регулярного выражения POSIX (без учета регистра)
Вы можете сделать это с учетом регистра, опуская *.
Регулярные выражения могут вызывать боль в работе, но вы бы могли улучшить этот запрос используя функции для полнотекстового поиска с использованием индексов:

select products.sku,
products.title
from products
where to_tsvector(title) @@ to_tsquery('Mastering');
    sku     |              title
------------+---------------------------------
 aspnet4    | Mastering ASP.NET 4.0
 wp7        | Mastering Windows Phone 7
 hg         | Mastering Mercurial
 linq       | Mastering Linq
 git        | Mastering Git
 ef         | Mastering Entity Framework 4.0
 ag         | Mastering Silverlight 4.0
 jquery     | Mastering jQuery
 csharp4    | Mastering C# 4.0 with Jon Skeet
 nhibernate | Mastering NHibernate 2
(10 rows)

Но это немного сложнее. Postgres имеет тип поля, использующий полнотекстовый поиск tsvector. Вы можете даже иметь эту колонку в таблице, если захотите и это здорово, так как это не спрятано в каком то бинарном индексе где-нибудь. Я конвертировал title налету в tsvector, используя функцию to_tsvector. Она разбивает и подготавливает строку к поиску. Я показываю это через to_tsquery функцию. Этот запрос строиться из термина «Mastering». Биты @@ просто говорят возвратить true, если tsvector поля соответствуют tsquery. Синтаксис немного режет глаз, но работает очень хорошо и быстро. Вы можете использовать concat функцию для объединения строк вместе с дополнительными полями:

select products.sku,
products.title
from products
where to_tsvector(concat(title,' ',description)) @@ to_tsquery('Mastering');
    sku     |              title
------------+---------------------------------
 aspnet4    | Mastering ASP.NET 4.0
 wp7        | Mastering Windows Phone 7
 hg         | Mastering Mercurial
 linq       | Mastering Linq
 git        | Mastering Git
 ef         | Mastering Entity Framework 4.0
 ag         | Mastering Silverlight 4.0
 jquery     | Mastering jQuery
 csharp4    | Mastering C# 4.0 with Jon Skeet
 nhibernate | Mastering NHibernate 2
(10 rows)

Объединение title и description в одну область позволяет вам искать их в то же время, используя все возможности полнотекстового поиска.

Генерация cерий

Есть хорошая функция generate_series, выводящая последовательность, которую вы можете использовать в запросах по разным причинам:

select * from generate_series(1,10);
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10

Если последовательные функции вам не подходят вы можете использовать другие функции типа random():

select * from generate_series(1,10,2)
order by random();
 generate_series
-----------------
               3
               5
               7
               1
               9
(5 rows)

Здесь я добавил дополнительный аргумент, говоря о пропуске значений по 2. Она так же работает с датами:

select * from generate_series(
         '2014-01-01'::timestamp,
         '2014-12-01'::timestamp,
         '42 days');

   generate_series
---------------------
 2014-01-01 00:00:00
 2014-02-12 00:00:00
 2014-03-26 00:00:00
 2014-05-07 00:00:00
 2014-06-18 00:00:00
 2014-07-30 00:00:00
 2014-09-10 00:00:00
 2014-10-22 00:00:00
(8 rows)

Я говорю о датах 2014 года с интервалом в 42 дня. Вы можете сделать это в обратном направлении, просто используя отрицательный интервал. Почему это полезно? Вы можете использовать alias и подключить номера от генерируемых серий, смотря что будете считать:

select x as first_of_the_month
from generate_series('2014-01-01'::timestamp,'2014-12-01'::timestamp,'1 month') as f(x);                                                           first_of_the_month
---------------------
 2014-01-01 00:00:00
 2014-02-01 00:00:00
 2014-03-01 00:00:00
 2014-04-01 00:00:00
 2014-05-01 00:00:00
 2014-06-01 00:00:00
 2014-07-01 00:00:00
 2014-08-01 00:00:00
 2014-09-01 00:00:00
 2014-10-01 00:00:00
 2014-11-01 00:00:00
 2014-12-01 00:00:00
(12 rows)

Alias функции позволяют вам использовать результат строки в соответствии с SQL вызовом.
Такие вещи удобно использовать для аналитики и проверки ваших данных. Кроме того обратите внимание на спецификацию month. Это интервал Postgres — то, что вы будите использовать часто в запросах.

Работа с датами

Интервалы это хорошее сочетание для работы с датами в Postgres. Для примера, если вы сегодня хотите знать дату которая будет через неделю:

select '1 week' + now() as a_week_from_now;
        a_week_from_now
-------------------------------
 2015-03-03 10:08:12.156656+01
(1 row)

Postgres видит now () как timestamp и использует оператор (+) чтобы преобразовать в строку '1 week' как интервал. Результат 12015-05-06 17:59:30.587874 получился интересным.
Это скажет мне текущую дату и время вплоть до миллисекунды. И также таймзону (+1 которая сейчас в Италии ) Если вы когда либо боролись с датами в UTC, то знаете что это большая боль. Postgres имеет встроенный timestamptz тип – данных (представляющий метку с часовым поясом) конвертация будет проходить автоматически когда будет производиться расчет даты.
Для примера я хочу спросить у Postgres какое время в Калифорнии:

SELECT now() AT TIME ZONE 'PDT' as cali_time;
         cali_time
----------------------------
 2015-02-24 02:16:57.884518
(1 row)

Возвращает interval разницы между двумя timesamp. В часа 2 утра лучше не звонить Jon Galloway чтобы сказать, что его SQL сервер горит в огне. Посмотрим как много часов между мной и Джоном:

select now() - now() at time zone 'PDT' as cali_diff;
 cali_diff
-----------
 08:00:00
(1 row)

Обратите внимание, возвращающее значение с отметкой 8 часов, которое не является целым. Почему это важно Время вещь относительная, очень важно знать часовой пояс вашего сервера, когда вы высчитываете данные в зависимости от времени. Для примера в моей Takepub базе я записывал когда были размещены заказы. Если 20 заказов приходили под конец года, моему бухгалтеру хотелось знать, какие заказы пришли раньше или позже 1 Января 2013 года. Мой сервер находиться в Нью Йорке моя компания зарегистрирована на Гавайях.

Эти важные вещи в Postgres: обработчики и многие другие функции для работы с датами довольно приятны.

Агрегация

Работа с накоплением и агрегацией в Postgres может быть утомительной потому что это очень и очень соответствует стандартам. Вы всегда можете быть уверенными в неважности GROUP BY в вашем SELECT выражении. Если вы хотите посмотреть продажи за месяц, сгруппированных за неделю вы нуждаетесь в запуске следующих запросов:

select sku, sum(price), 
date_part('month',created_at) from invoice_items
group by sku,date_part('month',created_at)
having date_part('month',created_at) = 9

Это немного экстремальный синтаксис, пользуйтесь лучше будущем Postgres — оконными функциями:

select distinct sku, sum(price) OVER (PARTITION BY sku)
from invoice_items
where date_part('month',created_at) = 9

Те же данные, но лишнего меньше (оконные функции также доступны на SQL сервере).
Здесь я делаю набор на основе расчетов, указав что я хочу запустить функцию SUM над разделом данных для данной строки. Если не указать DISTINCT здесь запрос выдал бы все продажи как будто мы просто указали SELECT запрос.
Прекрасная возможность использования оконных функция вместе с агрегирующими:

select distinct sku, sum(price) OVER (PARTITION BY sku) as revenue,
count(1) OVER (PARTITION BY sku) as sales_count
from invoice_items
where date_part('month',created_at) = 9

Дает мне количество ежемесячных продаж рассчитанных по полю sku и доходы. Я также могу вывести общий объем продаж в месяц в следующей колонке:

select distinct sku, 
sum(price) OVER (PARTITION BY sku) as revenue,
count(1) OVER (PARTITION BY sku) as sales_count,
sum(price) OVER (PARTITION by 0) as sales_total
from invoice_items
where date_part('month',created_at) = 9

Я использую PARTITTION BY 0, тем самым говоря, что нужно использовать “весь набор раздела” это выведет все продажи в сентябре… И объединим это включение в CTE (a Common Table Expression ) я могу запускать некоторые интересные вычисления:

with september_sales as (
    select distinct sku, 
    sum(price) OVER (PARTITION BY sku) as revenue,
    count(1) OVER (PARTITION BY sku) as sales_count,
    sum(price) OVER (PARTITION by 0) as sales_total
    from invoice_items
    where date_part('month',created_at) = 9
)

select sku, 
    revenue::money, 
    sales_count, 
    sales_total::money,
    trunc((revenue/sales_total * 100),4) as percentage
from september_sales

В финальном select выберем поля revenue и sales_total как тип money – будет красиво отформатировано с символом валюты. Довольно всеобъемлющий запрос продаж – я получаю общий sku, количество продаж и проценты от продаж в месяц, получается довольно простой SQL. Я использую trunc CTE, чтобы округлить до 4-значных цифр, так как результат в процентах может быть достаточно длинными.

Строки

Я показывал вам некоторые прелести над Regex. Но гораздо больше вы можете сделать над строками в Postgres. Рассмотрим запрос, который я использую довольно часто:

select products.sku, 
    products.title, 
    downloads.list_order, 
    downloads.title  as episode
from products
inner join downloads on downloads.product_id = products.id
order by products.sku, downloads.list_order;

Запрос получает все мои видео и индивидуальные эпизоды (так называемые загрузки) я мог бы использовать этот запрос на страницах, которые отображаются пользователю. Но что если вы хотите суммировать эпизоды? Я могу использовать некоторые агрегирующие функции для этого. Простейший пример – строка названия, разделенная запятыми:

select products.sku, 
    products.title, 
    string_agg(downloads.title, ', ') as downloads
from products
inner join downloads on downloads.product_id = products.id
group by products.sku, products.title
order by products.sku

string_agg работает как String.join()  в вашем любимом языке. Но мы можем сделать лучше, объединив через concat а потом уже в массив:

select products.sku, 
    products.title, 
    array_agg(concat(downloads.list_order,') ',downloads.title)) as downloads
from products
inner join downloads on downloads.product_id = products.id
group by products.sku, products.title
order by products.sku

Здесь я использую array_agg вытягивающий данные из list_order и title для объединения загрузок в таблицу и на выходе получается массив.

Я использую concat функцию для объединения list_order.
Если вы используете Node.Js на выходе вы можете сразу пробежаться по нему итератором.
Также используя Node, вы можете использовать JSON:

select products.sku, 
    products.title, 
    json_agg(downloads) as downloads
from products
inner join downloads on downloads.product_id = products.id
group by products.sku, products.title
order by products.sku

Где я показываю отношения загрузочный битов (т.е Дочерних записей) с полями которых я легко могу работать на клиенте с массивом JSON.

Выводы

Если вы знаете SQL не очень хорошо — особенно как ваши любимая СУБД реализует его – воспользуйтесь этой неделей чтобы узнать его лучше. Это очень мощное средство для работы вашего приложения: ваши данные.