23.05.2018 10:57
bzq 35 10200 Источник

Здравствуйте, в эфире Радио SQL!


Продолжаем тему популяризации языка SQL среди широких масс IT-населения нашей планеты, на этот раз в русскоязычной его части. Впрочем, жители других планет, тоже подтягивайтесь.


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


В этой статье я собираюсь провести разбор задачи про календарь, которую я давал на Олимпиаде по SQL, про которую я уже писал раньше. Захватывающая рекурсия и загадочные агрегатные функции, вложенные запросы и вооружённые группировки данных — всё это нас ждёт сегодня!


Обращаю внимание, что это именно разбор, а не готовое решение. Чтобы избежать тупого copy-paste, я намерено предприму пару действий, которые позволят получить готовый результат только тем, кто немного поработает головой.


Во-первых, я не буду приводить полный и окончательный код запроса. Чтобы получить финальное решение, придётся осмысленно собрать все части запроса в одно целое. Для человеков это несложно, достаточно задействовать головные ганглии. Также я опущу некоторые громоздкие, но совершенно неинтересные части (типа выравнивания названий месяцев по центру) для доработки желающими напильником по месту самостоятельно. Соответственно, собранный результат без некоторой доводки формально не будет правильным решением исходной задачи. Но меня это не волнует ни капли, так как моя цель — показать как подобные задачи решаются в принципе, а не получить готовый результат в данном частном случае.


Во-вторых, я возьму другой, не оракловый диалект SQL. Конечно любая сколько-нибудь нетривиальная задача требует всяких плюшек, которые в разных вариантах SQL поддерживаются слегка по-разному, вызывая размышления о том, что наша матрица всё же слегка сбоит. Нам существенным образом нужны будут CTE, которые позволяют собирать части запроса с подвыражением WITH ..., да и входные параметры в условии задачи так заданы. Также нам будут нужны рекурсивные запросы или их аналог для генерации последовательностей заранее неизвестной длины, и, наконец, агрегатная функция склейки строк, чтобы собрать всё вместе. С такими мягкими ограничениями на роль SQL-сервера может претендовать любая кофемолка почти что угодно, где в описании встречается аббревиатура SQL. Это и PostgreSQL, и SQLite, и даже MySQL наконец-то стал поддерживать CTE. Коммерческие БД само собой всё это давно умеют.


Поколебавшись некоторое время, я остановил свой выбор на PostgreSQL, чтобы пощупать поближе, как он по сравнению с оракловой базой будет смотреться в этой задаче. Выразить все необходимые шаги решения на другом диалекте SQL не должно быть проблемой, лично я с этим справился быстро. Напомню, что на олимпиаде, откуда взята задача, использовался именно Oracle SQL, на котором и было изначально написано референсное решение. Ну и мне интереснее будет, не всё же только публике развлечения.


Что ж, шутки в сторону, приступим к разбору. Напомню условие.


Задача №1. Календарь

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


with param(year, c, r) (…)

где, соответственно,


  • year – год календаря
  • c – количество столбцов матрицы календаря
  • r – количество строк матрицы.

Месяцы расположены в клетках матрицы календаря по порядку слева направо и потом сверху вниз. Числа в каждом месяце расположены по дням недели, первый день недели в первом столбце и так далее. Начало недели должно соответствовать настройкам локализации базы на момент запуска запроса. Название месяца берётся тоже из настроек локализации и выводится по центру над числами. Между месяцами нужно оставить промежуток, чтобы числа соседних месяцев «не слипались». Самой первой строчкой должен идти выровненный по центру год. Пустых строк быть не должно.


Например, при следующих заданных параметрах:


with param(year, c, r) as (select 2016, 3, 4 from dual)

