• SQL: задача о рабочем времени
    0
    Какое-то ну очень вымученное решение, потерявшее всю легкость, непринужденность и элегантность.

    ...
    with periods(id, start_time, stop_time) as (
        values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
               (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
               (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
               (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
               (5, '2018-06-10 18:46:10'::timestamp, '2018-06-13 10:18:18'::timestamp),
               (6, '2019-04-28 21:00:00'::timestamp, '2019-04-28 21:00:00'::timestamp)
    ),
         holidays (h_date) as (
             values ('2018-01-01'),
                    ('2018-01-02'),
                    ('2018-01-03'),
                    ('2018-01-04'),
                    ('2018-01-05'),
                    ('2018-01-07'),
                    ('2018-01-08'),
                    ('2018-02-23'),
                    ('2018-03-08'),
                    ('2018-05-01'),
                    ('2018-05-09'),
                    ('2018-06-12'),
                    ('2018-11-04'),
                    ('2018-12-31'),
                    ('2019-01-01'),
                    ('2019-01-02'),
                    ('2019-01-03'),
                    ('2019-01-04'),
                    ('2019-01-05'),
                    ('2019-01-07'),
                    ('2019-01-08'),
                    ('2019-02-23'),
                    ('2019-03-08'),
                    ('2019-05-01'),
                    ('2019-05-09'),
                    ('2019-06-12'),
                    ('2019-11-04')
         ),
         subst_days (s_date) as (
             values ('2018-04-28'),
                    ('2018-06-09'),
                    ('2018-12-29')
         ),
         magic as (
             select id,
                    to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                    to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                    case
                        when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                             extract(hours from stop_time) between 10 and 18
                            then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                        when extract(hours from dd) < 10
                            or extract(hours from dd) >= 19 then interval '0 hours'
                        when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                             extract(hours from start_time) between 10 and 18
                            then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                        else interval '1 hour' end               as w_hours
             from periods,
                  generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
             where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
               and not exists(select 0
                              from holidays
                              where h_date::date = dd::date)
             union all
             select id,
                    to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                    to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                    interval '0 hours'
             from periods,
                  generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
             where not exists(select 1
                              from subst_days
                              where dd::date = s_date::date
                                 or extract(dow from dd) between 6 and 7
                                 or exists(select 0
                                           from holidays
                                           where h_date::date = dd::date)
                 ))
    select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
    from magic
    group by id, start_time, stop_time
    order by id;
    

  • SQL: задача о рабочем времени
    0
    Ну и дабы уже закрыть гештальт и перестать себя чувствовать как провинившийся студент…

    finally
    with periods(id, start_time, stop_time) as (
        values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
               (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
               (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
               (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    ),
         holidays (h_date) as (
             values ('2018-01-01'),
                    ('2018-01-02'),
                    ('2018-01-03'),
                    ('2018-01-04'),
                    ('2018-01-05'),
                    ('2018-01-07'),
                    ('2018-01-08'),
                    ('2018-02-23'),
                    ('2018-03-08'),
                    ('2018-05-01'),
                    ('2018-05-09'),
                    ('2018-06-12'),
                    ('2018-11-04'),
                    ('2018-12-31'),
                    ('2019-01-01'),
                    ('2019-01-02'),
                    ('2019-01-03'),
                    ('2019-01-04'),
                    ('2019-01-05'),
                    ('2019-01-07'),
                    ('2019-01-08'),
                    ('2019-02-23'),
                    ('2019-03-08'),
                    ('2019-05-01'),
                    ('2019-05-09'),
                    ('2019-06-12'),
                    ('2019-11-04')
         ),
         subst_days (s_date) as (
             values ('2018-04-28'),
                    ('2018-06-09'),
                    ('2018-12-29')
         ),
         magic as (
             select id,
                    to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                    to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                    case
                        when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                             extract(hours from stop_time) between 10 and 18
                            then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                        when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                             extract(hours from start_time) between 10 and 18
                            then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                        when extract(hours from dd) < 10
                            or extract(hours from dd) >= 19 then interval '0 hours'
                        else interval '1 hour' end               as w_hours
             from periods,
                  generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
             where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
               and not exists(select 0
                              from holidays
                              where h_date::date = dd::date)
         )
    select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
    from magic
    group by id, start_time, stop_time
    order by id;
    

  • SQL: задача о рабочем времени
    0
    В очередной раз понял, нашел и исправил.

    n + 1
    with periods(id, start_time, stop_time) as (
        values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
               (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
               (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
               (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    ),
         holidays (h_date) as (
             values ('2018-01-01'),
                    ('2018-01-02'),
                    ('2018-01-03'),
                    ('2018-01-04'),
                    ('2018-01-05'),
                    ('2018-01-07'),
                    ('2018-01-08'),
                    ('2018-02-23'),
                    ('2018-03-08'),
                    ('2018-05-01'),
                    ('2018-05-09'),
                    ('2018-06-12'),
                    ('2018-11-04'),
                    ('2019-01-01'),
                    ('2019-01-02'),
                    ('2019-01-03'),
                    ('2019-01-04'),
                    ('2019-01-05'),
                    ('2019-01-07'),
                    ('2019-01-08'),
                    ('2019-02-23'),
                    ('2019-03-08'),
                    ('2019-05-01'),
                    ('2019-05-09'),
                    ('2019-06-12'),
                    ('2019-11-04')
         ),
         magic as (
             select id,
                    to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                    to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                    dd,
                    first_value(dd) over (partition by id, start_time, stop_time),
                    last_value(dd) over (partition by id, start_time, stop_time),
                    case
                        when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                             extract(hours from stop_time) between 10 and 18
                            then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                        when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                             extract(hours from start_time) between 10 and 18
                            then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                        else interval '1 hour' end               as w_hours
             from periods,
                  generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
             where extract(dow from dd) between 1 and 5
               and extract(hours from dd) >= 10
               and extract(hours from dd) < 19
               and not exists(select 0 from holidays where h_date::date = dd::date)
         )
    select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
    from magic
    group by id, start_time, stop_time
    order by id;
    

  • SQL: задача о рабочем времени
    0
    Вот так вот должно быть хорошо.

    Просветление достигнуто
    with periods(id, start_time, stop_time) as (
        values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
               (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
               (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
               (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    ),
         holidays (h_date) as (
             values ('2018-01-01'),
                    ('2018-01-02'),
                    ('2018-01-03'),
                    ('2018-01-04'),
                    ('2018-01-05'),
                    ('2018-01-07'),
                    ('2018-01-08'),
                    ('2018-02-23'),
                    ('2018-03-08'),
                    ('2018-05-01'),
                    ('2018-05-09'),
                    ('2018-06-12'),
                    ('2018-11-04'),
                    ('2019-01-01'),
                    ('2019-01-02'),
                    ('2019-01-03'),
                    ('2019-01-04'),
                    ('2019-01-05'),
                    ('2019-01-07'),
                    ('2019-01-08'),
                    ('2019-02-23'),
                    ('2019-03-08'),
                    ('2019-05-01'),
                    ('2019-05-09'),
                    ('2019-06-12'),
                    ('2019-11-04')
         ),
         magic as (
             select id,
                    to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                    to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                    dd,
                    case
                        when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                             extract(hours from stop_time) between 10 and 18
                            then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                        else interval '1 hour' end               as w_hours
             from periods,
                  generate_series(start_time, stop_time, '1 hour') dd
             where extract(dow from dd) between 1 and 5
               and extract(hours from dd) >= 10
               and extract(hours from dd) < 19
               and not exists(select 0 from holidays where h_date::date = dd::date)
         )
    select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
    from magic
    group by id, start_time, stop_time
    order by id;

  • SQL: задача о рабочем времени
    0
    Почему недостаточно?) Результат получен, в логике расхождений с истиной особо не вижу, что не так?
  • SQL: задача о рабочем времени
    0
    Добавил еще шаманства для минут и секунд.

    Еще больше загадочности
    with periods(id, start_time, stop_time) as (
        values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
               (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
               (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
               (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    ),
         holidays (h_date) as (
             values ('2018-01-01'),
                    ('2018-01-02'),
                    ('2018-01-03'),
                    ('2018-01-04'),
                    ('2018-01-05'),
                    ('2018-01-07'),
                    ('2018-01-08'),
                    ('2018-02-23'),
                    ('2018-03-08'),
                    ('2018-05-01'),
                    ('2018-05-09'),
                    ('2018-06-12'),
                    ('2018-11-04'),
                    ('2019-01-01'),
                    ('2019-01-02'),
                    ('2019-01-03'),
                    ('2019-01-04'),
                    ('2019-01-05'),
                    ('2019-01-07'),
                    ('2019-01-08'),
                    ('2019-02-23'),
                    ('2019-03-08'),
                    ('2019-05-01'),
                    ('2019-05-09'),
                    ('2019-06-12'),
                    ('2019-11-04')
         ),
         magic as (
             select id,
                    start_time,
                    stop_time,
                    dd,
                    case
                        when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                             extract(hours from stop_time) between 10 and 19
                            then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                        else interval '1 hour' end as w_hours
             from periods,
                  generate_series(start_time, stop_time, '1 hour') dd
             where extract(dow from dd) between 1 and 5
               and extract(hours from dd) >= 10
               and extract(hours from dd) < 19
               and not exists(select 0 from holidays where h_date::date = dd::date)
         )
    select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
    from magic
    group by id, start_time, stop_time
    order by id;
    

  • SQL: задача о рабочем времени
    0
    Решение в лоб
    with periods(id, start_time, stop_time) as (
        values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
               (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
               (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
               (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
    ),
         holidays (h_date) as (
             values ('2018-01-01'),
                    ('2018-01-02'),
                    ('2018-01-03'),
                    ('2018-01-04'),
                    ('2018-01-05'),
                    ('2018-01-07'),
                    ('2018-01-08'),
                    ('2018-02-23'),
                    ('2018-03-08'),
                    ('2018-05-01'),
                    ('2018-05-09'),
                    ('2018-06-12'),
                    ('2018-11-04'),
                    ('2019-01-01'),
                    ('2019-01-02'),
                    ('2019-01-03'),
                    ('2019-01-04'),
                    ('2019-01-05'),
                    ('2019-01-07'),
                    ('2019-01-08'),
                    ('2019-02-23'),
                    ('2019-03-08'),
                    ('2019-05-01'),
                    ('2019-05-09'),
                    ('2019-06-12'),
                    ('2019-11-04')
         ),
         magic as (
             select id,
                    start_time,
                    stop_time,
                    dd
             from periods,
                  generate_series(start_time, stop_time - interval '1 hour', '1 hour') dd
             where extract(dow from dd) between 1 and 5
               and extract(hours from dd) >= 10
               and extract(hours from dd) < 19
               and not exists(select 0 from holidays where h_date::date = dd::date))
    select id, start_time, stop_time, count(1) as work_hrs
    from magic
    group by id, start_time, stop_time
    order by id
    ;

  • И снова о рекурсивных запросах
    0
    Неплохо, спасибо за статью.
  • Релиз DataGrip 2016.3
    +1
    Прочитал статью и оплатил. Животворящая сила гифок и подробностей о функционале.
  • О влиянии full-page writes
    0
    Попробуйте почитать вот здесь. У меня под рукой имеется только такого рода «общая» информация.
  • DBGlass — Open-Source кросс-платформенный PostgreSQL GUI клиент
    0
    Третий пгадмин вис хотя бы в процессе эксплуатации, ругался и вылетал. Четвертый пошел дальше, если просто оставить его свернутым, а потом развернуть — 7 секунд!!! Я целых 7 секунд могу считать пылинки на белом фоне моего монитора. Это core i7 и 8 гиг RAM.

    К слову, тот же DBeaver просто шедевр на фоне пгадминов. Хоть и со своими тараканами.

    И главная печаль… Всему опэнсорсу не хватает дебага сиквельного кода из коробки((
  • Ограничения (сonstraints) PostgreSQL: exclude, частичный unique, отложенные ограничения и др
    +2
    О. это извечная проблема. Особенно при переводе статей. Назовешь по-русски — теряется смысл, по-английски — выбивается из текста. Получается замкнутый круг из мучений и компромиссов с самим собой.
  • Новый релиз PostgreSQL 9.6: вклад Postgres Professional
    +2
    Спасибо Вам большое за Ваш вклад! Новые фичи просто огонь, особенно для полнотекстового поиска.
  • Как думать на SQL?
    0
    Боженьки мои!
  • PostgreSQL 9.6: Параллелизация последовательного чтения
    +1
    Ваш вариант воспринимается лучше всего, спасибо, прислушался, поправил везде в тексте.
  • PostgreSQL 9.6: Параллелизация последовательного чтения
    +1
    Ох уж эта невнимательность… благодарю, исправлено.
  • Поиск по Postgres с использованием ZomboDb и elasticsearch
    0
    1. Проблема в поиске именно двух подряд идущих слов. Например, Иван Иванов. Из очевидного — искали регуляркой по тексту уже полученных через поиск по вектору данных. Гибкости абсолютно никакой. Необходимо учитывать все вариации окончаний обоих слов, плюс одно дело это формировать руками в базе, и совсем другое — в автоматическом режиме. Да еще и запрос притормаживает ощутимо, так как лайк, или симилар.
    2. Использовали GIN.
  • Пять способов пагинации в Postgres, от базовых до диковинных
    +1
    Да, индекса не было нужного.

    Было:
    (cost=1365076.05..1365161.13 rows=10 width=2501) (actual time=2078.275..2078.763 rows=50 loops=1)
    

    Стало:
    (cost=2589.09..2674.17 rows=10 width=2501) (actual time=59.720..60.222 rows=50 loops=1)
    

    И сам explain:
    Index Scan (cost=2589.09..2674.17 rows=10 width=2501) (actual time=59.720..60.222 rows=50 loops=1)
        ->  Limit  (cost=1294.04..2587.51 rows=50 width=32) (actual time=23.419..59.575 rows=50 loops=1)
              ->  Index Scan (cost=0.56..11409104.75 rows=441025 width=32) (actual time=0.579..59.549 rows=100 loops=1)
        ->  CTE Scan on temp_rows  (cost=0.00..1.00 rows=50 width=16) (actual time=23.423..59.661 rows=50 loops=1)
    Planning time: 0.647 ms
    Execution time: 60.287 ms
    

    О — оптимизация)
  • Пять способов пагинации в Postgres, от базовых до диковинных
    +1
    Там вообще все очень непросто, увы. Но спасибо за совет.
    1. Изменения там регулярные, кроме того, долгие хождения по вектору, тяжелому вектору. С вакуумом и статистикой особых проблем нет. Все регулярно, по мере необходимости. Помимо автовакуума, само собой.
    2. Обязательно проверю еще раз что там с индексами.


  • Пять способов пагинации в Postgres, от базовых до диковинных
    0
    Это проблематично, так как тестировалось на боевом контуре. В таком виде недостаточно?

    Index Scan (cost=1310619.54..1310704.63 rows=10 width=2041) (actual time=1906.651..1907.164 rows=50 loops=1)
      CTE temp_rows
        ->  Limit (cost=1310617.85..1310617.97 rows=50 width=32) (actual time=1906.502..1906.517 rows=50 loops=1)
              ->  Sort (cost=1310542.85..1311220.85 rows=271201 width=32) (actual time=1899.475..1904.427 rows=30050 loops=1)
                    ->  Bitmap Heap Scan (cost=399032.63..1289016.16 rows=271201 width=32) (actual time=821.209..1723.774 rows=218436 loops=1)
                          ->  Bitmap Index Scan (cost=0.00..398964.83 rows=305711 width=0) (actual time=705.200..705.200 rows=320530 loops=1)
      InitPlan 2 (returns $1)
        ->  CTE Scan on temp_rows  (cost=0.00..1.00 rows=50 width=16) (actual time=1906.506..1906.544 rows=50 loops=1)
    Planning time: 0.966 ms
    Execution time: 1911.184 ms
    
  • Пять способов пагинации в Postgres, от базовых до диковинных
    +1
    В общем при различных вариациях одного и того же запроса выигрывает вариант с джоином. With стабильно на втором месте. Но, прошу прощения, запрос с with возвращает неотсортированный список?
  • Пять способов пагинации в Postgres, от базовых до диковинных
    +1
    У меня с более сложных подзапросом внутри WITH, с сортировкой по двум полям результаты следующие:

    Обычный limit-offset:
    cost=725946.11..725946.11 rows=1 width=1147) (actual time=1554.900..1554.915 rows=50 loops=1

    JOIN:
    cost=725954.67..725962.71 rows=1 width=2041) (actual time=1200.102..1201.138 rows=50 loops=1

    WITH:
    cost=1654277.66..1654362.74 rows=10 width=2041) (actual time=2729.149..2729.608 rows=50 loops=1
  • Пять способов пагинации в Postgres, от базовых до диковинных
    +1
    Заранее извиняюсь за некоторую корявость перевода в определенных местах. Увы, не всегда легко найти эквивалент в русском языке некоторым техническим оборотам и словосочетаниям. В целом же, перевод максимально близкий к оригиналу.
  • О полезности индексов по выражениям
    +1
    Реально очень полезный инструмент, как выяснилось. До прочтения оригинала статьи, увы, не знал о такой возможности.
  • Шпаргалка по mongodb: e-commerce, миграция, часто применяемые операции и немного о транзакциях
    0
    Внушительных размеров работа проделана! Спасибо за статью.
  • Postgres NoSQL лучше, чем MongoDB?
    0
    Это не побуждало многочисленных авторов такого рода сравнений к написанию чего-то нового. А я, увы и ах, не обладаю достаточным багажом знаний, дабы уместно и правильно сопоставлять и комментировать сходства и различии вышеупомянутых баз.
  • Postgres NoSQL лучше, чем MongoDB?
    –3
    Для начала надо чтобы актуальная информация существовала. Свежайшей аналитики еще не видел, увы.

    Тем лучше будут видны изменения в более свежей сравнительной характеристике на фоне этой.
  • Postgres NoSQL лучше, чем MongoDB?
    +2
    Версия Монги 3.0 вышла 3 марта 2015. Оригинал статьи опубликован 24 февраля 2015.
  • Postgres NoSQL лучше, чем MongoDB?
    0
    Выбирая статью для перевода я понимал, что все кто постоянно использует Монго и очень хорошо относится к ней, воспримут эту информацию в штыки. Тем не менее, в статье не один раз встречается фраза типа «выбрать решение для своих потребностей». У каждой из вышеупомянутых систем есть свои плюсы, равно как и минусы — это вечный спор. А сам факт использования jsonов в Постгрес не может не радовать и не привлекать, хотя бы в качестве эксперимента. Для сравнения, для саморазвития. Тем более, если такое решение подойдет под потребности проекта, на котором преимущественно используется Постгрес.