должен получиться следующий вывод запроса:


                                  2016
         Январь                 Февраль                   Март
              1  2  3     1  2  3  4  5  6  7        1  2  3  4  5  6
  4  5  6  7  8  9 10     8  9 10 11 12 13 14     7  8  9 10 11 12 13
 11 12 13 14 15 16 17    15 16 17 18 19 20 21    14 15 16 17 18 19 20
 18 19 20 21 22 23 24    22 23 24 25 26 27 28    21 22 23 24 25 26 27
 25 26 27 28 29 30 31    29                      28 29 30 31
         Апрель                   Май                     Июнь
              1  2  3                       1           1  2  3  4  5
  4  5  6  7  8  9 10     2  3  4  5  6  7  8     6  7  8  9 10 11 12
 11 12 13 14 15 16 17     9 10 11 12 13 14 15    13 14 15 16 17 18 19
 18 19 20 21 22 23 24    16 17 18 19 20 21 22    20 21 22 23 24 25 26
 25 26 27 28 29 30       23 24 25 26 27 28 29    27 28 29 30
                         30 31
          Июль                   Август                 Сентябрь
              1  2  3     1  2  3  4  5  6  7              1  2  3  4
  4  5  6  7  8  9 10     8  9 10 11 12 13 14     5  6  7  8  9 10 11
 11 12 13 14 15 16 17    15 16 17 18 19 20 21    12 13 14 15 16 17 18
 18 19 20 21 22 23 24    22 23 24 25 26 27 28    19 20 21 22 23 24 25
 25 26 27 28 29 30 31    29 30 31                26 27 28 29 30
        Октябрь                  Ноябрь                 Декабрь
                 1  2        1  2  3  4  5  6              1  2  3  4
  3  4  5  6  7  8  9     7  8  9 10 11 12 13     5  6  7  8  9 10 11
 10 11 12 13 14 15 16    14 15 16 17 18 19 20    12 13 14 15 16 17 18
 17 18 19 20 21 22 23    21 22 23 24 25 26 27    19 20 21 22 23 24 25
 24 25 26 27 28 29 30    28 29 30                26 27 28 29 30 31
 31

Подход к решению


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


Единственное действительно нетривиальное, что тут есть — это представить себе, как можно сгенерировать матрицу календаря, когда ширина и высота задаются параметрами. С высотой всё совсем просто — все диалекты SQL так или иначе позволяют генерировать запросами заданное параметром количество записей. Обычно это рекурсивные запросы, хотя иногда и попадаются специальные конструкции. Например, в том же PostgreSQL нашлась специальная конструкция generate_series(MIN, MAX), которая генерирует серию значений от MIN до MAX. Можно воспользоваться и "классическим" рекурсивным запросом вида:


with recursive seq(n) as (
    select MIN
        union all 
    select n+1 from seq where n<MAX)

, но специальная конструкция будет короче. Так можно получить нужное количество строк.


Теперь определимся с тем, как сгенерировать количество столбцов, заданных параметром. В принципе всё так же, как и со строками выше, можно сгенерировать необходимое количество записей. А потом, когда их надо будет вывести, сгруппируем и склеим эти записи агрегатной функцией для работы со строками. В PostgreQSL для этого нашлась подходящая функция string_agg():


select string_agg(t::text,'-') from generate_series(MIN,MAX) as s(t);

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


xx xx xx xx xx xx xx
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx
xx xx xx xx xx xx xx

Назовём каждую такую конструкцию месяцеместом, так дальше будет удобно ссылаться. По столбцам месяцеместа мы потом будем расставлять числа в соответствии с днями недели. А по строкам в соответствии с номером недели в месяце — сначала первую строчку заполнять, потом вторую и так далее. Столбцов нужно семь по количеству дней недели, строк пусть будет шесть. Больше шести недель в месяце никак не может быть. Я конечно имею в виду земной Григорианский календарь. Просьба жителям других планет отнестись с пониманием, эта задача была придумана в первую очередь для землян XXI века (013 в тентуре, налево от БМ).


Теперь, когда с самым нетривиальным стало всё понятно, займёмся остальными техническими деталями. Нам нужно будет сгенерировать все дни года, чтобы потом их расставить в полученную выше матрицу. Тут закавыка может быть с тем, чтобы правильно определить это количество дней. Например, с учётом того, что год может быть високосный. Или вот в земном Григорианском календаре в 1582 году отсутствуют дни с 05 по 14 октября (и Oracle честно это показывает!). Так что дни нужного года будем получать так: все дни с первого дня года, указанного в параметрах, и до (но не включая) первого дня следующего года.


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


Итого по порядку:


  1. Сгенерировать все дни года.
  2. Сгенерировать матрицу-заготовку с нужным количеством строк и столбцов.
  3. Вписать дни года в матрицу календаря на нужные места.
  4. Собрать всё вместе для вывода результатов.

Реализация


Поехали.


Вот наши исходные параметры для задачи:


with params(year, r, c) as (select 2016, 3, 4)

Генерация всех дней года. Сами дни можно сгенерировать через generate_series(START_DATE, END_DATE), указав началом первый день года и концом предыдущий день от первого дня следующего года. Дальше нам кроме самой даты понадобится из неё получить некоторые полезные данные, которые нам пригодятся: номер дня недели, номер месяца, номер дня в месяце и день недели для первого дня месяца. Мы можем получать эти данные по мере надобности, но это будет громоздко, лучше посчитать сразу. Посмотрев документацию по функциям работы с датами в PostgreSQL, я вижу, что для этого можно воспользоваться функцией extract().


...
days(day, moy, dom, dow, fdow) as
  (select d                                -- day of year (date)
        , extract(month from d)::int-1     -- month, 0-11
        , extract(day from d)::int         -- day of month, 1-31
        , extract(isodow from d)::int-1    -- day of week, 0-6
        , extract(isodow from date_trunc('month', d))::int-1   -- day of week of first day in month, 0-6
    from params p
       , generate_series( (p.year   ||'-01-01')::date
                       , ((p.year+1)||'-01-01')::date - 1, '24:00') as s(d))

Теперь сгенерируем матрицу календаря с нужным количеством месяцев в колонках и строках. Для удобства дальнейшей расстановки дат по местам, в каждом месяцеместе (которое 7х6 клеточек) мы сразу запишем номер месяца, который будем на это место подставлять, и номер позиции дня в месяце по порядку. Понадобится некоторое количество мутноватой целочисленной арифметики с делением нацело и остатками, зато потом расстановка дат будет простой и удобной:


...
matrix(c, r, moy, pos) as
  (select c.c, r.r, c.c/7 + r.r/6*p.c, c.c%7 + r.r%6*7 + 1
     from params p
        , generate_series(0, p.c*7-1) as c(c) -- columns
        , generate_series(0, p.r*6-1) as r(r)) -- rows

Теперь собираем вместе, расставляя дни по местам. Как я и сказал, с учётом предварительной подготовки, это делается на раз:


...
cal (r,c,dom) as
  (select r,c,dom
     from matrix m
   left outer join days d
                on d.moy = m.moy       -- same month
              and d.fdow+d.dom = m.pos  -- position is day no plus weekday of first day
)

Что характерно, если в параметрах задачи матрица календаря по размерам получится больше или меньше 12 месяцев, то всё сработает корректно. Либо заполнятся все месяцеместа и часть останется пустыми, либо лишние месяцы не поместятся, но в обоих случаях матрица не разъедется.


Всё, основная часть сделана. Осталось всё аккуратно собрать вместе в cal_all. Начнём с месяцемест с числами:


...
cal_all (no, line) as
  (-- days in cal matrix
   select r, string_agg(lpad(coalesce(dom::text,' '), 3+case when c%7=0 then 2 else 0 end) ,'' order by c)
     from cal
   group by r
...

Тут склеивается в одну строчку функцией string_agg() всё, что попало в одну строку матрицы календаря. При этом пустые значения дней заменяются на пробелы, и все числа дополняются слева пробелами, чтобы их выровнять. Причём внутри одного месяцеместа на каждое число отводится по 3 знакоместа, а между месяцами (условие c%7=0) — по 5. Это позволяет визуально отделить месяцы друг от друга. Также обращаю внимание на то, что мы сохраняем номер строки. По нему будет определяться правильный порядок в финальном выводе.


Дальше добавляем сюда же названия месяцев. Для этого выбираем из уже сделанного ранее представления days только первые дни месяца, группируем их по params.c штук в строке и склеиваем с помощью string_agg(). Если в матрицу календаря не помещаются все месяцы, то возьмём названия только тех, что помещаются. Не забываем дополнить каждое имя пробелами, чтобы месяцы были над своими месяцеместами, а также дать каждой получившейся строке такой номер, чтобы в финальной сортировке месяцы встали на свои места. То есть первая строчка с месяцами должна встать перед первой строкой с числами, вторая перед седьмой (помните, что мы по шесть строк на месяц отводили?) и так далее. Всё вместе получается так:


...
   union all
   -- month names
   select (moy/p.c)*6-0.5, string_agg(lpad(to_char(day, 'Month'), 7*3+2) , '' order by moy)
     from days d, params p
    where dom = 1  -- first days of months only
      and moy < p.r*p.c
   group by (moy/p.c)

Осталось сверху посередине дописать год:


...
   union all
   select -1, lpad(year::text, (7*3+2)*c/2+length(year::text)/2) from params

Теперь осталось из cal_all выбрать все строки в правильном порядке и отбросить пустые, если таковые найдутся:


...
select line from cal_all
 where trim(line) <> ''
order by no

Это и есть финальная часть нашего запроса.


Что осталось за кадром. Напрашивается сделать представление params2 и вынести в него константы типа "количество знакомест на каждое число месяца" и "количество пробелов между месяцеместами". Потому что если вдруг понадобится их поменять, то придётся выискивать эти числа в коде, который не везде очевиден, что чревато ошибками. Ну и все функции выравнивания я упростил, чтобы не перегружать код. А по условию задачи всё нужно выравнивать по центру.


Выводы


Главный и основной, который я старался донести до аудитории: нет ничего сверхсложного в решении подобных задач.


Что можно сказать про PostgreSQL в сравнении с Oracle по итогам данной задачи. Функционально всё соответствует, выразить можно приблизительно то же и приблизительно так же. Некоторые нюансы удобнее в одном диалекте, некоторые в другом. Функции отличаются, но на то и документация нам дана. Есть ли в чём-то существенная разница? Да, есть. На примере данной задачи я вижу по крайней мере в двух местах.


Во-первых, Oracle поддерживает локали, и в разных локалях неделя может начинаться с разных дней недели. Например, в большей части Европы неделя начинается понедельником, а в США с воскресенья. В PostgreSQL нет настроек локали для первого дня недели и невозможно сгенерировать календарь так, чтобы он начинался с привычного пользователю дня.


Во-вторых, отличается поддержка преобразования дат и работа с календарём. В Oracle за 04 октября 1582 года идёт 15 октября (как и было определено при вводе Григорианского календаря), в PostgreSQL есть 05 и все остальные числа октября 1582 года. Вопрос не так прост, как может показаться сходу, в документации PostgreSQL есть даже специальный раздел, объясняющий проблему и почему в PostgreSQL она решена таким образом. Но факт остаётся фактом: календари в Oracle и PostgreSQL разные, хоть оба и Григорианские, и, соответственно, логика работы с датами существенно отличается. Это может быть важно при портировании.


Если вам понравилось изложение, и у вас есть идеи о чём ещё околоSQLном следует написать, жду вас в комментариях. Также можете ответить на вопрос внизу, поддерживаете ли вы публикацию разбора олимпиадных задач.


На этом сегодня я прощаюсь с нашей аудиторией, stay tuned!..

Комментарии (35)


  1. musicriffstudio
    23.05.2018 15:15
    #11369056

    это пример очень плохой задачи для SQL. Суть реляционных баз данных это быстрая обработка и получения результатов запросов из большого объёма сложноструктурированных данных.

    Например
    выбрать первый 30 пользователей хабра которые больше всего минусовали карму авторам первой сотни самых высокорейтинговых статей с начала года.

    А тут даже не простейшая выборка, а просто форматирование текста. Т.е. вообще из другой области задача.


    1. bzq Автор
      23.05.2018 15:53
      #11369100
      +3

      Ой. Это нездоровый практицизм. Я с таким подходом даже боюсь интересоваться Вашим мнением по поводу школьных задач и упражнений. Они к реальной жизни вообще никак не прикладываются. Все экзотерические языки, коих зачем-то понапридумывали десятки, если не сотни, — тоже почему-то не нацелены на практический результат. Ну и так далее, могу привести ещё примеров. Так вот при изучении новой для себя области знаний нужны задачи, которые не важно насколько востребованы. Они нужны, чтобы понять и усвоить новые знания. Осознать, что новый инструмент может, а чего не может. Молоток во время освоения не помешает попробовать и на зуб. А уж для программистов игры чистого разума важны как ни для какой другой специальности, головные ганглии тренировать.

      А уж о сути реляционных БД я готов с Вами поспорить. Начиная с быстрой обработки (далеко не всегда) сложноструктурированных (зачем же непременно «сложно»?) и до большого объёма. Вот, например, самая распространённая БД на нашей планете — SQLite — отнюдь в большие объёмы не просится, скоростью упирается в железо, на котором работает, сложностью структур обычно тоже не может похвастаться. Но вот удобство работы с данными и хороший запас неубиваемости — и все чайники, микроволновки и пылесосы используют. Ну и смартфоны конечно тоже.


      1. musicriffstudio
        23.05.2018 16:10
        #11369126

        как можно проверить степень владения языком запросов по задачам на форматирование текста?

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


        1. bzq Автор
          23.05.2018 19:01
          #11369492
          +1

          как можно проверить степень владения языком запросов по задачам на форматирование текста?


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

          Для предложенной Вами задачи навыков нужно меньше, а сама задача проще.


          1. musicriffstudio
            23.05.2018 19:13
            #11369524

            именно так и появляются комментарии в духе "скл умер, все переходят на носкл т.к. там форматировать текст проще".


          1. musicriffstudio
            23.05.2018 19:16
            #11369534

            так, используя подручные инструменты и буханку хлеба, можно сделать троллейбус. Но зачем?


          1. akryukov
            24.05.2018 09:14
            #11370062

            как можно проверить степень владения языком запросов по задачам на форматирование текста?
            Нормально так можно, если для вышеозначенной обработки текста нужно владеть навыками построения рекурсивных запросов, группировкой и агрегацией данных, да ещё довольно витиевато совместить это всё несколько раз для получения результата.

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


            1. StrangerInTheKy
              24.05.2018 09:26
              #11370086

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


              1. akryukov
                24.05.2018 09:40
                #11370126

                Это будет задача на бизнес-логику, а не на знание SQL.

                Что от этого меняется? Постановка задачи может потребовать использования всех возможностей SQL для решения.


              1. Danik-ik
                24.05.2018 10:29
                #11370214
                +1

                Это будет задача на бизнес-логику, а не на знание SQL

                А потом мы скажем работодателю: мы тут запросы пишем, причём здесь задачи бизнеса? А он такой: "За прочтение должностной инструкции расписывался, да?"


                Я, конечно, знал всегда, что олимпиадные задачи далеки от практики, но чтобы настолько — не представлял.


                Для меня sql — прежде всего и больше всего система хранения и выборки данных, представленных в виде таблиц. А для внешнего вида есть другие средства.


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


                1. bzq Автор
                  24.05.2018 11:22
                  #11370350
                  -1

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

                  Нет, Вы просто не под тем углом на эту задачу смотрите. Это олимпиадная, то есть в первую очередь учебная задача. Работодатель Вам такой задачи никогда не даст.


                  1. Danik-ik
                    24.05.2018 12:31
                    #11370522

                    Да, я смотрю под тем углом, под которым учебная задача должна не только прокачивать мозг (это не плохо само по себе, но это не для меня уже), но и дать какое-то потенциально полезное умение. Учебная… Чему учебная? Если не рассматривать цель экстремального самовыражения (то есть отбросить голый спорт), то всё, что я вижу здесь — хардкорная смесь бизнес-логики (построение календаря) и представления (форматирование по столбцам) — то есть то, от чего лучше бы отучать.


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


                    1. bzq Автор
                      24.05.2018 13:15
                      #11370602

                      Уважаемые Danik-ik, musicriffstudio, akryukov! Давайте я вам всем вместе отвечу и наконец закрою эту ветку дискуссии, которая относится к практичности данной задачи.

                      Эта задача была придумана и использована на втором заочном туре олимпиады по SQL, статью о которой я публиковал раньше. Там как раз объяснялось почему эти пять задач второго тура делались совершенно преднамеренно в стиле, который должен был вызвать у участников вопросы типа «О ужас, да разве такое вообще на SQL делается?!?» Практичность тут не только не была в более низких приоритетах, она вообще не рассматривалась. Если такой подход вызывает вопросы то надо было там это обсуждать, в той статье.

                      Тут же я дал разбор одной из тех задач. Если есть вопросы по разбору, что-то осталось непонятным, или есть пожелания, что рассмотреть ещё на темы, связанные с SQL, то прошу высказываться. Участвовать в дискуссии о практичности данной задачи, в которую я невольно вступил, я больше не хочу. В контексте того что тут рассматривается только решение, а не создание данной задачи, считаю эту дискуссию неуместной. А то приходят люди со стороны, видят что задача и в самом деле далека от практики, и не разобравшись лепят мне минусы ни за что ни про что, понижая мою мотивацию популяризировать SQL. Предлагаю поставить точку и останоиться. Спасибо.


                      1. musicriffstudio
                        24.05.2018 13:36
                        #11370642
                        -2

                        предлагаю прекратить самомодерирование.


            1. bzq Автор
              24.05.2018 09:56
              #11370168

              Знаете, тут нет никакой проблемы. SQL — это ЯП. Несколько специфический, но ЯП. Это инструмент. Если он подходит для решения задачи, то его можно использовать. Если есть более подходящий инструмент, то надо использовать более подходящий. Все вот эти комментарии вокруг, что SQL подходит для этого и не подходит для того, для меня звучат как анекдот про котов. Просто надо уметь готовить. Нам видите ли SQL не для группировки и агрегации (а для чего же ещё?), а C очевидно не для циклов. Рок против наркотиков, коты против сметаны. (голосом Иа-Иа) Ха-ха.

              Конкретно рассмотренная задача берёт из БД год и размер календаря. Пример такой задачи — просмотр каких-нибудь календарных данных (отпусков, отключений горячей воды, дежурств, доступности каких-то ресурсов) в интерфейсе, который может настраиваться по ширине (десктоп и мобильный клиент или самостоятельно пользователем). Если у Вас будет именно такая задача, и лучше всего её будет решать на сервере, то подобного вида запрос как раз и сможет это делать. Хотя для реальной жизни конечно это слишком заумный запрос, поддерживать будет тяжело.


              1. akryukov
                24.05.2018 10:22
                #11370202

                Вижу, что мне не удалось донести свою мысль вам.


                Все вот эти комментарии вокруг, что SQL подходит для этого и не подходит для того, для меня звучат как анекдот про котов. Просто надо уметь готовить.

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


                Нам видите ли SQL не для группировки и агрегации (а для чего же ещё?)

                Группировка и агрегация — просто приемы, которые мы используем для решения каких-то прикладных задач. Топор нам не для того, чтобы бить по дереву, а придания деревянному бруску желаемой формы. Точно так же и SQL нам нужен не для выполнения запросов, а для извлечения информации из данных.


                Если у Вас будет именно такая задача, и лучше всего её будет решать на сервере, то подобного вида запрос как раз и сможет это делать.

                У SQL есть более распространенные способы применения, нежели "генерация красивой картинки в интерфейсе с динамической шириной". Я считаю, что и олимпиаду надо делать для более распространенных задач.


                1. StrangerInTheKy
                  24.05.2018 10:51
                  #11370260
                  +1

                  Я считаю, что и олимпиаду надо делать для более распространенных задач.
                  Олимпиады в любой области, хоть по физике, хоть по математике, хоть по программированию предлагают нестандартные заковыристые задачи. Но почему-то именно для SQL вы требуете исключения. А в чем олимпиадность тогда?
                  Кроме того, вы явно не работали разработчиком БД. 99,(9)% практических задач настолько банальны, что максимум через год любые запросы можно писать, не включая мозг вообще.
                  Мозг бывает нужен в трех местах:
                  1) на этапе проектирования БД
                  2) заковыристые пожелания заказчика вроде предложенных здесь (раз в год в одном проекте из 10 получается так, что вот в данных конкретных условиях написать условный «календарь» проще всего именно на SQL)
                  3) очень большие базы, где надо писать запрос так, чтобы у него получился хороший план

                  Бывают еще всякие крайности, типа «профакапили этап проектирования». У меня жена как раз в таком стартапе работает, там какая-то хипстота налепила таблицы как бог на душу положит (я ее по SQL консультировал как раз). В итоге связанные по смыслу таблицы, которые по уму должны были бы быть связаны через foreign key (ну типа одна таблица — что купил пользователь, а вторая — сколько заплатил), не связаны вообще никак, из общих данных только ID клиента и дата покупки с округлением чуть ли не до дня. Ну да, можно сделать такую олимпиаду: вот вам через одно место спроектированная БД, сделайте к ней запрос, возвращающий конфетку.


                  1. akryukov
                    24.05.2018 11:06
                    #11370312

                    Олимпиады в любой области, хоть по физике, хоть по математике, хоть по программированию предлагают нестандартные заковыристые задачи.

                    Я только "за" нестандартные заковыристые задачи. Но считаю, что сравнивать SQL и математику — некорректно. Это термины разных уровней абстракции. Вот если бы вы сравнили информатику с математикой или SQL с интегрированием, то я бы согласился.


                    Кроме того, вы явно не работали разработчиком БД. 99,(9)% практических задач настолько банальны, что максимум через год любые запросы можно писать, не включая мозг вообще.

                    В другой ветке комментариев я предлагал постановку достаточно сложной задачи, с которой приходится иметь дело.


                    3) очень большие базы, где надо писать запрос так, чтобы у него получился хороший план

                    Я думаю такая задача тоже достойна включения в олимпиаду. Правда она будет ориентирована на конкретный набор СУБД.


                    Ну да, можно сделать такую олимпиаду: вот вам через одно место спроектированная БД, сделайте к ней запрос, возвращающий конфетку.

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


                    1. bzq Автор
                      24.05.2018 11:27
                      #11370362

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

                      Если уж речь о практике, то нужной задача становится ровно тогда, когда находится кто-то, готовый заплатить за её решение. Других критериев нужности я пока не встречал. (:

                      Было бы неплохо видеть подобные на олимпиадах

                      Кстати, в финале мы давали пару задач как раз на технику оптимизации.


                1. bzq Автор
                  24.05.2018 11:14
                  #11370330

                  Да не, Вашу мысль я понял ещё за долгие годы до сегодняшних дней. Каких только практических задач мне не попадалось в жизни.

                  Точно так же и SQL нам нужен не для выполнения запросов, а для извлечения информации из данных.

                  Ну вот вам бизнес-задача, не раз попадавшаяся в Функциональных дизайнах доработок. Нужно сделать отчёт, оборотную ведомость по месяцам с и по, где даты начала и конца задаёт пользователь. Типа такого:
                            месяц1   месяц2  ...  месяцN
строка1      ХХХ      ХХХ  ...     ХХХ
строка2      ХХХ      ХХХ  ...     ХХХ
...

                  То есть нужно выбрать данные и сформировать отчёт с заранее неизвестным количеством столбцов. Это решается той самой техникой, которая применена в олимпиадной задаче. Не самая распространённая, но встречающаяся в жизни задача.

                  Ну и я не разделяю всей этой суеты c практичностью. Задача придумывалась для всесоюзной олимпиады. Нужно было с её помощью отобрать в финал десятку лучших из потенциальных 20 тысяч участников. Тут нельзя давать задачи уровня «свяжите три таблицы, отсортируйте и покажите первые 30 записей». Это все сделают, кроме клинических непрограммистов. Ну и конечно для олимпиадной задачи быть немножко «because we can» никак не повредит.


        1. StrangerInTheKy
          24.05.2018 09:02
          #11370042

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


      1. rezdm
        23.05.2018 16:20
        #11369142

        >> Я с таким подходом даже боюсь интересоваться Вашим мнением по поводу школьных задач и упражнений

        Вот как раз глубочайшая тема. Мне попадался разбор результатов World pupil rankings in science and maths. Выводы про Россию были весьма плачевными, в некотором роде.
        Если требовалось решить задачку из учебника в виде «вот уравнение, решите его», то российские школьники — впереди планеты всей. Но, как только надо задачу из жизни перевести в уравнение и потом это уравнение решить — стопор.
        Я в некотором роде увидел это на собственном опыте, когда пытался помочь племяннику с его уроками математике в Германии. (Не помню какой класс) возраст — 11-12 лет. Задачки на простую теорию вероятностей, но условия заданы жизненной ситуацией, а не сразу заданным «решить вот это».

        Аналогично, лично для себя — когда я учился в ВУЗе (прикладная математика), то и обратная связь от математики в реальную жизнь была оторвана. Что означает такое решение для реальной жизни? Хоп-ля, неясно.

        Аналогично, я на эту же тему разговаривал с коллегой (phd в физике, из Утрехта) — он использовал какие-то российские материалы. Тоже самое — формулировки статей оторваны от жизни.

        «Любой дифур можно решить через формулу Коши» и на этом объяснения заканчиваются. Ни как дойти до этого дифура, ни как «прочитать» его решение.

        Пару примеров:

        Пример из дисера моего папы. Как промежуточный результат, у него было какое-то время в секундах. Ну время и время. Это просто промежуточное значение. Когда он обсуждал это с коллегами, кто-то заметил, что это время сопоставимо со временем существования вселенной. Папахен нашёл ошибку в расчётах

        У меня был курсач по теории систем: просчитать наиболее эффективный режим ввода лекарства, оптимизация — стабильность температуры больного. Я получил три результата:
        1. Что-то сложное, дозирование
        2. Бахнуть сразу всё — температура там через флуктуации придёт к нормальной
        3. Вообще не давать лекарства — грубо говоря, дать умереть, температура стабилизируется.
        Корректное решение, «в натуре», «в жизни» — только первое.

        Вот эти связи — от жизни к формуле, от формылы к жизни как раз и оторваны.

        Попросите любого старшеклассника подсчитать:
        «Вклад под 3% годовых, вкладываешь 1000р, сколько будет через 10 лет». Всё. Сколько их решит (выведет формулу сначала?) я, в своё время, наблюдал. Единицы. Как сейчас в России — не знаю.


        1. musicriffstudio
          23.05.2018 16:25
          #11369156

          приведённый пример задачи

          выбрать первый 30 пользователей хабра которые больше всего минусовали карму авторам первой сотни самых высокорейтинговых статей с начала года.


          вполне реалистичный. А задача из статьи просто никак не касается заявленной темы. Эта задача ни хорошая, ни плохая. Она просто не про обработку данных.


          1. rezdm
            23.05.2018 17:18
            #11369270

            Я согласен с этим утверждением, мой камент был про более общий вопрос.
            Со своей стороны могу сказать, что я не могу сразу «выдать», что мне видится подходящей задачей для олимпиады по сиквелу вообще (так, чтобы и каждый год разные, т.п.)


            1. akryukov
              24.05.2018 09:13
              #11370060

              Я со своей стороны думал над этим еще с прошлой статьи про эту олимпиаду и пришел к такой задаче


              Существует некий многоэтапный бизнес-процесс.


              • Нужно найти нарушителей процесса
              • Нужно вычислить производительность всех его участников
              • Нужно определить производительность всей системы при новом данном количестве участников

              Бизнес-процесс можно выразить графом. Можно сколько угодно менять его структуру, способ его представления в данных и количество участников процесса.


          1. bzq Автор
            23.05.2018 18:49
            #11369466

            Да какая же это задача. Чему научиться на её примере можно? Это упражнение. Руку набивать. Или её можно дать на собеседовании чтобы посмотреть, как кандидат (а) связывает таблицы, (б) делает групировки и сортировки, (в) как вообще пишет SQL-запросы. Как раз на полчаса-час хватит что пообсуждать, если кандидат сразу не убежит. Теоретически это упражнение должен уметь сделать любой студент, окончивший курс по базам данных, но тут теория с практикой конечно расходятся. А вот сгенерировать динамическую матрицу M на N на SQL сможет очень не кажый даже из тех, кто пишет на SQL на работе за деньги.


            1. musicriffstudio
              23.05.2018 19:01
              #11369490

              нормальная задача. Про базы данных. И реальная.


              А в статье описана задача не про базы данных и практической ценности не имеющая.


              1. bzq Автор
                24.05.2018 11:28
                #11370370

                Вы цели перепутали. Это разбор олимпиадной, то есть учебной задачи.


                1. musicriffstudio
                  24.05.2018 13:01
                  #11370582
                  -1

                  причём неудачной задачи, см. обоснование выше


  1. mspain
    23.05.2018 15:21
    #11369068
    -1

    Про слизь и панцири в тему, учитывая, что на планете Земля давно победили nosql и orm-фреймворки :)


    1. bzq Автор
      23.05.2018 15:57
      #11369112
      +5

      Не, это только среди ухоженно бородатых, которые смузи с вэйпами потягивают. А промышленные решения ещё годы и десятилетия будут использовать SQL, зачем-то отваливая за софт и сервера такое бабло, о котором авторы orm-фреймворков могут только мечтать…

      (тут смайлики по вкусу, а то опять найдутся не понимающий шуток минусаторы)


      1. mspain
        23.05.2018 18:34
        #11369422
        -1

        Я про техническую победу. А это ваше oracle sql за сотни килобаксов нужно лишь чтобы бюджеты осваивать. Что характерно, всем плевать oracle или там hana. Можно ещё туда-сюда помигрировать, чтобы ещё побольше напилить. Это про огого промышленные решения. А стартапы, которые деньги считают давно уже от голого sql ушли. Долго и тормозно.


        1. GlukKazan
          23.05.2018 19:30
          #11369558
          +3

          Обратите внимание на PostgreSQL. С ним бюджеты осваивать ещё удобнее, поскольку за Oracle переплачивать не приходится. А если серьёзно, есть задачи для Hadoop-а, а есть для старых добрых RDBMS (в том числе и в стартапах). Не всем же смузи пить.


  1. StrangerInTheKy
    23.05.2018 16:57
    #11369228

    Годная статья по SQL! Не каждый день месяц встретишь.

    А будут у вас еще олимпиады по SQL? Я бы поучаствовал. Задачу про календарь я как-то решал уже, остальные задачи тоже кажутся вполне по силам. Разве что лабиринт вызывает некоторые опасения.


    1. bzq Автор
      23.05.2018 18:12
      #11369388

      Поймите меня правильно, я не организатор таких олимпиад. Меня поймали и попросили подготовить техническую часть. Задачи придумал, базу подготовил и провёл мероприятие.

      А про олимпиады спрашивайте у гугла, он найдёт кучку. Тот же самый организатор IT-Planet, для которого я готовил задания, делает эти олимпиады ежегодно. Но для студентов и молодых специалистов.